Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    3

    Unanswered: Issues with Counting (DCount, Criteria, etc)

    Hello,

    I have a table that I would like to perform an Access query on, preferably avoiding VBA. Using Access 2010.

    I have a table of records, with surrounding quantitative and qualitative data. What I'd like to do is count the total number of records for each business division, and then within each business division the number of records fulfilling two different criteria, and then perform a calculation on sums of values within records in each business division.

    For instance, I have store A and B. Store A made 100 sales (# of records), and store B made 200 sales. Of those 100 and 200 sales, 30 and 40 sales respectively have a value of "Yes" in a 'Yes' or 'No' column. Of those 100 and 200 sales, 70 and 90 have a sale price that is neither blank or non-zero (not necessary to know how many of the 70 and 90 had yes/no). Lastly, I'd like to sum up the total value of sales over those 100 records and 200 records and subtract the total value of another variable. Let's say the difference is 300 and 400.

    Outcome should be:

    A / 100 / 30 / 70 / 300
    B / 200 / 40 / 90 / 400

    I tried using a standard Query, where I grouped by store name. Then I tried to add query columns with "Total: Count" for the other fields, but when I add Criteria values such as {<>"No"} or {>0} there's a "Data type mismatch in Criteria expression." If I include a Total: Group By column I can add the filter, but that adjusts the filter for my entire query (it will filter out all the "No" and non-zero records)

    I tried DCount, where I entered a column with something like the following in the top column:

    DCount("Sale Price","tblSales", "[Sale Price] > 0")

    But that told me the total number of non-sales over all stores, not just A and B.

    Not really sure what to do at this point. I could see myself creating multiple queries and building one query to sample all of them, but this record table is massive. Advice? Any additional info I can provide?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you have a sales value column that could be blank or zero?
    do you mean NULL?
    when you say "yes" or " no do you mean the yes/no repesentation of Boolean columns in Access

    in the mean time this should get you close to where you want to be
    Code:
    SELECT Sales.DivisionID, SUM( IIF(Sales.MyBooleanColumn = TRUE,1,0)) AS NoTrue, sum(iif(Sales.Value<>0,1,0)) AS NoOfSales, sum(Sales.Value) as SumOfSales
    FROM Sales
    group by DivisionID;
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    3
    Quote Originally Posted by healdem View Post
    why would you have a sales value column that could be blank or zero?
    do you mean NULL?
    when you say "yes" or " no do you mean the yes/no repesentation of Boolean columns in Access

    in the mean time this should get you close to where you want to be
    Code:
    SELECT Sales.DivisionID, SUM( IIF(Sales.MyBooleanColumn = TRUE,1,0)) AS NoTrue, sum(iif(Sales.Value<>0,1,0)) AS NoOfSales, sum(Sales.Value) as SumOfSales
    FROM Sales
    group by DivisionID;
    It's complicated why we'd have 0s, and the blanks are actual blanks from Excel when someone neglected to input a 0 instead. I'm not sure if Access forces these to "Null" values when importing. But your code worked out for that column, thanks!

    The "Yes" and "No" are strings, or Text values. The words aren't actually "Yes" or "No", but I thought that would be more simple to get across. The words are "Certified" or "Not Certified". I'm trying to figure out how to use Iff statements to compare strings right now. Any way to do it as simple as your code?

    Also, how would I go about finding the difference between the sum of one column and another, per on DivisionID?

    Thanks again for your help!
    Last edited by Computerz; 09-25-13 at 12:44.

  4. #4
    Join Date
    Sep 2013
    Posts
    3
    I got it! Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •