Results 1 to 7 of 7
  1. #1
    Victor93 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5

    Combine tables

    Hello,

    I am new to Access and I am struggling with a probably simple problem. I want to compare invoicing from different years for each of my clients. Let me elaborate.

    I have a separate excel table for each year (lets say, 2017, 2018 ). In each of those tables, I have a column with client name and a column with total invoiced amount.

    I would like to have a final table where I have "client name", "2017 invoiced" and "2018 invoiced" as columns.

    My main problem is that some client may appear in 2017 but NOT in other years. I might have:

    CLIENT_NAME INVOICED17
    clientA 1000€
    clientB 500€
    clientC 100€


    CLIENT_NAME INVOICED18
    clientA 800€
    clientC 200€
    clientD 50€


    I want my final table to look like this:

    CLIENT_NAME INVOICED17 INVOICED18
    clientA 1000€ 800€
    clientB 800€
    clientC 500€ 200€
    clientD 50€


    What is the easiest way to do this? The best result so far I have is importing each table, and then using a UNION query, but I have a not perfect result that looks like this:


    CLIENT_NAME17 CLIENT NAME18 INVOICED17 INVOICED18
    clientA clientA 1000€ 800€
    clientB 800€
    clientC clientC 500€ 200€
    clientD 50€

    Here is my UNION query SQL code:



    Code:
    SELECT inv17.[client_name], inv18.[client_name], inv17.inv17,inv18.inv18
    FROM inv17 LEFT JOIN inv18 ON inv17.[client_name]=inv18.[client_name]
    
    
    UNION ALL 
    SELECT inv17.[client_name], inv18.[client_name], inv17.inv17,inv18.inv18
    FROM inv17 RIGHT JOIN inv18 ON inv17.[client_name]=inv18.[client_name]
    WHERE inv17.[client_name] IS NULL
    Thanks a lot!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Welcome Victor93,
    It would be helpful if you described the requirement in business terms. How many Invoice Years are involved? What exactly prompted this proposed design?

    You should review Normalization.

    What would your layout look like if you add 5 or 10 new years?
    Better table design would be (but I don't know your real reuirement)

    Client
    InvoiceYear
    InvoiceAmount


    this would allow multiple invoice years that could be queried or used in reports.

    Perhaps you are looking for a Report.

    Good luck.

  3. #3
    Victor93 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5
    Hi orange, thanks for your reply

    I would need to keep 3 years in my table: last 2 years and current one (which, yes, I need to actualice periodically). My goal is to look for weird changes in how much a client buys me. I want to check if any client has stopped to buy me, or if he suddenly has reduced (or increased) his bought to half (for example). Eventually, I will be adding incoming years, but I am mostly interested in the last 3 years. Also, the calculations do not worry me, it can easily done in excel. My problem is the layout.

    I will look into normalization and reports, thanks for your suggestions.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are welcome.
    Access and relational database is built on a different object model than Excel/spreadsheet.
    The Database Planning and Design link in my signature has several articles that may be helpful to you in your design efforts. There are several youtube videos on Access- forms, reports etc--some of which can be quite instructive.

    Also, remember that data presentation is not necessarily the same as data storage(tables).
    You can modify/customize presentation with queries, forms,reports, vba code.. but getting the tables "normalized" is often the most critical element in database.
    Good luck with your project.
    Last edited by orange; 05-10-2019 at 09:10 AM. Reason: spelling

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Normalization of your data is where you want to go with this per orange's message.

    If you are keeping your excel spreadsheets and you are just going to import your data once per year you should be able to do it with excel pivot charts.

    What you'd need though is to get your data (at a minimum) into a single table like

    Code:
    Customer  SaleYear  TotSold
    Cust A    2018      1000
    Cust B    2018      500
    Cust A    2019      5000
    Cust C    2019      450
    If you import your data like this it's a simple step to generate a crosstab query which is what I think you're after.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Once data is imported to normalized structure, can use queries to manipulate data for display in other arrangements.

    Aggregate (GROUP BY) query with conditional expressions or CROSSTAB can rearrange data to get the years side-by-side.
    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
    Victor93 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5
    Thank you all for your comments, they were very helpful.

    I have been able to solve my problem as some of you suggested: I have redistributed my data like rpeare showed and then I used excel pivot tables. I was not familiar with them and I feel like a whole new world of possibilites has opened! I knew I was trying to do something simple in a too much complicated way.

    Again, thank you all for your help

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

Similar Threads

  1. Need to Combine Two Tables
    By CharissaBelle in forum Queries
    Replies: 4
    Last Post: 05-17-2016, 01:23 PM
  2. Replies: 7
    Last Post: 09-11-2015, 11:44 AM
  3. Combine Tables
    By stbeaver in forum Queries
    Replies: 5
    Last Post: 02-26-2014, 11:15 AM
  4. How do I combine 3 tables into one?
    By Steven Seagal in forum Access
    Replies: 3
    Last Post: 09-18-2012, 06:54 AM
  5. combine two tables
    By boomkrekel in forum Access
    Replies: 5
    Last Post: 02-18-2012, 11:26 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
  •  
Other Forums: Microsoft Office Forums