MS Access SQl Loops

[cpc]

Good day all, just a relative beginner here with Access. Checked out a number of sites but was unable to find a definitve answer to my issue (that is my Access issue). Below is a sample of my code:

SELECT [Employee Name] as TRAINED, 0 as TRNG,[Mod1].[0] AS DATA FROM [Mod1]
UNION ALL
SELECT [Employee Name] as TRAINED, 1 as TRNG,[Mod1].[1] AS DATA FROM [Mod1]
UNION ALL
SELECT [Employee Name] as TRAINED, 2 as TRNG,[Mod1].[2] AS DATA FROM [Mod1]
UNION ALL
SELECT [Employee Name] as TRAINED, 3 as TRNG,[Mod1].[3] AS DATA FROM [Mod1]
UNION ALL
SELECT [Employee Name] as TRAINED, 4 as TRNG,[Mod1].[4] AS DATA FROM [Mod1]
UNION ALL
SELECT [Employee Name] as TRAINED, 5 as TRNG,[Mod1].[5] AS DATA FROM [Mod1]
UNION ALL
SELECT [Employee Name] as TRAINED, 6 as TRNG,[Mod1].[6] AS DATA FROM [Mod1]
UNION ALL
SELECT [Employee Name] as TRAINED, 7 as TRNG,[Mod1].[7] AS DATA FROM [Mod1]
........

As you can see it can be extensively long. To complete the program I need to iterate to the maximum allowable field (ie 255). The code will only allow me to go about 45 statements before I get the "too complex" message. A loop makes sense, but I am not sure how to implement.

Appreciate the expertise!!
cpc

 

[boblarson]

The key would be to fix your table structure. What you currently have looks like it is in serious need of fixing.

 

[cpc]

The key would be to fix your table structure. What you currently have looks like it is in serious need of fixing.

You said it, serious fixing! Actually the data came from an excel spreadsheet that the company just kept adding on to and now realize that they were using the wrong approach. The original spreadsheet had over 450 columns! I had to cut it in half. Not being familiar with SQL, I did not know if a FOR..NEXT statement was available in SQL. I guess I need to go back and see how to cut the spreadsheet some more:banghead:

Thank you!

 

[cpc]

Pat, You're right! This came from a huge excel spreadsheet and my code was an attempt to convert the fields into a normalized table. It really is a basic table with names for rows, trained modules for columns, dates for data. I need to dig deeper on how to use make table and append queries to normalize this data some more. Appreciate the direction.