Results 1 to 4 of 4
  1. #1
    DOAKLEY is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    3

    Newbie needs help with a Que

    To start with, I've never worked with Access before so I need help.



    I have four tables that have similar information. The first table contains a list of all possible customer names. The next three tables contain sales data for individual years. These tables contain the customer name and a transaction amount. There is a row of data for each transaction the customer made in a year. Not every customer in the first table will appear in the other tables. So as an example, the individual tables for years will look like:

    Adam 50.00
    Adam 75.00
    Adam 42.32
    Bob 12.00
    Dave 15.00
    Dave 25.00

    But the first table with all customer names will look like:

    Adam
    Bob
    Chris
    Dave

    I need to have Access look to the four tables and output a table or report (something I can print) that will show all of the customer names from the first table with with sales totals for the years from the other tables. Like this:

    Customer Name 2012 Sales 2013 Sales 2014 Sales
    Adam 167.32 225.00 300.00
    Bob 12.00 0.00 0.00
    Dave 40.00 10.00 0.00

    Can someone tell me how to do this?

    Thanks

    Dennis

  2. #2
    JamesDeckert is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Salina, KS
    Posts
    262
    You'll create a query and add all 4 tables.
    You will have a join line from the customer table to each of the other 3 tables. These join lines should be set (right click line) to show all records from the customer table and matching records in the sales tables.
    Put the customer field on the query and the sales figure from each of the other 3 tables.
    Click the Totals button on the ribbon. This will add a Total row on the query, which will be "Sum" for each sales figure and "Group by" below the customer name.
    You can print from this query or create a report based on this query.

  3. #3
    DOAKLEY is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    3
    Hi James,

    Thanks for your help. It seems like it's trying to work but I get an error that it can't complete the query because it either exceed the maximum size of 2GB or there's not enough temporary room to create the file.

  4. #4
    DOAKLEY is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    3
    I think I got it. I created a query to sum each table first. Then created a new query off the individual queries instead of the tables. Seems to have done the trick.

    Thanks again for your help.

    Dennis

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

Similar Threads

  1. Newbie needs help
    By Daryl2106 in forum Access
    Replies: 8
    Last Post: 01-24-2012, 09:12 PM
  2. Help - newbie
    By Sorbz62 in forum Database Design
    Replies: 13
    Last Post: 09-29-2011, 01:30 AM
  3. newbie needs help
    By hollyh in forum Queries
    Replies: 1
    Last Post: 08-23-2011, 03:39 PM
  4. I need (newbie) help, please!
    By Over in forum Access
    Replies: 2
    Last Post: 02-05-2011, 10:14 AM
  5. Help a newbie
    By g8rnc in forum Access
    Replies: 1
    Last Post: 06-09-2010, 12:58 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
  •  
Other Forums: Microsoft Office Forums