Counting Missing values in a specific field.

  • Thread starter Thread starter Chuckfonta
  • Start date Start date
C

Chuckfonta

I have a table of purchase orders which includes: client, price range,
payment status, and amount. I am trying to count the number of purchses
base upon the price range for each client. Say price ranges could be one of:
full, discount, or returned. If there is at least one purchase in each
price range count works fine, and returns the number of purchases.
However; if there is no discount purchase for example, count returns a value
which I can not use in subsequent calculations.

What I'm trying to do is calculate a rewards program based upon the number
of full price purchases and discout purchases.

I'm not famalior with Visual Basic, and have designed my system using only
the graphic interface.
 
Chuckfonta said:
I have a table of purchase orders which includes: client, price range,
payment status, and amount. I am trying to count the number of
purchses base upon the price range for each client. Say price ranges
could be one of: full, discount, or returned. If there is at least
one purchase in each price range count works fine, and returns the
number of purchases.
However; if there is no discount purchase for example, count returns
a value which I can not use in subsequent calculations.

What I'm trying to do is calculate a rewards program based upon the
number of full price purchases and discout purchases.

I'm not famalior with Visual Basic, and have designed my system using
only the graphic interface.
Click to expand...

Create a new table called PriceRanges. It should contain at least the
PriceRange field. Add the three records, one for each type of price range.

Then open your counting query in Design mod, add the PriceRanges table to
the query, click and drag to create the join between the the price range
fields in the upper window, right-click the resulting join line (this takes
some practice) and choose join properties from the context menu. Click the
option to make it select all the records from PriceRanges and only the
matching records from purchase orders. Click OK. If you switch to SQL View
(with which you really should familiarize yourself - get in the habit of
looking at the sql that the query builder generates - when you have a
question about a query, we will usually need to see that generated sql
statement), you will see that the query now uses a LEFT OUTER JOIN. Look
that up in online help. Read the associated topics carefully.

Switch back to Design View and, in the grid, change the grouping field to
the PriceRange field from PriceRanges. Run the query - it should now give
you what you want.
 
Back
Top