2 identical table designs

[Danny]

I have 2 tables with the same numbers of fields and same name
Table 1 is the original data and
Table 2 is the adjustments data, which needs to be kept seperate

I am having trouble combining data from both tables. In some instances there
is data in table 2 but not in table one

eg account number may be in t2 not not in t1
I have considered using a junction table but this does not work
Other than the ID in both tables there are no other primary keys

Fields are

ID
Company Code (duplicate YES)
Account (duplictaes YES)
Value 1
value 2
Text

Can some help and suggest ways of improving or meths that I can use to
return data say based on both Company code and account numbers.

Regards
Danny

 

[Krizhek]

In this case I would try to first create a union query (this should be done
as a SQL query which you can create by starting a new query not selecting a
table then go to SQL view) then use a similar statment to below.

SELECT [A].* FROM [table a] as A
UNION ALL SELECT .* FROM [table b] as B;

This should essentially combine the two tables into one query. Then you can
filter out what you need.


Good luck,

Krizhek

 

[John Vinson]

I have 2 tables with the same numbers of fields and same name
Table 1 is the original data and
Table 2 is the adjustments data, which needs to be kept seperate

I am having trouble combining data from both tables. In some instances there
is data in table 2 but not in table one

eg account number may be in t2 not not in t1
I have considered using a junction table but this does not work
Other than the ID in both tables there are no other primary keys

Fields are

ID
Company Code (duplicate YES)
Account (duplictaes YES)
Value 1
value 2
Text

Can some help and suggest ways of improving or meths that I can use to
return data say based on both Company code and account numbers.

Sounds like you need a UNION query:

SELECT ID, [Company Code], [Account], [Value 1], [Value 2], [Text]
FROM T1
UNION ALL
SELECT ID, [Company Code], [Account], [Value 1], [Value 2], [Text]
FROM T2
ORDER BY <whatever makes sense for your needs>

John W. Vinson[MVP]

 

[Craig Alexander Morrison]

Why must it be kept separate? Is it kept in a different physical location,
if it is you can ignore the following. Assuming the reason for the different
physical location is logical or down to some "policy" decision.

Keep it all in the same table, add a field to indicate whether the record is
an "original" or an "adjustment".

If there is a natural primary key (say an index based on both Company Code
and Account) then use it instead of the "id stuff".

If the combination of Company Code and Account are currently unique in both
the current tables then the addition of the "original"/"adjustment" marker
to the primary key will create the necessary primary key.

With this construct you can now query on just one table and use the marker
to differentiate from "original" and "adjustment" data.

BTW If there is no natural key you could stick with the "id stuff" and just
use the marker as mentioned above.