Solved

Combine multiples Tables with common index into a new single table.

Posted on 2013-06-16
4
313 Views
Last Modified: 2013-06-17
Experts,

I need to combine 11 tables into one on their common index.  This of course will require an addition of fields to  the combined table.  I have tried both asking the query to add and by addings fields to the Combined table "T00AllCat" to no avail.  I always get the same "___"record(s) due  to validation rule violations.

What is the simplest way to accomplish this simple task?

The data base attached contains Tables T01 through T11, plus my effort to make a combination table; "T00AllCat", "T00AllCatCopy", and "T00Base".  The query I have been trying to use has been multiple versions of  "QAppT00AllCat_T01" .

Any and all guidance will be greatly appreciated.

Timothy
PSAssmTAMod.accdb
0
Comment
Question by:TimothyA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39252203
It is not clear what you mean by "combining". I can see that T01 - T11 tables are all completely different. They have different structure, and according to their data they are designed to hold different type of information.

What exactly are you trying to achieve?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 39252632
You need to create one query which uses all of your tables to select the fields you want from each table,

You need to start from one table which you know has every record you want to see in the final result.  Add the fields you want into the result grid.
 Then add the second table to the query ; join the tables on the correct field; change the join type (right click the join line) so that you have all records from the first table and any matching records from the second table. Add the fields you want from the second table to the result grid.
add each table in turn to the query, always  linking it to the first table and always changing the join type. So you end up with the first table linked to every other table and all the fields you want in the result grid.  

View the results to check you are seeing what you expect..

Then convert the query to a Make-table query (menu option).

Save the query and then run it.
0
 

Author Comment

by:TimothyA
ID: 39252923
Peter,

Thanks so so much.   I will begin working on your suggestion and will get back to you later in the week.

Timothy
0
 

Author Closing Comment

by:TimothyA
ID: 39254636
Solution worked perfectly!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question