Creating many instances of a single record.

[davidrsutton]

Hi...

I have a table that is organised like so:

ID Step1 Step2 Step3 Step4..... Step 9
234 13/10/05 20/10/05 25/10/05

where one row is returned for Each 'ID'. the Problem is, I need to display this info so that a row is returned for for each instance that a 'Step' is completed. So if a record has 3 of its 9 steps filled in, the query would return something like:

ID Step
234 13/10/05
234 20/10/05
234 25/10/05... and so on.....
235 13/10/05
235 20/10/05
236 13/10/05

I am using Access 97.

Any help is greatly appreciated.

Dave.

 

[PHV]

Have a look at Union query:
SELECT ID, Step1 AS Step FROM yourTable WHERE Step1 Is Not Null
UNION SELECT ID, Step2 FROM yourTable WHERE Step2 Is Not Null
...
UNION SELECT ID, Step9 FROM yourTable WHERE Step9 Is Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidrsutton]

Thanks for that PH...

Here is my real SQL

SELECT WorkerID, 1d AS Step FROM tblclientcm WHERE 1d <>0
UNION SELECT WorkerID, 2e FROM tblclientcm WHERE 2e <>0
UNION SELECT WorkerID, 3e FROM tblclientcm WHERE 3e <>0
UNION SELECT WorkerID, 4e FROM tblclientcm WHERE 4e <>0
UNION SELECT WorkerID, 5e FROM tblclientcm WHERE 5e <>0
UNION SELECT WorkerID, 6e FROM tblclientcm WHERE 6e <>0
UNION SELECT WorkerID, 7e FROM tblclientcm WHERE 7e <>0
UNION SELECT WorkerID, 8e FROM tblclientcm WHERE 8e <>0

but when I try to execute it it tells me there is a "Syntax error in query expression '1d'"

Any ideas? by the way... fields 1d, 2e, 3e, etc.. are all YEs/No fields

Many thanks in advance...

Dave.

 

[r937]

SELECT WorkerID, [1d] ... WHERE [1d]<>0 ...

and you might want to consider UNION ALL instead of UNION



r937.com | rudy.ca

 

[davidrsutton]

Legend!!!! cheers.