Adding a new field when a new record is added in another table (1 Viewer)

jinkx · May 12, 2015

I am beginner student trying learning Access. I am learning Access by doing some simple examples.

Suppose I am a car manufacturer. I have a table A in which I write down orders for cars. A record is an order. A single order may contain multiple cars in varying quantities.

Each car has its components. Some cars may have some of their components common. There is a table B which indicates each car and its components required with their quantities required to build the car. There is a record for each different car.

Now suppose there is a new car we are going to produce so we need a new record in table B for the car and all its components. Also we need a new field in table A because people can now order the new car(in some quantity).

With form for table B we can introduce a new record. But how can we add a field in table A automatically after a record is added in table B?

jinkx · May 12, 2015

Ok seems like a similar question is answered earlier.

But that still doesn't solve the original problem of how to represent the information or orders and components.

jdraw · May 12, 2015

Please step back from Access for the moment. Make a plan of what you are dealing with in business terms (simple English - no Access etc).
From your post, it is way too early to be talking Forms etc.

You are a Car manufacturer.
You have/receive Orders for Cars.
1 Order May be for 1 or Many Cars
1 Car may have 1 or Many Components.

You haven't said it, but it would be a Customer who makes the Order, right?
What exactly is a Component? Do you have some sample data?

Here are some data models that may help you with the requirements of your proposed database.

http://www.databaseanswers.org/data_models/vehicle_manufacturers/index.htm
http://www.databaseanswers.org/data_models/customers_and_car_parts/data_model_with_attributes.htm

For a tutorial on designing a database, tables and relationships, here is one that you should work through. It should take 30-45 minutes. There is a solution provided. You will learn if you work through the tutorial.
Good luck.

gemma-the-husky · May 12, 2015

you don't need a column/field in a table for each different car that might be bought.

you need a cars table
you need an orders table
you need an orderlines table to allow for an order having several cars on the order.

so the orders table orderno, date, customer
Order1, 12/5/15, AB Ltd

orderlines table is orderno, orderline, cartype, quantity
order1, 1, ModelA, 4
order1, 2, ModelB, 6
order1, 3, ModelC, 10

and your cars table is cartype, delails ...
ModelA, etc
ModelB, etc
ModelC, etc

now, to add another car model, you just add another row to the CARS table. No new fields are needed in any table at all

jinkx · May 12, 2015

That seems to be the perfect approach. But I see that there is some redundancy in the orderlines table about orderno getting repeated. Is there any way to remove the redundancy? I thought of normalisation of table but the number of cars may be in hundreds as well as the components that make up the cars may be in hundreds so it may not be possible to make that many tables.

It seems like hierarchical problem which can only be solved in Access by having some redundancy.

@jdraw: I was writing the specifications but then I got logged out and the post got deleted. Meanwhile there was another post which seemed to solve my query.

BlueIshDan · May 12, 2015

I had a system where we literally had everything in separate tables and linked together...

[ORDER] 1 -> N [Parts] N -> 1 [PART_LINK] 1 -> N [CARS]

Something like that maybe?

jinkx · May 12, 2015

Yeah that seems to be right way to solve the problem.

gemma-the-husky · May 12, 2015

jinkx said:
That seems to be the perfect approach. But I see that there is some redundancy in the orderlines table about orderno getting repeated. Is there any way to remove the redundancy? I thought of normalisation of table but the number of cars may be in hundreds as well as the components that make up the cars may be in hundreds so it may not be possible to make that many tables.

It seems like hierarchical problem which can only be solved in Access by having some redundancy.

@jdraw: I was writing the specifications but then I got logged out and the post got deleted. Meanwhile there was another post which seemed to solve my query.

The order number is not repeated in terms of being redundant. You need a way of linking the order to the order lines table - ie primary key in the orders table, which becomes the foreign key in the order lines table.

You can replace both with a numeric ID Ref, which is what we probably would do, but it doesn't change the logic. There is no redundancy involved. There are no repeating groups in the orderlines table.

jinkx · May 13, 2015

Oh so when we use a foreign key it is not actually repeated? I remember reading in some tutorial that its a pointer to the original table which has the details.

Ok so instead of writing the entire details regarding the primary key in the same table and wasting memory we only occupy the memory required by a pointer.

gemma-the-husky · May 13, 2015

a cross-reference has to exist in both tables

let's say you have a car record for a Ford Fiesta 1.8i, or it can simply be a numeric such as 2469 (assuming that ref 2469 identifies a Ford Fiesta 1.8i).

A numeric reference is more efficient than a string - especially as you may want to change the string at some point.

So the order(orderlines) table stores whatever is the reference appropriate for the linked item. either the string "Ford Fiesta 1.8i", or the 2469. That is PK/FK. The PK is the reference in the cars table. The FK is the same value in the order lines table. So for each PK there may be several rows with this value as an FK. This is a 1 to many relationship.

Any information held on the car record other than the PK (engine size, performance, etc) is not stored in the order line, as that can be determined from the car record. That would be redundant, if you stored that.

Where it gets complicated is in terms of what constitutes a "Ford Fiesta 1.8i" - and how you deal with model variations, such as year of manufacture, colour, fitted extras and so on. I imagine a motor dealer database is quite complicated.

Adding a new field when a new record is added in another table (1 Viewer)

jinkx

New member

jinkx

New member

jdraw

Super Moderator

gemma-the-husky

Super Moderator

jinkx

New member

BlueIshDan

☠

jinkx

New member

gemma-the-husky

Super Moderator

jinkx

New member

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread

Adding a new field when a new record is added in another table (1 Viewer)

New member

New member

Super Moderator

Super Moderator

New member

&#9760;

New member

Super Moderator

New member

Super Moderator

Similar threads

Users who are viewing this thread

☠