Embedding query within another query 2

[TimGoff]

Hi there,

I would like to embed to SQL from query 5 into query 6 as I need to embed the SQL in code outside of access. Would anybody be able to tell me what the combined string would be please?

Query 5:

SELECT tblScheduleDataForExport.HouseNumber, dbo_Prog_Versions.Version_Sys_Id, Max(dbo_Prog_Parts.Part_Number) AS MaxOfPart_Number
FROM tblScheduleDataForExport INNER JOIN (dbo_Programmes INNER JOIN ((dbo_Prog_Patterns INNER JOIN dbo_Prog_Versions ON dbo_Prog_Patterns.Version_Sys_Id = dbo_Prog_Versions.Version_Sys_Id) INNER JOIN dbo_Prog_Parts ON dbo_Prog_Patterns.Pattern_Sys_Id = dbo_Prog_Parts.Pattern_Sys_Id) ON dbo_Programmes.Programme_Sys_Id = dbo_Prog_Versions.Programme_Sys_Id) ON tblScheduleDataForExport.HouseNumber = dbo_Programmes.Programme_Id_No
GROUP BY tblScheduleDataForExport.HouseNumber, dbo_Prog_Versions.Version_Sys_Id;

Query 6:

SELECT Query5.HouseNumber, dbo_Prog_Parts.TimeCode_Out, dbo_Prog_Parts.Part_Number
FROM ((Query5 INNER JOIN dbo_Prog_Versions ON Query5.Version_Sys_Id = dbo_Prog_Versions.Version_Sys_Id) INNER JOIN dbo_Prog_Patterns ON dbo_Prog_Versions.Version_Sys_Id = dbo_Prog_Patterns.Version_Sys_Id) INNER JOIN dbo_Prog_Parts ON (Query5.MaxOfPart_Number = dbo_Prog_Parts.Part_Number) AND (dbo_Prog_Patterns.Pattern_Sys_Id = dbo_Prog_Parts.Pattern_Sys_Id);

Thanks in advance
Tim

 

[SkipVought]

Tim,

How do you want to use the embedded query? Are the values joined in any way? Is it a UNION ie

Select * From Query5 
UNION 
Select * From Query6

???

Sometimes, when only a few columns are returnes an embedded query can be used in the Where Clause to return a subset of values.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[TimGoff]

Hi Skip

I want to pass one of the values, dbo_Prog_Parts.TimeCode_Out, to a function - the return of which will be updated to a table

 

[SkipVought]

Well both querys return 3 values???

Please be clear what you intend to do!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[TimGoff]

OK ! What I want to do is....

For each tblScheduleDataForExport.HouseNumber I want to return the dbo_Prog_Parts.TimeCode_Out for the last part - ie max(dbo_Prog_Parts.Part_Number)

This SQL returns the Max part number, but if I can in dbo_Prog_Parts.TimeCode_Out it returns multiple Max parts - rather than just the one, which i expected it to do...

SELECT tblScheduleDataForExport.HouseNumber, Max(dbo_Prog_Parts.Part_Number) AS MaxOfPart_Number
FROM tblScheduleDataForExport INNER JOIN (dbo_Programmes INNER JOIN ((dbo_Prog_Patterns INNER JOIN dbo_Prog_Versions ON dbo_Prog_Patterns.Version_Sys_Id = dbo_Prog_Versions.Version_Sys_Id) INNER JOIN dbo_Prog_Parts ON dbo_Prog_Patterns.Pattern_Sys_Id = dbo_Prog_Parts.Pattern_Sys_Id) ON dbo_Programmes.Programme_Sys_Id = dbo_Prog_Versions.Programme_Sys_Id) ON tblScheduleDataForExport.HouseNumber = dbo_Programmes.Programme_Id_No
GROUP BY tblScheduleDataForExport.HouseNumber;

 

[SkipVought]

MAX is a arithmetic function.

I'd venture to say that PartNumber is a STRING. I'd use FIRST or LAST and ORDER BY to sort the recordset.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[TimGoff]

Hi Skip....part_number is a number...

 

[SkipVought]

The field is defined as NUMERIC? Or it contains numeric characters? I've worked in manufacturing systems for years and often fields that are referred to as x-number or contain numeric characters, are really Text fields restricted to numeric characters. How/why would you do arithmetic operations on a PartNumber?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[TimGoff]

Yes, definitely defined as numeric (as an Integer).
I need to find the last part for each HouseNumber - so Max seemed the easiest function to use...

 

[PHV]

Quick and dirty, have you tried this (untested) ?
SELECT Query5.HouseNumber, dbo_Prog_Parts.TimeCode_Out, dbo_Prog_Parts.Part_Number
FROM (((
SELECT tblScheduleDataForExport.HouseNumber, dbo_Prog_Versions.Version_Sys_Id, Max(dbo_Prog_Parts.Part_Number) AS MaxOfPart_Number
FROM tblScheduleDataForExport
INNER JOIN (dbo_Programmes INNER JOIN ((dbo_Prog_Patterns INNER JOIN dbo_Prog_Versions ON dbo_Prog_Patterns.Version_Sys_Id = dbo_Prog_Versions.Version_Sys_Id) INNER JOIN dbo_Prog_Parts ON dbo_Prog_Patterns.Pattern_Sys_Id = dbo_Prog_Parts.Pattern_Sys_Id) ON dbo_Programmes.Programme_Sys_Id = dbo_Prog_Versions.Programme_Sys_Id) ON tblScheduleDataForExport.HouseNumber = dbo_Programmes.Programme_Id_No
GROUP BY tblScheduleDataForExport.HouseNumber, dbo_Prog_Versions.Version_Sys_Id
) Query5
INNER JOIN dbo_Prog_Versions ON Query5.Version_Sys_Id=dbo_Prog_Versions.Version_Sys_Id)
INNER JOIN dbo_Prog_Patterns ON dbo_Prog_Versions.Version_Sys_Id=dbo_Prog_Patterns.Version_Sys_Id)
INNER JOIN dbo_Prog_Parts ON Query5.MaxOfPart_Number=dbo_Prog_Parts.Part_Number AND dbo_Prog_Patterns.Pattern_Sys_Id=dbo_Prog_Parts.Pattern_Sys_Id
;

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

 

[TimGoff]

Hi PH

Thanks for your post, it works perfectly...but is there anyway of replacing Query5 with the actual SQL?

 

[PHV]

but is there anyway of replacing Query5 with the actual SQL
???
In my post, query5 is just an alias ...
For the proof, try this (no more query5 anywhere):
SELECT G.HouseNumber, dbo_Prog_Parts.TimeCode_Out, dbo_Prog_Parts.Part_Number
FROM (((
SELECT tblScheduleDataForExport.HouseNumber, dbo_Prog_Versions.Version_Sys_Id, Max(dbo_Prog_Parts.Part_Number) AS MaxOfPart_Number
FROM tblScheduleDataForExport
INNER JOIN (dbo_Programmes INNER JOIN ((dbo_Prog_Patterns INNER JOIN dbo_Prog_Versions ON dbo_Prog_Patterns.Version_Sys_Id = dbo_Prog_Versions.Version_Sys_Id) INNER JOIN dbo_Prog_Parts ON dbo_Prog_Patterns.Pattern_Sys_Id = dbo_Prog_Parts.Pattern_Sys_Id) ON dbo_Programmes.Programme_Sys_Id = dbo_Prog_Versions.Programme_Sys_Id) ON tblScheduleDataForExport.HouseNumber = dbo_Programmes.Programme_Id_No
GROUP BY tblScheduleDataForExport.HouseNumber, dbo_Prog_Versions.Version_Sys_Id
) G
INNER JOIN dbo_Prog_Versions ON G.Version_Sys_Id=dbo_Prog_Versions.Version_Sys_Id)
INNER JOIN dbo_Prog_Patterns ON dbo_Prog_Versions.Version_Sys_Id=dbo_Prog_Patterns.Version_Sys_Id)
INNER JOIN dbo_Prog_Parts ON G.MaxOfPart_Number=dbo_Prog_Parts.Part_Number AND dbo_Prog_Patterns.Pattern_Sys_Id=dbo_Prog_Parts.Pattern_Sys_Id
;

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

 

[TimGoff]

Oh I see now!
Superb - thanks very much
Can I ask how the alias fits into the SQL - i would of thought you needed to present it SELECT ... AS G ..or something like that?

 

[PHV]

The basic idea:
... FROM (SELECT ...) G ...

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

 

[TimGoff]

Great - thanks!