Formula's

[Kevin]

I am setting up a report from a query that has multiple products (36),
shipping locations (1,500)/ types (2), retail price for each product at each
location, and amount sold from each location per product/location.

What I need this report to do is to have one line per product that has four
total fields. Two type fields that will count the number locations per the
type. In the third field, calculate the average retail price. The last field
would then sum the total items sold (all locations).

My problem is creating the correct formula’s, I have little experience with
access formulas. What would be the correct way to write these formulas?

 

[Allen Browne]

Use a Totals query to aggregate the data:

1. Create a query using this table.

2. Depress the Total button (on the toolbar, in query design view.)
Access adds a Total row to the grid.

3. Drag the Product field into the grid.
Accept Group By in the Total row under this field.

4. Drag the Location field into the grid.
In the Total row, choose Count.

5. Drag RetailPrice field into the grid.
In the Total row, choose Average.

6. Drag the amount field into the grid.
In the Total row, choose Sum.

You can now make a report based on this query.

 

[Kevin]

Regarding the location field… I actually need two fields; one to count each
location type. How would you suggest that adjust for that?

 

[Allen Browne]

It sounds like you will have a lookup table for the location types. This
table will have 2 records (for the 2 location types.)

You probably already have a table for locations (one record for each
location.) This table will have a LocationTypeID field, which relates to the
LocationType table (above.)

This structure means you know the type of each location, so you can group on
that.