Compare Data

[Daniel]

Hello All,

I have an urgent problem. I have three tables with lot numbers and
quantity. Each table is data taken at different dates. In the tables, some
of the lot numbers are the same, some are in one, but not the others. I
need to compare this data by lot number in combine it into a single table,
but the quantity must not be combined, and needs to be added to quanty2,
quantity3, etc. Can anyone help me?!

Thanks,

Daniel

 

[Guest]

make a table_query

 

[Jeff Boyce]

Daniel

When you say "the quantity must not be combined", do you mean you wish to
keep separate rows in your combined table, one for each row appearing in the
three "source" tables?

If so, consider the following approach:

* create a new, empty table, with a structure compatible with your
three source tables
* add a field that you use to indicate a date (if the structure doesn't
have this already)
* create a Select query of the first of your source tables, all fields
* if you don't have a date field in that query/table, add a date value
in a new field in the query -- the end of the date range for your first
table's data might do
* when the query works properly, convert it to an Append query and
append to your new empty table
* repeat as needed for the other two source tables, changing the
(?added) date field

Your new (formerly empty) table now holds one row for each row in the three
source tables. Your new combined table has lot numbers and quantity. Do a
Totals query, grouping by lot number and summing by quantity to find the
total quantity for each lot number.

 

[Pieter Wijnen]

Create a Query like this:
SELECT 'T1' AS T LotNo, QTY FROM <Table1>
UNION ALL
SELECT 'T2' AS T LotNo, QTY FROM <Table2>
UNION ALL
......
SELECT 'TX' AS T LotNo, QTY FROM <TableX>

Save it As tst (use the CrossTab Q wiz to) create this query:

TRANSFORM Sum(QTY) AS A
SELECT LOT, Sum(QTY) AS Result
FROM tst
GROUP BY LOT
PIVOT T;

And you should have it

HTH

Pieter