Results 1 to 5 of 5

Trying to use a Sumif Style Statement in Access using a Concatenate Function

  1. #1
    NathanGay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2016
    Posts
    2

    Trying to use a Sumif Style Statement in Access using a Concatenate Function


    So essentially I am trying to move a bunch of various excel and access files into one access database. Ill do my best to explain my predicament.

    The monthly data (pounds processed) is inputted into an excel sheet by date, shift, material type, etc. There is a separate monthly summary sheet that totals the pounds produced for all material throughout the day by using a Concatenate function to combine the DATE with the SHIFT (Day or Night) and using a SumIf function to combine all entries totals that share the same DATE/SHIFT. Is there a way I can do a similar method in Access? Im able to concatenate easily enough on access, but I am unable to create SumIf style function that totals the pounds produced for all materials whose dates and shifts are equivalent?

    Followup question, is there a way to have a date on a table and have each new record have the following days date?

    Thank you!!!

    Im relatively new with microsoft access, but Ive been using it for a couple weeks now. Ive had some coding experience, but its been awhile and on different programs.

    -Nathan

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,786
    Presuming the data looks like that, a totals query would let you group and sum the data pretty much any way you want. For example:

    SELECT date, shift, Sum(Pounds) As TotalPounds
    FROM TableName
    GROUP BY date, shift

    Hopefully your field isn't actually named "date", as sooner or later Access will confuse it with the Date() function.

    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,786
    Oh, and to get the next date on a form you could use:

    DMax("DateField", "TableName") + 1

    Adding the Nz() function if the table could be empty.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    NathanGay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2016
    Posts
    2
    I got it to work how I intended.

    Thank you!

    -Nathan

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,786
    No problem, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Concatenate Function for Table1
    By harpreett.singhh@gmail.co in forum Access
    Replies: 1
    Last Post: 03-17-2014, 08:04 PM
  2. SumIF Function?
    By LeoGets in forum Access
    Replies: 13
    Last Post: 01-22-2014, 06:39 PM
  3. SumIF Statement in Query
    By athyeh in forum Queries
    Replies: 2
    Last Post: 09-09-2013, 04:25 PM
  4. Complex "sumif" style formula
    By groonpooch in forum Queries
    Replies: 3
    Last Post: 12-16-2011, 07:05 PM
  5. Concatenate function problem
    By Matt18 in forum Programming
    Replies: 1
    Last Post: 11-19-2011, 05:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums