Sub Query

[skizzly]

Hello Experts,

I have the following query saved as "Query6":

 "SELECT FlexiWorkers_PatternDistribution.Watch_ID, " & _
    "FlexiWorkers_PatternDistribution.PatternValue, " & _
    "FlexiWorkers_PatternDistribution.PatternStartDate, " & _
    "MasterEveryone.WorkGroup, IIf([Start Time] Between TimeValue(""05:30"") " & _
    "And TimeValue(""09:59""),""M"",IIf([Start Time] Between TimeValue(""10:00"") " & _
    "And TimeValue(""12:59""),""D"",IIf([Start Time] Between TimeValue(""13:00"") " & _
    "And TimeValue(""21:59""),""A"",IIf(IsNull([Start Time]),""O"",""N"")))) AS FiCat, " & _
    "FlexiWorkers_PatternDistribution.Staff_Number " & _
    "FROM LU_Sort_Person_ShiftPallet_WorkGroup INNER JOIN ((MasterEveryone INNER JOIN FlexiWorkers_PatternDistribution ON MasterEveryone.Staff_Number = FlexiWorkers_PatternDistribution.Staff_Number) INNER JOIN LU_Sort_Person_ShiftPallet ON FlexiWorkers_PatternDistribution.PatternValue = LU_Sort_Person_ShiftPallet.PatternValue) ON (LU_Sort_Person_ShiftPallet_WorkGroup.WorkGroupID = LU_Sort_Person_ShiftPallet.WorkGroupID) AND (LU_Sort_Person_ShiftPallet_WorkGroup.WorkGroupDescription = MasterEveryone.WorkGroup) " & _
    "WHERE (((FlexiWorkers_PatternDistribution.PatternValue)<>""O"") " & _
    "AND ((FlexiWorkers_PatternDistribution.PatternStartDate) Between #5/1/2015# And #5/31/2015#));"

and the following query, which is based on above query, "Query6"

"TRANSFORM Count(*) AS [Count] " & _
                            "SELECT (Format([PatternStartDate],""Short Date"")) AS Expr1 " & _
                            "FROM Query6 " & _
                            "WHERE (((Query6.FiCat) <> ""O"") And ((Query6.WorkGroup) = """ & strWorkGroup & """)) " & _
                            "Group BY(Int([PatternStartDate])), (Format([PatternStartDate], ""Short Date"")) " & _
                            "PIVOT Query6.FiCat In (""M"",""D"",""A"",""N"");"

What i am trying to do is to NOT use a saved query, "Query6", as the basis of the second query.

The Second query is used as the data source for an Access Chart.

What i do not know is, how i can incorporate both queries into a single query that can be passed to the chart as it's data source.

An example of how i can achieve this is greatly appreciated.

Thanks in advance

 

[arnelgp]

"TRANSFORM Count(*) AS [Count] " & _
                            "SELECT (Format([PatternStartDate],""Short Date"")) AS Expr1 " & _
"from (SELECT FlexiWorkers_PatternDistribution.Watch_ID, " & _
    "FlexiWorkers_PatternDistribution.PatternValue, " & _
    "FlexiWorkers_PatternDistribution.PatternStartDate, " & _
    "MasterEveryone.WorkGroup, IIf([Start Time] Between TimeValue(""05:30"") " & _
    "And TimeValue(""09:59""),""M"",IIf([Start Time] Between TimeValue(""10:00"") " & _
    "And TimeValue(""12:59""),""D"",IIf([Start Time] Between TimeValue(""13:00"") " & _
    "And TimeValue(""21:59""),""A"",IIf(IsNull([Start Time]),""O"",""N"")))) AS FiCat, " & _
    "FlexiWorkers_PatternDistribution.Staff_Number " & _
    "FROM LU_Sort_Person_ShiftPallet_WorkGroup INNER JOIN ((MasterEveryone INNER JOIN FlexiWorkers_PatternDistribution ON MasterEveryone.Staff_Number = FlexiWorkers_PatternDistribution.Staff_Number) INNER JOIN LU_Sort_Person_ShiftPallet ON FlexiWorkers_PatternDistribution.PatternValue = LU_Sort_Person_ShiftPallet.PatternValue) ON (LU_Sort_Person_ShiftPallet_WorkGroup.WorkGroupID = LU_Sort_Person_ShiftPallet.WorkGroupID) AND (LU_Sort_Person_ShiftPallet_WorkGroup.WorkGroupDescription = MasterEveryone.WorkGroup) " & _
    "WHERE (((FlexiWorkers_PatternDistribution.PatternValue)<>""O"") " & _
    "AND ((FlexiWorkers_PatternDistribution.PatternStartDate) Between #5/1/2015# And #5/31/2015#))) As Query6 " & _
    "WHERE (((Query6.FiCat) <> ""O"") And ((Query6.WorkGroup) = """ & strWorkGroup & """)) " & _
    "Group BY(Int([PatternStartDate])), (Format([PatternStartDate], ""Short Date"")) " & _
    "PIVOT Query6.FiCat In (""M"",""D"",""A"",""N"");"

 

[skizzly]

Hi arnelgp,

Thanks for the response...

Question on your solution is that there is still a reference to Query6.... what should replace this?

Thanks

 

[arnelgp]

please see that i renamed the sub query as Query6, if you would like another alias for that table just find and replace Query6 in the above sql and replace it with whatever alias you like.

 

[skizzly]

Appreciate the time to show how its done.