Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Sampoline is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2020
    Posts
    6

    Merging multiple tables into one master table

    Hello everyone, I'm new here so excuse my rookie wording etiquette.



    I have an access database that has around 100 tables with the same field names.
    The table names are for tertiary records.
    The problem is that there are so many different tertiaries.
    Yes there are quite a few same tertiaries. But the tables that are from the same tertiaries have their own separate tables based on date of the record and may even be further separated by a ending keyphrase like shown below:

    E.g.
    University of Batman 01/11/04 Robin
    University of Batman 01/11/04 Catwoman
    University of Batman 01/11/04 Joker
    University of Batman 07/12/04 Bay
    University of Batman 07/12/04 Country

    If this makes sense?

    How do I go about merging this much data with so many variables into one table? I know that an "append query is required" but what sort of code is required and I do I go about adding that code in. And how do I do these queries?

    I'm a rookie at access so please dumb this down for me!

    Thanks!!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Welcome to the forum. I for one don't understand the problem, largely because of your terminology I think. I know what tertiary means, but have no clue how it relates to what you're saying. If that's the data you have, then posting the desired outcome will help, but I get the impression that list is only a partial glimpse. Whatever the goal, be careful that you don't create some kind of flat, Excel type table of data, otherwise you're setting yourself up for further troubles.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    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 with Micron, not really enough info. How are tables related - with autonumber PK/FK or custom PK/FK? If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    This feels absolutely non-Access!

    You need a single table instead. And a tertiary report, or a filter for form to display tertiary data whenever you need it.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If same fields in all the tables you could maybe use a union query to combine them all in one entity. But not sure on 100 tables worth.

  6. #6
    Sampoline is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2020
    Posts
    6
    Sorry everyone, I used my phone write this thread (silly me) hence why I didn't see the option to post images or my db. My apologies.

    I've added screenshots of a mock db. My issue is running an append or whatever else I need to do to create a 'master table' with all the tables. All the field names are the same. There are so many tables! I'm very new to Access, so anything can help me here!

    Click image for larger version. 

Name:	1.PNG 
Views:	21 
Size:	17.5 KB 
ID:	40693

    Click image for larger version. 

Name:	3.PNG 
Views:	20 
Size:	18.0 KB 
ID:	40694

    Click image for larger version. 

Name:	4.PNG 
Views:	20 
Size:	18.2 KB 
ID:	40695

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Should not be 'so many tables' to begin with, should be 1 table with another field for identifier (such as ProjectID maybe).

    Process for merging these tables is dependent on how they are related to dependent tables.

    We do not yet have a full picture of data structure.
    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.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Sampoline,

    Step back and tell us in simple, plain English WHAT are you trying to do?
    WHAT is the purpose of the database?
    You are showing us HOW you have done something, but we need more info on the something.

  9. #9
    Sampoline is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2020
    Posts
    6
    Alright so these multitude of tables aren't actually made by me. They were given to me through a client who asked us to handle these school/tertiary records.

    They've provided us with an access sheet with all the schools and records. Each table represents a school. And inside each table are a set of field names to identify the individual box and file codes pertaining to a specific student.

    Now the trouble I'm have to find a way to bring all this information together into one table so that I can quote/bill the client for our services.

    As you all you can see, there are tables with different names, numbers and even some that have the same names but different dates..

    How can I merge all these tables into one? That's my question!

  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,820
    A UNION query is a 'merge' tool. However, there is a limit of 50 SELECT lines in a UNION. Perhaps can do UNION of UNION queries, but I've never explored that.

    A VBA procedure could loop through tables collection and write each to 'master' table.

    Could even copy/paste records and run an UPDATE action or simply Find/Replace to fill in new identifier field. This 'manual' approach with 100+ tables might take a day but if this is a one-time effort, maybe acceptable.

    Again, we do not know relationships to dependent (child) tables, if there are any.

    Still not clear to me what is meant by 'tertiary'.
    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
    Sampoline is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2020
    Posts
    6
    Tertiary is just another way of saying postsecondary education, so like university/college..

    And the screenshots I've provided are exactly how each table looks like in this db. It's stumped me for sure. I'm not much of a coder, hence why I realised this maybe out my league.

    So to do a UNION of a UNION, I would have to code as well you reckon?

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So each table looks like the picture of what I guess is a table (first field is named 'BoxCode') and you have to put all the data in all of those tables into one table that looks like the picture. What's not clear (at least to me) is, is there a field that will distinguish between the records that came from any particular table?
    In other words, Leasa 11/11/2019... records end up in this new table. What field identifies the records from this table from any other table after they have been added - Surname? Proj number?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    An experienced coder would likely take about an hour to build and run a VBA procedure.

    A UNION query can actually be built fairly quickly. A simple example, assuming exact same field names and in exact same order in table design:

    SELECT table1.*, 1 AS Src FROM table1
    UNION SELECT table2.*, 2 FROM table2
    UNION SELECT table3.*, 3 FROM table3;

    UNION query is built by typing and/or copy/paste in SQLView of query builder. Then this query can be source of an INSERT or MAKE TABLE action or even select records and copy/paste to another table.

    If you have 100 tables, that means at least 2 UNION queries.

    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.

  14. #14
    Sampoline is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2020
    Posts
    6
    Yes sir! All those field names are as is and will need to be as the picture shows.

    And with distinguishing records, I don't need the original table names (but I will need the preexisting, original tables to still be there, so no deletion).

    All I need is the data from each table merged into the one. Just not sure on how to do that that. So those field names in my screenshot, they are the same for every table.

  15. #15
    Sampoline is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2020
    Posts
    6
    Quote Originally Posted by June7 View Post
    An experienced coder would likely take about an hour to build and run a VBA procedure.

    A UNION query can actually be built fairly quickly. A simple example, assuming exact same field names and in exact same order in table design:

    SELECT table1.*, 1 AS Src FROM table1
    UNION SELECT table2.*, 2 FROM table2
    UNION SELECT table3.*, 3 FROM table3;

    UNION query is built by typing and/or copy/paste in SQLView of query builder. Then this query can be source of an INSERT or MAKE TABLE action.

    If you have 100 tables, that means at least 2 UNION queries.

    Oh okay! Well I guess I'll have to speak with someone at work who does code to help with me with this one. Alright then, thanks for all your help guys. Sorry for the amateur word choices, will work on it!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 09-25-2017, 04:25 PM
  2. Replies: 16
    Last Post: 02-09-2017, 03:48 PM
  3. Replies: 11
    Last Post: 01-13-2017, 06:58 PM
  4. combining multiple tables in to one master
    By joebox8 in forum Queries
    Replies: 5
    Last Post: 06-23-2011, 06:18 AM
  5. Replies: 8
    Last Post: 11-04-2009, 04:22 AM

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