Update single fields in multiple tables from one field in a form.

[mtdew4243]

I’m working on my first major project using MS Access. MS Excel had been the program of choice before this. I work in the Operations department for a distribution company. We track daily performance at each of 5 warehouses locations. I have setup 5 identical tables, one for each location. The exception is the Canadian facility that also has a ConversionRate field. The table structure is:

Date (PK)
Day
Orders
Units
Sales
Transfers
UnshpdUnits
UnshpdDolrs
ConversionRate (Canada)

I have created a Form where I can enter all the data and was hoping not having to key the Date and Day field each time.

Can I create a field for Date and a field for Day in the Form that will populate to the 5 tables?

I believe that creating a separate table for each warehouse was the way to go vs 1 large one but, maybe that is not so. I will run weekly, monthly and yearly reports from the data collected.

Thanks!

[Stewart Ross]

You can append records to any table using SQL statements built in VBA code and executed in response to particular form events - but in many ways you have answered your own question when you have queried splitting your data into five identical tables. For your data, the warehouse location is merely another attribute to consider. There is no need at all to create 5 tables, one for each warehouse, as this makes data update and maintenance very laborious indeed. It also makes reporting and other day-to-day querying virtually impossible.

Ask yourself what would happen if you open another warehouse? Would you expect to have to add a new table, design new queries, modify your reports and so on just because you now operate in six locations?

You may well find the following article on database normalisation and table structures useful - it is an essential skill when designing databases. I would suggest it is even more important when moving to relational databases such as Access from the free-form structure of Excel!

-Stewart