Combining/tracking records from multiple tables.

[RussG]

I have tables containing the same fields, with each one holding data based on
a different date (e.g. market value at MMYY). However, each month some
records (accounts) drop off and some new ones come in. I would like to
create one "master" table and/or query that tracks ALL records from all
tables, without dublicates. Simle Example:

tbl1206 Records: A, B, C
tbl0107 Records: A, C, D
Master Table/Query: A, B, C, D
and so on...

Hope my question is clear!
Thanks much,
RG

 

[Duane Hookom]

You can append all records to a single table that has a column for the date.
If you don't want to create a new table, you can possibly create a union
query that does the same.

 

[RussG]

I don't mind creating a new table. I tried an append query to do so, but it
seems that I need to be able to run an append query AND a find unmatched
records query simultaneously. Is this possible? Basically, I want the append
query to paste new, and only new, records to the Master table.

 

[RussG]

The fields in each table are:
CODE (Account number, also used as primary key)
NAME (Account Name)
MKTVAL (Market value of account)

Each table has a slightly different number of records, as some accounts drop
off and new ones are added. A "new" record would simply be an account that
did not exist in any other table. I'm not concerned with identifiying
accounts that drop off (i.e. "old" accounts), as I want to maintain the
record of that account in the master table.

At this point I'm only focusing on the CODE field. I'd like a table that
basically keeps a running list of all accounts. So either a table that
simply pastes new records, or a table/query that combines the CODE field from
all tables and then returns unique records only. Hope this is making sense!

 

[Duane Hookom]

Do you have a primary key in the new table? Can you share some field names
and identify how you would determine a new or old record?

 

[Duane Hookom]

Do you want to keep a history of Market Values? If so, the CODE can't be a
primary key. I'm not really sure what your objective is.