Query Results Conundrum

  • Thread starter Thread starter DubboPete
  • Start date Start date
D

DubboPete

Hi all,

I have a query where I filtered a particular record, and then when
adding a table of demographics, linked by the common ClientCode, I get
a result of four identical records in the query, not just the one I
expected. Where did I go wrong??? :-)

TIA

17th Century Goat-Herder
 
I am guessing you have a one to many relationship from Clients to
Demographics. And in this specific example, you have 4 demongraphics records
for the Client you have extracted in your query. And hence it will show 4
records and duplicate the records from the Client table. For example

Client Demographic
XYZ 1
XYZ 2
XYZ 3
XYZ 4

If your query has not selected (or or selected but unchecked the show check
box ) any fields from the demographic table, your resulting recordset may
look like

Client
XYZ
XYZ
XYZ
XYZ

If I have understood your situation, I recommend you look at the join type
between you table Client and Demographic table, right click on the join,
click on the join type button and change from the default of "1. Only inlcude
rows where join fields are equal" to either 2 or 3 depending on your needs.

Hope this helps.
 
Hi Dylan,

There is only one record for a particular ClientCode in each table,
therefore I presumed it would only pull one record into the query. I don't
have more than one demographic record per clientcode..... hmmm

What it is actually returning is :

Client Demographic
XYZ 1
XYZ 1
XYZ 1
XYZ 1

go figure?

Pete
 
So if I understand you correctly, you have a Client table eg tblClient and a
demographic table eg tblDemographic. Example data for these tables below:

tblClient
1 Cleint A
2 Client B
3 Client C

tblDemographic
1 Demogrpahic 1
2 Demogrpahic 2
3 Demogrpahic 3

and you run a query and the results are

Cleint A Demongraphic 1
Cleint A Demongraphic 1
Cleint A Demongraphic 1
Cleint A Demongraphic 1

If this is the case, I suggest you may like to use the word DISTINCT in your
query.

That is
SELECT DISTINCT tblClient.ClientName from tblClient where ....
Hope this helps.
It may pay to post your actual SQL of the query that you are using if this
does not solve your problem.
 
Back
Top