Crosstab query by quarter nightmare

[Guest]

I am trying to create a crosstab query to populate a report. I have a many
to many relationship. For instance table 1 customerid, startdate; table 2
savcustid, customerid, savingsID; table 3 savingsID, savings.

What I am trying to do is on a form select some date corresponding to the
startdate then in the crosstab query have it show all of the savings type
even if there is none in table 2. Then have the crosstab count each
customerid for each savings and have it display the months. However, I only
want it to show the 3 months for the particualr quarter that the startdate
selected is in. Once this is done it will populate a report as a subreport,
but I need the 3 labels to reflect the correct months of the quarter selected
for the main part of the report.

I hope I made it clear as possible.

First can this be done and how do I go about doing this? I have been trying
to solve this all day without success.

Any help would be greatly appreciated. Thanks in advance.

 

[Duane Hookom]

Consider using relative quarters. Search google groups on
crosstab relative date mth0 group:*Access* author:hookom
for a possible solution.

 

[Allen Browne]

I am not sure I understand the goal here. StartDate is part of the Customer
table? So you are recording when a customer came on board. Then you are
trying to match when a new customer took out one of the accounts in the
Savings table, so you have the junction table between Customers and Savings,
but there is no date in the junction table? So the whole crosstab relies
only on the date the Customer joined, regardless of when they opened the
Savings account?

Because I am not clear on this, I can only offer general suggestions, and
let you piece them together.

a) The way to get every possible combination of customer and savings is a
query containing Table1 and Table3. There must be no join-line between the
tables (a Cartesian product.)

b) To limit it to customers that joined in a quarter, can we assume that the
date on Form1 will be the first date of the quarter? If so, the Criteia
would be:

= [Forms].[Form1].[Text0] And < DateAdd("m", 3,

[Forms].[Form1].[Text0])

c) Type this expression into a fresh column in the Field row:
MonthOfQuarter: DateDiff("m", [Forms].[Form1].[Text0], [startdate])
This will show 0 for the first month in the quarter, 1 for the next, and 2
for the last. These field names are consistent regardless of the quarter, so
you can later build a report that expects these names.

d) Use an outer join between Table2 and the query above to make your
crosstab. Set the Column Headings property of the crosstab query (in the
Properties box) to:
0,1,2
so that all 3 show up, regardless of the actual data for the quarter. The
crosstab must generate all 3, since the report wll look for these fields.

e) In the report, use a text box for the "label" over the column. The
Control Source for the first column will be:
= [Forms].[Form1].[Text0]
Set the Format of the text box to whatever you want, e.g.:
mmm
The next field's heading will be:
= DateAdd("m", 1, [Forms].[Form1].[Text0])
and the third:
= DateAdd("m", 2, [Forms].[Form1].[Text0])

Hopefully there is enough there to get you through the maze.

 

[Guest]

Thank you both for your replies.

Allen, I hope I can explain it a little better here. I revised my thought
of selecting a date on form. Instead what I will do is have an option group
for the user to select which qtr they want with a drop down to show all the
years from the date field. This part I have completed.

What I am attempting to do is have a crosstab to count all the customerIDs
for each savings /quarter and have it show all the savings even if they don't
have any customerIDs related to it, with this I want it broken by month. Now
the customer can have more than one savings type that't why there is a many
to many relationship.

The reason I need it to display all the savings and have it count each
customerID even if none is present is because the user can add additional
savings types at a later date and I don't want to have to hard code this
everytime they add a new one. I want them to display the information
dynamically on the report.

I will give a try of what you suggested and thanks again for the help.

 

[Guest]

Allen thanks for the response. I am stuck on some of this. For some reason
I am getting the same count for each savings. Here is my query for cartesian
and the 2nd query can you see what I may be doing wrong? Thanks. By the way
I am using an option group and a combo box to build the criteria in a hidden
text box then when I click sumbit it will run the report. This hidden text
box is what is used in the queries.

Cartesian
SELECT tblSavings.Savings, tblCustomers.CustomerID,
DateDiff("m",[Forms].[frmRpts].[Text1],[StartDate]) AS MonthOfQuarter,
tblCustomer.StartDate
FROM tblCustomers, tblSavings
WHERE (((tblCustomers.StartDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomers.StartDate)<DateAdd("m",3,[Forms].[frmRpts].[Text1])));

Query2
TRANSFORM Count(tblCustomerSavings.CustomerID) AS CountOfCustomerID
SELECT qrySavingsCartesian.Savings, Count(tblCustomerSavings.CustomerID) AS
Total
FROM tblCustomerSavings INNER JOIN qrySavingsCartesian ON
tblCustomerSavings.CustomerID = qrySavingsCartesian.CustomerID
GROUP BY qrySavingsCartesian.Savings
PIVOT qrySavingsCartesian.MonthOfQuarter In (0,1,2);

For some reason it won't allow an OUTER JOIN as you suggested for query 2.

I am not sure I understand the goal here. StartDate is part of the Customer
table? So you are recording when a customer came on board. Then you are
trying to match when a new customer took out one of the accounts in the
Savings table, so you have the junction table between Customers and Savings,
but there is no date in the junction table? So the whole crosstab relies
only on the date the Customer joined, regardless of when they opened the
Savings account?

Because I am not clear on this, I can only offer general suggestions, and
let you piece them together.

a) The way to get every possible combination of customer and savings is a
query containing Table1 and Table3. There must be no join-line between the
tables (a Cartesian product.)

b) To limit it to customers that joined in a quarter, can we assume that the
date on Form1 will be the first date of the quarter? If so, the Criteia
would be:

= [Forms].[Form1].[Text0] And < DateAdd("m", 3,

[Forms].[Form1].[Text0])

c) Type this expression into a fresh column in the Field row:
MonthOfQuarter: DateDiff("m", [Forms].[Form1].[Text0], [startdate])
This will show 0 for the first month in the quarter, 1 for the next, and 2
for the last. These field names are consistent regardless of the quarter, so
you can later build a report that expects these names.

d) Use an outer join between Table2 and the query above to make your
crosstab. Set the Column Headings property of the crosstab query (in the
Properties box) to:
0,1,2
so that all 3 show up, regardless of the actual data for the quarter. The
crosstab must generate all 3, since the report wll look for these fields.

e) In the report, use a text box for the "label" over the column. The
Control Source for the first column will be:
= [Forms].[Form1].[Text0]
Set the Format of the text box to whatever you want, e.g.:
mmm
The next field's heading will be:
= DateAdd("m", 1, [Forms].[Form1].[Text0])
and the third:
= DateAdd("m", 2, [Forms].[Form1].[Text0])

Hopefully there is enough there to get you through the maze.

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

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

I am trying to create a crosstab query to populate a report. I have a many
to many relationship. For instance table 1 customerid, startdate; table 2
savcustid, customerid, savingsID; table 3 savingsID, savings.

What I am trying to do is on a form select some date corresponding to the
startdate then in the crosstab query have it show all of the savings type
even if there is none in table 2. Then have the crosstab count each
customerid for each savings and have it display the months. However, I
only
want it to show the 3 months for the particualr quarter that the startdate
selected is in. Once this is done it will populate a report as a
subreport,
but I need the 3 labels to reflect the correct months of the quarter
selected
for the main part of the report.

I hope I made it clear as possible.

First can this be done and how do I go about doing this? I have been
trying
to solve this all day without success.

Any help would be greatly appreciated. Thanks in advance.

 

[Allen Browne]

Not sure what else to suggest.

The hidden text box sounds fine. (Setting its Format to Short Date may help
Access to understand it, as might declaring the parameter in the query, but
that won't solve the issue here.)

The Cartesian query gives every combination of customer and savings in the
quarter. That's what you wanted, but it might be the reason the count it the
same across the quarters?

You should be able to get an outer join by double-clicking the line joining
the 2 tables in query design view. Access offers a dialog with 3 options,
and you choose #2 or #3 depending on the direction of the outer join.

In any structure I can follow, I would have expected the date would be in
the junction table, so I guess I don't really understand the scenario.

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

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

Allen thanks for the response. I am stuck on some of this. For some
reason
I am getting the same count for each savings. Here is my query for
cartesian
and the 2nd query can you see what I may be doing wrong? Thanks. By the
way
I am using an option group and a combo box to build the criteria in a
hidden
text box then when I click sumbit it will run the report. This hidden
text
box is what is used in the queries.

Cartesian
SELECT tblSavings.Savings, tblCustomers.CustomerID,
DateDiff("m",[Forms].[frmRpts].[Text1],[StartDate]) AS MonthOfQuarter,
tblCustomer.StartDate
FROM tblCustomers, tblSavings
WHERE (((tblCustomers.StartDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomers.StartDate)<DateAdd("m",3,[Forms].[frmRpts].[Text1])));

Query2
TRANSFORM Count(tblCustomerSavings.CustomerID) AS CountOfCustomerID
SELECT qrySavingsCartesian.Savings, Count(tblCustomerSavings.CustomerID)
AS
Total
FROM tblCustomerSavings INNER JOIN qrySavingsCartesian ON
tblCustomerSavings.CustomerID = qrySavingsCartesian.CustomerID
GROUP BY qrySavingsCartesian.Savings
PIVOT qrySavingsCartesian.MonthOfQuarter In (0,1,2);

For some reason it won't allow an OUTER JOIN as you suggested for query 2.

I am not sure I understand the goal here. StartDate is part of the
Customer
table? So you are recording when a customer came on board. Then you are
trying to match when a new customer took out one of the accounts in the
Savings table, so you have the junction table between Customers and
Savings,
but there is no date in the junction table? So the whole crosstab relies
only on the date the Customer joined, regardless of when they opened the
Savings account?

Because I am not clear on this, I can only offer general suggestions, and
let you piece them together.

a) The way to get every possible combination of customer and savings is a
query containing Table1 and Table3. There must be no join-line between
the
tables (a Cartesian product.)

b) To limit it to customers that joined in a quarter, can we assume that
the
date on Form1 will be the first date of the quarter? If so, the Criteia
would be:

= [Forms].[Form1].[Text0] And < DateAdd("m", 3,

[Forms].[Form1].[Text0])

c) Type this expression into a fresh column in the Field row:
MonthOfQuarter: DateDiff("m", [Forms].[Form1].[Text0], [startdate])
This will show 0 for the first month in the quarter, 1 for the next, and
2
for the last. These field names are consistent regardless of the quarter,
so
you can later build a report that expects these names.

d) Use an outer join between Table2 and the query above to make your
crosstab. Set the Column Headings property of the crosstab query (in the
Properties box) to:
0,1,2
so that all 3 show up, regardless of the actual data for the quarter. The
crosstab must generate all 3, since the report wll look for these fields.

e) In the report, use a text box for the "label" over the column. The
Control Source for the first column will be:
= [Forms].[Form1].[Text0]
Set the Format of the text box to whatever you want, e.g.:
mmm
The next field's heading will be:
= DateAdd("m", 1, [Forms].[Form1].[Text0])
and the third:
= DateAdd("m", 2, [Forms].[Form1].[Text0])

Hopefully there is enough there to get you through the maze.

I am trying to create a crosstab query to populate a report. I have a
many
to many relationship. For instance table 1 customerid, startdate; table
2
savcustid, customerid, savingsID; table 3 savingsID, savings.

What I am trying to do is on a form select some date corresponding to
the
startdate then in the crosstab query have it show all of the savings
type
even if there is none in table 2. Then have the crosstab count each
customerid for each savings and have it display the months. However, I
only
want it to show the 3 months for the particualr quarter that the
startdate
selected is in. Once this is done it will populate a report as a
subreport,
but I need the 3 labels to reflect the correct months of the quarter
selected
for the main part of the report.

I hope I made it clear as possible.

First can this be done and how do I go about doing this? I have been
trying
to solve this all day without success.

Any help would be greatly appreciated. Thanks in advance.

 

[Guest]

Thanks for the response, here is the scenario.

The customer table shows all the information about the customer including
some history info. The savings tables shows the various types of savings.
And of course the savingscustomer table shows the combo of the two. During
any time the customer can have more that one type of savings. The users want
to pull up a report to show how many customers have each type of savings.
However, they want to use the date the customer started. For instance they
can pull all the savings records and count them for each month during a
particular quarter that any customer was started. The start date is just a
field they use when they receieve the initial record. I was able to
accomplish initially in a crosstab without any trouble except during some
quarters/month it did not show all the saving types which I need it to
populate the report. Sometimes there was only 4 when in fact it should show
all 11.

The cartesian shows that for every savings type I have 63 customers for a
particular quarter. I think this is where the problem lies.

Should I have the start date popluate the many to many relationship along
with the customerID? I lready have the start date within the customer table.

Not sure what else to suggest.

The hidden text box sounds fine. (Setting its Format to Short Date may help
Access to understand it, as might declaring the parameter in the query, but
that won't solve the issue here.)

The Cartesian query gives every combination of customer and savings in the
quarter. That's what you wanted, but it might be the reason the count it the
same across the quarters?

You should be able to get an outer join by double-clicking the line joining
the 2 tables in query design view. Access offers a dialog with 3 options,
and you choose #2 or #3 depending on the direction of the outer join.

In any structure I can follow, I would have expected the date would be in
the junction table, so I guess I don't really understand the scenario.

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

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

Allen thanks for the response. I am stuck on some of this. For some
reason
I am getting the same count for each savings. Here is my query for
cartesian
and the 2nd query can you see what I may be doing wrong? Thanks. By the
way
I am using an option group and a combo box to build the criteria in a
hidden
text box then when I click sumbit it will run the report. This hidden
text
box is what is used in the queries.

Cartesian
SELECT tblSavings.Savings, tblCustomers.CustomerID,
DateDiff("m",[Forms].[frmRpts].[Text1],[StartDate]) AS MonthOfQuarter,
tblCustomer.StartDate
FROM tblCustomers, tblSavings
WHERE (((tblCustomers.StartDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomers.StartDate)<DateAdd("m",3,[Forms].[frmRpts].[Text1])));

Query2
TRANSFORM Count(tblCustomerSavings.CustomerID) AS CountOfCustomerID
SELECT qrySavingsCartesian.Savings, Count(tblCustomerSavings.CustomerID)
AS
Total
FROM tblCustomerSavings INNER JOIN qrySavingsCartesian ON
tblCustomerSavings.CustomerID = qrySavingsCartesian.CustomerID
GROUP BY qrySavingsCartesian.Savings
PIVOT qrySavingsCartesian.MonthOfQuarter In (0,1,2);

For some reason it won't allow an OUTER JOIN as you suggested for query 2.

I am not sure I understand the goal here. StartDate is part of the
Customer
table? So you are recording when a customer came on board. Then you are
trying to match when a new customer took out one of the accounts in the
Savings table, so you have the junction table between Customers and
Savings,
but there is no date in the junction table? So the whole crosstab relies
only on the date the Customer joined, regardless of when they opened the
Savings account?

Because I am not clear on this, I can only offer general suggestions, and
let you piece them together.

a) The way to get every possible combination of customer and savings is a
query containing Table1 and Table3. There must be no join-line between
the
tables (a Cartesian product.)

b) To limit it to customers that joined in a quarter, can we assume that
the
date on Form1 will be the first date of the quarter? If so, the Criteia
would be:
= [Forms].[Form1].[Text0] And < DateAdd("m", 3,
[Forms].[Form1].[Text0])

c) Type this expression into a fresh column in the Field row:
MonthOfQuarter: DateDiff("m", [Forms].[Form1].[Text0], [startdate])
This will show 0 for the first month in the quarter, 1 for the next, and
2
for the last. These field names are consistent regardless of the quarter,
so
you can later build a report that expects these names.

d) Use an outer join between Table2 and the query above to make your
crosstab. Set the Column Headings property of the crosstab query (in the
Properties box) to:
0,1,2
so that all 3 show up, regardless of the actual data for the quarter. The
crosstab must generate all 3, since the report wll look for these fields.

e) In the report, use a text box for the "label" over the column. The
Control Source for the first column will be:
= [Forms].[Form1].[Text0]
Set the Format of the text box to whatever you want, e.g.:
mmm
The next field's heading will be:
= DateAdd("m", 1, [Forms].[Form1].[Text0])
and the third:
= DateAdd("m", 2, [Forms].[Form1].[Text0])

Hopefully there is enough there to get you through the maze.

I am trying to create a crosstab query to populate a report. I have a
many
to many relationship. For instance table 1 customerid, startdate; table
2
savcustid, customerid, savingsID; table 3 savingsID, savings.

What I am trying to do is on a form select some date corresponding to
the
startdate then in the crosstab query have it show all of the savings
type
even if there is none in table 2. Then have the crosstab count each
customerid for each savings and have it display the months. However, I
only
want it to show the 3 months for the particualr quarter that the
startdate
selected is in. Once this is done it will populate a report as a
subreport,
but I need the 3 labels to reflect the correct months of the quarter
selected
for the main part of the report.

I hope I made it clear as possible.

First can this be done and how do I go about doing this? I have been
trying
to solve this all day without success.

Any help would be greatly appreciated. Thanks in advance.

 

[Guest]

I was wondering if I should base my report off the savings tables that way
all the saving types are listed on the report. Have a hidden field for the
savingstypeid and a visible field for the savings type. I would also have 4
other text boxes for the count of each type for the 3 months plus a total.
Then when the report is built have the savings type field populated and have
it look at the hidden field and have it populate the other 4 corresponding
fields that relate to that hidden field. If not present in the crosstab have
it display 0 (zero). Does this make sense? If so how do I go about having it
popluate the other fields? I guess it would some type of loop or an IIF
statement in the control source.

Not sure what else to suggest.

The hidden text box sounds fine. (Setting its Format to Short Date may help
Access to understand it, as might declaring the parameter in the query, but
that won't solve the issue here.)

The Cartesian query gives every combination of customer and savings in the
quarter. That's what you wanted, but it might be the reason the count it the
same across the quarters?

You should be able to get an outer join by double-clicking the line joining
the 2 tables in query design view. Access offers a dialog with 3 options,
and you choose #2 or #3 depending on the direction of the outer join.

In any structure I can follow, I would have expected the date would be in
the junction table, so I guess I don't really understand the scenario.

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

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

Allen thanks for the response. I am stuck on some of this. For some
reason
I am getting the same count for each savings. Here is my query for
cartesian
and the 2nd query can you see what I may be doing wrong? Thanks. By the
way
I am using an option group and a combo box to build the criteria in a
hidden
text box then when I click sumbit it will run the report. This hidden
text
box is what is used in the queries.

Cartesian
SELECT tblSavings.Savings, tblCustomers.CustomerID,
DateDiff("m",[Forms].[frmRpts].[Text1],[StartDate]) AS MonthOfQuarter,
tblCustomer.StartDate
FROM tblCustomers, tblSavings
WHERE (((tblCustomers.StartDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomers.StartDate)<DateAdd("m",3,[Forms].[frmRpts].[Text1])));

Query2
TRANSFORM Count(tblCustomerSavings.CustomerID) AS CountOfCustomerID
SELECT qrySavingsCartesian.Savings, Count(tblCustomerSavings.CustomerID)
AS
Total
FROM tblCustomerSavings INNER JOIN qrySavingsCartesian ON
tblCustomerSavings.CustomerID = qrySavingsCartesian.CustomerID
GROUP BY qrySavingsCartesian.Savings
PIVOT qrySavingsCartesian.MonthOfQuarter In (0,1,2);

For some reason it won't allow an OUTER JOIN as you suggested for query 2.

I am not sure I understand the goal here. StartDate is part of the
Customer
table? So you are recording when a customer came on board. Then you are
trying to match when a new customer took out one of the accounts in the
Savings table, so you have the junction table between Customers and
Savings,
but there is no date in the junction table? So the whole crosstab relies
only on the date the Customer joined, regardless of when they opened the
Savings account?

Because I am not clear on this, I can only offer general suggestions, and
let you piece them together.

a) The way to get every possible combination of customer and savings is a
query containing Table1 and Table3. There must be no join-line between
the
tables (a Cartesian product.)

b) To limit it to customers that joined in a quarter, can we assume that
the
date on Form1 will be the first date of the quarter? If so, the Criteia
would be:
= [Forms].[Form1].[Text0] And < DateAdd("m", 3,
[Forms].[Form1].[Text0])

c) Type this expression into a fresh column in the Field row:
MonthOfQuarter: DateDiff("m", [Forms].[Form1].[Text0], [startdate])
This will show 0 for the first month in the quarter, 1 for the next, and
2
for the last. These field names are consistent regardless of the quarter,
so
you can later build a report that expects these names.

d) Use an outer join between Table2 and the query above to make your
crosstab. Set the Column Headings property of the crosstab query (in the
Properties box) to:
0,1,2
so that all 3 show up, regardless of the actual data for the quarter. The
crosstab must generate all 3, since the report wll look for these fields.

e) In the report, use a text box for the "label" over the column. The
Control Source for the first column will be:
= [Forms].[Form1].[Text0]
Set the Format of the text box to whatever you want, e.g.:
mmm
The next field's heading will be:
= DateAdd("m", 1, [Forms].[Form1].[Text0])
and the third:
= DateAdd("m", 2, [Forms].[Form1].[Text0])

Hopefully there is enough there to get you through the maze.

I am trying to create a crosstab query to populate a report. I have a
many
to many relationship. For instance table 1 customerid, startdate; table
2
savcustid, customerid, savingsID; table 3 savingsID, savings.

What I am trying to do is on a form select some date corresponding to
the
startdate then in the crosstab query have it show all of the savings
type
even if there is none in table 2. Then have the crosstab count each
customerid for each savings and have it display the months. However, I
only
want it to show the 3 months for the particualr quarter that the
startdate
selected is in. Once this is done it will populate a report as a
subreport,
but I need the 3 labels to reflect the correct months of the quarter
selected
for the main part of the report.

I hope I made it clear as possible.

First can this be done and how do I go about doing this? I have been
trying
to solve this all day without success.

Any help would be greatly appreciated. Thanks in advance.

 

[Guest]

I think I figured it out. You were on the right track. Here is what I came
up with. It shows all the type of savings even if there are no customers for
any month during that quarter.

qrySavingsForCrossTab:
SELECT tblSavings.SavingsTypeId, tblSavings.Savings, tblCustomer.CustomerID,
DateDiff("m",[Forms].[frmRpts].[Text1],[StartDate]) AS MonthOfQuarter
FROM (tblCustSavings INNER JOIN tblSavings ON tblCustSavings.SavingsTypeID =
tblSavings.SavingsTypeId) INNER JOIN tblCustomer ON tblCustSavings.LogID =
tblCustomer.CustomerID
WHERE (((tblCustomer.StartDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.StartDate)<DateAdd("m",3,[Forms].[frmRpts].[Text1])));

CrossTab:
TRANSFORM Count(qrySavingsForCrossTab.CustomerID) AS CountOfCustomerID
SELECT tblSavings.Savings, Count(qrySavingsForCrossTab.Total) AS Total
FROM qrySavingsForCrossTab RIGHT JOIN tblSavings ON
qrySavingsForCrossTab.SavingsTypeId = tblSavings.SavingsTypeId
GROUP BY tblSavings.Savings
PIVOT qrySavingsForCrossTab.MonthOfQuarter In (0,1,2);

Not sure what else to suggest.

The hidden text box sounds fine. (Setting its Format to Short Date may help
Access to understand it, as might declaring the parameter in the query, but
that won't solve the issue here.)

The Cartesian query gives every combination of customer and savings in the
quarter. That's what you wanted, but it might be the reason the count it the
same across the quarters?

You should be able to get an outer join by double-clicking the line joining
the 2 tables in query design view. Access offers a dialog with 3 options,
and you choose #2 or #3 depending on the direction of the outer join.

In any structure I can follow, I would have expected the date would be in
the junction table, so I guess I don't really understand the scenario.

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

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

Allen thanks for the response. I am stuck on some of this. For some
reason
I am getting the same count for each savings. Here is my query for
cartesian
and the 2nd query can you see what I may be doing wrong? Thanks. By the
way
I am using an option group and a combo box to build the criteria in a
hidden
text box then when I click sumbit it will run the report. This hidden
text
box is what is used in the queries.

Cartesian
SELECT tblSavings.Savings, tblCustomers.CustomerID,
DateDiff("m",[Forms].[frmRpts].[Text1],[StartDate]) AS MonthOfQuarter,
tblCustomer.StartDate
FROM tblCustomers, tblSavings
WHERE (((tblCustomers.StartDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomers.StartDate)<DateAdd("m",3,[Forms].[frmRpts].[Text1])));

Query2
TRANSFORM Count(tblCustomerSavings.CustomerID) AS CountOfCustomerID
SELECT qrySavingsCartesian.Savings, Count(tblCustomerSavings.CustomerID)
AS
Total
FROM tblCustomerSavings INNER JOIN qrySavingsCartesian ON
tblCustomerSavings.CustomerID = qrySavingsCartesian.CustomerID
GROUP BY qrySavingsCartesian.Savings
PIVOT qrySavingsCartesian.MonthOfQuarter In (0,1,2);

For some reason it won't allow an OUTER JOIN as you suggested for query 2.

I am not sure I understand the goal here. StartDate is part of the
Customer
table? So you are recording when a customer came on board. Then you are
trying to match when a new customer took out one of the accounts in the
Savings table, so you have the junction table between Customers and
Savings,
but there is no date in the junction table? So the whole crosstab relies
only on the date the Customer joined, regardless of when they opened the
Savings account?

Because I am not clear on this, I can only offer general suggestions, and
let you piece them together.

a) The way to get every possible combination of customer and savings is a
query containing Table1 and Table3. There must be no join-line between
the
tables (a Cartesian product.)

b) To limit it to customers that joined in a quarter, can we assume that
the
date on Form1 will be the first date of the quarter? If so, the Criteia
would be:
= [Forms].[Form1].[Text0] And < DateAdd("m", 3,
[Forms].[Form1].[Text0])

c) Type this expression into a fresh column in the Field row:
MonthOfQuarter: DateDiff("m", [Forms].[Form1].[Text0], [startdate])
This will show 0 for the first month in the quarter, 1 for the next, and
2
for the last. These field names are consistent regardless of the quarter,
so
you can later build a report that expects these names.

d) Use an outer join between Table2 and the query above to make your
crosstab. Set the Column Headings property of the crosstab query (in the
Properties box) to:
0,1,2
so that all 3 show up, regardless of the actual data for the quarter. The
crosstab must generate all 3, since the report wll look for these fields.

e) In the report, use a text box for the "label" over the column. The
Control Source for the first column will be:
= [Forms].[Form1].[Text0]
Set the Format of the text box to whatever you want, e.g.:
mmm
The next field's heading will be:
= DateAdd("m", 1, [Forms].[Form1].[Text0])
and the third:
= DateAdd("m", 2, [Forms].[Form1].[Text0])

Hopefully there is enough there to get you through the maze.

I am trying to create a crosstab query to populate a report. I have a
many
to many relationship. For instance table 1 customerid, startdate; table
2
savcustid, customerid, savingsID; table 3 savingsID, savings.

What I am trying to do is on a form select some date corresponding to
the
startdate then in the crosstab query have it show all of the savings
type
even if there is none in table 2. Then have the crosstab count each
customerid for each savings and have it display the months. However, I
only
want it to show the 3 months for the particualr quarter that the
startdate
selected is in. Once this is done it will populate a report as a
subreport,
but I need the 3 labels to reflect the correct months of the quarter
selected
for the main part of the report.

I hope I made it clear as possible.

First can this be done and how do I go about doing this? I have been
trying
to solve this all day without success.

Any help would be greatly appreciated. Thanks in advance.

 

[Guest]

Actually there was an error of what I sen it should have been.

qrySavingsForCrossTab:
SELECT tblSavings.SavingsTypeId, tblSavings.Savings, tblCustomer.CustomerID,
DateDiff("m",[Forms].[frmRpts].[Text1],[StartDate]) AS MonthOfQuarter
FROM (tblCustSavings INNER JOIN tblSavings ON tblCustSavings.SavingsTypeID =
tblSavings.SavingsTypeId) INNER JOIN tblCustomer ON
tblCustSavings.CustomerID =
tblCustomer.CustomerID
WHERE (((tblCustomer.StartDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.StartDate)<DateAdd("m",3,[Forms].[frmRpts].[Text1])));

I had LogID in one place when it should have been CustomerID.

Not sure what else to suggest.

The hidden text box sounds fine. (Setting its Format to Short Date may help
Access to understand it, as might declaring the parameter in the query, but
that won't solve the issue here.)

The Cartesian query gives every combination of customer and savings in the
quarter. That's what you wanted, but it might be the reason the count it the
same across the quarters?

You should be able to get an outer join by double-clicking the line joining
the 2 tables in query design view. Access offers a dialog with 3 options,
and you choose #2 or #3 depending on the direction of the outer join.

In any structure I can follow, I would have expected the date would be in
the junction table, so I guess I don't really understand the scenario.

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

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

Allen thanks for the response. I am stuck on some of this. For some
reason
I am getting the same count for each savings. Here is my query for
cartesian
and the 2nd query can you see what I may be doing wrong? Thanks. By the
way
I am using an option group and a combo box to build the criteria in a
hidden
text box then when I click sumbit it will run the report. This hidden
text
box is what is used in the queries.

Cartesian
SELECT tblSavings.Savings, tblCustomers.CustomerID,
DateDiff("m",[Forms].[frmRpts].[Text1],[StartDate]) AS MonthOfQuarter,
tblCustomer.StartDate
FROM tblCustomers, tblSavings
WHERE (((tblCustomers.StartDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomers.StartDate)<DateAdd("m",3,[Forms].[frmRpts].[Text1])));

Query2
TRANSFORM Count(tblCustomerSavings.CustomerID) AS CountOfCustomerID
SELECT qrySavingsCartesian.Savings, Count(tblCustomerSavings.CustomerID)
AS
Total
FROM tblCustomerSavings INNER JOIN qrySavingsCartesian ON
tblCustomerSavings.CustomerID = qrySavingsCartesian.CustomerID
GROUP BY qrySavingsCartesian.Savings
PIVOT qrySavingsCartesian.MonthOfQuarter In (0,1,2);

For some reason it won't allow an OUTER JOIN as you suggested for query 2.

I am not sure I understand the goal here. StartDate is part of the
Customer
table? So you are recording when a customer came on board. Then you are
trying to match when a new customer took out one of the accounts in the
Savings table, so you have the junction table between Customers and
Savings,
but there is no date in the junction table? So the whole crosstab relies
only on the date the Customer joined, regardless of when they opened the
Savings account?

Because I am not clear on this, I can only offer general suggestions, and
let you piece them together.

a) The way to get every possible combination of customer and savings is a
query containing Table1 and Table3. There must be no join-line between
the
tables (a Cartesian product.)

b) To limit it to customers that joined in a quarter, can we assume that
the
date on Form1 will be the first date of the quarter? If so, the Criteia
would be:
= [Forms].[Form1].[Text0] And < DateAdd("m", 3,
[Forms].[Form1].[Text0])

c) Type this expression into a fresh column in the Field row:
MonthOfQuarter: DateDiff("m", [Forms].[Form1].[Text0], [startdate])
This will show 0 for the first month in the quarter, 1 for the next, and
2
for the last. These field names are consistent regardless of the quarter,
so
you can later build a report that expects these names.

d) Use an outer join between Table2 and the query above to make your
crosstab. Set the Column Headings property of the crosstab query (in the
Properties box) to:
0,1,2
so that all 3 show up, regardless of the actual data for the quarter. The
crosstab must generate all 3, since the report wll look for these fields.

e) In the report, use a text box for the "label" over the column. The
Control Source for the first column will be:
= [Forms].[Form1].[Text0]
Set the Format of the text box to whatever you want, e.g.:
mmm
The next field's heading will be:
= DateAdd("m", 1, [Forms].[Form1].[Text0])
and the third:
= DateAdd("m", 2, [Forms].[Form1].[Text0])

Hopefully there is enough there to get you through the maze.

I am trying to create a crosstab query to populate a report. I have a
many
to many relationship. For instance table 1 customerid, startdate; table
2
savcustid, customerid, savingsID; table 3 savingsID, savings.

What I am trying to do is on a form select some date corresponding to
the
startdate then in the crosstab query have it show all of the savings
type
even if there is none in table 2. Then have the crosstab count each
customerid for each savings and have it display the months. However, I
only
want it to show the 3 months for the particualr quarter that the
startdate
selected is in. Once this is done it will populate a report as a
subreport,
but I need the 3 labels to reflect the correct months of the quarter
selected
for the main part of the report.

I hope I made it clear as possible.

First can this be done and how do I go about doing this? I have been
trying
to solve this all day without success.

Any help would be greatly appreciated. Thanks in advance.