Combining data rows in MSAccess

I have a few tables in ms access that I need to condense as there are too many fields in the database. What have currently is something along the开发者_高级运维 lines of:

ID     Class     Date     
123    101       1/1/1111
123    202       2/2/2222
123    303       3/3/3333
456    111       4/4/4444
456    222       5/5/5555
456    333       6/6/6666

What I need the tables to look like is this:

ID     Class(1)  Date(1)    Class(2)    Date(2)    Class(3)    Class(3) ..... etc
123    101       1/1/1111   202         2/2/2222   303         3/3/3333 ..... etc
456    111       4/4/4444   222         5/5/5555   333         6/6/6666 ..... etc

There are not a standard number of entries by ID and some of the data may be repeated. Is there any way to do this?

Thank you for the response.

I think you got the examples backwards (what you have, and what you need).

The proper table format for this data would be vertical (like your first example), not horizontal. (If you want to display it horizontally, you can do that through a query for presentation purposes -- but the table shouldn't actually be structured that way.)

To convert your horizontal format, one way is like this:

INSERT INTO myNewTable
(ID, class, date)

SELECT
   ID,
   Class(1),
   Date(1)
FROM
   myOldTable
WHERE
   Class(1) IS NOT NULL

UNION

(ID, class, date)
SELECT
   ID,
   Class(2),
   Date(2)
FROM
   myOldTable
WHERE
   Class(2) IS NOT NULL

UNION

...

I'll give you an idea of how I might approach the problem... This isn't an abstractible / re-usable function type of approach -- just a practical, get-it-done kind of thing.

0) copy your old table for a backup.

1) give your original table a unique record identifier. Add a column in the table design called 'pk', click on the little key button. Make sure it populates with an auto-incremented integer after you save.

2) create your new table, with columns: pk, id, class1, date1, class2, date2, ... , classn, daten. You can do a

select id, count(class) from table order by count(class) desc.

for a quick look at how many fields you need to create.

2) do insert # 1

Insert into newtable (pk, id, class1, date1) 
select min(pk) id, class, date from oldtable

3) do delete # 1

Delete from oldtable
where pk in(select pk from newtable)

4) do update #1

Update newtable a
set a.pk = b.pk, a.class2 = b.class, a.date2 = b.date
FROM (select min(pk) pk, id, class, date from oldtable) b

5) do delete # 2 (repeat step 3)

6) do update # 2 (repeat step 4, but change the 2's to 3's). I.e.:

Update newtable a
set a.pk = b.pk, a.class3 = b.class, a.date3 = b.date
FROM (select min(pk) pk, id, class, date from oldtable) b

...and so on until there are no more records left in your old table.

Like I said -- this isn't re-usable, you have to modify it on every loop, etc. If you know how to use ADO recordsets, etc., you could convert this into something more abstract / re-usable, but it doesn't sound like you're going to use it more than once.

good luck.