How to combine 2 linked Excel tables in access 2003 with same field names, using query?

5 pts.
Tags:
Access 2003
Data Management
Microsoft Access query
I have 3 employees working who have no access on their PC. they are performing their data entry in excel and I ahve linked all three tables to one access file as I have Access on my PC. I need to combine all 3 tables using query or some other method in access to create one master table. Here is the example of data entered by each employee.

                   Field name1         Field name2       Field name3

                   Emploee ID         CustomerID         Address

table1          1222                   001                    123 main st

                   1222                   002                    123 king st

table2          1233                   003                    11 john st

                   1233                   006                    22 hughson st

table3          1244                   004                     11 mary st

                   1244                   005                      93 king st

 

Is there any way to combine these data using query to come up with one master table showing the following?

 

                   Emploee ID         CustomerID         Address

                   1222                   001                    123 main st

                   1222                   002                    123 king st

                   1233                   003                    11 john st

                   1233                   006                    22 hughson st

                   1244                   004                     11 mary st

                   1244                   005                      93 king st

 Any help on this would be greatly appericiated.

 

Thanks you in advance.



Software/Hardware used:
2003 microsoft office/ access
ASKED: November 5, 2009  6:37 PM
UPDATED: June 22, 2012  1:03 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

For the linked table approach. Make a Union query in Access.

Select EmployeeID, CustomerID, Address From LinkedTable1
Union All
Select EmployeeID, CustomerID, Address From LinkedTable2

This should do what you are asking for. The All part of the union brings in records that might be the same in both tables. If it could happen that the same records are in both tables and you don’t want both of the records, take off the All clause.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Accesstables
    Here is a different approach. Rather than merge the tables in Access, use XL as a multiuser data entry program to post to a single Access Table somewhere on the network. This approach requires VBA macros in XL. 1) Create a button to for the user to click and post the data. 2) Assign a Macro to the button that reads the XL range and formats the entries into an SQL INSERT Statement (example:) sSQL = "INSERT INTO myAccessTable EmployeID, CustomerID, Address VALUES ('" & cell(1, 1) & "', '" & cell(1,2) & '", " & cell(1,3) & "' " 3) Call the routine below with the SQL string and a connection string pointing to your access data. Public Function SQLExec(sSQL As String, sConnect As String) As Variant ' SQLExec: Execute and SQL Statement 'Prerequisites: ' Include "Microsoft ActiveXData Objects 2.0 Library" or higher reference ' Parameters: sSQL - a string containing an SQL command to execute ' sConnect - a connection string ' Example: bResult = SQLExec("CREATE INDEX index_name ON table_name (column_name)", _ ' "ODBC;DSN=AS400;Driver={iSeries Access ODBC Driver}") ' Date Init Modification ' 01/01/01 CWH Initial Programming On Error GoTo ErrHandler SQLExec = "Failure" 'Assume Something went wrong Dim cn As ADODB.Connection Dim errLoop As ADODB.Error On Error GoTo ErrHandler Debug.Print "Start:", Time, sSQL Set cn = New ADODB.Connection cn.Properties("Prompt") = adPromptComplete cn.Open sConnect, "", "" cn.Execute sSQL Debug.Print "End:", Time ' Application.StatusBar = cn.RecordsAffected & " Records affected." If cn.Errors.Count = 0 Then SQLExec = Success Else SQLExec = cn.Errors(0).Description End If ErrHandler: On Error Resume Next If Err.Number <> 0 Then SQLExec = "Failure" If Err.Number <> 0 Then MsgBox "SQLExec - Error#" & Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error", Err.HelpFile, Err.HelpContext Else For Each errLoop In cn.Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description, vbCritical, "Error", errLoop.HelpFile, errLoop.HelpContext Next errLoop End If End If cn.Close On Error GoTo 0 End Function
    0 pointsBadges:
    report
  • Ikiddikidd
    Follow up question to your question. A union would generally work, but I am attempting to merge and find a sum total of data of the same type and running into a problem. I have 2 Linked Tables, both with a UNIT#, Date Sold, Qty Sold. When I attempt a union between the 2 Tables I get duplicate listings for the multiple tables, such as: Unit Date Sold Qty Sold 111 6/1/12 5 (Table1) 111 6/1/12 3 (Table 2) Whereas ideally, table 1 and 2 would sum quantities sold under that day for the same Unit: Unit Date Sold Qty Sold 111 6/1/12 8 (Table1&2 combined)
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following