Query lists repeat data

[davidbodhi]

Hi, all... welcome back to the new Access World Forums... glad we're still operational, even if some data was lost. It's the minds, anyway, not the data...

I have a query that pulls from 3 transaction tables.

tblTransactions - lists purchaser, amt tendered, other details
tblPurchased_Items - lists products and services paid for
tblTherapy_Treatments - lists treatments paid for

(Note: these are not separate lists of available items. They're two subforms' tables, one that deals with insurance and one that doesn't.)

I can pull this data fine, but in a case where someone had 1 treatment, but purchased 2 products, the query lists the treatment twice. I do have unique IDs for each table's items, but am not sure how to "list" each transaction once, but each thing paid for once, also.

For example:
Joe Blow comes in and has physical therapy and buys 2 books.

My query will show (bogus fields for illustration purposes):
TransactionID Name TherapyID TherapyCost ProductID ProductCost
33 Joe Blow 10 180.00 15 12.00
33 Joe Blow 10 180.00 17 19.00

So, if I total TherapyCost it really gives me twice the correct amount.

Can someone point me to a solution?
Pardon my rank ignorance, yet again.

 

[davidbodhi]

Thanks, Pat....... another of the millions of pieces of information I didn't have has been put in place.

I'll start looking into subreports.