Solved

Combine tables to one Access

Posted on 2013-01-13
15
495 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

821 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