none
Multiple days data on a single line RRS feed

  • Question


  • My data table looks like this: 
    Day|employee number|bins finished 

    I want a single line on my report to show a week's worth of product (finished bins). An employee normally will have several entries per day.

    The first bin field on the first line of my report was simple:
    =sum ([bins finished])

    For the next day, same line, I tried this for that fields data:
    =Dsum("[bins finished]","bin table","[day]=#" &[pay period start date]&"#+1" and "[employee number] = 1")

    Should just get bins for employee one but getting all employees. 

    Bear with me, I am not good at this at all. I program with access one every several years.
    Wednesday, July 13, 2016 9:37 PM

Answers

  • Does this work better?

    =DSum("[bins finished]","bin table","[day]=DateAdd('d', 1, #" & [pay period start date] & "#) And [employee number]=1")

    If there is a chance that the database will be used by someone who doesn't use US format dates (m/d/yyyy), change it to

    =DSum("[bins finished]","bin table","[day]=DateAdd('d', 1, #" & Format([pay period start date], "mm\/dd\/yyyy") & "#) And [employee number]=1")


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

    Wednesday, July 13, 2016 10:12 PM

All replies

  • Does this work better?

    =DSum("[bins finished]","bin table","[day]=DateAdd('d', 1, #" & [pay period start date] & "#) And [employee number]=1")

    If there is a chance that the database will be used by someone who doesn't use US format dates (m/d/yyyy), change it to

    =DSum("[bins finished]","bin table","[day]=DateAdd('d', 1, #" & Format([pay period start date], "mm\/dd\/yyyy") & "#) And [employee number]=1")


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

    Wednesday, July 13, 2016 10:12 PM
  • Thank you Hans!  

    Thursday, July 14, 2016 4:17 PM