[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Combine tables to one Access

Posted on 2013-01-13
15
Medium Priority
?
507 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 400 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 1600 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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

656 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