Solved

Merge data from two tables

Posted on 2014-04-25
7
464 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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0
 
LVL 7

Author Closing Comment

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

762 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

12 Experts available now in Live!

Get 1:1 Help Now