I have a database with different tables linked with each other. I have to collect data periodically from an external source. Every time I collect data I have to update/append records in each & every table which is very time consuming & invite error in the process. Is there a way so that I can automatically update/append records in all the tables by updating/appending records in a single master table.
Auto updation of tables
- Thread starter malaydash
- Start date
- Local time
- Today, 19:39
- Joined
- Sep 12, 2006
- Messages
- 16,075
you shouldnt really have to update lots of tables when you import a data fiel
the data file may contain some new lookups, which will need loading - but generally, you should only have to update a very few tables - typically one or two
if you are to update many more, it indicates your table structure is likely not to be normalised
----------
but in any event, doing it the way you are doing it IS still the best way. load the data to a new table, validate it in the new table, and then, if you are satisfied, load it to your main database tables
note that this can/should all be automated, so although designing the process may be tricky, once you have it done, it will look after itself - or report any issues
the data file may contain some new lookups, which will need loading - but generally, you should only have to update a very few tables - typically one or two
if you are to update many more, it indicates your table structure is likely not to be normalised
----------
but in any event, doing it the way you are doing it IS still the best way. load the data to a new table, validate it in the new table, and then, if you are satisfied, load it to your main database tables
note that this can/should all be automated, so although designing the process may be tricky, once you have it done, it will look after itself - or report any issues
Dear Dave
Thanx for ur suggestion. But it will really be helpful if you elaborate a little. I have a database for around 3500 orders, 10 different client having around 1100 components. I've split every entity into different tables with one-to-many relationship with referential integrity enforced. But the clients, components & orders keep changing their value & I have to update them every time I import data.
Plz help
Thanx for ur suggestion. But it will really be helpful if you elaborate a little. I have a database for around 3500 orders, 10 different client having around 1100 components. I've split every entity into different tables with one-to-many relationship with referential integrity enforced. But the clients, components & orders keep changing their value & I have to update them every time I import data.
Plz help
- Local time
- Today, 19:39
- Joined
- Sep 12, 2006
- Messages
- 16,075
the clients details should not change too often
occasionally you may get new clients - so use a unmatched query to find the new ones, and append them. use an update query to change any modfiied details for existing customers
the same with orders
run an append query to add new orders
run an update query to modify existing orders.
but surely the majority of the data doesnt (or shouldnt) change?
occasionally you may get new clients - so use a unmatched query to find the new ones, and append them. use an update query to change any modfiied details for existing customers
the same with orders
run an append query to add new orders
run an update query to modify existing orders.
but surely the majority of the data doesnt (or shouldnt) change?
Similar threads
- Deleted Bruce 182381
- Queries
