Solved

Merge data from two tables

Posted on 2014-04-25
7
487 Views
Last Modified: 2014-06-17
I am building a customer call log in MSAccess and want to track calls for our Customer Base as well as our internal representative base.  My plan is to have the user enter an account number or select from a combo-box, click a button and call up a form/subform.  Form will show some basic account info (acctNo, Name, Contact, Addrs, etc...  )  and subform will be a running call log.

My problem is I have 3 externally sourced tables I'm pulling this information from.  One table holds all of my Rep info (account, name, address, etc...) and the other two tables hold all of the customer info I'd need (also account, name, address, etc...).  

Is it possible to merge the separate sets of account data into one table, which I'd then use to bind my various forms?  external tables are read only while new merged table would allow the ability to add standalone records, etc...  modifications to existing records would only be done on the external source with linked data getting updated from that source.  

thanks,
0
Comment
Question by:valmatic
[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
7 Comments
 
LVL 84
ID: 40023229
You could import your external data into local Access tables. That would allow you to handle the New record scenario while still giving you the ability to interact with existing data.

Of course, if new data is added from the external source then you'd have to have a utility that would pull that new data in.

Do you really need the ability to add new Representative or Customer records? Or will they always be sourced from that external content?

Your Notes table could be something as simple as this:

tNotes
NoteID
ParentID
NoteText

Where 'ParentID' would be the unique identifier for a Customer or Representative.
0
 
LVL 7

Author Comment

by:valmatic
ID: 40023258
Hi Scott  That's a good point.  We'd most likely never add a rep in as a standalone record.  Customer however could be a new customer that decides never to order so that customer would not be entered in at the external source but we'd still provide the call logs.  

Users demand to see account info on each log record though so I'd still need the form/subform format.  Does the merge of my three tables into one data source even seem possible?
thanks
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40023663
To merge three tables into a single recordset requires a union query.  The union query produces a non-updatable recordset though so if you wanted to keep additional data, you would need a separate, local table and that table would need to be bound to a popup form or a separate subform.  You can't join it to the union query because that would make your local table non-updateable also.

Union queries require that the same column from each data source be the same data type but the column names do not have to be identical.  The column names from the first table/query are the ones that will be used for the recordset.

Select fld1, fld2, fld23 From tblA
Union Select fld1, fld2, fld44 from tblB
Union Select fld1, fld3, fld23 From tblC;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40024377
Does the merge of my three tables into one data source even seem possible?
If they contain the same data (i.e. Fields) then you can do so. You may want to add a "PersonType" field (or someting of that nature) to identify which type of entity you're dealing with (Customer or Representative).

There's still the issue of updates from the external sources, but that could easily be done with an Append query. Only issue there is to insure that you don't duplicate existing data.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40026052
It would be best to keep the three tables separate, since they contain different types of data.  They can be linked using a primary key in the Accounts table, and foreign keys in the Reps and Customers tables, so you can make a query to get linked data from all, or make main form/subform forms to work with the data.
0
 
LVL 7

Author Comment

by:valmatic
ID: 40105432
thanks
0
 
LVL 7

Author Closing Comment

by:valmatic
ID: 40140199
I apologize Scott..  I had to refocus on something else and lost sight of this case.  You've been a huge help though.  Take points and if I get back to it and need more help, I'll open a new case.
0

Featured Post

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)

Question has a verified solution.

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

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

740 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