Report doesn’t show all the data underlying it (2003)

Topic

New Reply

If you select May 2005 on the startup form of the attached mdb and click the Comando10 button, the report that opens up doesn’t show data for the month of May but I just can’t make out why since the data in the tables underlying the report are up to May 2005.

Reply | Quote

Replies

In the query QueryFermiM1com you have an inner join between COM1 and FERMI1 on CODF, and the value field is the sum of OREF.
For all records except 2 in COM1 with Giorno in May, 2005, the CODF field is null, so they won’t be included because of the inner join.
There are 2 records for May, 2005 with CODF=3, but for those records OREF is null, so the sum is null too.

Reply | Quote

Thank you guys, your input made me realise I had to substitute OREF with 24-[ORE_marc] in the queries underlying the crosstabs (QueryFermiM1com and QueryFermiM2com).

Reply | Quote

The problem relates to missing data – or an incorrect join. The queries underlying the crosstabs (QueryFermiM1com and QueryFermiM2com) link a table (either COM1 or COM2) to table FERMI1 using the field CODF.

In the case of table COM1 no records dated after April contain any value in the CODF field. In the case of table COM2, no records dated after March contain a value in this field. Because the two queries use inner joins, the only records being returned will be those with values in the CODF field which are also in the FERMI1 table, so no data will be generated for May at all, and only some (from COM1 only) for April.

Reply | Quote