Building query with sub-queries

[StevoNZ]

Hi,

I'm creating a query (queries more like) to build data for upload into a SAP system. For a given material I require a row of data defined, and for the same material another row with one field changed, repeat...

Normally I would just create a query, copy it, make change, save as and then create a final query utilising the UNION function. While this may appear a poor way of defining my queries, it works & I'm generally happy with performance.

However, I have been given a new requirement which requires me to do this several times & therefore my UNION query would end up with 10 queries within... there must be a better way!

From my readings, I believe that creating sub-queries are the way to go... can anyone give me a 101 on sub-query creation (if this is in fact the best way to proceed)?

Sample of final data:
Matl Plant SLoc ...
0001 AB11 AB01 ...
0001 AB11 AB02 ...
0001 AB11 AB03 ...
0002 AB11 AB01 ...
0002 AB11 AB02 ...
0002 AB11 AB03 ...

The SLoc data is defined where if a Matl exists in Plant AB11, then create SLoc AB01, AB02, AB03 and so on.

Here is an example

SELECT [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, '0201' AS SLoc
FROM [Status 4 Materials - HIBE NZ02];

...

[StevoNZ]

Actually, I just worked this out myself...

SELECT * FROM
(SELECT [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, '0201' AS SLoc
FROM [Status 4 Materials - HIBE NZ02])
UNION
(SELECT [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, '0202' AS SLoc
FROM [Status 4 Materials - HIBE NZ02]);

But the real pain is when I then need to add other conditions, this doesn't work...

SELECT * FROM
(SELECT [Status 4 Materials - HIBE NZ02].MESC, [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, SLoc: 0201', IIf(IsNull([MRP_ND].[PARTNO]),Null,'001') AS Picking_area
FROM [Status 4 Materials - HIBE NZ02] LEFT JOIN MRP_ND ON [Status 4 Materials - HIBE NZ02].MESC = MRP_ND.PARTNO)
UNION
(SELECT [Status 4 Materials - HIBE NZ02].MESC, [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, SLoc: 0202', IIf(IsNull([MRP_ND].[PARTNO]),Null,'001') AS Picking_area
FROM [Status 4 Materials - HIBE NZ02] LEFT JOIN MRP_ND ON [Status 4 Materials - HIBE NZ02].MESC = MRP_ND.PARTNO)
UNION
(SELECT [Status 4 Materials - HIBE NZ02].MESC, [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, SLoc: 0203', IIf(IsNull([MRP_ND].[PARTNO]),Null,'001') AS Picking_area
FROM [Status 4 Materials - HIBE NZ02] LEFT JOIN MRP_ND ON [Status 4 Materials - HIBE NZ02].MESC = MRP_ND.PARTNO);

And I still need to work through other plants: [Status 4 Materials - HIBE NZ03].MESC, [Status 4 Materials - HIBE NZ04].MESC...

[MMcCarthy]

OK, I'm a little confused. Can we go back to the logic of what you are trying to do.

What is the structure of your table?

Here is an example of how to post table MetaData :
Table Name=tblBookings

 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Next can you explain again exactly what you want to achieve with your query from a logic point of view.