Solved

Merge data from two tables

Posted on 2014-04-25
7
477 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

770 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