Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Merge data from two tables

Posted on 2014-04-25
7
Medium Priority
?
512 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 85
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 39

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

596 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