Query - Force non-existant "Group by" values

[Guest]

I have a lookup table which contains 4 options(L_tblCode). I need all 4 of
these options to show after I run a query from my main
table(qryfilter_Data_Entry_3_Months). Currently, this does not happen
because the query is filtered by a 3 month date range. Those 4 options
exist, but not always within that 3 month date range. How can I force all
the options to show even if they do not exist in that 3 month date range?

My current query:

PARAMETERS [Enter the Area] Text ( 255 ), [Enter the Code] Text ( 255 );
SELECT Format([L_Date],"m") AS Expr2,
qryreport_Dept_Total_EOM_3_Months.Expr1 AS Expr1, L_tblArea_Tech.L_Area AS
Dept, L_tblCode.L_Code AS Code,
Nz(Count(qryfilter_No_OTHR.Code),0)-Nz(Count(tblFail.Code),0)+Nz(Sum([Kits_Failed]),0)
AS [Code Fails], qryreport_Dept_Total_EOM_3_Months.Fails AS Total, [Code
Fails]/[Total] AS [%]
FROM (((qryfilter_Data_Entry_3_Months LEFT JOIN (qryfilter_No_OTHR LEFT JOIN
L_tblCode ON qryfilter_No_OTHR.Code = L_tblCode.L_Code_ID) ON
qryfilter_Data_Entry_3_Months.tblData_Entry_ID =
qryfilter_No_OTHR.tblData_Entry_ID) LEFT JOIN (tblKits LEFT JOIN tblFail ON
tblKits.tblData_Entry_ID = tblFail.tblData_Entry_ID) ON
qryfilter_Data_Entry_3_Months.tblData_Entry_ID = tblKits.tblData_Entry_ID)
LEFT JOIN L_tblArea_Tech ON qryfilter_Data_Entry_3_Months.Tech =
L_tblArea_Tech.L_Area_Tech_ID) LEFT JOIN qryreport_Dept_Total_EOM_3_Months ON
L_tblArea_Tech.L_Area = qryreport_Dept_Total_EOM_3_Months.Dept
GROUP BY Format([L_Date],"m"), qryreport_Dept_Total_EOM_3_Months.Expr1,
L_tblArea_Tech.L_Area, L_tblCode.L_Code,
qryreport_Dept_Total_EOM_3_Months.Fails
HAVING (((qryreport_Dept_Total_EOM_3_Months.Expr1)=Format([L_Date],"m")) AND
((L_tblArea_Tech.L_Area)=[Enter the Area]) AND ((L_tblCode.L_Code)=[Enter the
Code])) OR (((qryreport_Dept_Total_EOM_3_Months.Expr1)=Format([L_Date],"m"))
AND ((L_tblArea_Tech.L_Area) Is Not Null) AND ((L_tblCode.L_Code)=[Enter the
Code]) AND ((([L_tblArea_Tech].[L_Area]) Like [Enter the Area]) Is Null)) OR
(((qryreport_Dept_Total_EOM_3_Months.Expr1)=Format([L_Date],"m")) AND
((L_tblArea_Tech.L_Area)=[Enter the Area]) AND ((L_tblCode.L_Code) Is Not
Null) AND ((([L_tblCode].[L_Code]) Like [Enter the Code]) Is Null)) OR
(((qryreport_Dept_Total_EOM_3_Months.Expr1)=Format([L_Date],"m")) AND
((L_tblArea_Tech.L_Area) Is Not Null) AND ((L_tblCode.L_Code) Is Not Null)
AND ((([L_tblArea_Tech].[L_Area]) Like [Enter the Area]) Is Null) AND
((([L_tblCode].[L_Code]) Like [Enter the Code]) Is Null))
ORDER BY Format([L_Date],"m"), qryreport_Dept_Total_EOM_3_Months.Expr1,
L_tblArea_Tech.L_Area, L_tblCode.L_Code;

 

[Michel Walsh]

***** If your actual query produces:

Period Stat
August 2007 44.56
July 2007 43.78
June 2007 43.89



and you want:

Period Stat
August 2007 44.56
July 2007 43.78
June 2007 43.89
May 2007 {null}




use:

SELECT allNames.period, previousQuery.Stat
FROM allNames LEFT JOIN previousQuery
ON allNames.period = previousQuery.period



where table allName have the field period with values:

August 2007
July 2007
June 2007
May 2007





***** If your actual query produces:


Period Client Stat
August 2007 joe 44.56
July 2007 joe 43.78
June 2007 joe 43.89
June 2007 mary 47.12
May 2007 mary 42.99


and you want

Period Client Stat
August 2007 joe 44.56
July 2007 joe 43.78
June 2007 joe 43.89
May 2007 joe {null}
August 2007 mary {null}
July 2007 mary {null}
June 2007 mary 47.12
May 2007 mary 42.99




Then, assuming you have the tables allPeriods (already mentioned) and
allClients:


allClients 'table name
client ' field names
Joe
Mary


then, make

SELECT allClients.Clients, allPeriods.Period
FROM allClients, allPeriods


as query1



and use:



SELECT query1.period, query1.client, yourOriginalQuery.Stat
FROM query1 LEFT JOIN yourOriginalQuery
ON query1.period = yourOriginalQuery.period
AND query1.client = yourOriginalQuery.client








*******************



In both cases, the idea is to produces all rows and then, use a LEFT JOIN to
pick the data supplied by your original query (but with missing rows,
because there is missing data).




Hoping it may help,
Vanderghast, Access MVP

I have a lookup table which contains 4 options(L_tblCode). I need all 4 of
these options to show after I run a query from my main
table(qryfilter_Data_Entry_3_Months). Currently, this does not happen
because the query is filtered by a 3 month date range. Those 4 options
exist, but not always within that 3 month date range. How can I force all
the options to show even if they do not exist in that 3 month date range?

My current query:

PARAMETERS [Enter the Area] Text ( 255 ), [Enter the Code] Text ( 255 );
SELECT Format([L_Date],"m") AS Expr2,
qryreport_Dept_Total_EOM_3_Months.Expr1 AS Expr1, L_tblArea_Tech.L_Area AS
Dept, L_tblCode.L_Code AS Code,
Nz(Count(qryfilter_No_OTHR.Code),0)-Nz(Count(tblFail.Code),0)+Nz(Sum([Kits_Failed]),0)
AS [Code Fails], qryreport_Dept_Total_EOM_3_Months.Fails AS Total, [Code
Fails]/[Total] AS [%]
FROM (((qryfilter_Data_Entry_3_Months LEFT JOIN (qryfilter_No_OTHR LEFT
JOIN
L_tblCode ON qryfilter_No_OTHR.Code = L_tblCode.L_Code_ID) ON
qryfilter_Data_Entry_3_Months.tblData_Entry_ID =
qryfilter_No_OTHR.tblData_Entry_ID) LEFT JOIN (tblKits LEFT JOIN tblFail
ON
tblKits.tblData_Entry_ID = tblFail.tblData_Entry_ID) ON
qryfilter_Data_Entry_3_Months.tblData_Entry_ID = tblKits.tblData_Entry_ID)
LEFT JOIN L_tblArea_Tech ON qryfilter_Data_Entry_3_Months.Tech =
L_tblArea_Tech.L_Area_Tech_ID) LEFT JOIN qryreport_Dept_Total_EOM_3_Months
ON
L_tblArea_Tech.L_Area = qryreport_Dept_Total_EOM_3_Months.Dept
GROUP BY Format([L_Date],"m"), qryreport_Dept_Total_EOM_3_Months.Expr1,
L_tblArea_Tech.L_Area, L_tblCode.L_Code,
qryreport_Dept_Total_EOM_3_Months.Fails
HAVING (((qryreport_Dept_Total_EOM_3_Months.Expr1)=Format([L_Date],"m"))
AND
((L_tblArea_Tech.L_Area)=[Enter the Area]) AND ((L_tblCode.L_Code)=[Enter
the
Code])) OR
(((qryreport_Dept_Total_EOM_3_Months.Expr1)=Format([L_Date],"m"))
AND ((L_tblArea_Tech.L_Area) Is Not Null) AND ((L_tblCode.L_Code)=[Enter
the
Code]) AND ((([L_tblArea_Tech].[L_Area]) Like [Enter the Area]) Is Null))
OR
(((qryreport_Dept_Total_EOM_3_Months.Expr1)=Format([L_Date],"m")) AND
((L_tblArea_Tech.L_Area)=[Enter the Area]) AND ((L_tblCode.L_Code) Is Not
Null) AND ((([L_tblCode].[L_Code]) Like [Enter the Code]) Is Null)) OR
(((qryreport_Dept_Total_EOM_3_Months.Expr1)=Format([L_Date],"m")) AND
((L_tblArea_Tech.L_Area) Is Not Null) AND ((L_tblCode.L_Code) Is Not Null)
AND ((([L_tblArea_Tech].[L_Area]) Like [Enter the Area]) Is Null) AND
((([L_tblCode].[L_Code]) Like [Enter the Code]) Is Null))
ORDER BY Format([L_Date],"m"), qryreport_Dept_Total_EOM_3_Months.Expr1,
L_tblArea_Tech.L_Area, L_tblCode.L_Code;