Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add 'many' fields into one field on Report (2000/XP)

    Hello,
    I need to combine a field from several "many" tables and several non-many tables, by date, onto a report.
    I would like to do it with one line for each date.
    For example, for Feb. 3, 2004 -on the expense report I would like to add all the 'Shop Charges' from Feb. 3, 2004 into one single number to show on the Date line for Feb. 3, 2004 in the Shop Charges field. And all The Gas charges for Feb. 3, 2004 to be added together to show one total number on the Date line for Feb. 3, 2004.
    Will I need to do an actual query for each many table field.
    I am currently trying to use an Expense Query to generate the data for the Expense Report.
    Any help would be appreciated.
    Bart Putnam

    I have included a zipped version of the database.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add 'many' fields into one field on Report (2000/XP)

    I'm not 100 % sure I understand what you mean, but try this:

    - Open the Expenses Query in design view.
    - Select View | Totals or click the Totals button on the toolbar
    - Leave the Totals option for the Date column as Group By, but change it for the other columns to Sum. You will have to remove the Non-Accts Notes column from the query and from the report.
    - Save the query.

    Or, alternatively:

    - Leave the query as it is.
    - Open the report in design view.
    - Select View | Sorting and Grouping or click the Sorting and Grouping button on the toolbar.
    - Set Group Footer for the Date field to Yes.
    - Copy the controls from the report footer section to the group footer for Date.
    - Adjust the height of the group footer section.

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add 'many' fields into one field on Report (2000/XP)

    Hi Hans,
    I tried your first solution, but it gives multiple rows for the same date. For instance Each new entry into the Gas Charges table seems to add a new line with all other fields in the table duplicated.

  4. #4
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add 'many' fields into one field on Report (2000/XP)

    What I'm trying to do is for the Expense Report - pull data for the Gas Charges, Shop Charges and Stock from their own table (can be multiple entries for the same date) and the rest of the columns in the Report are coming from the Expense table itself (only one entry per date)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add 'many' fields into one field on Report (2000/XP)

    You probably forgot to change the Totals option for Gas Charges from Group By to Sum.

  6. #6
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add 'many' fields into one field on Report (2000/XP)

    I did and it still adds up incorrectly. It duplicates somehow if you look at what is actually in the Gas Charges table, there are 2 entries - one for $20 and one for $30, yet the query show a total of $100 for the Gas Charges column.
    I have reattached the db take a look at the Gas Charges table than at the Expenses query. What the heck am I doing wrong?
    I really do appreciate your time Hans. Its been awhile since I have used Access and I really didn't do much with numbers and totals, etc.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add 'many' fields into one field on Report (2000/XP)

    Sorry, that's my fault, although your description was misleading. It would get worse if all the tables had multiole records for the same date. You will have to create separate totals queries, then join those on Date. See attached modified version.
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add 'many' fields into one field on Report (2000/XP)

    Thanks Hans. I'll give that a try and let you know how it works out for me.
    Thanks very much!

  9. #9
    New Lounger
    Join Date
    Apr 2004
    Location
    Midland, Michigan, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add 'many' fields into one field on Report (2000/XP)

    Thanks Hans. Once again you have helped me immensly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •