Solved

Combine tables to one Access

Posted on 2013-01-13
15
494 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
  • 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 39

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 39

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 39

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 39

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

777 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