K
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))
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))