Calculating fields from separate queries based on a condition

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

Guest

I have a table with payments that are either "self" or "insurance" and a
dollar amount.

I have a separate one with payments from a facility with the dollar amount.

These two tables are unrelated.

In my report, I would like to add payments for "self" only from the first
table with all facility money from the second. How would I do this?
 
What do you mean by "add payments"? Do you want to display them together in
a list or do some math with them?

Can you provide some sample records and desired final display?
 
To clarify….

One table has..

Facility Date Payment
Adult 6/20/05 $10
Child 6/20/05 $20
Etc…

Another has…

Type of payment Date Payment

Self 6/20/05 $300
Insurance 6/20/05 $400

What I am being asked to show is the total for self (so, show only if type
=self) plus the total for facility. So in this example, I would need to
display

Total: $330

I hope that helps.. thanks
 
You can create a union query of both tables
SELECT Facility as PmtType, [Date] as PmtDate, Payment
FROM OneTable
UNION ALL
SELECT [Type Of Payment], [Date], Payment
FROM Another
WHERE [Type Of Payment]="Self";

You can then use this query to total all payments.
 
Back
Top