Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating table information (A2002)

    Does this table from Excel have a fixed record structure, even if it isn't normalized? If so, you could write code that uses DAO or ADO to loop through the records, parse out the information and update your normalized tax table. The code would open two recordsets: the denormalized table and the normalized table.

    BTW, is it really necessary to analyze the differences between the old and new denormalized tables, or would it be OK to transfer the data from the new table to the normalized table, even if it replaces unchanged data with the same data?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating table information (A2002)

    I'm afraid that this kind of code always has to be "hand-written" because it is dependent on the precise structure of both tables. The rough idea in pseudo-code is:

    Open recordsets, say rstNormalized and rtDenormalized, on the two tables
    Do While Not rstDenormalized.EOF
    Process record
    rstDenormalized.MoveNext
    Loop
    Close recordsets and release object variables

    Process record is:
    - Find record in rstNormalized that contains data for this Location and first tax field in rstDenormalized
    - Update and save this record
    - Repeat this for the other tax fields in rstDenormalized

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating table information (A2002)

    Thanks for getting me started. I've attached the data structure of both the tblTax and the imported information (TaxRatesImp) showing how the info matches up...or doesn't match up.

    As you can see, the TaxRatesImp has information in one field that the tblTax needs to have in two fields. For example, the imported information has a State Sales Tax field that lists the tax rate as a percentage while the tblTax needs both a TaxTypeID (state) and a Percentage field. How does this affect the code structure you were suggesting?

    Thanks for your ongoing help,
    Elizabeth
    Attached Images Attached Images

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating table information (A2002)

    You guessed right, they are tax types. There are three types total:
    1. state
    2. SILO (school infrastructure local option)
    3. LOST (local option sales tax)

    Elizabeth

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating table information (A2002)

    Are LOST and SILO the two possible tax types? (I have no idea what LOST and SILO mean in this context)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating table information (A2002)

    Sorry to keep on asking questions. I see that tblTax has CountyID and CityID, whereas TaxRatesImp has County Name and City. In your other thread, CountyId and CityID are lookup fields linked to tlkpCounty and tlkpCity, but I don't see a County Name or City field in the lookup tables. How to match CountyID to County Name and CityID to City?

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating table information (A2002)

    Does this table from Excel have a fixed record structure, even if it isn't normalized?
    ::Yes

    If so, you could write code that uses DAO or ADO to loop through the records, parse out the information and update your normalized tax table. The code would open two recordsets: the denormalized table and the normalized table.
    ::This sounds very promising

    Would it be OK to transfer the data from the new table to the normalized table, even if it replaces unchanged data with the same data?
    ::This is even better. As long as the key stays the same, it doesn't matter if it's replacing unchanged data. What's the best way to set this up? Do you know of an example I could study?
    Attached Images Attached Images

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    updating table information (A2002)

    My objective is to update tax info contained in a normalized table.

    I need to use tax rates in some calculations and those rates change periodically. I can download an Excel spreadsheet that lists current rates. However, the spreadsheet doesn't match my tax table's structure. The spreadsheet has a single record for a location which lists all the taxes and their corresponding rates while my tax table has a separate record for each tax and its rate for a location.

    Is there a good way to handle this?

    The best that I could come up with is to download the spreadsheet and import it "as is" into a table. This denormalized table is in addition to my normalized tax table. When it's time to update tax information, download and import the spreadsheet "as is" again into a second table. Then compare the original denormalized table with the new denormalized table with a third party "compare data" add-in. Make note of the individual changes by hand and then update my tax table.

    There's got to be a better way! Any suggestions?

    E.
    Attached Images Attached Images

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating table information (A2002)

    I'm not sure I'm getting what you're asking; I must be a little dense this morning.

    "tblTax/CountyID" maps to "TaxRatesImp/County Name" and "tblTax/CityID" maps to "TaxRatesImp/City"

    The only reason both of the city and county fields don't have the same name is because I accepted the Excel field names when I imported the spreadsheet. What I'm confounded by is how to match up the fields that don't map directly. I've attached a snapshot of the Excel spreadsheet. I'll go back and attach a design view of the tblTax and a report of it's relationships to earlier messages in this thread so that you can see what I'm working with.

    Elizabeth
    Attached Images Attached Images

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating table information (A2002)

    The reason I asked was that the difference in naming made me assume that CountyID was a numeric field (e.g. an AutoNumber field) in tlkpCounty. Thanks for the screen shots. Please make a backup copy before trying the following code. I decided to use only one recordset (based on the imported table) and construct three SQL statements to update each of the tax types. I have assumed that you're only updating existing values, not adding new ones.

    Sub ProcessImport()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rst.Open "TaxRatesImp", cnn, adOpenForwardOnly

    Do While Not rst.EOF
    ' State Sales Tax
    strSQL = "UPDATE tblTax SET [Percent] = " & rst![State Sales Tax] & _
    " WHERE CountyID = " & Chr(34) & rst![County Name] & Chr(34) & _
    " And CityID = " & Chr(34) & rst!City & Chr(34) & _
    " And TaxTypeID = 1"
    cnn.Execute strSQL, , adCmdText
    ' LOST
    strSQL = "UPDATE tblTax SET [Percent] = " & rst!LOST & _
    " WHERE CountyID = " & Chr(34) & rst![County Name] & Chr(34) & _
    " And CityID = " & Chr(34) & rst!City & Chr(34) & _
    " And TaxTypeID = 2"
    cnn.Execute strSQL, , adCmdText
    ' SILO
    strSQL = "UPDATE tblTax SET [Percent] = " & rst!SILO & _
    " WHERE CountyID = " & Chr(34) & rst![County Name] & Chr(34) & _
    " And CityID = " & Chr(34) & rst!City & Chr(34) & _
    " And TaxTypeID = 3"
    cnn.Execute strSQL, , adCmdText
    rst.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating table information (A2002)

    Have run into a complication. Users have changed their minds. Turns out I can't overwrite the old values. They need to maintain the information on tax rates even if the tax is no long applicable. So, I need to create a new record if the values of the imported table do not match the values of the existing table. How is that going to alter the code solution you provided?

    I'm running into several problems importing the data or working with the data imported. One, I get errors when trying to importing the Excel column heads, so I've been letting Access assign field names. That means the import has Field1, Field2, etc. Two, I realized I had city and county info in two separate tables so combined the two tables. I run into data mismatchs when combining this tax info table that contains city and county for the state with another table that contains city, state and zip info for the nation. I came across a promising fix that I wanted to run by you. Looks like I can use a DDL query to set both field names and data types after I've imported the table which would eliminate both of the above problems. Would that be your solution? Or, is there a better way?

    A final issue with the import is that the import contains the county name for one county field but the county number(s) for a second field. Currently I'm running an update query to change county numbers to county names and then plan to use parsing to split the second column into a second and third column. I don't know if there's a more efficient way to accomplish my ends.

    Thanks for taking the time to help me out. Wish I had your knowledge. I'm learning but it seems so agonizingly slow at times!

    Elizabeth

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating table information (A2002)

    How are you going to distinguish between the old and new records, if you have records with the same city, county, tax type, activate date and deactivate date, but different rates?

  13. #13
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating table information (A2002)

    When the rates change, so too does one or more of the dates associated with it.

    Elizabeth

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating table information (A2002)

    From the sceenshot in one of your replies higher up in this thread, you have Effective Date and Sunset Date for LOST and SILO, but not for State Tax. What happens if the State Tax changes?

  15. #15
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating table information (A2002)

    It's a rare thing for the state sales tax to change. It's been the same for the entire 10 years that I've lived in the state. I figured if it did change I'd make a copy of the tax table, run an update query on it and append the results to the original table.

    Elizabeth

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •