Calculating fields from separate queries based on a condition

[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?

 

[Duane Hookom]

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?

 

[Guest]

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

 

[Duane Hookom]

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.