Include months w/no data

[Tony]

Hi All,

I've got a report that I'm working on that groups records by month and
within that, by customer. Sample data looks like this:

Jan Count
cust1 countOfRecords
cust2 countOfRecords
cust3 countOfRecords

Feb
cust1 countOfRecords
cust2 countOfRecords
cust3 countOfRecords

Mar Count
cust1 countOfRecords
cust2 countOfRecords
cust3 countOfRecords

Apr
cust1 countOfRecords
cust2 countOfRecords
cust3 countOfRecords

etc.

The problem that I've run into is that there are some months that won't have
any records. For example, if Mar has no data, my report looks like this:

Jan Count
cust1 countOfRecords
cust2 countOfRecords
cust3 countOfRecords

Feb
cust1 countOfRecords
cust2 countOfRecords
cust3 countOfRecords

Apr
cust1 countOfRecords
cust2 countOfRecords
cust3 countOfRecords

Anyone know how I can get around this? I'm using Access 2000.

Thanks

 

[Jeff Boyce]

Have you looked into using a cross-tab query (and report)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony]

Hi Jeff,

Thanks for the reply. Yes, I've looked at trying this with a cross-tab
query but am having difficulty. I know I can make the months a row header,
but I want to display the months in a colum on the report. I'm probably
missing something and will keep mucking around with it.

Thanks,

Tony

 

[Jeff Boyce]

Tony

I may have done something similar by first creating a table or query that
returns all of the thing I want showing (months, in your case). Then I join
that to my actual data in a query and get something (or nothing) along with
each of the ... "months".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony]

Thanks Jeff,

Working on something like that right now. I appreciate the help.

Tony

 

[David]

Please share your success (show example code). I have the same
problem and its driving me crazy.

 

[Tony]

Hey David,

You're assuming success ;-)

No problem - if I figure it out, I'll post back.

Tony



Please share your success (show example code). I have the same
problem and its driving me crazy.

 

[Tony]

Hi David,

I've got something that appears to be working for me. My customer
introduced additional complexity yesterday so it may or may not apply to
your situation. As Jeff suggested, I had to build a query to put everything
together. In my case this is Customer, Year, Month. I set up a table for
each of these values:

CUSTOMER
cust1
cust2
cust3
etc.

YEAR
1999
2000
2001
etc.

MONTH (& sort order)
January(1)
February(2)
March(3)

Added these tables to a query grid and added all the fields to the gird,
sorting ASC by cust, year, month sort order. What I got is exactly what I
needed to start. NOTE: Your tables don't need to be linked or set up to be
linked in the query grid

cust1 - 1999 - January
cust1 - 1999 - February
..
..
..
cust3 - 2001 - November
cust3 - 2001 - December

From here I connected this to the table that has the data (tblMaster) I need
with an outer join to force a record for each combination of cust/year/month
even when tblMaster doesn't have a corresponding record. I built a crosstab
query based on this one to aggregate the date I needed to dispaly on the
reports.

So, even though it runs data through 3 queries and isn't very elegant, it
works. Going to try to see if I can make it more efficient but I've got
enough to run with now.

Good luck,

Tony




Please share your success (show example code). I have the same
problem and its driving me crazy.