Results 1 to 14 of 14
  1. #1
    LeoGets is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    6

    SumIF Function?


    Hello,
    I am trying to add up fields on if certain criteria are met. User uses a drop down list for Expenses Category for the daily balance sheet using a form. At the end of the week I am having to add up every field that corresponds to: Labor, Entertainment, Repairs, Etc. So each day can have all or none of these expenses. Is there a way to show just labor for the whole week, and zeros for anything else on a daily or weekly basis? And also Entertainment, Repairs, and so on? Thanks in advance.

    Here is what the table looks like:
    Day Misc1 Misc1Cat Misc2 Misc2Cat Misc3 Misc3Cat
    Wednesday, January 01, 2014 $14.00 Re-Deliveries $45.00 Labor $30.00 Hardware
    Thursday, January 02, 2014 $30.00 Labor $100.00 Misc $4.00 Re-Deliveries
    Friday, January 03, 2014 $25.00 Labor $11.00 Misc $20.00 Misc
    Saturday, January 04, 2014 $35.00 Labor $1,075.00 Labor $60.00 Vehicle Expense
    Sunday, January 05, 2014 $40.00 Labor $600.00 Hardware $4.00 Re-Deliveries
    Monday, January 06, 2014 $35.00 Labor $16.00 Re-Deliveries $168.00 Food Purchases
    Tuesday, January 07, 2014 $30.00 Hardware $20.00 Labor $8.00 Re-Deliveries
    Wednesday, January 08, 2014 $55.00 Labor $16.00 Re-Deliveries $75.06 Checks
    Thursday, January 09, 2014 $60.00 Vehicle Expense $8.00 Re-Deliveries $28.00 Food Purchases
    Friday, January 10, 2014 $20.00 Misc $25.00 Labor $70.00 Repairs
    Saturday, January 11, 2014 $68.00 Food Purchases $88.00 Food Purchases $45.00 Vehicle Expense
    Sunday, January 12, 2014 $2,000.00 Misc $30.00 Hardware $20.00 Labor
    Monday, January 13, 2014 $14.00 Hardware $166.00 Misc $25.00 Labor
    Tuesday, January 14, 2014 $10.00 Re-Deliveries $25.00 Labor $10.00 Food Purchases
    Wednesday, January 15, 2014 $13.00 Refund $7.00 Re-Deliveries $30.00 Labor
    Thursday, January 16, 2014 $40.00 Labor $8.00 Re-Deliveries $100.00 Flyer Distribution
    Friday, January 17, 2014 $65.00 Gas $19.00 Re-Deliveries $94.60 Checks
    Saturday, January 18, 2014 $20.00 Re-Deliveries $7.00 Re-Deliveries $30.00 Labor
    Sunday, January 19, 2014 $20.00 Labor $0.00
    $0.00
    Monday, January 20, 2014 $25.00 Labor $85.00 Labor $17.00 Hardware

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Have you tried using the query builder to create a Totals action query?

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    You have labor in three different columns (this is a non - normalized structure so it's going to be more difficult for you). Are you saying you want any record where labor appears in misc1cat, misc2cat or misc3cat? or are you saying, say, at the end of this report you would want a summary that went something like this:

    Code:
    Labor           1685$
    Re-Deliveries   141$
    etc...
    If you want the latter you will likely have to create a subreport based on a union query because of your non-normalized structure.

  4. #4
    LeoGets is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    6
    Hello,
    I had a feeling my problem would have something to do with the structure itself. I actually want to see any record where labor appears, but only to get to the final TOTAL report which would look like the code you have included here. I have used excel for years, and have recently moved over to access and love the flow it provides with everything I have been doing with it. I Am stuck on this though and am open to any recommendations you are willing to provide even structure, etc.
    Thanks again.
    This form actually has a list of 12 possible "payouts" from a list that I set up as a table and created a combo box from that list. Make sense? Sorry if its confusing, but this is the best I can describe it.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Can you provide a sample of your database, without a good idea of your data structure I'd really be guessing at a solution that would work for you.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,874
    A UNION query could manipulate the data to a normalized structure. Must type or copy/paste into SQL View of query builder. Is there a unique ID field?

    SELECT ID, [Day] AS EnterDate, 1 AS Source, Misc1Cat AS Cat, Misc1 As Data FROM tablename
    UNION SELECT ID, [Day], 2, Misc2Cat, Misc2 FROM tablename
    UNION SELECT ID, [Day], 3, Misc3Cat, Misc3 FROM tablename;

    Now use that query as source for subsequent queries to filter records and to calculate aggregate data.

    BTW, Day is a reserved word and should not use reserved words in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    LeoGets is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    6
    I've attached the file per the instructions above. Please feel free to give any and all type of input on my database structure. My main goal is to make it fool proof on the end user to force them to use drop down list for any cash payouts they have on a daily basis, and then get me a total at the end of the week. I've been using this form to get NET CASH. This was my main goal at the time I created it, and have been trying to evolve since.
    ThanksSampleData2.accdb

    Also, what can be used as "DAY" if day is a reserved word? I get that error also when adding a DATE field. Has been working, so I have gotten into the habit of ignoring the error.
    Last edited by June7; 01-22-2014 at 07:05 PM.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,874
    It will work and most of the time probably not an issue. But there are situations where those names could be confused with the functions.

    Use whatever makes sense: DatePaid, DateSpent, DateExp.

    Did you try the UNION?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    I think you should revisit your database design. For instance you're using the text value of a field name to populate a combo box. The table holding the field name is just 12 blank records.

    What this really should be is a table with a unique key (autonumber) plus the description of the category.

    Secondly, you don't have a great structure for the misc reasons.

    However, to get a query you can you you'd want something like this:

    Code:
    SELECT Sum(Misc1) As Category, Misc1Cat FROM SalesTable GROUP BY Misc1Cat
    This is your basic query, from here you'd have to create a union for each one of your 12 possible fields like this:

    Code:
    SELECT Sum(Misc1) As Category, Misc1Cat FROM SalesTable GROUP BY Misc1Cat
    UNION ALL
    SELECT Sum(Misc2) As Category, Misc2Cat FROM SalesTable GROUP BY Misc2Cat
    UNION ALL
    SELECT Sum(Misc3) As Category, Misc3Cat FROM SalesTable GROUP BY Misc3Cat
    UNION ALL
    <do the same thing for the remaining 9 fields>
    This will give you a list of each of your categories and how much you've spent on them. Then you'd just have to summarize this data in a query to get your monthly/weekly/yearly reporting.

  10. #10
    LeoGets is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    6
    I've tried June7:
    SELECT ID, [Day] AS EnterDate, 1 AS Source, Misc1Cat AS Cat, Misc1 As Data FROM tablename
    UNION SELECT ID, [Day], 2, Misc2Cat, Misc2 FROM tablename
    UNION SELECT ID, [Day], 3, Misc3Cat, Misc3 FROM tablename;

    Works fine as far as getting me a list of all possible Misc fields, then as you mentioned, use that as a base query for subsequent queries. I will try that in a few minutes and post the results. Rpeare, thanks for the reply, you have touched on something that I know I could or should have done different. That table you mention does indeed hold 12 blank records. Can you be a bit more specific on how the table holding the source of my combobox should look? I know I'm on the right path now with the UNION function but would like to take advantage here and learn about my overall design if possible.

    I just ran another query based on the union query and the only thing I can figure out to do is create a query for each one of my possible Categories from combobox? For instance, I have a query for Labor, then in the criteria, I add "Labor" and sum by data, and so on for Re-Deliveries, Hardware, etc. Works great, but now I would have 12 queries (one for each possible Category). If this is the way in needs to be done, that's fine, just want to know if I'm doing it the best way.
    Thanks in advance.
    Last edited by June7; 01-22-2014 at 07:03 PM.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,874
    One aggregate query should be able to sum each category.

    SELECT Cat, Sum(Data) AS SumData FROM DataUNION GROUP BY Cat;

    Or use the UNION query as the RecordSource for report and use report Grouping & Sorting with aggregate calcs features. This allows display of detail records as well as summary info in group footer.

    Having a category show 0 if there are no records for specified period is another issue. Gets complicated. Several ways to accomplish. One involves DSum domain aggregate function in textboxes on report, one for each category. Another requires a 'master' dataset of all category/period combinations. It's been a topic more than once, here is one https://www.accessforums.net/queries...ist-37742.html. Another approach would be dummy record for each category/period with 0 value.
    Last edited by June7; 01-22-2014 at 03:40 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    LeoGets is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    6
    I did the aggregate query, and worked great, but how do I filter by date so I can see weekly expenses? Not sure on how to run the report as you stated, can you please be a bit more detailed. Eventually, I would like to see it as a report.
    Thanks

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,874
    Building a report with Grouping & Sorting and aggregate calcs is fairly basic Access functionality. Refer to Help or search web for tutorials.

    If you want data filtered by a week begin/end range, use a form for entry/selection of beginning date. Parameters in the aggregate query can reference the form control for criteria, something like:

    WHERE [DateEnter] BETWEEN Forms!formname!controlname AND Forms!formname!controlname + 6
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    LeoGets is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    6
    Thanks again for your help. I am reading up on UNION queries and have learned quite a bit.

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

Similar Threads

  1. SumIF Statement in Query
    By athyeh in forum Queries
    Replies: 2
    Last Post: 09-09-2013, 04:25 PM
  2. sumif in Access
    By JGrots in forum Queries
    Replies: 12
    Last Post: 01-17-2013, 02:37 PM
  3. Complex "sumif" style formula
    By groonpooch in forum Queries
    Replies: 3
    Last Post: 12-16-2011, 07:05 PM
  4. Lookup and Sumif
    By hatimn in forum Queries
    Replies: 7
    Last Post: 07-18-2011, 11:50 AM
  5. Sumif in an Access report
    By tigers in forum Reports
    Replies: 0
    Last Post: 03-15-2007, 12:19 PM

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