Update and Inner Join Question

snoopy92211 · February 25th, 2005, 03:46 PM

Howdy. I'm trying to figure this out; I keep erring out at the code.

Here is what my code's trying to accomplish.
1. Import a file
2. once that file has been imported, create and add the following fields to this table: location address, location zip, location state.
3. Then I want to check this file against the masterfile table, based on locationcode. the masterfile has the locationcode address information, where the imported file does not.
4. I'm trying to match the location code from the imported file to the location code on the mastertable. once I match the code, append the locationcode address from the masterfile to the imported table, which is the reason why I created the location fields in the imported table.
5. Then I want to check for any locations that aren't in the masterfile table. for example, there may have been a new location code that's been added to the imported file, and that location code needs to be appended to the masterfile table. BUT -- I need a prompt that will allow the user to enter the locationaddress information into the master table.

The BOLD information is where I need help. I provided the previous code to show how I was trying to accomplish this.

Thanks!!

Snoopy

Code:

Public Function ImportTable()
'The Imports Function imports tables into the database.
Dim strInput As String, strMsg As String
    strMsg = "Enter the filepath where the File is located"
    strInput = InputBox(Prompt:=strMsg, Title:="Import Filepath", Default:="C:\2004\Oct 04\All701", XPos:=2000, YPos:=2000)
    Dim qryDelete As QueryDef
    Dim rstImport As recordset
    Dim db As Database
    Dim tdf As TableDef
    Set db = CurrentDb()
    Set rstImport = db.openrecordset("tblFilePath1")
    Dim fld1 As Field, fld2 As Field, fld3 As Field, fld4 As Field
    Dim A
    Dim B



    rstImport.MoveFirst
    Do Until rstImport.EOF()
        DoCmd.TransferText acImportFixed, rstImport.Fields(4).Value, rstImport.Fields(3).Value, strInput, "0"

   'set location field definitions
    Set tdf = db.TableDefs(rstImport.Fields(2).Value)
    Set fld1 = tdf.CreateField("LocationAddress", dbText)
    Set fld2 = tdf.CreateField("LocationCity", dbText)
    Set fld3 = tdf.CreateField("LocationState", dbText)
    Set fld4 = tdf.CreateField("LocationZip", dbText)
    ' Append fields.

tdf.Fields.Append fld1
tdf.Fields.Append fld2
tdf.Fields.Append fld3
tdf.Fields.Append fld4


db.Execute "Update rstImport.Fields(3).Value A Inner Join mpslocations B ON A.Location_Code=B.LocationCode" _
& "Set A.LocationAddress = B.StreetAddress" _
& "Set A.LocationCity = B.City" _
& "Set A.LocationState = B.State" _
& "Set A.LocationZip = B.Zip" _
& "where A.Location_Code  B.LocationCode""," _
& "A.LocationAddress = B.StreetAddress""," _
& "A.LocationCity = B.City""," _
& "A.LocationState = B.State""," _
& "A.LocationZip = B.Zip"

db.Execute "SELECT A.* FROM rstImport.Fields(3).Value A LEFT JOIN mpslocations B ON A.Location_Code=B.LocationCode" _
& "where B.Locationcode Is Null"

rstImport.MoveNext
    Loop

mmcdonal · February 28th, 2005, 04:30 PM

Hi,

   This sounds more like a structure problem. Your database should not have the locationcode address in each record, just the location code. The locationcode field should be a foreign key field, and it should look up to a locationcode address table. Then you won't need to duplicate this address in each record when you already have the address stored in one place. It doesn't sound normalized.

   If you normalize the data, then you won't need to update each record with the location address each time you run this process.

   You may want to provide a default value for the locationcode, so that if one is not provided, then the user can be prompted to change from the default. If the locationcodes might be new ones not in the locationcode look up table, then you can capture the new ones in a query that report to the user at the end of the update process.

HTH.

mmcdonal