Append Multiple Fields to one table field

[razchip]

I have a table that has 10 separate fields, I need to combine them into one field. I have not had any luck getting the UNION query to work.

INSERT INTO 698Engraving ( [Sequence No], Cycle, [Truck Route], [Pack No], [Pack Qty], [Pack Sort] )
SELECT Cop698D.[Sequence No], Cop698D.Cycle, Cop698D.[Truck Route], Cop698D.[Pack No 1], Cop698D.[Pack Qty 1], Cop698D.[Pack Sort 1]
FROM Cop698D;

The 2nd field would be Pack No 2...and so on.

Can I even do these through a query or do I have to develop a module looping through each record (not very good at VBA)?

Thanks for the help.
Greg

 

[dhookom]

10 separate fields"? I only see 6. Are you actually combining values into one field? Are you attempting to create a normalizing union query?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Sorry, I hit submit by accident without provided a guess at the SQL you need:

INSERT INTO 698Engraving ( [Sequence No], Cycle, [Truck Route], [Pack No], [Pack Qty], [Pack Sort] )
SELECT [Sequence No],Cycle, [Truck Route], [Pack No 1], [Pack Qty 1], [Pack Sort 1]
FROM Cop698D
UNION ALL
SELECT [Sequence No],Cycle, [Truck Route], [Pack No 2], [Pack Qty 2], [Pack Sort 2]
FROM Cop698D
UNION ALL
SELECT [Sequence No],Cycle, [Truck Route], [Pack No 3], [Pack Qty 3], [Pack Sort 3]
FROM Cop698D;

Duane
Hook'D on Access
MS Access MVP

 

[razchip]

I need a table or query resulting in Seq, TruckRt, PackNo, PackQty and PackSort

The table has multiple fields
Seq, Truck Route,[Pack No 1],[Pack Qty 1],[Pack Sort 1], [Pack No 2],[Pack Qty 2],[Pack Sort 2],[Pack No 3],[Pack Qty 3],[Pack Sort 3] Through [Pack No 10],[Pack Qty 10],[Pack Sort 10]

I want to combine all the Pack No #'s into one field along with their corresponding Qty and Sort.

Thanks for the help.
Greg

 

[dhookom]

Do you want one field in one record or does the union query work for you?

If the union query isn't what you want, you may need to provide some sample records and the desired output. You don't need to include unimportant fields.

I assume you understand the Cop698D table seems very un-normalized and that is what you are attempting to correct.

Duane
Hook'D on Access
MS Access MVP

 

[razchip]

SeqNo Cycle Truck PackNo Pack Pac
Route Qty Sort
0000098 207 FN 831 1 R
0000098 207 FN 7625 1 R
0000098 207 FN 4203 1 R
0000098 207 FN 7509 2 P

This is what I'm looking for. The Union All did not work, it said there was invalid syntax with the From clause.

Thanks for the help.
Greg

 

[dhookom]

You may need to first create the union query and save it. Then create the append query based on the saved union query. If this doesn't work, come back with the SQL you used.

If you need the "field set number", you can use:

SELECT 1 as FieldSet, [Sequence No],Cycle, [Truck Route], [Pack No 1], [Pack Qty 1], [Pack Sort 1]
FROM Cop698D
UNION ALL
SELECT 2, [Sequence No],Cycle, [Truck Route], [Pack No 2], [Pack Qty 2], [Pack Sort 2]
FROM Cop698D
UNION ALL
SELECT 3, [Sequence No],Cycle, [Truck Route], [Pack No 3], [Pack Qty 3], [Pack Sort 3]
FROM Cop698D
--- etc ---
;

Duane
Hook'D on Access
MS Access MVP

 

[razchip]

Thanks, this worked great, I appreciate the help.

Thanks for the help.
Greg