CODEWITHSUNDEEP
sqldatabasems-accessdatabase-design
Coder014
Coder014

Reputation: 41

Merging database tables containing identical fields

This is a follow-up question to this one: Planning a database describing a multi-versioned product (no need to read it to answer this one).

To recap, I need to define multiple tables describing multiple versions of the same entity, namely print job log entry. These versions stem from different printer models that produce them. The thing is, all these different versions will always contain some number of common fields, e.g. [Copies Printed], regardless of the printer model, and others that are unique to a certain model (and thus, table version).

I'm working in MS Access.

I'm currently leaning towards the solution in which there is a table for each version. Let's say for simplicity's sake that there are only two printer models, and thus two tables, each one containing log entries produced by that model. In both of them there are some identical fields (same name, same type, same meaning). One of the queries that I will have to run will be to select rows from BOTH tables (e.g. select all entries with more than 10 copies in June 2014 run on any printer).

My question: how to write such a query, and how to define these tables, so that the DB knows that we're talking about the same [CopiesPrinted] field? A regular SELECT on such two tables (let's call them PrinterLog1 and PrinterLog2) will return another table, in which there will be two distinct columns, [PrinterLog1.CopiesPrinted] and [PrinterLog2.CopiesPrinted]. What needs to be done so that there is only one column [CopiesPrinted] in the resulting table, that will contain the data from the corresponding printer log table? In other words, how do I merge these two tables into one in a smart way? "JOIN ... ON PL1.CP=PL2.CP" queries won't help either, because the contents of [PrinterLog1.CopiesPrinted] and [PrinterLog2.CopiesPrinted] are supposed to be different.

Thanks!

Edit 1: Thanks everyone, UNION seems to do the job. However, it seems to change column type for some fields. For instance it changed Yes/No (in the source table) to Number (in the result table) and put -1 and 0 for true and false respectively. Is there a way to remedy this?

Also, I can't seem to find anywhere on the web the list of SQL data types for Access. All I could find was the ones meant for Access users (Yes/No, Currency, etc.), whereas what I need is SQL-style types (VARCHAR, DECIMAL, etc.). Is there a list of SQL-style types for MS Access?

Upvotes: 1

Views: 163

Answers (1)

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

You would need to use the UNION statement.

For instance:

SELECT CopiesPrinted, Blahblah FROM PrinterLog1 
UNION ALL
SELECT CopiesPrinted, Blahblah FROM PrinterLog2

More reading:

Upvotes: 1

Related Questions