Union query?

[Les Isaacs]

Hi All

I have a query [qryFees] which includes the date field [accsDate], currency fields [credit] and [debit] and text field [practice] (plus others).


I have a table [tblFeesDue] which includes the date field [DueDate], currency field [fee], and text field [practice] (plus others).
The two fields [practice] (in [qryFees] and [tblFeesDue]) are related - ie represent the same data
I need a query that will output all the records from [qryFees] and all the records from [tblFeesDue], so that I can see, for each [practice], all the records from either table.
Essentially, [qryFees] outputs all the payments actually made to/from each [practice], and [tblFeesDue] holds all the amounts periodically due from each [practice] (ie the standing payments they should have paid!), and I need to generate statements showing all payments along with all the standing payments (and resulting balances).

I've been struggling with this for too long now, so hope someone can help?

Many thanks
Les

[ranman256]

Make 1 query for each: qsFees, & qsFeesDue
make sure they both have same fields in same order, then union is:

select * from qsFees
union

select * from qsFeesDue

[Les Isaacs]

Hi ranman - thanks for your reply. I did try something like that, but a problem is that there are two currency fields [debit] and [credit] in [qryFees], but only one currency field [fee] is [tblFeesDue] - and the output needs to include all records with values in any of the 3 fields. So I'm not sure how to 'match' the one currency field with the two?

Thanks again.
Les

[ranman256]

create a zero field, Fee:0

[moke123]

you can use an alias as a placeholder

select credit, debit, 0 as fee from ...
union
select 0 as credit, 0 as debit, fees from ...

[June7]

First SELECT line dictates data type and field names.

SELECT practice, credit, debit, 0 AS Fee FROM ...
UNION
SELECT practice, 0, 0, fee FROM ...

Each SELECT line can be sql that JOINs tables or JOIN this UNION in another query to table.

UNION will not allow duplicate rows. Use UNION ALL if need every record, even if there are duplicates.

[Les Isaacs]

Brilliant - placeholders - that's what I hadn't thought of!
Many thanks guys