SQL Query vs MS Access (1 Viewer)

[ajs101803]

Here is my query:

SELECT dbo.T_Inspections.Status, dbo.T_Inspections.Atts, dbo.T_Inspections.[Job Name], dbo.T_Inspections.Job_Id, dbo.T_Inspections.Est_Num_Completion, dbo.T_Inspections.[Street Addr], dbo.T_Inspections.City, dbo.T_Inspections.State, dbo.T_Inspections.County, dbo.T_Inspections.Zip, dbo.T_Inspections.Rep, dbo.T_Inspections.Disp, dbo.T_Inspections.Due, dbo.T_Inspections.[Rep Due], dbo.T_Inspections.Start, dbo.T_Inspections.Created, dbo.T_Inspections.Compl, dbo.T_Inspections.Submt, dbo.T_Inspections.Client, dbo.T_Inspections.Form, dbo.T_Inspections.Job_Auto_ID, dbo.T_Receipts.Amount, dbo.T_Receipts.Deduction, dbo.T_Inspections.File_Source, dbo.T_Payments.Amt
FROM (dbo.T_Inspections LEFT JOIN dbo.T_Receipts ON dbo.T_Inspections.Job_Id = dbo.T_Receipts.Invoice_Number) LEFT JOIN dbo.T_Payments ON dbo.T_Inspections.Job_Id = dbo.T_Payments.Job_ID;

What is strange is that the same query in MS Access will return Job Name values while SQL has Nulls. I can tell you that the person who created this use an MS Access Select Query which takes some time to load on the form. I am using Pass Through Query which takes seconds.

Any Ideas

 

[GPGeorge]

Are you sure the data is the same in both environments? Are you talking about an ODBC-linked table in Access returning different values for the same set of data? Thanks, for providing clarity.

 

[ajs101803]

Are you sure the data is the same in both environments? Are you talking about an ODBC-linked table in Access returning different values for the same set of data? Thanks, for providing clarity.

Yes the ODBC passthrough shows different data than the MS Access SQL which takes a long time. MS Access uses a linked table while the pass through goes straight to the tables. This is the MS Access query.

 

[theDBguy]

Hi. Welcome to AWF!

Are you saying if you run the same query in SSMS, you get values but get empty when ran in Access?

 

[ajs101803]

Hi. Welcome to AWF!

Are you saying if you run the same query in SSMS, you get values but get empty when ran in Access?

Correct! Empty Values in SSMS but not using MS Access SQL Query .

 

[Gasman]

Hi. Welcome to AWF!

Are you saying if you run the same query in SSMS, you get values but get empty when ran in Access?

The other way around I thought?

 

[theDBguy]

Correct!

I wonder if it's a permission issue.

 

[ajs101803]

The other way around I thought?

Yes just corrected that.

 

[theDBguy]

Yes just corrected that.

Oh, I guess I responded to the wrong info then.

 

[ajs101803]

I wonder if it's a permission issue.

No I don't see that as an issue. This query has left joins and the connection to the Receipts table is made in Access but I wonder if SQL cannot correctly make that join.

 

[Josef P.]

T-SQL does not need the brackets, but I don't think that's the problem.

   ...
FROM
   dbo.T_Inspections
   LEFT JOIN
   dbo.T_Receipts ON dbo.T_Inspections.Job_Id = dbo.T_Receipts.Invoice_Number
   LEFT JOIN
   dbo.T_Payments ON dbo.T_Inspections.Job_Id = dbo.T_Payments.Job_ID

Is the database or individual tables/data fields possibly set to case sensitive (CS vs CI)? ... However, this is only relevant for string comparisons.

BTW:
Are these tables or views? The primary keys looked a bit strange, as if they had only been set in Access.
Noticeable: PK of Payments is Job_ID? And Job_ID corresponds to Invoice_Number in T_Recipients.

 

[GPGeorge]

No I don't see that as an issue. This query has left joins and the connection to the Receipts table is made in Access but I wonder if SQL cannot correctly make that join.

Not likely, SQL Server tends to be more flexible, not less. But that's getting off track a bit.

Try this please.

Navigate to the SQL converter tool on the AUG website.

Copy the SQL from the "working" Access query. Use the converter tool to render it as SQL Server SQL. You'll have to correct the schema/table naming convention because Access changed DBO.XXX to DBO_XXX.

Now take that SQL Server SQL back into you passthrough and try again. Does it still return the same problems?

Thanks for doing some trouble-shooting on your data.

 

[ajs101803]

GPGeorge,

I followed your instructions and have the same result.

 

[GPGeorge]

That leaves us at an impasse. I don't think it's possible to analyze further based only on the single SQL statement provided.

One last thing to consider. Are you sure the ODBC-linked tables are in the same SQL Server database as the tables in the passthru? In other words, are you looking at a Development database and a Production database, by any chance? Can we rule out differences in data?

 

[ajs101803]

That leaves us at an impasse. I don't think it's possible to analyze further based only on the single SQL statement provided.

One last thing to consider. Are you sure the ODBC-linked tables are in the same SQL Server database as the tables in the passthru? In other words, are you looking at a Development database and a Production database, by any chance? Can we rule out differences in data?

Yes the linked tables are the same. Something with the Access query will pull the data that the SQL pass through cannot.

 

[ajs101803]

Got it! For some reason Null values were affecting the Job Name field. Now it populates correctly! Access must only filter on non nulls in a left join.

I added this and it works fine.

WHERE dbo.T_Inspections.[Job Name] IS NOT NULL

 

[GPGeorge]

Oh, you hadn't included the fact that only SOME fields were not returned. The way the original question was phrased was a bit misleading.

"the same query in MS Access will return Job Name values while SQL has Nulls. " That part of the original post seemed to imply your returned recordset only had Nulls in that field.

But now your Access SQL, without a WHERE clause, should return different numbers of records from the SQL Server SQL, with A WHERE clause.

Is that not the case? Does this modified Passthru actually return the same records, and the same number of records?

In any event, I'm surprised that this was, in fact, what was happening anyway.

However, if you are happy that the results are complete and accurate, you're good.

 

[ajs101803]

Oh, you hadn't included the fact that only SOME fields were not returned. The way the original question was phrased was a bit misleading.

"the same query in MS Access will return Job Name values while SQL has Nulls. " That part of the original post seemed to imply your returned recordset only had Nulls in that field.

But now your Access SQL, without a WHERE clause, should return different numbers of records from the SQL Server SQL, with A WHERE clause.

Is that not the case? Does this modified Passthru actually return the same records, and the same number of records?

In any event, I'm surprised that this was, in fact, what was happening anyway.

However, if you are happy that the results are complete and accurate, you're good.

I'll put it this way, the MS Access query had some nulls there but when I did a check setting the same field to not null both queries match in the number of records. The original developer did not for some reason understand this. At over 500K records this may be why I saw all of those null and as there was no sort order I can only surmise why I was observing the nulls populated first and why the MS Query didn't do that. I am good and thanks.

 

[Pat Hartman]

Got it! For some reason Null values were affecting the Job Name field. Now it populates correctly! Access must only filter on non nulls in a left join.

I added this and it works fine.

WHERE dbo.T_Inspections.[Job Name] IS NOT NULL

That isn't it at all. What you are seeing is the difference in the way that each database engine retrieves the data. To prove this to yourself- Go back to your original queries. Add an order by [Job Name] to each. That should pop the null values to the top of the list in your Access query as well.

If this WHERE clause makes sense, try it with the Access query to reduce the number of rows returned. It may speed up enough to be useful.

Why is Job_ID named Invoice_Number in one of the tables? That is really poor design and has to be confusing. I would think there would be multiple invoices for a job so this makes no sense at all.