none
Access RRS feed

  • Question

  • Need a formula for access to show the change in sales from one day to the next by sales person. Example if on 1/1/16 Sally had $100 in sales and the next days her sales went to $150 how do I write a formula to show she increased her sales by $50

    Friday, August 12, 2016 7:44 PM

Answers

  • This is essentially the same as the common scenario for determining the differences in readings from some form of instrumentation, e.g. the following query will return the consumption between readings from meters of some sort:

    SELECT MeterID, ReadingDate, Reading, Reading -
        NZ((SELECT Reading
              FROM Readings AS R2
              WHERE R2.MeterID = R1.MeterID
              AND ReadingDate =
                  (SELECT MAX(ReadingDate)
                   FROM Readings AS R3
                   WHERE R3.MeterID = R1.MeterID
                   AND R3.ReadingDate < R1.ReadingDate)),0) AS Consumption
    FROM Readings AS R1
    ORDER BY MeterID, ReadingDate DESC;

    Think of meters as being analogous to your sales people, and readings as analogous to the value of sales.

    Ken Sheridan, Stafford, England

    Wednesday, August 17, 2016 11:14 PM

All replies

  • Hi. The answer might be similar to this one. Hope it helps...
    Friday, August 12, 2016 8:01 PM
  • For example:

    Difference: [Sales]-DLookup("[Sales]", "[NameOfTable]", "[SalesDate]=#" & [SalesDate]-1 & "#")

    substituting the correct names.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, August 12, 2016 8:04 PM
  • Thank you that helped

    Tuesday, August 16, 2016 8:43 PM
  • The formula works perfect when I query on one sales person, but not when there is more than one sales person.  What do I need to do to get this to calculate for each sales person?

    Tuesday, August 16, 2016 8:54 PM
  • If each sales person has a numeric ID:

    Difference: [Sales]-DLookup("[Sales]", "[NameOfTable]", "[SalesDate]=#" & [SalesDate]-1 & "# AND [SalesPersonID]=" & [SalesPersonID])

    If you just have the name of the sales person:

    Difference: [Sales]-DLookup("[Sales]", "[NameOfTable]", "[SalesDate]=#" & [SalesDate]-1 & "# AND [SalesPerson]=" & Chr(34) & [SalesPerson] & Chr(34))

    Again, substituting the correct field names.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 16, 2016 9:16 PM
  • My Sales ID is a Text as well as the sales employee name.  When I use the above formula for sales employee I am getting an action query cannot be used as a row source.  Here is my actual formula

    Difference: [Revenue]-DLookup("[Revenue]", "[Revenue Table]", "[ImportDate]=#" & [ImportDate]-1 & "# AND [SA ID]=" & Chr(34) & [SA ID] & Chr(34))

    Any suggestions on how to fix that?

    Wednesday, August 17, 2016 2:59 PM
  • What kind of query are you using this expression in?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 17, 2016 6:46 PM
  • Select Query


    Me2metoo

    Wednesday, August 17, 2016 7:06 PM
  • I also removed the SA from my sales id so they would be number format and I am still getting an error


    Me2metoo

    Wednesday, August 17, 2016 7:07 PM
  • I'm afraid I don't understand why you'd get an error about a row source.

    Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Access forum. You can attach files up to 250 KB to a post there.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 17, 2016 7:48 PM
  • It looks like I have two problems,

    1st when I created the update query to change the rep id to numeric numbers only the table updates as text.

    2nd is that my dates are not consecutive, missing weekend dates so the formula is not calculating on those dates.


    Me2metoo

    Wednesday, August 17, 2016 8:05 PM
  • This is essentially the same as the common scenario for determining the differences in readings from some form of instrumentation, e.g. the following query will return the consumption between readings from meters of some sort:

    SELECT MeterID, ReadingDate, Reading, Reading -
        NZ((SELECT Reading
              FROM Readings AS R2
              WHERE R2.MeterID = R1.MeterID
              AND ReadingDate =
                  (SELECT MAX(ReadingDate)
                   FROM Readings AS R3
                   WHERE R3.MeterID = R1.MeterID
                   AND R3.ReadingDate < R1.ReadingDate)),0) AS Consumption
    FROM Readings AS R1
    ORDER BY MeterID, ReadingDate DESC;

    Think of meters as being analogous to your sales people, and readings as analogous to the value of sales.

    Ken Sheridan, Stafford, England

    Wednesday, August 17, 2016 11:14 PM
  • PS:  remove the NZ function call from the query if you want the initial 'difference' per sales person to be Null rather than the initial sales amount.

    Ken Sheridan, Stafford, England

    Wednesday, August 17, 2016 11:22 PM
  • Hans, I have joined www.eileenslounge.com under the id Me2metoo and posted the formula and the results in my query for your assistance. 

    Me2metoo

    Thursday, August 25, 2016 4:21 PM