Combining 2 Excel-linked tables

[matttan]

I have two tables that are importing/linking data from Excel worksheets. They contain exactly the same fields, but different data. However, I want to construct queries, etc, on the combined data. Each data record has a reference number, which would form the basis of the combined data records. I am trying to work out what the best way of doing this is.

At the moment, I have my linked tables:
tblDataNth
tblDataSth

I have also set up a query for each that cleans the data (there are a few dummy/blank rows from the data link):
qryCleanDataNth
qryCleanDataSth

There are about 20 fields, but for ease of the explanation, I'll give a couple of fields:
Deal Reference Number (this is the identifier for the combined data)
Value
RV
Settlement Date
State

The fields are varying data types - number, text and date

One approach I was thinking of was to set up a 'make table' query for one of the linked tables, and an 'append' query for the other. Then, when I run the make table query, it would replace the previous combined table each time, followed by the append query to include the rest of the data. I haven't tried it yet, so don't know if it would work. Also, it would be someone else using the end product, so I'd also need to build a macro or some code to automate the update.

Any suggestions on how else I can do this?

 

[SydneyGeek]

Hi Matt,

Yes the Make-table followed by the Append is the way to go. To automate it, do the following:
1. Paste this code into a new module, then save the module as TableCode

Function CombineTables()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "YourMakeTableQuery"
    DoCmd.OpenQuery "YourAppendQuery"
    DoCmd.SetWarnings True
    MsgBox "Your data is updated"
End Function

2. Create a blank form with a Command Button. Right-click the button in Design view, select Properties, then Events.
3. Double-click the blank line next to On Click. You'll see Event Procedure in square brackets. Click the Builder (...) button, and in the code module, type TableCode.CombineTables
Save the form and give it a go.

{EDIT} -- change the query names to suit your names, or it will fail.

Denis

 

[matttan]

All working fine - thanks for the code.