Pass Through query displays dups even with Distinct and/or GroupBy?

[ordendelfai]

I have a query that I have tried to run three ways using a passthrough query from Access 97 to a SQL2k server. When I run either of the queries in a standard Access query, the results have no dups, but when I run them in a passthrough, I get dups. I cannot figure out why this would happen. Any suggestions?

SELECT tbl_Cmpny_Consultants.PHID, tbl_Cmpny_Consultants.DoNotDisplay, tbl_Agency_Master.MasterID, tbl_Agency_Master.Master_FirmName
FROM ((tbl_Consultant_Data LEFT JOIN tbl_Agency_Codes ON tbl_Consultant_Data.[Agency_ID#] = tbl_Agency_Codes.[AgencyID#]) RIGHT JOIN tbl_Cmpny_Consultants ON tbl_Consultant_Data.[ConsultantID#] = tbl_Cmpny_Consultants.[ConsultantID#]) LEFT JOIN tbl_Agency_Master ON tbl_Agency_Codes.MasterID = tbl_Agency_Master.MasterID
GROUP BY tbl_Cmpny_Consultants.PHID, tbl_Cmpny_Consultants.DoNotDisplay, tbl_Agency_Master.MasterID, tbl_Agency_Master.Master_FirmName;

SELECT DISTINCT tbl_Cmpny_Consultants.PHID, tbl_Cmpny_Consultants.DoNotDisplay, tbl_Agency_Master.MasterID, tbl_Agency_Master.Master_FirmName
FROM ((tbl_Consultant_Data LEFT JOIN tbl_Agency_Codes ON tbl_Consultant_Data.[Agency_ID#] = tbl_Agency_Codes.[AgencyID#]) RIGHT JOIN tbl_Cmpny_Consultants ON tbl_Consultant_Data.[ConsultantID#] = tbl_Cmpny_Consultants.[ConsultantID#]) LEFT JOIN tbl_Agency_Master ON tbl_Agency_Codes.MasterID = tbl_Agency_Master.MasterID
GROUP BY tbl_Cmpny_Consultants.PHID, tbl_Cmpny_Consultants.DoNotDisplay, tbl_Agency_Master.MasterID, tbl_Agency_Master.Master_FirmName;

SELECT DISTINCT tbl_Cmpny_Consultants.PHID, tbl_Cmpny_Consultants.DoNotDisplay, tbl_Agency_Master.MasterID, tbl_Agency_Master.Master_FirmName
FROM ((tbl_Consultant_Data LEFT JOIN tbl_Agency_Codes ON tbl_Consultant_Data.[Agency_ID#] = tbl_Agency_Codes.[AgencyID#]) RIGHT JOIN tbl_Cmpny_Consultants ON tbl_Consultant_Data.[ConsultantID#] = tbl_Cmpny_Consultants.[ConsultantID#]) LEFT JOIN tbl_Agency_Master ON tbl_Agency_Codes.MasterID = tbl_Agency_Master.MasterID;

 

[dhookom]

Is DoNotDisplay a yes/no or Bit field?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ordendelfai]

Access indicates it to be a yes/no field, however in enterprise manager, it is a bit field. All the tables are linked to the SQL server. Will this affect the pass-through query?

 

[dhookom]

If your SQL statements are true pass-through queries then the YesNo/Bit fields may be the issue in the group by. I can't see your actual data or results so I can't tell.

What happens if you remove the DoNotDisplay column? What happens if you Convert() the column to a varchar field?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ordendelfai]

I'll be darned, when I remove the bit field, the pass-through query does not return any dup's, but when I include it, the dups are returned. I tried Cast and Convert to varchar for the bit, but for some reason it removes most of the 0's and -1's, which is a large problem because I need the values.

Any reason as to why this happens with bits? Anything I can do to fix it?

Sample data with original query (the top 2 are dups):

PHID DoNotDisplay MasterID Master_FirmName
[COLOR=red yellow]1000 0 2 Firm1
1000 0 2 Firm2[/color]
1002 0 14 Firm3
1004 0 22 Firm4
1004 -1 46 Firm5

Here is the new code I tried:

SELECT tbl_Cmpny_Consultants.PHID, Convert(varchar, tbl_Cmpny_Consultants.DoNotDisplay) AS Display, tbl_Agency_Master.MasterID, tbl_Agency_Master.Master_FirmName
FROM ((tbl_Consultant_Data LEFT JOIN tbl_Agency_Codes ON tbl_Consultant_Data.[Agency_ID#] = tbl_Agency_Codes.[AgencyID#]) RIGHT JOIN tbl_Cmpny_Consultants ON tbl_Consultant_Data.[ConsultantID#] = tbl_Cmpny_Consultants.[ConsultantID#]) LEFT JOIN tbl_Agency_Master ON tbl_Agency_Codes.MasterID = tbl_Agency_Master.MasterID
GROUP BY tbl_Cmpny_Consultants.PHID, Convert(varchar, tbl_Cmpny_Consultants.DoNotDisplay), tbl_Agency_Master.MasterID, tbl_Agency_Master.Master_FirmName;

Sample data with Convert query:

PHID Display MasterID Master_FirmName
1000 2 Firm1
1000 0 2 Firm2
1002 14 Firm3
1004 22 Firm4
1004 1 46 Firm5

 

[dhookom]

I would add the number of characters to the convert() function. Run some tests with the least amount of complexity. This should help identify the issue. Then add more fields and tables. BTW: you can omit the ";"

SELECT Convert(varchar(1), DoNotDisplay) AS Display, Count(*) as NumOf
FROM tbl_Cmpny_Consultants
GROUP BY Convert(varchar(1), DoNotDisplay)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ordendelfai]

I think I have it figured out. I recently added the "DoNotDisplay" column to the table in the sql table. There are approximately 300 records in the table. In access, I see "0" for all records in DoNotDisplay except for the few I have recently added that indicate True.

I ran a select query looking for "0" in the field and it only returned the records I had recently added. I then change the criteria to "Is Null" and 98% of the records returned, even though "0" is showing up.

I tried to do an update query to change any Nulls to 0", but access returns a message "You are about to update zero rows", and the null's are not changed.

Any tips how to update the null's to a true 0 for the existing records? It is not a problem with newly created records, just existing.

Thanks!!

~Orden

 

[cmmrfrds]

The bit data type in sql server and ODBC don't work well together. The bit in sql server holds 1 or 0, so I am surprised that the example you show has a -1. Was the data loaded directly from an Access mdb to the sql server table?
If you are going to use ODBC which is what the pass-thru query uses, then is it possible to convert the bit field to a tinyint in the sql server table. That will save you additional problems using that field in Access Forms. BTW, ADO handles the bit field okay it is only thru ODBC that it is a problem. The other thing is to check that it is valid in sql server to use a bit data type in a group by - it may not be. You could post the question on the sql server forum on tek-tips.

 

[dhookom]

Good advice from cmmrfrds. I never use the bit type in sql server since the same functionality (and more) is available in tinyint.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ordendelfai]

Thanks for the advice everyone! It turned out that my last post before this one solved my problem. I never did figure out a way to make the null's that showed a "0" to really be a "0", so I just went through them one by one deleting the "null" and putting a "0". That ended up doing the trick.

Thanks again!

~Joel
As common courtesy and to help other members who might find this thread in the future, please respond if the advice given resolved your issue.