Clicky

I am creating a database that is intended for maintenance by someone with minimal Access skills. Amongst the many other functions the database provides, one function in particular will be taking automated excel spreadsheet reports and importing them into an existing database. Unfortunately, the sources of data come from different spreadsheets made by different systems (hence the need for this DB), yet they reference the same households. The uniform criteria between the automated reports is the address, which works well for data sorting.

The problem is that the sheets each contain different types of data, but the access table has all the different data types. What I want to do is import a sheet, have it check to see if an address already exists, and if so update the record. If not, I want to append a new record. I want  to do this for each type of spreadsheet (there are 3).

I won't get into the specifics of the fields, but for simplicity let's use the following fields:
A = Address, B = Field B, C= Field C, etc.

This is an example of what I have going on:
Excel Sheet 1: A, B, C
Excel Sheet 2: A, D, E
Excel Sheet 3: A, F, G

I have table with: A, B, C, D, E, F, G

Hopefully I made sense here. Please help, this is a critical database for merging documents from various Gov't Agencies.

asked 12/11/2011 09:03

jid103179's gravatar image

jid103179 ♦♦


7 Answers:
Many times here was stated:
1. If you like import excel files into Access, import to temporary table (or link) file.
2. Make some queries (code) for data checking and check imported data.
3. Import data (update existing) into real tables.

May be you can prepare some sample data?
link

answered

als315's gravatar image

als315

I'd recommend creating three 'staging' tables, sequentially numbered, for these files in your access database, with the three fields from each soreadsheet that you need.

ie:
tblStaging1:  A, B, C
tblStaging2:  A,D,E
tblStaging3: A,F,G

Than create a form with a button to import the excel data into the staging tables.  The code behind the button would be something like this
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
Dim I as Integer
dim rs as dao.recordset

' Clear the tables
For I = 1 to 3
    CurrentDB.Execute "DELETE * FROM tblStaging" & I, dbfailOnerror
Next
' Import the data
docmd.TransferSpreadsheet acImport,,"tblStaging1","C:YourPathYourFileA.xls"
docmd.TransferSpreadsheet acImport,,"tblStaging2","C:YourPathYourFileB.xls"
docmd.TransferSpreadsheet acImport,,"tblStaging3","C:YourPathYourFileC.xls"

' Update or insert the data to the real table
'**** You'll need a similar block of code for each staging table/spreadsheet import.
set rs = Currentdb.openrecordset ("SELECT * FROM tblStaging1")
   do until rs.EOF 
        ' Check for field A in your real table
        IF DCount("*","YourRealTable", "A = " & chr(34) & rs!A & chr(34)) = 0
             ' If it does not exist, add it
              CurrentDB.Execute ("INSERT INTO YourRealTable (A,B,C) VALUES('" & rs!A & "','" & rs!B & "','" & RS!C & "'")),dbfailonerror
        Else  
             'If it exists, update the entry
             strSQL = "UPDATE YourRealTable SET A ='" & rs!A &"', B ='" & rs!B & "', C='" & rs!C & "'"
             currentdb.execute strSQL, dbFailonError
       End If
Loop


The idea is to:
1. Import the data into staging tables
2. Determine whether a record exists for that address in your target table
3. Update (edit) or insert (add)  the record according to that determination

That code is fairly close, but you'll have to adjust it for your own specific fields, tables, etc.
link

answered 2011-12-12 at 07:04:26

mbizup's gravatar image

mbizup

mbizup,

I understand the code; makes sense. I should have thought to create staging tables.

So I will have three tables with a common field (A). I suppose that the final concatenated A-G fields would be done via query. I'll have to look specifically on how I would do that to ensure the final product is correct.

Thanks for the help. If I have further questions I will post back here.

als315,

Looks like you were providing the same solution. I can not provide the data, even the specific fields. I apologize. I think I understand the solution, however.
link

answered 2011-12-12 at 07:35:12

jid103179's gravatar image

jid103179

You are lucky if address field from different sources is exactly same and you will be able to link tables. Usually you should prepare address for use. Generally you had to remove double spaces, errors in street names etc.
link

answered 2011-12-12 at 08:02:29

als315's gravatar image

als315

als315,

Actually, I do have issues. I plan on using "Like" with street names and match with last name. It won't be perfect but, literally, "its good enough for gov't work".

Obviously striving for 100% match.
link

answered 2011-12-12 at 09:33:05

jid103179's gravatar image

jid103179

I can recommend you to divide address to separate fields (ZIP, State, City, Street, etc.). Sometimes very useful is vocabulary of street names, you can find unmatched names and correct them manually.
link

answered 2011-12-12 at 12:02:43

als315's gravatar image

als315

Perfect.
link

answered 2011-12-12 at 20:34:44

jid103179's gravatar image

jid103179

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×14
×9

Asked: 12/11/2011 09:03

Seen: 285 times

Last updated: 12/13/2011 02:45