Results 1 to 11 of 11
  1. #1
    phuduong is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2018
    Posts
    2

    Question How to combine 3 table into 1

    Hello, I am new to access. I have 3 table for example table A,B,C. With each table have 1 header is Label, another header is Month (table A is Jan, table B is Feb, table C is Mar). New month will have more new labels than last month like Jan have 100 labels, Feb have 120 (so Feb have 20 new labels compare 100 Jan labels), Mar have 140 labels.

    My question is how can I combine 3 tables into 1 new master table with total 140 labels in Label header or column and another 3 column is Jan, Feb, Mar?

    Would someone can help me please?

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Have a table with columns MonthNo, Label, LabelQty,

    MonthNo contains months in format yyyymm (An integer, or a text as you prefer - this format allows to have data over any number of years in table.). Or use a date as 1st of month instead of MonthNo (you can use then some other name for month field like MonthDate - but not Month as this is reserved word in Access), and then use any date format like yyyy.mmm when displaying the month in form or report.

    And it looks like you enter the label quantity as total over all earlier periods + current month! Enter the quantity of labels used/purchased/whatever in this month only. To get totals for any period, you then run an aggregate query.

  3. #3
    phuduong is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2018
    Posts
    2

    Red face

    All table just is a simple format with just text and number. Each table have more than 10k rows so I cannot check for every new items each month.

    Table A:
    Label Jan2014
    1000S 10
    1000M 20


    Table B:
    Label Feb2014
    1000S 30
    1000M 40
    1000L 50
    1000XL 60

    I want to combine 2 tables into 1 like this
    Label Jan2014 Feb2014
    1000S 10 30
    1000M 20 40
    1000L 0 (or blank) 50
    1000XL 0 (or blank) 60

  4. #4
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Look into SQL Joins and you can pretty easily build one to pull your data together correctly. You can use SQL code directly in the query design interface, just change view type to SQL

  5. #5
    Hipromark is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    Bogota, Colombia
    Posts
    15
    If I understood your explanation well, what you need to do is this:
    -Create a table (Table 01) in wich you will hold all the label IDs
    -Create a query (Query 01) in which you will combine Table 01with the three tables, linked by label ID
    -Then you can use that query to show the info in forms or reports
    -If you need a table, then just create a query that takes the data from query 01 into a new table.

    Let me know if this worked for you

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you really need a table, then you should NOT design it as you've shown. It should be more like

    tblLabels
    LabelID Label
    1 100S
    2 100M
    3 100L
    4 100XL

    and

    tblDateQty
    LabelDateID LabelIDfk LabelDate QTY
    1 1 JAN2014 10
    2 2 JAN2014 20
    3 1 FEB2014 30
    4 2 FEB2014 40
    5 3 FEB2014 50
    6 4 FEB2014 60

    Your post doesn't indicate if there should be other supporting tables or not. To do what you've posted is an example of repeating like data in fields, which is a no - no. I suspect you're using your Excel brain to design tables, which you should not do. Maybe research db normalization - could start here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Did Micron's solution make sense? Once you have the data in a flat table with Label, Month, Value then build a Cross-Tab Query to produce the output you want with Label as the Row heading and Month as the column heading.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    NOTE
    I didn't notice that I had grabbed 2 urls so my hyperlink doesn't work

    http://rogersaccessblog.blogspot.com...on-part-i.html and/or
    http://holowczak.com/database-normalization/
    Last edited by Micron; 10-20-2020 at 08:49 PM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by Micron View Post
    NOTE
    I didn't notice that I had grabbed 2 urls so my hyperlink doesn't work

    http://rogersaccessblog.blogspot.com...on-part-i.html and/or
    http://holowczak.com/database-normalization/
    Somethings still wrong with your link to rogersaccessblog
    try this one

    http://rogersaccessblog.blogspot.com...on-part-i.html
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Agree with Micron, data structure is not optimal for a relational database. Can force it to work as described by Hipromark for a while but eventually it will break.
    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.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by moke123 View Post
    Somethings still wrong with your link to rogersaccessblog
    try this one
    Fixed. Can't win for trying some days.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-21-2015, 02:25 PM
  2. How to combine two table into one in access.
    By behnam in forum Programming
    Replies: 2
    Last Post: 11-14-2014, 11:25 AM
  3. Combine data from one table
    By Ray67 in forum Queries
    Replies: 19
    Last Post: 08-04-2014, 07:12 AM
  4. Combine Two Records into One from One Table
    By darcien in forum Queries
    Replies: 3
    Last Post: 10-10-2012, 09:17 AM
  5. Combine records in a table
    By smoothlarryhughes in forum Queries
    Replies: 3
    Last Post: 09-14-2012, 08:14 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