473,849 Members | 1,797 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to Combine Data from multiple tables or sources?

I am at a complete loss on how to do this. I am using Microsoft Access
2000, to implement a preliminary version of a reasonably modest
relational database.

I have two Access Tables (imported from MS Excel spreadsheets), and I
need to somehow get the data for a "Person_ID" brought over from one to
the other.

In Table_A, there are many records for each person, and the first and
last name are included, but the Person_ID field is empty. There are
more than 65,000 records.

In Table_B, there is only one entry for each person, and each record
contains a field called Person_ID. There are more than 15,000 records.

I cannot figure out any way to bring the Person_ID data from Table_B to
Table_A, to allow me to create a relationship through the Person_ID
field to several other tables.

Can someone point me to a resource that would tell me how to do this?
If the data sources were smaller, I could export them to Excel and
manipulate them there, and then re-import tham, but there are more than
65,000 records.

Jul 19 '06 #1
8 22199
user an update query to set the value of the TableA.personID to the
value of TableB.personID where

UPDATE TableA
SET PersonID = TableB.PersonID
WHERE TableA.FirstNam e=tableB.FirstN ame
AND TableA.Lastname =tableB.Lastnam e
AND TableA.SSN=Tabl eB.SSN ...

but you need a combination of fields in the two tables to define an
accurate relationship.
How do you know which records are related just by looking at them? You
need to join on that/those field(s) in your update query.

Jul 19 '06 #2
This sounds like the right sequence of directions, but I don't know how
or where to input this kind of script into Access 2000.
UPDATE TableA
SET PersonID = TableB.PersonID
WHERE TableA.FirstNam e=tableB.FirstN ame
AND TableA.LastName =tableB.LastNam e
Jul 19 '06 #3
Can anyone point me to a resource that explains "Update Queries" in a
useful way? Or perhaps suggest what I may be doing wrong here?

I have now tried this a number of different ways, but no matter what I
do, Access simply prompts me for the value of each field, rather than
drawing any data from the database tables.

There are two tables: "Quotes_Tab le" and "Persons_Table" , each
containing fields called
First_Name, Last_Name, and Person_ID. In the "Quotes_Tab le" there are
multiple entries for each person, including first and last name but NOT
Person_ID. I want to "bring over" the Person_ID field from the
Persons_Table. I have tried doing this with the Person_ID fields in
the two databases set to be related, and again in several permutations
without any relationship set; it seems to make no difference. (I cannot
relate the other fields (First_Name or Last_Name), apparently because
they are not primary keys?)

No matter what I do, the system prompts me to enter values. At one
point, I thought this was an issue related to Access inserting brackets
around some fields but not others, but the query functions exactly the
same with or without brackets:

UPDATE Quotes_Table SET Quotes_Table.Pe rson_ID =
[Persons_Table].[Person_ID]
WHERE (([Quotes_Table].[Person_ID]=[Persons_Table].[First_Name]) And
([Quotes_Table].[Person_ID]=[Persons_Table].[LastName]));

UPDATE Quotes_Table SET Quotes_Table.Pe rson_ID =
Persons_Table.P erson_ID
WHERE ((Quotes_Table. Person_ID=Perso ns_Table.First_ Name) And
(Quotes_Table.P erson_ID=Person s_Table.LastNam e));

Can anyone point me to a resource that explains "Update Queries" in a
useful way? Or perhaps suggest what I may be doing wrong here?

Jul 19 '06 #4
you might want to start with a select query where you join the tables
together to get the data together. then turn that into an update query.

Jul 19 '06 #5
I don't understand how I'd do that, but I think the logical sequence of
what I want is:

For each record in [Quotes_Table]:
Find the entry in [Persons_Table] with the same First_Name and
Last_Name;
SET [Quotes_Table].[Person_ID] = [Persons_Table].[Person_ID]

or

For each record in [Persons_Table]
Find all entries in [Persons_Table] with the same First_Name and
Last_Name;
For all matching records, SET [Quotes_Table].[Person_ID] =
[Persons_Table].[Person_ID]

Jul 19 '06 #6
Try this... the problem is that with 65K+ records, I don't know if
you're going to get a proper relationship working.

UPDATE Quotes_Table INNER JOIN Persons_Table ON
(Quotes_Table.P ersonLast = Persons_Table.L astName) AND
(Quotes_Table.P ersonFirst = Persons_Table.F irstName) SET
Quotes_Table.Cu stomerID = [Persons_Table].[PersonID];
Copy and paste the above statement into a query.

Create a new query. Click on the SQL button in DESIGN view. paste.

Oh, make a backup of your table before you do it... don't want to mess
up the data you have with no back door.

Jul 19 '06 #7
Thanks for the detailed response, but Access just prompts me manually
for each field again. I am exporting the data to Excel, where I will
break it into chunks to merge manually -- it will probably take 3-4
hours but I don't think I will find a faster way.

Jul 19 '06 #8

Mark Welch wrote:
Thanks for the detailed response, but Access just prompts me manually
for each field again. I am exporting the data to Excel, where I will
break it into chunks to merge manually -- it will probably take 3-4
hours but I don't think I will find a faster way.
Once way to get the prompts is to not have those fields in your table.
What happens if you try to create a relationship between the two tables
(on FirstName and LastName)? If that part works, you _may_ be able to
run an update. The problem is that you have to have a one-to-many
relationship between the two tables.

If you have to do it in Excel, you can use H/VLookup, but it's about
the same thing. I'd spend a few more minutes in Access.

Try running the Find Unmatched query wizard and see if there are values
in one table that are not in the other. That will tell you which
records are screwing things up for you. Lots easier than doing
everything manually.

Jul 19 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
38653
by: Sami | last post by:
I can create queries and reports based on info from one table. How do I create one using information from multiple tables. What do I need to make sure the information from one table will be correctly assigned to specific person from another table? Thanks.
1
23362
by: mojo | last post by:
We have set up an Access database with tables x,y, & z where we would like to have multiple people entering data into a table x. Each person has an identical copy of the database on their PC's. When the person is done entering the data into table x, we want them to e-mail their database to one person who will combine the data from table x into a single database. What's the easiest method to combine the tables into a single table? ...
7
2255
by: jude | last post by:
Hello, We are starting to discuss a new ASP.NET application that will be a data acquisition display application. The data to be displayed will come from multiple sources--database tables, serial and Ethernet PLCs, OPC servers, etc. The application will need to display graphic gauges and charts, in addition to the actual values retrieved from the various data sources. The application will need to continually update itself in a real-time...
1
1981
by: abc my vclass | last post by:
How to design and build reports which data source from business logic layer ? Is there any documents or tutorials explain the steps to design and build reports if my projects' data source from business logic layer, not directly connecting database.
6
3155
by: Mike Wilson | last post by:
Dear Group, I have a heirarchical set of database tables, say - "order" and "order_type" and want to display a series of orders in a grid control, and in place of the order_type foreign key identifier, I would like a dropdown combo box (lookup from the "order_type" table) to change the type of the order. I also need an update command button, a delete row button and also an insert new row button. I'm sure this is a very common design...
5
3008
by: jhutchings | last post by:
Hello everyone, I have a database where I collect shipment data from various tables. However, I have a problem. Whenever I want to see shipping data for orders that were set to ship on or before a certain date (in this case January 30th) the database will return 2 rows for an order as you can see below. Order ID: Line: Due Date: Qty: Ship Qty: Part #: Shipped: 141285 1 1/30/2006 31 10 S15F-55
0
1603
by: Christoph Haas | last post by:
Hi, list... I have written an application in Perl some time ago (I was young and needed the money) that parses multiple large text files containing nested data structures and allows the user to run quick queries on the data. (For the firewall admins among you: it's a parser and web-based query tool for CheckPoint firewall rulebases. The user can search for source and destination IPs and get the matching rules.) The current application...
13
8027
by: deville75 | last post by:
Hi, I'm somewhat new to MS Access and I'm having trouble with displaying two forms. Firstly my program is going to use a search button to search through several different Tables. The Search Form has a search field and once the 'Search' Button is pressed it is supposed to open multiple forms showing the correct results. I'm able to do this correctly now, but I'm having a problem displaying them. When the search button is pressed I do a simple...
1
3421
by: bluereign | last post by:
Thank you for your assistance. I am a novice looking to JOIN and append or combine records from 2 current Tables into 2 new Tables named below. I have been able to JOIN Tables with the script below. My problem is if I don’t use the WHERE clause in my script below, the script can query up to 3 records with the same “LoanNo” due to the fact that there can be up to 3 “TypeCodes” in each Table (Borrower, Co-Borrower 1 and Co-Borrower 2...
0
9893
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11005
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10665
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10723
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10349
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9501
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7070
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5735
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
3177
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.