number rows in query not a form or report

[Guest]

Hi,

I am working on a sales table. I have sales data by month for product ids.
I need to compaire the first 18 months of sales for all products no matter
the date. Therefore I need to add an incrimental field to the query.

 

[Allen Browne]

Presumably you have:
- a table of products, with a StartDate indicating when the product first
went to market;
- a table of sales, with fields:
o SaleDate when this sale occurred.
o ProductID which product was sold
o Quantity how many in this sale.

Now you want to sum the quantity of each product for its first 18 months on
the market.

1. Create a query using both the Product and Sale tables.

2. Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row to the query design grid.

3. Drag the ProductID field from the Product table into the grid.
Accept Group By in the Total row.

4. Drag the Quantity field into the grid.
Choose Sum in the Total row for under this field.

5. Drag the SaleDate field into the grid.
Choose Where in the Total row.
In the Criteria, enter this (one line):
Between [Product].[StartDate] And DateAdd("m", 18,
[Product].[StartDate])