Report not pulling data correctly

[haV3rhill]

I have a problem with one of my reports not pulling data correctly. There is a form I use to manually enter data, and the data that has been entered through the form does not match what comes out on my report. It seems that it is only allowing two entries per date, and as long as I am only entering two entries per date the report pulls fine and will capture every audit that has been entered, but if all the entries are from the same date or if I have more than two entries for the same date, the report only pulls data for two of the entries. What can I do to make sure my report pulls data correctly from all audits entered?

[aytee111]

The first thing you need to do is get the query working, the one which you are using as the record source for the report. The report only displays what is in the query. So, create a query, show us samples of what the data looks like going in and what you would like it to look like afterwards.

[haV3rhill]

This is what my report looks like. as you can see it is only picking up two audit entries.


I actually have five audit entries that were entered on the same date as shown below.


Here is the query that the report is pulling from.

[aytee111]

Where is the problem occurring? So far you have shown us two queries - which one is not producing the correct results? Are there any more queries? Like I said, get the queries working, start from the beginning and see which query is dropping the data.

[haV3rhill]

So this is what I just sent.


and when I go into the table and change the dates manually so that there are only two entries for one date, the report pulls correctly and recognizes all audit entries. See report below compared to the first one I sent.





It seems to have something to do with the date cell for some reason.

[aytee111]

I am only interested in the queries - not the tables, not the report, no matter how many times you show them!

[haV3rhill]

QUERY 1
SELECT tbl_ZapResult.ZapResult_HazardID, tbl_ZapResult.ZapResult_HazardValue, tbl_Hazards.[Hazard_Name/Description], tbl_HazardCategory.HazCat_Name, tbl_Supervisor.Supervisor_Name
FROM tbl_Supervisor INNER JOIN ((tbl_ZAP_Observation INNER JOIN tbl_ZapResult ON tbl_ZAP_Observation.[Zap_ID] = tbl_ZapResult.[ZapResult_ZapID]) INNER JOIN (tbl_HazardCategory INNER JOIN tbl_Hazards ON tbl_HazardCategory.HazCat_ID = tbl_Hazards.Hazard_HazCat_ID) ON tbl_ZapResult.[ZapResult_HazardID] = tbl_Hazards.Hazard_ID) ON tbl_Supervisor.Supervisor_Id = tbl_ZAP_Observation.[Zap_SupervisorID]
GROUP BY tbl_ZapResult.ZapResult_HazardID, tbl_ZapResult.ZapResult_HazardValue, tbl_Hazards.[Hazard_Name/Description], tbl_HazardCategory.HazCat_Name, tbl_Supervisor.Supervisor_Name, tbl_ZAP_Observation.Zap_Date
HAVING (((tbl_ZAP_Observation.Zap_Date) Between [forms]![frm_mainboard]![txt_startdate] And [forms]![frm_mainboard]![txt_enddate]));

QUERY 2
SELECT qry_HazDRangUnsafe.ZapResult_HazardID, qry_HazDRangUnsafe.[Hazard_Name/Description], qry_HazDRangUnsafe.HazCat_Name, Sum(IIf([ZapResult_HazardValue]=0,1,0)) AS Safe, Sum(IIf([ZapResult_HazardValue]=-1,1,0)) AS Unsafe
FROM qry_HazDRangUnsafe
GROUP BY qry_HazDRangUnsafe.ZapResult_HazardID, qry_HazDRangUnsafe.[Hazard_Name/Description], qry_HazDRangUnsafe.HazCat_Name;

QUERY 3
SELECT qry_HazDRangUnsafe.ZapResult_HazardID, qry_HazDRangUnsafe.[Hazard_Name/Description], qry_HazDRangUnsafe.HazCat_Name, Sum(IIf(qry_HazDRangUnsafe.[ZapResult_HazardValue]=0,1,0)) AS Safe, Sum(IIf(qry_HazDRangUnsafe.[ZapResult_HazardValue]=-1,1,0)) AS Unsafe, qry_HazDRangUnsafe.Supervisor_Name
FROM qry_HazDRangUnsafe
GROUP BY qry_HazDRangUnsafe.ZapResult_HazardID, qry_HazDRangUnsafe.[Hazard_Name/Description], qry_HazDRangUnsafe.HazCat_Name, qry_HazDRangUnsafe.Supervisor_Name
HAVING (((qry_HazDRangUnsafe.HazCat_Name) Not Like "*SAI*"));

QUERY 4
SELECT Count(qry_zapsDrange.Zap_ID) AS CountOfZAP_ID, qry_zapsDrange.Supervisor_Name, qry_zapsDrange.Zap_Class
FROM qry_zapsDrange
GROUP BY qry_zapsDrange.Supervisor_Name, qry_zapsDrange.Zap_Class;

QUERY 5
SELECT Count(qry_zapsDrange.[ZAP_ID]) AS CountOfZAP_ID, qry_zapsDrange.Supervisor_Name
FROM qry_zapsDrange
GROUP BY qry_zapsDrange.Supervisor_Name;

QUERY 6
SELECT tbl_ZAP_Observation.ZAP_ID, tbl_ZAP_Observation.ZAP_Date, tbl_Supervisor.Supervisor_Name, tbl_ZAP_Observation.ZAP_Class
FROM tbl_Supervisor INNER JOIN tbl_ZAP_Observation ON tbl_Supervisor.Supervisor_Id = tbl_ZAP_Observation.[ZAP_SupervisorID]
GROUP BY tbl_ZAP_Observation.ZAP_ID, tbl_ZAP_Observation.ZAP_Date, tbl_Supervisor.Supervisor_Name, tbl_ZAP_Observation.ZAP_Class
HAVING (((tbl_ZAP_Observation.ZAP_Date) Between [forms]![frm_mainboard]![txt_startdate] And [forms]![frm_mainboard]![txt_enddate]));

[aytee111]

Which query is showing all the records correctly, and which is not?