Access Query Duplicates

masterkeedu

masterkeedu

Joined
Jun 13, 2005
Messages
148
Reaction score
0
I am trying to create a query based on several(4) linked tables each sharing a unique key(client ID).
PROBLEM: If i leave the tables linked, as they are the query brings back no results, i beleive because no 1 client exists in all tables.
HOWEVER: unlinking the tables(just in the query) returns all records, duplicated displaying inaccurate results for that record. (showing boxes checked that should be empty and so). There seems to be 2 results for each record, each time though displayin diffrent data for that revcord.

I have searched several places and havbe had no luck and suggestions AT ALL will be greatly appreciated.
below is a sample of a very small test query that still dont work.

PLEASE HELP


SELECT ClientDemographics.CaseNumber, [Confession Judgement].[01ConfessionJudgement], [Court Judgement].[01CourtJudgement], [Satisfaction Judgement].[01SatisfactionJudgement]
FROM ClientDemographics, [Confession Judgement], [Court Judgement], [Satisfaction Judgement];
 
My sql includes the distinct clause and i have set the queries properties to not allow duplicates, Unique keys, i think was the option. That action limited the amount of duplicates to , two per casenumber...... any other advise??????
 
Thank you for your continued assistance.


the sql before i unlink the tables(in hopes to get any results) is briefly like this.

SELECT ClientDemographics.CaseNumber, ClientDemographics.RepLastName, ClientDemographics.RepFirstName, [Confession Judgement].[01ConfessionJudgement], [Court Judgement].[01CourtJudgement], [Satisfaction Judgement].[01SatisfactionJudgement]
FROM ((ClientDemographics INNER JOIN [Confession Judgement] ON ClientDemographics.CaseNumber = [Confession Judgement].CaseNumber) INNER JOIN [Court Judgement] ON ClientDemographics.CaseNumber = [Court Judgement].CaseNumber) INNER JOIN [Satisfaction Judgement] ON ClientDemographics.CaseNumber = [Satisfaction Judgement].CaseNumber;

HOWEVER since that returns nada, i unlinked the tables and only allowed unique values(or so i thought).
That SQL resembles this....

SELECT DISTINCT ClientDemographics.CaseNumber, ClientDemographics.RepLastName, ClientDemographics.RepFirstName, [Confession Judgement].[01ConfessionJudgement], [Court Judgement].[01CourtJudgement], [Satisfaction Judgement].[01SatisfactionJudgement]
FROM ClientDemographics, [Confession Judgement], [Court Judgement], [Satisfaction Judgement];


Basically i need to see casenumber if they are in any of the 3 judgement tables, displaying a checkbox for that judgemnt, leaving the other two blank. But when i run it it shows casenumber for everytime it is in any table, with all boxes checked, because the first record in each table Does have 1 judgement checked.



THANK YOU!
 
Back
Top