Group By And Count

  • Thread starter Thread starter tye
  • Start date Start date
T

tye

Hi,

I have a database that has an orderno that starts with
letters then numbers. e.g BQ12563 OR WHS12564

I want the Query to do is count up all the same Campaigns
so it tells me how many sales where made on what campaign

e.g on BQ 79 SALES WERE MADE
ON HCC 104 SALE WERE MADE

I can get all the sale that where made on which date BUT,
if the total was 127 sale for one day it will count 127

This is beacuse it has letters before the number every
sale has different numbers after the letters.

The letters are the same for one campaigns


Thanks

tye
 
Tye

Congratulations! You have come up with a great explanation of why it is not
desirable in a relational database (like Access) to embed more than one
"fact" in a field. From your description, it sounds like your "orderno"
includes a fact about a "Campaign", and a fact about ???.

To save yourself headaches in the future, consider using two fields to store
two facts.

In the short run, you might use the Left(), Mid(), Right(), and Instr()
functions in a query to isolate out the string (letters) portion of your
orderno. Oh wait, you described one as "BQ" and another as "WHS". Does
that mean that the abbreviations you have for "Campaigns" are not all the
same number of characters?

If Campaigns have varying length designators, is there any other way you can
derive "Campaign"? If not, you'll need to write a procedure that steps
through each orderno, checking for the first time it hits a digit, then
returning all the characters up to that point. Wouldn't it be easier to
separate the "facts"?
 
Back
Top