Access Count Expression Help RRS feed

  • Question

  • I am currently building an Access Database to calculate payroll for a client, the have more than one thing to calculate.  I am incredibly rusty with Access (Over 6 years since I used it).  

    My issue is I am trying to make a Totals column that is a multiplying of a Count subquery column to 35.00.  Basically it is a total of commissions for the month times the fixed price of 35.00.  I am using Builder, to rusty to work with SQL.

    Thursday, January 19, 2017 11:40 PM

All replies

  • Can you post the SQL statement of the query? Thanks.
    Thursday, January 19, 2017 11:45 PM
  • This is what it looks like.

    SELECT [Commisions Extended].Employee, Count([Commisions Extended].[Client Name]) AS [Total Clients], Sum([Client Name]*[Mty Commision Amount]) AS Expr1
    FROM [Commisions Extended]
    GROUP BY [Commisions Extended].Employee, [Commisions Extended].[On Hold], [Commisions Extended].Canceled, [Commisions Extended].[Cancel Date]
    HAVING ((([Commisions Extended].[On Hold])=No) AND (([Commisions Extended].Canceled)=No Or ([Commisions Extended].Canceled) Is Null) AND ((DateDiff("m",[Cancel Date],Now()))>1)) OR ((([Commisions Extended].[On Hold])=No) AND (([Commisions Extended].Canceled)=No Or ([Commisions Extended].Canceled) Is Null) AND (([Commisions Extended].[Cancel Date]) Is Null));

    Friday, January 20, 2017 2:15 AM
  • When I have a complex query to write (and I always code in SQL, have never used and never will use the builder), I often write one query to do part of the task, and then write another query to read the output of the first query and do some more of the task. Sometimes I have several 'levels' of query, each reading from a query and passing output to yet another query. This may not be the most efficient way of handling a task but it is much easier to code and much more understandable when you come to make changes later. I encourage you to employ this technique. Everything does not have to be done in a single query.

    Friday, January 20, 2017 2:50 AM
  • I am pulling the data from one query to make the totals query.  I am bookkeeper with Knowledge of access but my SQL coding is just majorly rusty, I use Quickbooks normally.  Just a particular client has a very involved payroll of multiple different data pulled from different sources.  I am creating a summary database.
    Friday, January 20, 2017 3:59 AM
  • https://support.office.com/en-us/article/Count-Function-6923a7b3-aa59-4027-ada6-d774525a4009
    Tuesday, January 24, 2017 12:18 PM
  • Hi,

    Have you resolved your issue?

    Could you share sample data with us including table structure, table data, current query and expected result?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 25, 2017 9:07 AM