Reformat table columns into rows

[tygger719]

Here is the basis of what I am trying to do. I have a table with 11 fields. The first field is an ID field the others are date related fields. What I want to do is to create a new row for every date field.

Example:

TABLE
------
ID dt1 dt2 dt3 dt4 dt5 dt6 ...... dt10
1 1/5 3/6 4/9 5/8 6/29 7/17

QUERY Result
------

ID Date
1 1/5
1 3/6
1 4/9
1 5/8
1 6/29
1 7/17


If you have any suggestions on how to accomplish please HELP!

 

[The_Doc_Man]

The first field is an ID field the others are date related fields.

This is not a normalized table. You have what is called a "repeating group" in your table. Read up on normalization. First, search this forum for keyword "Normalization." Second, search the web for "Database Normalization" (and be prepared for about a gazillion hits on that one.) Pick some reputable-looking university-based web sites and download the articles. At least some of them are pretty easy to read.

Once you have done that, split your table according to normalization rules. Once you do that, you can create rows or columns of your data pretty much at will through appropriately shaped queries.

Excel could do this pretty quickly, but Access ain't Excel. Your DB shows Excel-class thinking, which is to say, flat representations. Which Access is not limited to. Once you break out of the flat-file mode, you can do pretty much whatever you need. Until then, you can't get there from here.