What sort of query do I need?

[freda42]

I have a number of tables of data for different depots, all of which need to be used to update a main table of data (containing data from all depots) on a regular basis.

The first time I did this, I did an append query to get the data into the main table from the depot's tables.

However, when any record changes in the depot's data, I want to be able to run something that will make that change in the main table as well. If I do another append query, it just adds all of the depot's records on the end of the main table again.

How would I do this?

Thanks!

 

[Mile-O]

Why don't you normalise you tables so that you only have one table for all depots?

 

[freda42]

How do I do that? (I'm new to access, teaching myself here!)

Thanks

 

[Mile-O]

Can you upload a stripped down version of your database or detail your current table structure?

You should only need, at the most basic level, two tables:

tblDepots
DepotID
DepotName

tblInfo
InfoID
DepotID
whatever stats


It seems that currently, should a new depot be added to your current list then you will have to add a new table which, in turn, would mean new queries, forms, reports, etc.

Getting your table structure correct from the start will ensure that the structure will not need tampered with even if you want to add 50 new depots.

 

[maxmangion]

do a search here on this forum for "normalization", you will find plenty of threads covering the issue.