Query - Results Template

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How could I force the query results to output to a specific format/template?
The reason for this is that certain values don't exist in certain date ranges
within the data pool. I need those values to be included in the query
results anyway.

I have tried linking another query(tried a table also) with the template to
the query that calculated the results.

Is there something I should consider when doing this? A better way?

Thank you
Erik
 
Create a new query that includes the table that contains the domain of
values you want to appear in the output and join that table to the existing
query with a left join. The new query will return all rows from the domain
table and any matching rows from the query.
 
Thank you for your reply, I believe my problem is that my data tables are
linked together with a 1to1 relationship. I did this because if they were
condensed into 1 table, there would be lots of null values. The values I
referenced come from lookup tables which don't contain the autonumber ID used
by the main data table. The supporting data tables are linked by the
autonumber ID in the main data table.

When I try your suggestion, I get the same results. Hopefully you can see a
problem with my query. L_ tblCode is the lookup table, and
qryfilter_Data_Entry_3_Months is the main data table filtered to only show 3
months.

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 RIGHT JOIN (qryfilter_No_OTHR RIGHT
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;

Thank you
Erik
 
Back
Top