I need to find the months without activity.

[Frank Martin]

I have Access2003 and Windows XPPro.

My application has a query "QryINVOICE" containing all the
data from the customer invoicing going back to 1999.

I have to make a chart showing the sales per customer over
this whole time period, but I must also include on this
chart the months in which a customer did not record any
sales.

How can I make a query from the "QryINVOICE", but also
include the months in which the customer did not buy?

Please help, Frank

 

[Allen Browne]

The data has to come from somewhere, so you will need a query that generates
a record for every month for every client, and you can then outer-join that
to your sales data (QryINVOICE.)

What do the 'months' look like in QryInvoice? Do you have a date field, with
(say) the first of each month? Or do you have 2 fields for the year and the
month?

If a date field, create a table that has one field called (say) TheDate, and
enter a record for the first of each month of each year you are interested
in. If you have 2 fields, create 2 tables: one with a record for each year,
and another with 12 fields for the months.

Now create a table that uses your Customer table and the date table(s.) It's
important that there is no join between the tables in the upper pane of the
query design window. This gives you every possible combination, so you get a
record for every customer for every month. Save this query. (It's called a
Cartesian Product.)

Now create another query that uses the one you just saved and QryInvoice.
Drag qryInvoice.CustomerID and drop it onto CustomerID in the new query.
Access draws a line between these in the upper pane of query design.
Double-click this line: Access pops up a dialog with 2 options. Choose the
one that says:
All records from QryInvoice, and any matches from ...
(This is called an outer join.)

Repeat that step to join the 2 queries on the date field(s) as well, making
them outer joins also.

The query will now have every customer for every month, with a blank field
where there were no sales to that customer in that month.

 

[Frank Martin]

Thank you very much for this.

I do have a date field in the QryINVOICE called InvoiceDate,
typically formatted as 'short date ddmmmyy' for data input.

When I have to check any of the past data I always call it
up via the monthly format from a query expression
"Format([InvoiceDate],"mmmyy") which works well.

I made a new table called "tblDate" and manually filled it
with the first date of every month. Then I made the
"Cartesian Product" query with my Customers table and the
new "tblDate" and ran it OK.

(I had never heard of this "Cartesian Product" principle
before, and this is why I was having trouble.)

When you say to join the date fields, does it matter how
they are formatted in the query? I have several formats:
the usual 'short date' dd-mmm-yy, then the one to make
date-sorting easier (Format([InvoiceDate],"yyyymm"), and
then one for checking out the month in question as above
("Format([InvoiceDate],"mmmyy").

Does it matter which of these formats I use for the outer
join of the date? Also the sorting of data (Customers,
then Date) does not seem to hold, and I wonder where and
when I should apply the date sorting.

Regards, Frank

 

[Allen Browne]

The output of Format() is Text (not a date.)

If you want it to sort correctly, you might use:
Format([InvoiceDate], "yyyy\-mm")

My preference is to user 2 number fields (one for year and one for month),
as it seems more reliable/accurate and possibly more efficient.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you very much for this.

I do have a date field in the QryINVOICE called InvoiceDate, typically
formatted as 'short date ddmmmyy' for data input.

When I have to check any of the past data I always call it up via the
monthly format from a query expression "Format([InvoiceDate],"mmmyy")
which works well.

I made a new table called "tblDate" and manually filled it with the first
date of every month. Then I made the "Cartesian Product" query with my
Customers table and the new "tblDate" and ran it OK.

(I had never heard of this "Cartesian Product" principle before, and this
is why I was having trouble.)

When you say to join the date fields, does it matter how they are
formatted in the query? I have several formats: the usual 'short date'
dd-mmm-yy, then the one to make date-sorting easier
(Format([InvoiceDate],"yyyymm"), and then one for checking out the month
in question as above ("Format([InvoiceDate],"mmmyy").

Does it matter which of these formats I use for the outer join of the
date? Also the sorting of data (Customers, then Date) does not seem to
hold, and I wonder where and when I should apply the date sorting.

Regards, Frank

The data has to come from somewhere, so you will need a query that
generates a record for every month for every client, and you can then
outer-join that to your sales data (QryINVOICE.)

What do the 'months' look like in QryInvoice? Do you have a date field,
with (say) the first of each month? Or do you have 2 fields for the year
and the month?

If a date field, create a table that has one field called (say) TheDate,
and enter a record for the first of each month of each year you are
interested in. If you have 2 fields, create 2 tables: one with a record
for each year, and another with 12 fields for the months.

Now create a table that uses your Customer table and the date table(s.)
It's important that there is no join between the tables in the upper pane
of the query design window. This gives you every possible combination, so
you get a record for every customer for every month. Save this query.
(It's called a Cartesian Product.)

Now create another query that uses the one you just saved and QryInvoice.
Drag qryInvoice.CustomerID and drop it onto CustomerID in the new query.
Access draws a line between these in the upper pane of query design.
Double-click this line: Access pops up a dialog with 2 options. Choose
the one that says:
All records from QryInvoice, and any matches from ...
(This is called an outer join.)

Repeat that step to join the 2 queries on the date field(s) as well,
making them outer joins also.

The query will now have every customer for every month, with a blank
field where there were no sales to that customer in that month.

 

[Frank Martin]

Thank you for the help.

I figured it all out and now the chart is working fine. But
have a few comments:

1/ For the outer join I had to do this on the Cartesian
field and not the InvoiceDate.

2/ Formatting the date axis of the chart shows yyyymm, but I
would prefer mmmyy (for clarity) and I cannot seem to change
it.

3/ The date in the 'Date" table is not immortal and so I
have to go and increment the date every 6 months for it to
appear on the chart.

The output of Format() is Text (not a date.)

If you want it to sort correctly, you might use:
Format([InvoiceDate], "yyyy\-mm")

My preference is to user 2 number fields (one for year and
one for month), as it seems more reliable/accurate and
possibly more efficient.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you very much for this.

I do have a date field in the QryINVOICE called
InvoiceDate, typically formatted as 'short date ddmmmyy'
for data input.

When I have to check any of the past data I always call
it up via the monthly format from a query expression
"Format([InvoiceDate],"mmmyy") which works well.

I made a new table called "tblDate" and manually filled
it with the first date of every month. Then I made the
"Cartesian Product" query with my Customers table and the
new "tblDate" and ran it OK.

(I had never heard of this "Cartesian Product" principle
before, and this is why I was having trouble.)

When you say to join the date fields, does it matter how
they are formatted in the query? I have several formats:
the usual 'short date' dd-mmm-yy, then the one to make
date-sorting easier (Format([InvoiceDate],"yyyymm"), and
then one for checking out the month in question as above
("Format([InvoiceDate],"mmmyy").

Does it matter which of these formats I use for the outer
join of the date? Also the sorting of data (Customers,
then Date) does not seem to hold, and I wonder where and
when I should apply the date sorting.

Regards, Frank

The data has to come from somewhere, so you will need a
query that generates a record for every month for every
client, and you can then outer-join that to your sales
data (QryINVOICE.)

What do the 'months' look like in QryInvoice? Do you
have a date field, with (say) the first of each month?
Or do you have 2 fields for the year and the month?

If a date field, create a table that has one field
called (say) TheDate, and enter a record for the first
of each month of each year you are interested in. If you
have 2 fields, create 2 tables: one with a record for
each year, and another with 12 fields for the months.

Now create a table that uses your Customer table and the
date table(s.) It's important that there is no join
between the tables in the upper pane of the query design
window. This gives you every possible combination, so
you get a record for every customer for every month.
Save this query. (It's called a Cartesian Product.)

Now create another query that uses the one you just
saved and QryInvoice. Drag qryInvoice.CustomerID and
drop it onto CustomerID in the new query. Access draws a
line between these in the upper pane of query design.
Double-click this line: Access pops up a dialog with 2
options. Choose the one that says:
All records from QryInvoice, and any matches from ...
(This is called an outer join.)

Repeat that step to join the 2 queries on the date
field(s) as well, making them outer joins also.

The query will now have every customer for every month,
with a blank field where there were no sales to that
customer in that month.

I have Access2003 and Windows XPPro.

My application has a query "QryINVOICE" containing all
the data from the customer invoicing going back to
1999.

I have to make a chart showing the sales per customer
over this whole time period, but I must also include on
this chart the months in which a customer did not
record any sales.

How can I make a query from the "QryINVOICE", but also
include the months in which the customer did not buy?

 

[Allen Browne]

Re your comments:
1) That sounds right.

2) You can Format() differently in the query, but the chart won't sort
right, e.g. Apr before Jan, Apr08, Apr09, Aug08, Aug09, Feb08, ...

3) You can programmatically add dates if it helps. This example adds every
day (may not be what you need):

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for the help.

I figured it all out and now the chart is working fine. But have a few
comments:

1/ For the outer join I had to do this on the Cartesian field and not the
InvoiceDate.

2/ Formatting the date axis of the chart shows yyyymm, but I would prefer
mmmyy (for clarity) and I cannot seem to change it.

3/ The date in the 'Date" table is not immortal and so I have to go and
increment the date every 6 months for it to appear on the chart.

The output of Format() is Text (not a date.)

If you want it to sort correctly, you might use:
Format([InvoiceDate], "yyyy\-mm")

My preference is to user 2 number fields (one for year and one for
month), as it seems more reliable/accurate and possibly more efficient.