Create report that subtracts data over two years

[kennysadm]

Please help.
I'll try to be as detailed as posssible.
I do not know VBA code, so if we can do it the the design wizards or
graphical interface, I would appreciate it. Although if you spell out the
code, I can probably follow along enough to put in the correct field names.

I need to a report that shows info in the following manner.

CustomerID
ProductID Jan. Feb. Mar. Apr. May etc...
2008 10 15 0 25 13
2007 5 30 10 15 70
Difference 5 -15 -10 10 -57


The numbers or the number of cases sold of that product ot that particular
customer that month.

I have tried a number of ways to do this.
Each customer may not have bought the same products each year, but I still
want all the products they ordered over the two years to be listed.
At present I am trying the following:

tbl2007MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

tbl2008MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

In both tables, I have a combination primary key of CustomerID, ProductID,
and Month. I do have a customer table, and a product table as well linked to
the two tables.

Crosstab query for each table.

Then a report based on the 2 queries. I use CustomerID from the Customer
table, and the ProductID from the Product table. I can't use them from them
from the yearly sales tables, because of different customers and products
each year, and it won't group. I use the months from the crosstab queries.

However, the following appears on the report:

CustomerID
Product ID Jan. Feb. Mar.
2008 11 0 1
2007 0 3 10
2008 10 5 3
2007 1 2 3

For some reason it repeats the case data many times, and doesn't relate the
correct ProductID to the correct CustomerID.
2007 and 2008 are labels I created in design view.

Any help would be appreciated. I am willing to rework everything, including
the sales tables. Also, I would appreciate help with the formula to subtract
the years.

I am using the following, but sometimes nothing comes up. It might have to
do with the way it is putting in many years for each product.

=(Nz([qry2008MonthlySales_Crosstab_January],0)-Nz([qry2007MonthlySales_Crosstab_January],0))

 

[Allen Browne]

The first thing you need to do is to combine the 2 tables into one, with an
extra field for the year. The new table will have fields like this:
- CustomerID
- ProductID
- TheYear
- TheMonth
- CasesBought
- CashPaid

(Note that Year and Month will give you problems, so I've suggested an
alternative.)

You can now create a crosstab where:
- CustomerID is a Row Heading
- ProductID is a Row Heading
- TheYear is a Row Heading
- TheMonth is a Column Heading
- sum of CasesBought (or sum of CashPaid) is the value.

Once you have that query working, you can modify the TRANSFORM clause by
adding Nz() so it gives you a zero for null. Details in:
Crosstab query techniques: Display zeros (not blanks)
at:
http://allenbrowne.com/ser-67.html#DisplayZero

Ultimately, the difference assumes there's only 2 specific years, so perhaps
you could use an expression in the ProductID group footer such as:
=Sum(IIf([TheYear] = 2008, [SumOfCasesBought], -[SumOfCasesBought])

It is actually possible to combine 2 tables with a UNION query if you are
comfortable writing the SQL (i.e. it cannot be done in graphical view.)
However, this does not solve your long-term problems, where combining into
one table does.

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

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

Please help.
I'll try to be as detailed as posssible.
I do not know VBA code, so if we can do it the the design wizards or
graphical interface, I would appreciate it. Although if you spell out the
code, I can probably follow along enough to put in the correct field
names.

I need to a report that shows info in the following manner.

CustomerID
ProductID Jan. Feb. Mar. Apr. May etc...
2008 10 15 0 25 13
2007 5 30 10 15 70
Difference 5 -15 -10 10 -57


The numbers or the number of cases sold of that product ot that particular
customer that month.

I have tried a number of ways to do this.
Each customer may not have bought the same products each year, but I still
want all the products they ordered over the two years to be listed.
At present I am trying the following:

tbl2007MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

tbl2008MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

In both tables, I have a combination primary key of CustomerID, ProductID,
and Month. I do have a customer table, and a product table as well linked
to
the two tables.

Crosstab query for each table.

Then a report based on the 2 queries. I use CustomerID from the Customer
table, and the ProductID from the Product table. I can't use them from
them
from the yearly sales tables, because of different customers and products
each year, and it won't group. I use the months from the crosstab
queries.

However, the following appears on the report:

CustomerID
Product ID Jan. Feb. Mar.
2008 11 0 1
2007 0 3 10
2008 10 5 3
2007 1 2 3

For some reason it repeats the case data many times, and doesn't relate
the
correct ProductID to the correct CustomerID.
2007 and 2008 are labels I created in design view.

Any help would be appreciated. I am willing to rework everything,
including
the sales tables. Also, I would appreciate help with the formula to
subtract
the years.

I am using the following, but sometimes nothing comes up. It might have
to
do with the way it is putting in many years for each product.

=(Nz([qry2008MonthlySales_Crosstab_January],0)-Nz([qry2007MonthlySales_Crosstab_January],0))

 

[kennysadm]

Thanks for your help Mr. Browne.

Unfortunately I can't get the formula in the report to work. The error
states "Data Type Mismatch in Criteria Expression".

=Sum(IIf([TheYear] = 2008, [January], -[January])

I combined the two tables into one, and used the Nz() function as well.

This expression just gives me the negative of the cases from January 2007.
=(IIf([TheYear]=2008,[January],0))-(IIf([TheYear]=2007,[January],0))

Any other suggestions?

The first thing you need to do is to combine the 2 tables into one, with an
extra field for the year. The new table will have fields like this:
- CustomerID
- ProductID
- TheYear
- TheMonth
- CasesBought
- CashPaid

(Note that Year and Month will give you problems, so I've suggested an
alternative.)

You can now create a crosstab where:
- CustomerID is a Row Heading
- ProductID is a Row Heading
- TheYear is a Row Heading
- TheMonth is a Column Heading
- sum of CasesBought (or sum of CashPaid) is the value.

Once you have that query working, you can modify the TRANSFORM clause by
adding Nz() so it gives you a zero for null. Details in:
Crosstab query techniques: Display zeros (not blanks)
at:
http://allenbrowne.com/ser-67.html#DisplayZero

Ultimately, the difference assumes there's only 2 specific years, so perhaps
you could use an expression in the ProductID group footer such as:
=Sum(IIf([TheYear] = 2008, [SumOfCasesBought], -[SumOfCasesBought])

It is actually possible to combine 2 tables with a UNION query if you are
comfortable writing the SQL (i.e. it cannot be done in graphical view.)
However, this does not solve your long-term problems, where combining into
one table does.

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

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

Please help.
I'll try to be as detailed as posssible.
I do not know VBA code, so if we can do it the the design wizards or
graphical interface, I would appreciate it. Although if you spell out the
code, I can probably follow along enough to put in the correct field
names.

I need to a report that shows info in the following manner.

CustomerID
ProductID Jan. Feb. Mar. Apr. May etc...
2008 10 15 0 25 13
2007 5 30 10 15 70
Difference 5 -15 -10 10 -57


The numbers or the number of cases sold of that product ot that particular
customer that month.

I have tried a number of ways to do this.
Each customer may not have bought the same products each year, but I still
want all the products they ordered over the two years to be listed.
At present I am trying the following:

tbl2007MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

tbl2008MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

In both tables, I have a combination primary key of CustomerID, ProductID,
and Month. I do have a customer table, and a product table as well linked
to
the two tables.

Crosstab query for each table.

Then a report based on the 2 queries. I use CustomerID from the Customer
table, and the ProductID from the Product table. I can't use them from
them
from the yearly sales tables, because of different customers and products
each year, and it won't group. I use the months from the crosstab
queries.

However, the following appears on the report:

CustomerID
Product ID Jan. Feb. Mar.
2008 11 0 1
2007 0 3 10
2008 10 5 3
2007 1 2 3

For some reason it repeats the case data many times, and doesn't relate
the
correct ProductID to the correct CustomerID.
2007 and 2008 are labels I created in design view.

Any help would be appreciated. I am willing to rework everything,
including
the sales tables. Also, I would appreciate help with the formula to
subtract
the years.

I am using the following, but sometimes nothing comes up. It might have
to
do with the way it is putting in many years for each product.

=(Nz([qry2008MonthlySales_Crosstab_January],0)-Nz([qry2007MonthlySales_Crosstab_January],0))

 

[Allen Browne]

Data type mismatch means that something you think is a number, Access thinks
is text (or some other type.)

What is the data type of your field TheYear? I assumed number.
If text, you need extra quotes:
=IIf([TheYear = "2008", ...

Similarly with the other columns.

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

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

Thanks for your help Mr. Browne.

Unfortunately I can't get the formula in the report to work. The error
states "Data Type Mismatch in Criteria Expression".

=Sum(IIf([TheYear] = 2008, [January], -[January])

I combined the two tables into one, and used the Nz() function as well.

This expression just gives me the negative of the cases from January 2007.
=(IIf([TheYear]=2008,[January],0))-(IIf([TheYear]=2007,[January],0))

Any other suggestions?

The first thing you need to do is to combine the 2 tables into one, with
an
extra field for the year. The new table will have fields like this:
- CustomerID
- ProductID
- TheYear
- TheMonth
- CasesBought
- CashPaid

(Note that Year and Month will give you problems, so I've suggested an
alternative.)

You can now create a crosstab where:
- CustomerID is a Row Heading
- ProductID is a Row Heading
- TheYear is a Row Heading
- TheMonth is a Column Heading
- sum of CasesBought (or sum of CashPaid) is the value.

Once you have that query working, you can modify the TRANSFORM clause by
adding Nz() so it gives you a zero for null. Details in:
Crosstab query techniques: Display zeros (not blanks)
at:
http://allenbrowne.com/ser-67.html#DisplayZero

Ultimately, the difference assumes there's only 2 specific years, so
perhaps
you could use an expression in the ProductID group footer such as:
=Sum(IIf([TheYear] = 2008, [SumOfCasesBought], -[SumOfCasesBought])

It is actually possible to combine 2 tables with a UNION query if you are
comfortable writing the SQL (i.e. it cannot be done in graphical view.)
However, this does not solve your long-term problems, where combining
into
one table does.

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

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

Please help.
I'll try to be as detailed as posssible.
I do not know VBA code, so if we can do it the the design wizards or
graphical interface, I would appreciate it. Although if you spell out
the
code, I can probably follow along enough to put in the correct field
names.

I need to a report that shows info in the following manner.

CustomerID
ProductID Jan. Feb. Mar. Apr. May etc...
2008 10 15 0 25 13
2007 5 30 10 15 70
Difference 5 -15 -10 10 -57


The numbers or the number of cases sold of that product ot that
particular
customer that month.

I have tried a number of ways to do this.
Each customer may not have bought the same products each year, but I
still
want all the products they ordered over the two years to be listed.
At present I am trying the following:

tbl2007MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

tbl2008MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

In both tables, I have a combination primary key of CustomerID,
ProductID,
and Month. I do have a customer table, and a product table as well
linked
to
the two tables.

Crosstab query for each table.

Then a report based on the 2 queries. I use CustomerID from the
Customer
table, and the ProductID from the Product table. I can't use them from
them
from the yearly sales tables, because of different customers and
products
each year, and it won't group. I use the months from the crosstab
queries.

However, the following appears on the report:

CustomerID
Product ID Jan. Feb. Mar.
2008 11 0 1
2007 0 3 10
2008 10 5 3
2007 1 2 3

For some reason it repeats the case data many times, and doesn't relate
the
correct ProductID to the correct CustomerID.
2007 and 2008 are labels I created in design view.

Any help would be appreciated. I am willing to rework everything,
including
the sales tables. Also, I would appreciate help with the formula to
subtract
the years.

I am using the following, but sometimes nothing comes up. It might
have
to
do with the way it is putting in many years for each product.

=(Nz([qry2008MonthlySales_Crosstab_January],0)-Nz([qry2007MonthlySales_Crosstab_January],0))

 

[kennysadm]

Thank you for your assistance Mr. Browne.

That did the trick.


Kennysadmin

Data type mismatch means that something you think is a number, Access thinks
is text (or some other type.)

What is the data type of your field TheYear? I assumed number.
If text, you need extra quotes:
=IIf([TheYear = "2008", ...

Similarly with the other columns.

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

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

Thanks for your help Mr. Browne.

Unfortunately I can't get the formula in the report to work. The error
states "Data Type Mismatch in Criteria Expression".

=Sum(IIf([TheYear] = 2008, [January], -[January])

I combined the two tables into one, and used the Nz() function as well.

This expression just gives me the negative of the cases from January 2007.
=(IIf([TheYear]=2008,[January],0))-(IIf([TheYear]=2007,[January],0))

Any other suggestions?

The first thing you need to do is to combine the 2 tables into one, with
an
extra field for the year. The new table will have fields like this:
- CustomerID
- ProductID
- TheYear
- TheMonth
- CasesBought
- CashPaid

(Note that Year and Month will give you problems, so I've suggested an
alternative.)

You can now create a crosstab where:
- CustomerID is a Row Heading
- ProductID is a Row Heading
- TheYear is a Row Heading
- TheMonth is a Column Heading
- sum of CasesBought (or sum of CashPaid) is the value.

Once you have that query working, you can modify the TRANSFORM clause by
adding Nz() so it gives you a zero for null. Details in:
Crosstab query techniques: Display zeros (not blanks)
at:
http://allenbrowne.com/ser-67.html#DisplayZero

Ultimately, the difference assumes there's only 2 specific years, so
perhaps
you could use an expression in the ProductID group footer such as:
=Sum(IIf([TheYear] = 2008, [SumOfCasesBought], -[SumOfCasesBought])

It is actually possible to combine 2 tables with a UNION query if you are
comfortable writing the SQL (i.e. it cannot be done in graphical view.)
However, this does not solve your long-term problems, where combining
into
one table does.

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

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

Please help.
I'll try to be as detailed as posssible.
I do not know VBA code, so if we can do it the the design wizards or
graphical interface, I would appreciate it. Although if you spell out
the
code, I can probably follow along enough to put in the correct field
names.

I need to a report that shows info in the following manner.

CustomerID
ProductID Jan. Feb. Mar. Apr. May etc...
2008 10 15 0 25 13
2007 5 30 10 15 70
Difference 5 -15 -10 10 -57


The numbers or the number of cases sold of that product ot that
particular
customer that month.

I have tried a number of ways to do this.
Each customer may not have bought the same products each year, but I
still
want all the products they ordered over the two years to be listed.
At present I am trying the following:

tbl2007MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

tbl2008MonthlySales with the following fields:
CustomerID
ProductID
Month
CasesBought
CashPaid

In both tables, I have a combination primary key of CustomerID,
ProductID,
and Month. I do have a customer table, and a product table as well
linked
to
the two tables.

Crosstab query for each table.

Then a report based on the 2 queries. I use CustomerID from the
Customer
table, and the ProductID from the Product table. I can't use them from
them
from the yearly sales tables, because of different customers and
products
each year, and it won't group. I use the months from the crosstab
queries.

However, the following appears on the report:

CustomerID
Product ID Jan. Feb. Mar.
2008 11 0 1
2007 0 3 10
2008 10 5 3
2007 1 2 3

For some reason it repeats the case data many times, and doesn't relate
the
correct ProductID to the correct CustomerID.
2007 and 2008 are labels I created in design view.

Any help would be appreciated. I am willing to rework everything,
including
the sales tables. Also, I would appreciate help with the formula to
subtract
the years.

I am using the following, but sometimes nothing comes up. It might
have
to
do with the way it is putting in many years for each product.

=(Nz([qry2008MonthlySales_Crosstab_January],0)-Nz([qry2007MonthlySales_Crosstab_January],0))