query by part

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi,

I have a table (download of data so have no control over structure) that
looks like this:

ProductName Comp1 Comp1Qty Comp2 Comp2Qty ..... Comp32
ABC XYZ 2 VWY 5
LMN 6

How can I change this so that I get table that looks like this

ProductName Component Qty Seq**
ABC XYZ 2 1
ABC VWY 5 2
ABC LMN 6 3

**NB Seq would be a sequential number starting at 1 for each ProductName
and incremented by 1 for each record of the same ProductName


Thanks
 
Newbie said:
I have a table (download of data so have no control over structure) that
looks like this:

ProductName Comp1 Comp1Qty Comp2 Comp2Qty ..... Comp32
ABC XYZ 2 VWY 5
LMN 6

How can I change this so that I get table that looks like this

ProductName Component Qty Seq**
ABC XYZ 2 1
ABC VWY 5 2
ABC LMN 6 3

**NB Seq would be a sequential number starting at 1 for each ProductName
and incremented by 1 for each record of the same ProductName
Click to expand...


Assuming you want t use a query:

SELECT ProductName, Comp1, comp1Qty, 1 As Seq
UNION ALL
SELECT ProductName, Comp2, comp2Qty, 2 As Seq
UNION ALL
SELECT ProductName, Comp3, comp3Qty, 3 As Seq
UNION ALL
. . .
 
Thanks. How would I do this in code?
Marshall Barton said:
Assuming you want t use a query:

SELECT ProductName, Comp1, comp1Qty, 1 As Seq
UNION ALL
SELECT ProductName, Comp2, comp2Qty, 2 As Seq
UNION ALL
SELECT ProductName, Comp3, comp3Qty, 3 As Seq
UNION ALL
. . .
Click to expand...
 
First, create the query and make sure it selects the right
data the way you want.

Then use code to run a make table query:

strSQL = "SELECT * INTO newtable FROM query1"
Set db = CurrentDb()
db.Execute strSQL
msgbox db.RecordsAffected & " records were added"
Set db = Nothing
 
Back
Top