Query to flip my table sideways (Crosstab query?)

[DrPat]

OK. Chalk this up to "I wish I knew then....."

My original DB tracked customer orders and, being a fresh Access newbie then (as opposed to a seasoned newbie, now), I put in 20 order fields on each customer's record (10 for date, 10 for item#), hoping one day I'd sell 10 orders to the same guy.

My DB skills have not out-paced business growth and now my staff is stuck deleting old orders on the customer form to add the new ones.

So my new DB is done properly with a separate customer and orders tables (order#(PK), date, CustID, Item#)

I don't want to tell one of my staff to repopulate these records by hand.

Is there a query that can turn......

Record ---> CustID, Date1, Item1, Date2, Item2, Date3, Item3, etc.

into

Record ---> Order#, CustID, Item, Date

for each occurrence of Date1, Item1???

In other words, can I write a query that creates a new record appended to the new orders table for each Date and Item in a record. And do it all at once for all my customers?

Thanks,
/Pat

 

[CJ_London]

Providing your fields all end as you have described you can create a query in vba to do this - change names to suit:

For i=1 to 10
    currentdb.execute("INSERT INTO NewTable (customerid, OrderDate,ItemNo) SELECT customerid, orderdate" & i & ", item" & i & " FROM myTable WHERE orderdate" & i & " is not null")
next i

 

[DrPat]

Thanks so much CJ. Now if I may impose on your expertise, can you explain (or provide a link that explains) how to create and then run a query in VBA? My code experience is minimal. Thanks again.

 

[CJ_London]

I would do the following:

Create a new form
Add a button to the form - cancelling out when prompted for more information
if you cannot see the properties, right click on the button control and select properties
choose the events tab
choose the onclick event and click on the 3 dot caret to the right
choose code builder and you will go into the vba screen where you will see something like

Private Sub Command0_Click()
 
End Sub

Copy and paste the code I provided between these two lines so you get this

Private Sub Command0_Click()
For i=1 to 10
    currentdb.execute("INSERT INTO [COLOR=red]NewTable[/COLOR] ([COLOR=red]customerid[/COLOR], [COLOR=red]OrderDate[/COLOR], [COLOR=red]ItemNo[/COLOR]) SELECT[COLOR=red] customerid[/COLOR], [COLOR=red]orderdate[/COLOR]" & i & ", [COLOR=red]item[/COLOR]" & i & " FROM [COLOR=red]myTabl[/COLOR]e WHERE [COLOR=red]orderdate[/COLOR]" & i & " is not null")
next i
End Sub

Now change all the bits in red for the names of the tables and fields you actually have

Finally open the form and click on the button

 

[DrPat]

You know what? I could've (should've) figured that out for myself....But it would've taken 3 weeks.

Thanks TONS!

 

[CJ_London]

no problem