Select query

[sarsmelt]

I have a database with 2 tables


SAP_Data
Old_MM WI_1 WI_2 ..... WI_15
12345 1 2
23456 1 10
34567 2 3
WI
ID WI
1 Clean
2 take to lab
3 Filter
.
.
.
What I need to do is make a query that selects the WI based on the number in WI_1, WI_2... and have it be in the right order.
I've come up with a select & union query

SELECT SAP_Data.Old_MM, WI.WI
FROM WI, SAP_Data
WHERE (((WI.ID)=[SAP_Data].[WI_1]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_2]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_3]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_4]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_5]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_6]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_7]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_8]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_9]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_10]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_11]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_12]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_13]))
UNION ALL Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_14]))
UNION Select SAP_Data.Old_MM, WI.WI
From WI, SAP_Data
Where (((WI.ID)=[SAP_Data].[WI_15]));

This does work but I can't for the life of me get it in the correct order. It all comes out seemingly random.
I've tried doing them all in separate queries but when I add them together it won't show the ones that are null.

If there is a better way to do this please let me know. I'm new to access and know very little SQL or VBA. I tried to upload the database but it's way too big.
Thanks, Sarah

[ranman256]

The table should have been designed as
[Old_MM], [WI]

This way the queries would work in all fields at once.

Have you tried importing the data into its own linked table? That way its separated from the main program, and will only contain that 1 table.
Ive done this and it held 15 million records.
Or is your data still too large for this?

[sarsmelt]

I see what you're saying about the design but I didn't design it that way so that it would be easier for the end user to edit (I have some pretty Access-phobic people using it). This way when they look at the table all of the info is in one line instead of multiple.
I'm not worried about the database being too big in the end. I just can't upload it here because it's 2560 KB and this website only supports up to 500 KB.

[JoeM]

I see what you're saying about the design but I didn't design it that way so that it would be easier for the end user to edit (I have some pretty Access-phobic people using it). This way when they look at the table all of the info is in one line instead of multiple.

Your design violates the rules of normalization (see: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf). When you do that in a relational database setting, it can make very simple task into very complex ones (as you are discovering). I know, because I have been there myself!