Query several tables

[AHopper]

The product I am working with has different formats that
need to be tracked. The result is tables that have
different fields but some that have the same type of
information. Following are some examples.
UniqueLabel, RegisteredLabel, WeightOnePack, Job #, Used
with Job #, EmployeeNumber, PackedDate, SkidNumber, Shift

Not all fields have the same names
e.g. "WeightOnePack", "RegisteredLabelOnePack" in
table "OneGPackData"
"WeightNinePack", "RegisteredLabelNinePack" in
table "NineDifinPackaged"
"WeightEightPack", "RegisteredLabelEightPack" in
table "EightDifinPackaged" etc..

Even though the UniqueLabel and Registgered Label are
unique to a carton, they may be in the table more than
once since it can take more than one skid to fill a
carton and the quantity taken from each skid to fill a
carton needs to be tracked.

I want to create a report using the information from all
the tables. Depending on the information needed the
criteria for the report could be a date and a shift, a
date range, Uniquelabel range, RegisteredLabel range, Job.

Is it possible to create a new table - temporary or
permanent - to consolidate the data? If so how do I do
that?

Should I send the information to a table when the records
are saved? This would mean I am saving the same
information twice but I could put similar information
under one field name and it would simplify reporting.

I would appreciate any help in how this can best be
handled.

Thank you in advance.

Allan

 

[LeAnne]

Hi Allan,

As you're (hopefully) aware, this isn't a relational design. If you're
planning to use the db long-term, and if that means you will be
continually adding new tables, then I'd suggest a redesign in accordance
with the 1-3 Normal forms. However, a one-time solution to your example
problem would be a "Normalizing Union Query." This assumes that all of
the tables have exactly the same structure & datatypes:

SELECT UniqueLabel, RegisteredLabel, RegisteredLabelOnePack AS
WeightOnePack, JobNum, UsedwithJobNum, EmployeeNumber, PackedDate,
SkidNumber, Shift
FROM OneGPackData
UNION
SELECT UniqueLabel, RegisteredLabel, RegisteredLabelNinePack AS
WeightOnePack, JobNum, UsedwithJobNum, EmployeeNumber, PackedDate,
SkidNumber, Shift
FROM NineDifinPackaged
UNION
SELECT etc. etc.

Note fieldname change to eliminate the special "#" character, which can
cause problems if used in fieldnames.

If you decide to redesign, you can use the above method to create a
single, normalized table with data like WEightOnePack as *data* rather
than as a *fieldname*, without having to redesign completely from
scratch. Just create the NUQ, then create a Make-table based on the NUQ.

PS. You DO have primary keys in your tables, right? It sounds like each
combination of UniqueLabel+RegisteredLabel+SkidNumber is unique, so I'm
assuming a multifield key.

hth,

LeAnne