Moving data from one table to another

[David Marks]

I need to move data from Table 1 to Table 2 and can't figure out how to do
it. Perhaps someone can help me.

Table 1: has 3 fields

DealerID Car Color
1 Buick Red
1 Buick Blue
1 Buick Green
1 Chevy Black
1 Chevy Blue
1 BMW Blue
1 BMW Red
2 Buick Green
2 Chevy Red
2 Chevy Green
2 BMW Red


Table 2: Has 17 Fields. They are
DealerID
Buick1
Buick2
Buick3
Buick4
Chevy1
Chevy2
Chevy3
Chevy4
BMW1
BMW2
BMW3
BMW4

What I need to do is, either with a query, or in vba, take the data from
table 1 and move it to table 2 such that in Record 1
DealerID 1
Buick1 Red
Buick2 Blue
Buick3 Green
Buick4 NULL
Chevy1 Black
Chevy2 Blue
Chevy3 NULL
Chevy4 NULL
BMW1 Blue
BMW2 Red
BMW3 NULL
BMW4 NULL

Then in record 2
DealerID 2
Buick1 Green
Buick2 NULL
Buick3 NULL
Buick4 NULL
Chevy1 Red
Chevy2 Green
Chevy3 NULL
Chevy4 NULL
BMW1 Red
BMW2 NULL
BMW3 NULL
BMW4 NULL

Is there a way to do this? Notice that the field names of Table 2 correspond
with the car names from table 1, with a number added to the car name to make
each field name different.

Thanks in advance.
David Marks

 

[Frank]

What you want can be done ... but should you want to do it?

Yor table design seems inflexible ... what, for example, happens if your
"Table 1" has FIVE Buicks?

May I ask the meaning of your data and the purpose of your application?

 

[David Marks]

I am trying to get data for a particular Dealer all into one record so that I
can use that record (along with data from another table - which will be
joined in a query) as a record to merge for mailmerge into WORD.