Query with Counter from multiple tables

[dmenger]

Hi, All
New to SQL so I clueless on how to perform simple loop query
Here is my problem.

I have three tables StNum, OpDs,WiNum.


StNum is the parent table with 1 to infinite relationships to the other two tables.

All Tables have columns such as
StId,StNum,OpNum1,OpNum2,OpNum3, Etc.
OpId,StNum,OpDs1,OpDs2,OpDs3, Etc.
WiId,StNum,WiNum1,WiNum2,WiNum3, Etc.

What I want is a query that will query the three tables to produce a record for each column listed by number. For example

Query STNum = 1 ( From Combox Box in a Form labeled "QST")
Produce a table with records
OpNum1,OpDs1,WiNum1
OpNum2,OpDs2,WiNum2
OpNum3,OpDs3,WiNum3

[June7]

The real issue here is a non-normalized data structure. The output you want is a normalized structure.

A UNION query can rearrange the data. There is no wizard for UNION query, must type or copy/paste into SQL View of query builder.

SELECT 1 AS LinkID, StNum, StId, "Num1" AS Category, OpNum1 AS Data FROM StNum
UNION SELECT 2, StNum, StId, "Num2", OpNum2 FROM StNum
UNION SELECT 3, StNum, StId, "Num3", OpNum3 FROM StNum;

Do similar UNION queries for the other 2 tables.

Then do a 4th query that joins all 3 UNION queries on the LinkID fields. Apply the combobox filter criteria to this query.

[dmenger]

Well the reason for the non normal structure is that if I had the normalized structure I would require more than the amount of fields allowable. I'm open to other suggestions this is just the way I figured was best to get around the field limit.

The real issue here is a non-normalized data structure. The output you want is a normalized structure.

A UNION query can rearrange the data. There is no wizard for UNION query, must type or copy/paste into SQL View of query builder.

SELECT 1 AS LinkID, StNum, StId, "Num1" AS Category, OpNum1 AS Data FROM StNum
UNION SELECT 2, StNum, StId, "Num2", OpNum2 FROM StNum
UNION SELECT 3, StNum, StId, "Num3", OpNum3 FROM StNum;

Do similar UNION queries for the other 2 tables.

Then do a 4th query that joins all 3 UNION queries on the LinkID fields. Apply the combobox filter criteria to this query.

[June7]

I have never known a normalized structure to utilize the limit of allowable fields for a table/query.

One indication of a non-normalized structure is multiple like-name fields, which is what I see with your example.