Solved

Combine tables to one Access

Posted on 2013-01-13
15
498 Views
Last Modified: 2013-01-15
Hello To ALL,

I have a MS access issue. OK, starting out I will be getting these three files as external excel files  for doing something. I guess I will import these excel files as linked tables or something else into access.
 My goal – how can I combine these two tables (Group_Entry_tbl and Item_Entry_tbl) into one table within Microsoft Access?
I have currently three tables
1.      Group_Entry_tbl
2.      Item_Entry_tbl
3.      Releationship

Table 1 and 2 have two columns – amtp,amt0 that are specific to a group in group entry tbl and…. Similarly, amtp, amto that are specific to an item in item entry tbl
Table in bullet 3 , list out each group and the items contained within each group so to speak..

Please check the attached excel for more sample data feel….
1.      The final table after combining Group_Entry_tbl  and Item_Entry_tbl should look like this with all the combined data in it

cutomer      custID      Grp_Name      Grp_ ID      item Name      item ID      amtP      amtO

What can I do in Access VBA or access to accomplish this? I am open to any suggestions – if I have to create extra column(s) or whatever to achieve this, I am ok with that :)

Thank You,
TwoINTOOne.xlsx
0
Comment
Question by:Rayne
[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
  • 10
  • 4
15 Comments
 

Author Comment

by:Rayne
ID: 38773420
It would be best if this could be achieved via VBA. so every time the three excel files are imported to access, the final desired table is automatically created, so to speak..
0
 
LVL 40

Expert Comment

by:als315
ID: 38773902
I don't see any coinciding values in tables. Can you show expected result with values? Will be good if you upload sample Access DB with these tables.
0
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 100 total points
ID: 38773941
using linked tables to connect to the two main tables, write a query to join them and select the data...with just the fields you want

then turn that into a maketable query and run it to make the new combined table.

then edit that query and make it a select query again.

do you need to only append new records or will you always be appending the whole data set from the excel files?

if the latter then just write the query to append to the combined set, otherwise, use the new query wziard to write an unmatched query from the selct query to the new master table, and then once you have checked it records ( there will be none initially, delete some rows from the new combined table to test), use it as an append to push new data into the combined table.
0
Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar

 

Author Comment

by:Rayne
ID: 38774987
Hello To Everyone for your feedbacks.
 did put together the sample data into access. Assuming the accounts has a possibility of being found on both sheets – ItemForm and GRPForm in the access file. How to combine the two tables into one?
testDB.accdb
0
 

Author Comment

by:Rayne
ID: 38775009
do you need to only append new records or will you always be appending the whole data set from the excel files?
-      appending the whole data set from the excel files
0
 

Author Comment

by:Rayne
ID: 38775024
0
 
LVL 40

Expert Comment

by:als315
ID: 38775409
You have 2 values in table Item-Form with not correct values (one custID, but different cutomer):
cutomer      custID
c_4543      c435
c_22319      c435
Is it real or it is an error? May be you can start from normalization? Add tables with Customers,  Items and groups.
Can you show expected result for any customer?
0
 

Author Comment

by:Rayne
ID: 38775482
Hello ALS,

Thatsa  error :)
0
 

Author Comment

by:Rayne
ID: 38775489
Desired Final Output                                    cutomer      custID      Grp_Name      Grp_ ID      item Name      item ID      amtP      amtO


thank you
0
 

Author Comment

by:Rayne
ID: 38775491
let me know if any other questions
0
 
LVL 40

Expert Comment

by:als315
ID: 38777027
Yes, I'd like to see output with data (only one record, filled manually). You have 2 tables with amtP and amtO. Which one (both, anything else) you like to see in combined table?

What about normalization?
0
 

Author Comment

by:Rayne
ID: 38778826
Please be patient with me :(
I was not sure which way I wanted it originally. I trying to craft out a format and will let you know so that you understand. Shortly.
Thank you :)
0
 
LVL 40

Accepted Solution

by:
als315 earned 400 total points
ID: 38779187
May be you can add some details about your real data? I think your example has too many errors. In table relations ItemID and ItemName seems reversed and Item Name is ablolutely different from Item Name in table Item-Form
0
 

Author Closing Comment

by:Rayne
ID: 38779518
Hello,

Thank you so much for your help so far. I am closing this question and I will let you know of any further follow up.

R
0
 

Author Comment

by:Rayne
ID: 38780189
0

Featured Post

Office 365 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

Question has a verified solution.

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

Suggested Solutions

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

737 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