Access My A**

March 31, 2008 10:35 AM

Why isn't a query in Access returning the correct data compared to the equivalent query in SQL Server. More inside.

Here's the setup:
Access front-end to SQL Server 2005 database. I have this pretty standard inner join query in Access. Something like this (please excuse the ridiculous column names):

SELECT
Attendance.[Course Number],
Attendance.[Customer Number],
Attendance.[Amount Paid], Attendance.[Shipping Paid], Attendance.Paid,
Attendance.OrderId,
Courses.[Course Name],
Courses.[Start Date],
Courses.City,
Courses.State,
Courses.IsCancelled
FROM Attendance
INNER JOIN Courses ON Courses.[Course Number] = Attendance.[Course Number]
WHERE Attendance.[Customer Number] = [CustomerId?];

When run on the SQL Server via SQL Management Studio, this returns the correct records as expected. When run from Access, the correct number of rows are returned, but the Attendance.OrderId is not showing correctly in the Access results. This is presenting a problem as there is a form in access that is using this query as the source of its data.

Here are the results for SQL:
Course Number,Customer Number,Amount Paid,Shipping Paid,Paid,OrderId,Course Name,Start Date,City,State,IsCancelled
PT101,44799,1.00,NULL,NULL,12345,Product Name 1,NULL,NULL,NULL,0
8800,44799,1.00,NULL,NULL,2,Product Name 2,NULL,online,NULL,0
8800,44799,NULL,NULL,NULL,2,Product Name 2,NULL,online,NULL,0
PT101,44799,200.00,NULL,NULL,3,Product Name 1,NULL,NULL,NULL,0
PT101,44799,75.00,NULL,NULL,12345,Product Name 1,NULL,NULL,NULL,0
PT101,44799,75.00,NULL,NULL,54231,Product Name 1,NULL,NULL,NULL,0
PT101,44799,0.00,NULL,NULL,0,Product Name 1,NULL,NULL,NULL,0

Here are the results for Access (note the weirdness fo the OrderId Field:

Course Number,Customer Number,Amount Paid,Shipping Paid,Paid,OrderId,Course Name,Start Date,City,State,IsCancelled
PT101,44799,1,,,12345,Product 1,,,,0
8800,44799,1,,,2,Product 2,,online,,0
8800,44799,1,,,2,Product 2,,online,,0
PT101,44799,1,,,12345,Product 1,,,,0
PT101,44799,1,,,12345,Product 1,,,,0
PT101,44799,1,,,12345,Product 1,,,,0
PT101,44799,1,,,12345,Product 1,,,,0

What is the deal here? Bug in MDAC? My own idiocy (hopefully)? Any input is greatly appreciated. The client is running Office 2003 SP3, MDAC version is 2.8 on XP Pro SP2.

posted by zackola to Technology (10 answers total)

Add ORDER BY Attendance.OrderId after the WHERE?
posted by unixrat at 10:39 AM on March 31, 2008

Me no type good.

If you're depending upon the order of some items, you can order them by inserting an ORDER BY clause into your SQL. (As in my first reply.)
posted by unixrat at 10:40 AM on March 31, 2008

Response by poster: I am not depending on the order of the items. In the Access results, the Order Id = 12345 for al PT101 items. in the real results (run from SQL Server) the Order Id column is 12345 for the first PT101, 3 for the second, 12345 for the third, 54321 for the fourth and 0 for the fifth.

Order Id has nothing to do with sequence ordering. I don't want ORDER BY here. The resultsets for the same query are not matching. Make more sense?
posted by zackola at 10:46 AM on March 31, 2008

Response by poster: I can't get the incorrect results by modifying the join type. I tried LEFT OUTER and FULL OUTER. Are there other types I should attempt?
posted by zackola at 10:49 AM on March 31, 2008

Best answer: Seems you have more issues there than just the the OrderID. Your Acess recordset is returning just one record for each instance of coursenum it finds. What accounts for the difference in [Course Name]? Is the Access running local on one table and linked to SQL on the other? This may be the problem.
posted by pivo at 10:51 AM on March 31, 2008

Best answer: The resultsets for the same query are not matching. Make more sense?

Kinda, but you may be getting stung by a side-effect of SQL.

There is no guaranteed results ordering when none is specified. I don't see any unique IDs in there, so I don't know how you can tell that the numbers aren't matching when you could be comparing completely different records.

Your final four results are different too. I don't think you're comparing apples to apples here.
posted by unixrat at 10:54 AM on March 31, 2008

I'm a little bit vague on some key details (where are the different tables physically that are being used?), but assuming the tables you're using are physically on SQL and you're linking to them with Access as a front end, you need to make sure that all your Access links to tables are current (I'd go ahead and re-link every table using the same names), and then make sure you're not using imported tables thinking they are linked. The latter means they are, in fact, keeping bogus, out of date data in the Access file by mistake.

This is unless I'm wrong about the different roles of Access and SQL you have in mind.
posted by croutonsupafreak at 12:19 PM on March 31, 2008

Response by poster: Croutons, you are correct. I've tried relinking the tables.

To simplify things a bit, I tried a basic query with just

SELECT * FROM Attendance where [Customer Number] = x

Once again, in Access and SQL the results were different sets of data.

Sticking a DISTINCT in there seems to have cured it, but I'm still not 100% sure why.
posted by zackola at 12:23 PM on March 31, 2008

Best answer: If distinct cures it, that usually means you have a Cartesian of some kind. Access will treat a Cartesian differently than SQL fairly often.

I'm still a little bit confused by that. The select * from attendance where [customer number] = x should get you the same thing on both but in different order.

Is Attendance a view? If not, what is the primary key?
posted by croutonsupafreak at 12:36 PM on March 31, 2008

Response by poster: Primary Key was NOT defined. Ug. Should have looked for that initially. Thanks for the help. This is what I get for trying to be helpful on a 6 year old Access application. :\
posted by zackola at 10:00 AM on April 1, 2008