Advanced Queries and Macros - Looking for a shortcut

[jlgray0127]

I have another question!
In my database, I have a table that has 120 unique fields that represent part numbers in a rack. I need to take this data and put it into another table where I can have Ea of the unique fields on a unique record.

I am attaching a word doc with samples of the Data Entry table, and the table I am creating with the Data Conversion Table.

The only thing I know to do, is to look at one record, and creat 120 append queries that add Field A1 to the table, then append query 2 to add A2 to the Table, ect... I hope the spreadsheet explains what I am trying to do.

I appreciate any help I can get. Even if it's "your crazy. Seek medical treatment!" lol

Thank you!

[pbaldy]

A UNION query can achieve that result for you.

[jlgray0127]

A UNION query can achieve that result for you.

Can you show me a sample of what you mean from the file I had attached? I am not efficient with Union Queries and am unsure how it would work with one table in the manner I demonstrated. Thank you very much for your help!

[pbaldy]

SELECT LogisticsPlanningEngineer, A1 AS Part, "A1" AS Quant
FROM TableName
UNION ALL
SELECT LogisticsPlanningEngineer, A2 AS Part, "A2" AS Quant
FROM TableName

[jlgray0127]

I did this, but there are 2 issues. I am doing it in 4 seperate queries because there is a limit to the Union Query. I got an error stating Query is to complex, so I had to break it down into different stages. Below is a sample of the SQL Statement.
The first one works, but the 3 that I did after, do not. The only thing that is different, are the fields. I went through all 3 of them and compared to the first one.
Any idea what is going on? All 4 queries are like this, A1 through A50, then A51 through A100, A101 through A150, and A 151 through A200....

SELECT [Logistics Planning Engineer],[Date and Time], [Old Location], [Location], [ILC Location], [PSA], A1 AS Part, "A1" AS Quant
FROM [Locations and Parts Racks]
UNION ALL
SELECT [Logistics Planning Engineer],[Date and Time], [Old Location], [Location], [ILC Location], [PSA], A2 AS Part, "A2" AS Quant
FROM [Locations and Parts Racks]
UNION ALL
SELECT [Logistics Planning Engineer],[Date and Time], [Old Location], [Location], [ILC Location], [PSA], A3 AS Part, "A3" AS Quant
FROM [Locations and Parts Racks]
UNION ALL
SELECT [Logistics Planning Engineer],[Date and Time], [Old Location], [Location], [ILC Location], [PSA], A4 AS Part, "A4" AS Quant
FROM [Locations and Parts Racks]

[pbaldy]

You're saying the 1-50 query works, and the others don't? I can't see why offhand. Can you post a sample db or something?

[jlgray0127]

Sure... please see attached... I am getting an error in the sample, which I did not get yesterday, in the original DBA.

Syntax error - incomplete clause

[pbaldy]

Each of the other 3 has a line with a period in it:

FROM [Locations and Parts Racks].

That period is causing the error. It's down near the bottom, like 144.

[jlgray0127]

Thank you, Thank you, THANK YOU! I did not see that and was ready to start deleting or just throw the whole pc through the window! Thank you!

[pbaldy]

LOL! Sometimes that's what they deserve; to be thrown out the window. Glad we got it sorted out.