Batch Query - 42 tables to one Master Table

[Guest]

hey
I have 42 Tables and i am looking to combine them into one MASTER table
there are 4 common field i am looking for.
There is the long way of doing this running 42 queries. I have that set up
but figure there is a better way.

I want the query to grab table 1 input the 4 fields into the MASTER Table,
when complete go to table 2 and do the same thing automatically. AS well i am
forcing the table name into a column in thet MASTER Table. Can this be added
into the query.

If the query has to prompt me or have me select which table is next i can
live with that no problems.

Any idea? Does anyone have something like this i can modify to fit my work
Let me know
thanks

 

[John W. Vinson]

hey
I have 42 Tables and i am looking to combine them into one MASTER table
there are 4 common field i am looking for.
There is the long way of doing this running 42 queries. I have that set up
but figure there is a better way.

I want the query to grab table 1 input the 4 fields into the MASTER Table,
when complete go to table 2 and do the same thing automatically. AS well i am
forcing the table name into a column in thet MASTER Table. Can this be added
into the query.

If the query has to prompt me or have me select which table is next i can
live with that no problems.

Any idea? Does anyone have something like this i can modify to fit my work
Let me know
thanks

I'd suggest doing it all in two queries, in one operation. First go into the
SQL window of a new query and type in a UNION query using all 42 tables:

SELECT this, that, who, what FROM Table1
UNION ALL
SELECT thisone, thatone, whozis, whatzat FROM Table2
UNION ALL
SELECT ...

through all 42 tables. Each SELECT clause must have the same number of fields
and they must match in datatype, but the fieldnames can be different.

Open this query to see if it correctly stitches your 42 tables together. If it
does, save it as (say) uniAllTables.

Then create an Append query based on uniAllTables, appending to MASTER. You
can easily specify the names of the fields you're appending from and appending
to, and they don't need to be the same.

Run the query and zip... everything will be in.

Note that it's absolutely a total waste of effort to be concerned about the
*order* of records in the MASTER table; tables are just big sacks of data, and
if you want to see the records in order, you must - no option!! - use a Query
sorting by some field in the table.

John W. Vinson [MVP]

 

[Guest]

hey
This works great but I need a couple more things from my query.

1) I would like 'hard code' one extra field in GPS_SUPER table to
input the Table name, field is called (GPStable) this is not in any of the
base tables (asupport, Bicycle, Bridge) can this be done?

I need to carry the original table name thru the union Query into the
GPS_Super table. without this there is no way ofr me to create a unique Id in
the GPS_Super table

2) In my base tables my it_Date, it_Time fields have a datatype of
text. We would like to switch them to Date\Time. Can this be done by the
query?

I know I am asking a lot if you can help us out at all that would be great.
Thanks
Email is (e-mail address removed) if you need more clarification.

------------------------- My Query
-----------------------------------------------------------
SELECT mslink, it_Name, it_Z, it_Date, it_Time
FROM GPS_SUPER

UNION ALL
SELECT mslink, it_Name, it_Z, it_Date, it_Time
FROM asupport
UNION ALL
SELECT mslink, it_Name, it_Z, it_Date, it_Time
FROM bicycle
UNION ALL
SELECT mslink, it_Name, it_Z, it_Date, it_Time
FROM bridge;

 

[John W. Vinson]

hey
This works great but I need a couple more things from my query.

1) I would like 'hard code' one extra field in GPS_SUPER table to
input the Table name, field is called (GPStable) this is not in any of the
base tables (asupport, Bicycle, Bridge) can this be done?

I need to carry the original table name thru the union Query into the
GPS_Super table. without this there is no way ofr me to create a unique Id in
the GPS_Super table

2) In my base tables my it_Date, it_Time fields have a datatype of
text. We would like to switch them to Date\Time. Can this be done by the
query?

Sure. You just need a couple of calculated fields in the individual queries in
the UNION:

SELECT mslink, it_Name, it_Z, CDate(it_Date & it_Time) AS it_datetime,
"GPS_SUPER" As GPStable
FROM GPS_SUPER

UNION ALL
SELECT mslink, it_Name, it_Z,CDate(it_Date & it_Time) AS it_datetime,
"asupport" AS GPStable
FROM asupport
UNION ALL
SELECT mslink, it_Name,CDate(it_Date & it_Time) AS it_datetime,
"bicycle" AS GPStable
FROM bicycle
UNION ALL
SELECT mslink, it_Name, it_Z, CDate(it_Date & it_Time) AS it_datetime,
"bridge" AS GPStable
FROM bridge;

Note that Access date/time values are stored internally as Double Float
numbers, a count of days and fractions of a day (times) since midnight,
December 30, 1899. As such it's almost always best to store a point in time as
a single field; it can be formatted to show both the date and time portions,
sorted chronologically, searched, etc.

If GPS_SUPER is your new table into which you're putting all this data, and
it's currently empty, don't include it in the UNION query (since it won't have
the same field structure nor any data) - instead, UNION all the source tables
and then base an APPEND query on the Union query.

John W. Vinson [MVP]