Report - Group By Date 1

[EastIndian]

I have a table that has three columns: ProjName, AnnualInsp, PLInsp. AnnualInsp and PLInsp are dates--they are never the same date. Annual Insp is always 10 months prior to PLInsp. I would like to create a report that will tell me what Inspections are due each month. For example, If January of 2004 has Two Annual Inspections and Two PL Inspections I would like it to look like this:

JANUARY 2004
Title AnnualInsp PLInsp
PlaceA 01/01/2004 10/01/2005
PlaceC 01/15/2004 10/15/2005
PlaceR 03/23/2003 01/23/2004
PlaceT 03/29/2003 01/29/2004

FEBRUARY 2004
Title AnnualInsp PLInsp
PlaceB 02/05/2004 11/05/2005
PlaceE 04/07/2003 02/07/2004

Thanks for your help!

 

[desikan]

When you create the report initially via report wizard it will ask you whether date field is to be grouped. If you click that option, then your data will be grouped automatically under various months.

Hope this helps you.

 

[EastIndian]

The report wizard will only let me group by one of the two date fields. By grouping on only one field, I can not get both AnnualInsp and PLInsp included in the same month and Year. Any idea how to make both columns be grouped together?

 

[glalsop]

Can you just select the correct values in your query?

So for January:

Month(AnnualInsp) = 1 OR Month(PLInsp) = 1



-Gary

 

[EastIndian]

I'm not quite sure that's what I need--I'm trying to run a report for the entire year so I can see each inspection necessary in each month of the year (for planning purposes). So far, by grouping in Reports, I have to run two reports, one that groups all Annual Inspections by Month and one that groups all PL Inspections by Month, but I just can't get both Inspection types to be listed under the same Month and Year. I appreciate everyone's help and further input.

 

[EastIndian]

Glalsop,
By following your lead, I was able to come up with individual month of year queries showing both inspections using:
Month(AnnualInsp) = 1 AND Year(AnnualInsp) = 2004 OR Month(PLInsp) = 1 AND Year(PLInsp) = 2004
Any ideas how to make this one yearly report?
Thanks again!

 

[glalsop]

Ahhh... That seems considerably tougher...

I think that I would just insert a subreport for the PLInsp into the report for the AnnualInsp, and link them on the month group. This may not be exactly what you are looking for, because it would list all of the AnnaulInsp for January, then all of the PLInsp. But you could tidy it up with proper headings:

January 2004[\b]

Annual Inspections due:
PlaceA 01/01/2004 10/01/2005
PlaceC 01/15/2004 10/15/2005

PL Inspections due:
PlaceR 03/23/2003 01/23/2004
PlaceT 03/29/2003 01/29/2004

Februaury 2004[\b]

Annual Inspections due:
PlaceB 02/05/2004 11/05/2005

PL Inspections due:
PlaceE 04/07/2003 02/07/2004

I suspect that if you lined the subreport up correctly you could even get the exact format you are looking for (keeping in mind that the AnnualInsp will all be listed before any PLInsp).





-Gary

 

[desikan]

Perhaps you can modify your table slightly by having only one field for date and another field for type of inspection and then I think you can get the report by month with both types of data.

I am using similiar ideas in my database for such problems.

 

[glalsop]

If you have individual monthly queries, you could place 12 subreports one under the other (unlinked), each one with a recordsource of one of the queries.

-Gary