Replacing data in a table

[techkenny1]

Hi all,

I have a table which holds tax data. There is about 2000 records in this table. Its for tax rates on gross amounts. I have to update this table with new data.
I have already prepared the new table with the new data.
How can I replace the previous table with this one.
Do I use an update query? I'm just not sure how to do this.
many thanks

KP

 

[PWise]

I would not replace /update the table
what i would do is add afield to the table for what piriod the rate is
and append the new data to the table

 

[techkenny1]

Hi pwise,
I can agree with that, so run a delete query first and then append the new data.
Would that be right way to do it?

KP

 

[randy700]

pwise is suggesting you retain the data that is currently in the table. I agree - you may need it later.
As suggested, add a new field to the existing table. Add the same new field to the "new" table.
Run an UPDATE query to populate your new field in the old table.
Finally, run an APPEND query to add the data from your new table.


Randy

 

[techkenny1]

Hi guys,
many thanks for your replies. i agree with you that this is a good way to do it,but I have a problem which I'm not sure how to resolve with your answers.
The field names are important in the tax table. For as you enter a gross amount into the pay form it automatically calculates the right amount of tax. If I have to add a new field, the filed name will be different then I'm sure that it will not work. Without renaming fields.
The tax table has 3 currency fields; Gross, TaxLo, TaxHi
The field gross directly relates to either Taxlo or Taxhi.
So how owuld I add a new field to this table?

Many thanks,

kp

 

[randy700]

1. Open your tax table in design view.
2. Add your new field.
3. Create and run an update query that populates the new field on every row.
4. Add the same new field to your new table.
5. Run the same update query, obviously using different data in the new field.
6. Create and run an append query to add the new table's data to your existing table.

None of this will affect any existing forms, queries, reports.


Randy

 

[PHV]

None of this will affect any existing forms, queries, reports
Really ?
I'd use the new field as criteria ...

 

[randy700]

PHV is correct.
You will need to update existing objects to only use data based on the new field.

Randy