Solved

Merge data from two tables

Posted on 2014-04-25
7
480 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
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 35

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 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