Need to convert multiple columns into 1 colum in MS Access table

  • Thread starter Thread starter Java
  • Start date Start date
J

Java

Hi,
I have a table which has dates & quantity going across as columns. I need
to get the dates in only 1 column & the dates in another column. How do I do
this in vba?

Here is a sample:
ID Fname Lname ProductID Order Date 1 Order Qty 1
100 William Smith 4/9 8
102 N West 4/9 8

Order Date 2 Order Qty 2 Order Date 3 Order Qty 3
4/9 8
Order Date 4 Order Qty 4 Order Date 5 Order Qty 5


I need the table to be in this format:
ID Fname Lname ProductID Date Quantity

What is the easiest way to do this?

Thanks!
 
Try a Union query:

SELECT ID, FName, LName, ProductID, Date1 AS WhatDate, Qty1 AS Quantity
FROM MyTable
WHERE Qty1 IS NOT NULL
UNION ALL
SELECT ID, FName, LName, ProductID, Date2, Qty2
FROM MyTable
WHERE Qty2 IS NOT NULL
UNION ALL
SELECT ID, FName, LName, ProductID, Date3, Qty3
FROM MyTable
WHERE Qty3 IS NOT NULL
UNION ALL
SELECT ID, FName, LName, ProductID, Date4, Qty4
FROM MyTable
WHERE Qty4 IS NOT NULL
UNION ALL
SELECT ID, FName, LName, ProductID, Date5, Qty5
FROM MyTable
WHERE Qty5 IS NOT NULL

Note that I renamed your field "WhatDate". Date is a reserved word, and you
should never use reserved words for your own purposes. For a comprehensive
list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html`
 
Java said:
I have a table which has dates & quantity going across as columns. I need
to get the dates in only 1 column & the dates in another column. How do I do
this in vba?

Here is a sample:
ID Fname Lname ProductID Order Date 1 Order Qty 1
100 William Smith 4/9 8
102 N West 4/9 8

Order Date 2 Order Qty 2 Order Date 3 Order Qty 3
4/9 8
Order Date 4 Order Qty 4 Order Date 5 Order Qty 5


I need the table to be in this format:
ID Fname Lname ProductID Date Quantity

What is the easiest way to do this?
Click to expand...


Tge easiest way would have been to normalize the table
before loading it up with date. Over the long haul, that's
still the best thing to do.

For a limited, quick and dirty solution, you can create a
UNION query to get around the immediate problem:

SELECT ID, Fname, Lname, ProductID,
[Order Date 1] As OrderDate,
[Order Qty 1] As OrderQty
FROM table
UNION ALL
SELECT ID, Fname, Lname, ProductID,
[Order Date 2], [Order Qty 2]
FROM table
UNION ALL
SELECT ID, Fname, Lname, ProductID,
[Order Date 3], [Order Qty 3]
FROM table
UNION ALL
. . .
 
Hi,
Thank you so very much!!! :) That was awesome! I would not have thought
to use a union query. I was thinking of doing it in vba using a loop to
write to the table. This is so much easier. You made my day!
 
Back
Top