Is an update query with user input of data to update TO, possible?

[Owl]

Please will somebody help me.

My users will need to update the Fee field of my database every year. The records of the RateOfFee are currently as follows:

RateOfFee Fee
0 R0.00


1 R200.00
2 R250.00
3 R400.00

There are 67 Categories of Professions and each of them has one of the above rates.

There are 5669 records, and each of them (amongst other fields, of course) has one of the above Categories.

I want to create an update query on a macro and button, BUT:
1.1. my first problem with that is that I need the user to be able to update the above 4 Fees to whatever the amounts will be each year, so I would then need the macro to allow them firstly to put in each of the RateOfFees in turn (I know how to do that with a parameter) AND secondly the actual fee (but I don’t know how to do that or whether it can be done)
1.2. I thought of having instead, a simple form with an ID, a RateOfFee1 and a Fee1 field based on a table so that I could use what is in the field Fee1 – so the user could update just the 4 Fees in Fee1 and then run the macro for the update query from a button instead of the user having to input the data into the update query for what the field will update to. However, I found couldn’t update the 5669 records from 4 records, and indeed, I don’t know whether that can be done.

Are either of these methods feasible? If so which and how? If not, please could you tell me another way.

Thank you for any help.

Owl

[Robeen]

Instead of updating the table with the 5669 records, and adding the new Fee Amount each year - you can just use a query to do this.

1. Change the Fee Amounts in your RateOfFee Table.
2. Create a Query that uses the two Tables and create a join between the Rate fields in the two tables.
3. Use the Fee field from the RateOfFee Table.

This way - it doesn't matter how often you change the Fee amount - the query will always pick up the latest value.
You can base your Forms etc on the Query rather than on the Table itself.

If you prefer to have the latest Fee for each row of data IN that Table - then:
1. You can make this query into a MakeTable query and create a Table with the updated Fees for the 5669 rows of data. After you verify the data - you can rename the Table . . .
2. You can make the query into an Append Query -> and append it to the Table after you have backed it up and then emptied [deleted] all the data from it.

I hope this helps!

[Owl]

Thank you for your reply, Robeen.

However, although you didn’t quite understood what I was asking, (I was already trying to do exactly what your first option was, but didn’t succeed) and my question was how to do your first option and whether it was possible to update from my 4-record RateOfFee table to my 67-record TL_ProfessionCategory table -which ultimately updates my 5669 records.

However, you indirectly:

1. answered my question because you didn’t say it wasn’t feasible, and

2. said I should have the RateOfFee linked (which I didn’t have – I had linked them from an ID to a foreign key) and when I linked them as you said, hey presto! it worked perfectly!

Thanks for the second option which I will keep in my library for my own use if I should need a similar procedure, but I won’t use it on the database for my users as I want them to be able to do the update (which has 2 other parts to it as well) with the push of a single button.

Thank you very much, Robeen. You have made a huge difference to my evening!

Owl

[Robeen]

Lol . . . sorry I got it all mixed up in there!!


But I'm glad I was able to help - even if it was in a roundabout way!!

[Owl]

And you were so prompt too, with both replies! Thank you! That, along with the solution, made the huge difference to my evening and they put s on my face!

Owl