Combine Data from Two Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I'm a database guy but I don't work with Access so I'm having problems that
doing something that would ordinarily be a piece of cake in the database I
work with. Therefore, I would truly appreciate any help you can provide.

A co-worker created two different tables to store data that should have been
in the same table. The 2nd table was created to record the date when the
stored document was uploaded to the web server. She finally realized all of
the data should be in one table, so now she wants to combine the two tables
into one.I've tried an Append Query and Importing the Data, but I keep
getting error messages.

The main table [Stored_Documents] has a primary key field [I temporarily
removed the primary key because I thougth that was the cause of the problem]
as follows:

AccessNumber [Text]

I modified the design of the table by adding three [3] new fields which are:
Uploaded [Yes/No]
UploadDate [Date/Time]
Comments [Text]

The 2nd table [Scanned_Documents] has the same fields, some of which have
null values such as the Upload Date. The matching field is [AccessNumber]

AccessNumber [Text]
Uploaded [Yes/No]
UploadDate [Date/Time]
Comments [Text]

The goal is to capture the data from the 2nd table [Scanned_Documents] and
add that to the Main Table [Stored_Documents] and then delete the 2nd table
because she will no longer need it.

Robert
 
First: Append queries ADD RECORDS to the table. You want to add/modify data
in existing records - that is an UPDATE.

You need a query that joins the two tables and then updates the fields.

UPDATE [Stored_Documents] as St Inner Join [Scanned_Documents] as Sc
On St.AccessNumber = Sc.AccessNumber
SET St.Uploaded = Sc.Uploaded,
St.UploadDate = Sc.UpLoadDate,
St.Comments = Sc.Comments

If you are doing this in the query grid
-- Add both tables to the grid
-- Link them on AccessNumber
--Drag the three Stored Documents fields into the grid
--Change the query type to Update Query (Menu: Query: Update Query)
-- Type the tablename and fieldname under each item, for example
[Scanned_Documents].[UpLoaded]
--Run the query (Menu: Query: Run)

Don't forget to run this on copy to see if you get correct results.

All of the above assumes there is only one matching record in
Scanned_Documents for each record in [Stored_Documents]. If there is more
than one record matching record, then I would suggest that you do need two
tables since you have repeating data.
 
Hello John:

As far as I know, there is only one matching records inthe Scanned_Documents
table because that process occurs only once. There are 451 records in this
table.

In the main table, there are over 20,000 records.

I tried your suggestion and ran into two problems.

[1] When I ran the query, a Parameters dialog box popped up for the
Stored_Documents.Upload_Date field. I didn't know what to put in there so I
left it blank.

[2] I was then warned that 451 records would be updated and that was the
goal, so I clicked OK. However, it appears as if no records were updated in
the main table, Stored_Documents.

Obviously I must have done something wrong.

Robert
 
If you got a prompt then Access didn't recognize the field or table name or
both. That usually indicates a typing error.

Is the field name Upload_Date or is it UploadDate? If you can't figure this
out, perhaps you can post the SQL view of the query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
Hello John:

Here's the SQL Syntax you requested. Please note, this query contains the
REAL names of the tables. I previously supplied psuedo names.

The main table is TCI Holdings Table and one with 451 records is called [TIC
Holdings Scanned Titles]

Update Query:

UPDATE [TIC Holdings Table] INNER JOIN [TIC Holdings Scanned Titles] ON [TIC
Holdings Table].[Access Number] = [TIC Holdings Scanned Titles].[Access
Number] SET [TIC Holdings Scanned Titles].Uploaded = [TIC Holdings
Table].[UpLoaded], [TIC Holdings Scanned Titles].[Date Scanned] = [TIC
Holdings Table].[Upload_Date], [TIC Holdings Scanned Titles].Comments = [TIC
Holdings Table].[Upload_Comments];
 
The SQL looks like it should work. As I said earlier, are you sure that you
have the field name and table name correctly spelled? That is almost always
the reason for Access to ask for a parameter. The names I suspected would
be the ones I typed in - Check Upload_Date to make sure it doesn't have an
extra space buried in it or that is is not "Upload Date" with no underscore
in the table TIC Holdings Table.

Also make sure you are updating the correct table. Do you want to update
Tic Holding Scanned Titles or Tic Holdings Table. You are updating the Tic
Holdings Scanned Titles with this query. I hope that is correct or if not,
that you have a backup copy of your data.

UPDATE [TIC Holdings Table] INNER JOIN [TIC Holdings Scanned Titles]
ON [TIC Holdings Table].[Access Number] = [TIC Holdings Scanned
Titles].[Access Number]
SET [TIC Holdings Scanned Titles].Uploaded = [TIC Holdings
Table].[UpLoaded],
[TIC Holdings Scanned Titles].[Date Scanned] = [TIC Holdings
Table].[Upload_Date],
[TIC Holdings Scanned Titles].Comments = [TIC Holdings
Table].[Upload_Comments];

..
 
Hello John:

I've been trying to send a reply advising you that I figured out why the
query wasn't working, however, screen redraws are crawling as in several
minutes. I don't know if it's only me or if everyone is having the same
problem with this website.

John, your solution was right on the money, however, I misunderstood the
initial set of instructions. I reversed the fields in the query so I was
updating the wrong table. I was actually updating the smaller table, the one
we want to delete. Once I figured out my mistake, the "Update" query you
described worked perfectly.

John, I can't begin to describe how much I appreciate your valuable
assistance. You are a good man and I hope you have a wonderful day. Thanks
again for donating your valuable time and in depth knowledge of Access.

Robert
 
Back
Top