HELP!!! - Me again

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

Guest

I have two tables..
Both have field names (AccountDescription, Amount, WTAX, InputTAX, NetofVAT,
StatementCode)
The Statement Code is either a BS or P&L.
I need one query that will group similar AccountDescriptions, give me the
sum of the Amount, WTAX, InputTAX, NetofVAT for BS on both the tables and
another for P&L on both tables. Please help on how to do this...
 
Hi,

Since you have a non-normalized design (2 tables with identical
structure), I recommend first contructing a UNION query to combine the
two tables, and then basing both Totals queries on the UNION query. Post
back if you need more info.

hth,

LeAnne
 
Hi LeAnne
Thank for the reply
Actually the 2 tables don't have identical structure. One table has more
fields than the other. It would really be helpful if you could give me some
idea on how to make a UNION query. Thanks!
 
I forgot to add... that one table has more fields than the other and one
table has more rows. I have read a bit about UNION query and i think that my
tables are not union-compatible... Can anyone help me get by this problem?
Thanks!
 
The number of rows makes no difference for Union queries. You do need the
same number of fields in each subquery. However, you can do this by querying
only a subset of the fields in the wider table, or by padding the field list
in the narrower table:

SELECT Field1, Field2, Field3
FROM NarrowTable
UNION
SELECT Field1, Field3, Field5
FROM WiderTable

or

SELECT Field1, Field2, Field3, Field4, Field5
FROM WiderTable
UNION
SELECT Field1, " ", Field2, 0, Field3
FROM NarrowerTable

Note in the second example that field types must agree: the example assumes
Field2 in WiderTable is a text field and Field4 is numeric, so I padded with
"" and 0 respectively. You could also pad with Null (regardless of the field
type), but depending on what you're doing with the resultant recordset,
that's not always appropriate.
 
Thanks! i'll let you know if it works!

Douglas J. Steele said:
The number of rows makes no difference for Union queries. You do need the
same number of fields in each subquery. However, you can do this by querying
only a subset of the fields in the wider table, or by padding the field list
in the narrower table:

SELECT Field1, Field2, Field3
FROM NarrowTable
UNION
SELECT Field1, Field3, Field5
FROM WiderTable

or

SELECT Field1, Field2, Field3, Field4, Field5
FROM WiderTable
UNION
SELECT Field1, " ", Field2, 0, Field3
FROM NarrowerTable

Note in the second example that field types must agree: the example assumes
Field2 in WiderTable is a text field and Field4 is numeric, so I padded with
"" and 0 respectively. You could also pad with Null (regardless of the field
type), but depending on what you're doing with the resultant recordset,
that's not always appropriate.
Click to expand...
 
Back
Top