Solved

Combine tables to one Access

Posted on 2013-01-13
15
489 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Rayne
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Rayne
Comment Utility
Hello ALS,

Thatsa  error :)
0
 

Author Comment

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


thank you
0
 

Author Comment

by:Rayne
Comment Utility
let me know if any other questions
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now