Summaries with zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a summary query, but I'm getting stuck on the zeros.
For each location, I need a summary of products even if there are no entries
for that product in my data table. I have created a second table for my
products but when I play around with the relationships I still can't seem to
get a summary for each product in each location.

HELP PLEASE!!
 
Presumably you have:
- a Products table (one record per product);
- a Location table (one record per location);
- some kind of sale table (one record for each time a product is sold at a
location).

If you create a query with these 3 tables you only get the records that
exist in the Sale table, i.e. if a product was not sold at a location, you
don't get any record for that combination of Product + Location

1. Create a query containing the Product and Location tables. There will be
no join between them. This gives you every combination of Product and
Location. Save as (say) Query1.

(Technically, this is called a Cartesian product - no join, so it yields
every possible combination.)


2. Create a query into the Sale table.
Depress the Total button on the toolbar (upper sigma icon.) Access adds a
Total row to the grid.

Drag ProductID and LocationID into the grid, accepting Group By under these
fields.

Drag the Quantity field into the grid. Choose Sum under this field.

Set any criteria you want (e.g. the limiting dates under the SaleDate
field), choosing Where in the Total row for these fields.

Save the query as (say) Query2.

(Technically, this is called an outer join.)


3. Create a third query, using both Query1 and Query2 as input "tables".

Drag Query1.ProductID onto Query2.ProductID, so Access shows a join line.
Double-click this line, and choose the option that says:
All records from Query1, and any matches from Query2.

Drag Query1.LocationID onto Query2.ProductID, so Access shows a 2nd join
line. Double-click this line also, and chose the same join type.

The 3rd query shows every combination of product and location (the Cartesian
product), outer-joined to the summary data for the period.
 
Vanessa said:
I'm trying to create a summary query, but I'm getting stuck on the zeros.
For each location, I need a summary of products even if there are no entries
for that product in my data table. I have created a second table for my
products but when I play around with the relationships I still can't seem to
get a summary for each product in each location.

HELP PLEASE!!
Click to expand...


Please provide the DDL (including CONSTRAINTS) of the
table(s) involved, or a well-formatted text
description of its(their) structure (including
relationships), and some sample data from the(each)
table.


Sincerely,

Chris O.
 
I learned a new vocabulary word! I saw you suggest that for some else, but I
didn't know what it meant.

I just thought I'd tell you that you just helped me change my most hated
part of my job from a week every month down to one working day. YEAH!!!

smiles, vw
 
Great news, Vanessa.

Hopefully your boss thinks, "Hey, this is one creative person, what else can
they achieve?" rather than, "Well, I don't need you as many hours then."
:-)
 
Back
Top