Query from two tables duplicating data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a query in Access 2002 using fields from two tables. The query
is duplicating data so that each line item is listed multiple times. I can
specify with more details if someone thinks they can help. Can you?
 
Post the SQL of the query. If you are not familiar with how to do that, open
your query in desingn mode, switch to SQL view, and copy/paste the code into
your post.
 
I have posted it below. Any ideas?


SELECT Checks.CarrierCode, Checks.StmtDate, Checks.RecdDate, [Line
Items].Group, [Line Items].ProdCode, [Line Items].CommMonth, [Line
Items].CommAmt
FROM Checks INNER JOIN [Line Items] ON Checks.ChkID = [Line Items].ChkID
ORDER BY Checks.StmtDate, [Line Items].Group, [Line Items].ProdCode;
 
I have posted it below. Any ideas?


SELECT Checks.CarrierCode, Checks.StmtDate, Checks.RecdDate, [Line
Items].Group, [Line Items].ProdCode, [Line Items].CommMonth, [Line
Items].CommAmt
FROM Checks INNER JOIN [Line Items] ON Checks.ChkID = [Line Items].ChkID
ORDER BY Checks.StmtDate, [Line Items].Group, [Line Items].ProdCode;
Click to expand...

This will display each field from Checks as many times as that check has
matches on the ChkID field in LineItems. This is exactly how a query is
designed and intended to work.

What result do you EXPECT? How do you want to see the data? Perhaps you should
consider a Form based on [Checks] with a subform based on [Line Items] if you
want to see each check once, with line items repeated.

John W. Vinson [MVP]
 
What I would like to see is each line item connected to the correct Carrier.
What I currently see is every single line item in the line items table listed
for the first carrier, followed by every single line item listed for the
second carrier, and so on. Each line item, regardless of which carrier's
check the items belongs to, is attributed to every carrier, resulting in
multiple incorrect records.

The data in the tables is intact, and the data in each stand-alone query is
intact. This query that is to pull the carrier, statement date and receive
date from the checks table and the group, product code, commission month, and
commission amount from the line items table into one place is intended to be
used as a data pool for reporting. Apparently I have not set something up
correctly, which is why I am seeking help.

FYI, the data was entered through a form "Checks" with a sub-form "Line
Items," as you suggested.

I hope this clarifies what I EXPECT to see. Your experience and expertise
as an MVP are what I EXPECT to learn from. I EXPECT to receive a reply to my
question that will help me learn a little more about this application and how
to use it more effectively. What I don't EXPECT is a condescending tone that
seems intended to make me feel stupid for seeking assistance.

Any practical advice you can offer is very much appreciated. The attitude is
not necessary. If you feel this is beyond your expertise or not worth your
time, perhaps another of your MVP teammates could be of some assistance.

Thank you.


John W. Vinson said:
I have posted it below. Any ideas?


SELECT Checks.CarrierCode, Checks.StmtDate, Checks.RecdDate, [Line
Items].Group, [Line Items].ProdCode, [Line Items].CommMonth, [Line
Items].CommAmt
FROM Checks INNER JOIN [Line Items] ON Checks.ChkID = [Line Items].ChkID
ORDER BY Checks.StmtDate, [Line Items].Group, [Line Items].ProdCode;
Click to expand...

This will display each field from Checks as many times as that check has
matches on the ChkID field in LineItems. This is exactly how a query is
designed and intended to work.

What result do you EXPECT? How do you want to see the data? Perhaps you should
consider a Form based on [Checks] with a subform based on [Line Items] if you
want to see each check once, with line items repeated.

John W. Vinson [MVP]
Click to expand...
 
What I would like to see is each line item connected to the correct Carrier.
What I currently see is every single line item in the line items table listed
for the first carrier, followed by every single line item listed for the
second carrier, and so on. Each line item, regardless of which carrier's
check the items belongs to, is attributed to every carrier, resulting in
multiple incorrect records.
Click to expand...

Then there's something wrong with the Join clause. You're joining Checks to
LineItems - each record in Checks has its own value of CarrierCode. If you
look directly in the Checks table does each check have the correct carrier
code?

Bear in mind I don't know anything about the structure or contents of your
table so I'm guessing here.
The data in the tables is intact, and the data in each stand-alone query is
intact. This query that is to pull the carrier, statement date and receive
date from the checks table and the group, product code, commission month, and
commission amount from the line items table into one place is intended to be
used as a data pool for reporting. Apparently I have not set something up
correctly, which is why I am seeking help.
Click to expand...

You've posted only one query. Would you be willing to post the SQL of the
other queries?
FYI, the data was entered through a form "Checks" with a sub-form "Line
Items," as you suggested.
Click to expand...

Thank you.
I hope this clarifies what I EXPECT to see. Your experience and expertise
as an MVP are what I EXPECT to learn from. I EXPECT to receive a reply to my
question that will help me learn a little more about this application and how
to use it more effectively. What I don't EXPECT is a condescending tone that
seems intended to make me feel stupid for seeking assistance.
Click to expand...

My sincere apologies. I was out of line yesterday posting this. I will be glad
to work with you if you're willing. But... your help is needed as well. You're
asking me in this (and the previous) post to debug a query which I have not
seen, based on a table whose structure is not known to me.

Let's work together, and I'll try to help as best as I can!

John W. Vinson [MVP]
 
John:

Thank you for your reply. I realize I did not provide much info going in,
but as I stated in my initial request, I would be happy to provide whatever
data would have been needed if someone felt they could help.

At any rate, I got frustrated enough yesterday afternoon to go back and
revisit the problem, and I finally figured out where I had gone wrong and
corrected the problem myself. While it took much longer than I would have
preferred, I have learned, through this process, more about Access and will
be able to apply what I have learned to many other projects.

Thanks anyway. Maybe next time.
 
Back
Top