Results 1 to 5 of 5
  1. #1
    jmoodley is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Location
    Cape Town, South Africa
    Posts
    2

    Consolidate Data from Multiple Columns

    Hello All

    I have a table that's laid out as follows:

    Click image for larger version. 

Name:	access example.jpg 
Views:	15 
Size:	63.2 KB 
ID:	33714

    I need to generate a report that will consolidate all the guide names and the amount they earned for each trip and add it up.

    The report should be grouped by Guide name and will look something like this:

    Click image for larger version. 

Name:	Access solution example.jpg 
Views:	15 
Size:	54.9 KB 
ID:	33715



    My thinking is that i need run a query that will append Guide1 + Guide2 + Guide3 columns as well as the Guidex_wage columns

    I've been unable to find a solution. Please help!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Welcome to the forum.

    Your table is structured like a spreadsheet.
    In a database each item of data should be in its own record.
    So your table should have fields
    TripID -PK field
    TripNo - integer
    GuideNo - integer
    GuideName -text
    GuideWage - currency or number

    It then becomes a trivial task to sum all wages for each guide

    With your current setup, if you ever need a 4th guide, you will need to redesign your table.
    If you want to keep it like this, use Excel instead
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    jmoodley is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Location
    Cape Town, South Africa
    Posts
    2
    Hello @ridders52

    Thank you for your feedback. I just put the samples together in excel to give a visual idea of what i'm facing.

    Each record is a tour summary for a trip. There are a lot of other fields such as Catering Costs, Vehicle mileages & petrol costs etc. Here is a snapshot of the actual database

    Click image for larger version. 

Name:	actual database.jpg 
Views:	15 
Size:	103.1 KB 
ID:	33716

    Thats just part of it - as i said there are a lot more fields.

    My challenge is that when i run a report on salaries i need it to consolidate guides of the same name. In the example above i need a report to show that Phumlani earned 600 throughout the month

    Sometimes a trip will have up to 3 guides on it, and i need to total each ones salary

    I'm not sure if that is clear?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It's totally clear as it was in post #1 and my previous advice still applies.
    In fact this should be split into several tables
    For example from the fields I can see, you need something like four tables
    TblTripInfo
    TblGuides
    TblMileage
    TblCosts

    If you have additional fields, you probably need additional tables as well.
    There is little point using a database if you structure your data like a spreadsheet
    Please do a search for normalisation to understand how to use Access effectively.
    Otherwise each step will be ten times harder than it needs to be.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I agree - normalize data structure. Otherwise, use a UNION query to rearrange data.
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-21-2017, 05:40 PM
  2. Replies: 2
    Last Post: 05-15-2014, 06:15 AM
  3. Consolidate Multiple Names
    By psquire in forum Access
    Replies: 3
    Last Post: 06-12-2013, 08:21 AM
  4. Consolidate Multiple Names
    By psquire in forum Queries
    Replies: 1
    Last Post: 06-11-2013, 08:45 PM
  5. Consolidate multiple columns into one
    By COforlife in forum Access
    Replies: 17
    Last Post: 10-19-2009, 01:12 PM

Tags for this Thread

Posting Permissions

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