Create/Update a new Table automatically, based on two other Tables

[BobbyBull]

Hi,



I am fairly new to Access, so please bare with me.

I created two tables, let's refer to them as Cars (VW, BMW and Audi) and Colours (White, Black and Grey).

Is it possible to create another table based on these tables - i.e. in the new table the rows will be the Cars and the columns the Colours as such:

	White	Black	Grey
VW
BMW
Audi

And should I enter another Car or Colour in one of the first mentioned tables, then I would like this "new" table to update automatically. For example, if I have a new Car (say, Merc), then I would like the "new" table to update to the following:

	White	Black	Grey
VW
BMW
Audi
Merc

I hope this makes sense...

Looking to hear from you!

[ranman256]

No, your master table should be:

MAKE	COLOR
BMW	WHITE
Audi	BLACK
BMW	BLUE
Audi	WHITE

then build a crosstab query to produce the ones you showed..
car, color, color ,color

[BobbyBull]

Hey Ranman256,
thanks for the reply.

the challange i have is that i am working with 5000 "cars" and 600 "colours". once a new colour is added, all existing cars can be ordered in the new coloúr. same for the car, if a new car is added, it can be ordered in all colours. i was hoping that i dont need to go throught the tedious job of adding a new car and having to copy and paste the line 600 times with all the different colours.

the perfect solution will be that i have 2 table (one for cars and one for colours) and a seperate table where a combination list is created (in this case 600 x 5000 = 3,2m records), and once a new car is added, the table will automatically create the new combinations (600 records). same for when i new colour is added in its table, then the combination table will automatically add the 5000 new combinations.

this might be trickier than i thought