Merging data in two tables

Question

Dear all, this question may sound too simple/easy for you all to answer. However I sincerely hope that someone can enlighten me on how to get my system to work. I am just short of this function.

The scenario goes like this:

Table 1:

Unique Key (i.e #AA1, #AA2, #AA5, #AA6, #AA8, #AA9)
[Note: Intentionally omitting #AA3, #AA4 and #AA7]

Each unique key (in table 1) will have their sub-data that is different from table 2.

Table 2: 

Unique Key (i.e #AA1, #AA3, #AA4, #AA5, #AA7, #AA8)
[Note: Intentionally omitting #AA2, #AA6 and #AA9]

Each unique key (in table 2) will have their sub-data that is different from table 1.

I then created a report with the intention to get all sub-data from #AA1 to #AA9. However only sub-data of #AA1, #AA5 and #AA8 appeared. The intention of this report is to help me determine which table (Table 1 and Table 2, which are actually the location of my 2 shops) did not include every transactions that happened in that month.

Question: How do i get data to appear in the report in the running sequence from #AA1 to #AA9?

Sincerely awaits for my knight in shining amour. 

Thank you.

Cheers,
Kelly.

Answer 1

Hi Kelly. Have you tried creating a query using the Find Unmatched Query Wizard? Just curious...

Answer 2

Use a UNION query to create a complete list of UniqueKey.   Left join that query to both table either in a SELECT query or another UNION query.

---

Build a little, test a little

Answer 3

Does each table have the same non-key columns, in both number and data type, though not necessarily in name?  If so you can return rows from both in a UNION ALL operation in which the table name is a constant in a column, e.g.

SELECT "Table 1" AS Shop, [SomeColumn], [SomeOtherColumn], etc
FROM [Table 1]
UNION ALL
SELECT "Table 2" AS Shop, [SomeColumn], [SomeOtherColumn], etc
FROM [Table 2];

You can then sort the report on the key column by means of the report's internal sorting and grouping mechanism.

However, having separate tables for each shop is very bad relational database design.  It 'encodes data as table names'.  A fundamental principle of the database relational model is the Information
Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.  There should be a single table in which a foreign key references the primary key of a separate Shops table.

---

Ken Sheridan, Stafford, England

Answer 4

Hi, I did not intend to use query (i know of this function) as I hope to achieve it using report function. I am trying to streamline the process and not adding more process to achieve what one can do manually.

Just wondering if report can pull both matched and unmatched records from two tables.

Answer 5

Hi Karl, similarly to what i mentioned to 'the DB guy', I did not intend to use query (i know of this function) as I hope to achieve it using report function. I am trying to streamline the process and not adding more process to achieve what one can do manually. 

Just wondering if report can pull both matched and unmatched records from two tables.

In any case, i am not familiar with using UNION query. But i will definitely try it out now.

Thank you.

Answer 6

In any case, i am not familiar with using UNION query. But i will definitely try it out now.

A UNION was one of Codd's eight original algebraic operations of the database relational model when he introduced it in 1970.  It returns a table which contains all rows that appear in either or both of the specified tables, subject of course to any restrictions (another of the original eight operations BTW) on those tables.  This of course means that duplicates are suppressed, which therefore produces some inefficiency where there can be no duplication (as in the example I gave, due to the different constants in the first column), which is why in that context a UNION ALL operation should be used, as this does not suppress duplicates and is consequently more efficient.

When performing a UNION (ALL) operation the result tables of each part must have the same number of columns and be in the same order, and must correspond, column for column, in their data types.  As I said earlier, they need not correspond in name, and if the names different those from the first part of the operation will be returned as the column headings of the result table.

---

Ken Sheridan, Stafford, England