Combining two queries

[BobNTN]

Have a query that simply displays two columns (Chargeamt and ChargeDate)
depending on the CID input

SELECT TblCharges.CID, TblCharges.Chargeamt, TblCharges.Chargedate
FROM TblCharges
WHERE (((TblCharges.CID)=[?]));

Works fine

Have another query that does the same thing for credits (Creditamt, RecDate, and Chknum depending on the CID input

SELECT TblPayments.CID, TblPayments.Creditamt, TblPayments.Recdate, TblPayments.Chknum
FROM TblPayments
WHERE (((TblPayments.CID)=[?]));

works fine

When I combine them to get a one page report showing both, it duplicates the first record about nine times of one of the tables, either Charges or credits and the other seems fine.

SELECT TblCharges.CID, TblCharges.Chargeamt, TblCharges.Chargedate, TblPayments.Creditamt, TblPayments.Recdate, TblPayments.Chknum
FROM TblCharges, TblPayments
WHERE (((TblCharges.CID)=[?]));

I have changed the joins various ways and swapped the tables order but still does it.

I know it is, once again, some simple something I am missing.

 

[gfultz]

The query you displayed doesn't have a join in it. The only way to merge that type of data together is with a join.

An inner join on CID would repeat data if one of the tables had more records that matched the criteria than the others.

A left join would yield all charges and only payments if they matched.

A right join would yield all payments and only charges if they matched.

It sounds like CID is a foreign key flor both tables. Possibly CustomerID? Any time you merge data you will have this go on. There isn't a way in a select query to say charge 1 and list all of the associated payments without repeating charge 1 data.

However, you can use a cross tab query to accomplish this. Or aggregate functions to get total charges and total payments for each cid which would give you a 1 for 1 relationship for each cid.

Maybe a little more info would help in figuring out the best solution to your task.

 

[BobNTN]

CID (Cust ID) is the common key for each table.

Actually, I am just trying to get both lists displayed on the same page of a report so the payments can easily be scrutinized against the charges. That would be used if a customer calls and wants a history of his charges and payments, as in a balance dispute.

But if it isn't worth the effort, the single lists will suffice.

 

[gfultz]

I would setup the report with a query on the customer then add subreports for each of the lists to link to the customer ID. so you would use your separate queries for each sub report and then they would appear side by side.

You might also be able to figure out a way to group by one of the charge fields and use that in a report as well.

If you really want to associate payments with charges, you should link them in some way, so that each payment is associated with a particular charge.

 

[BobNTN]

I would setup the report with a query on the customer then add subreports for each of the lists to link to the customer ID. so you would use your separate queries for each sub report and then they would appear side by side.

Never have done a subreport so I'll look into that

You might also be able to figure out a way to group by one of the charge fields and use that in a report as well.

If you really want to associate payments with charges, you should link them in some way, so that each payment is associated with a particular charge.

Can't do that since payments are made infrequently and don't always match charges. Some pay late, in advance, partial, more than, etc.