Microsoft Access- single button multiple table data push

Question

I am trying to make a form which require 4 fields to be filled.

1. Date
2. Material(drop down will give options to select from e.g Cement, Bricks etc)
3. Quantity
4. Price.

I want to push this data into different tables depending on the selected Material e.g

The data will be pushed into the brick's table if the user selects 'Brick' from the material's drop down.

The data will be pushed into the cement's table if the user selects 'Cement' from the material's drop down.

Simultaneously I want to push data into a table named 'All_Transactions" regardless of the materials selected just like in a ledger.

Summary: The data will be pushed into 2 tables simultaneously

1. All_transactions table
2. {Material}_table

Kindly help me out.

Answer 1

You should rethink your schema. It will require you to introduce a new table each time you introduce a new kind of material. Better create these tables

1. TABLE Material:

   • MaterialID (Autonumber, Long Integer, Primary Key)
   • Name (Text)

2. TABLE Order:

   • OrderID (Autonumber, Long Integer, Primary Key)
   • Date (DateTime)

3. TABLE OrderItem:

   • OrderItemID (Autonumber, Long Integer, Primary Key)
   • OrderID (Long Integer, Foreign Key)
   • MaterialID (Long Integer, Foreign Key)
   • Quantity (Double)
   • Unit (Text)

   Order                                          Material
 ???????????????                                ?????????????????
 ? OrderID PK  ?                                ? MaterialID PK ?
 ? Date        ?         OrderItem              ? Name          ?
 ???????????????       ??????????????????       ?????????????????
           ?           ? OrderItemID PK ?               ?
           ????????????? OrderID FK     ?               ?
                       ? MaterialID FK  ?????????????????
                       ? Quantity       ?
                       ? Unit           ?
                       ??????????????????

Like this, introducing a new material only consists of adding a new record to the Material table. This not only reduces the number of tables, but also the amount of programming, queries, forms, reports etc.

You will probably need some more tables for customers etc.

The database schema is the fundament of your application. With a good schema you can succeed in making a good application. If the schema is bad, the game is lost in advance.