Emilio
Neileg is giving you specific advice. I'm going to give you some more general advice.
The reason we suggest never storing a computed value in a table has to do with saving space vs. saving time. USUALLY, the computer is so fast that any minor in-line computations are pretty much invisible. There is also an issue called normalization that applies here. If the price is immutable in any place except your master pricing list, then computing the price is wrong. You want to look it up. If that means a lookup qualified by dates, so be it. But something you said makes me want to consider your problem from another viewpoint.
You suggested that it would be possible to alter prices a bit more casually than some people do. If that is your business rule, then it is absolutely right (in fact, NECESSARY) to take that fact into account. But it also means that a lookup and recompute is not so easy any more.
Here are some cases demonstrating the principle to consider. You have to decide which (if any) apply to you.
1. Prices stored in the price table are rock-solid but change over time as you economize, amortize, or whatever. In that case, you store starting and ending dates in the price table, which therefore includes item number, two dates, and a price. Then, when you deal with prices for any transaction, you have the transaction date and identification of each item sold. You can identify the price by basing your lookup on the item number and the dates during which that price was applicable.
2. Your sales reps have local price override capability because the price is only a guideline at best. In that case, you MUST store the price that they used for any give line item because you have no later way to capture it otherwise, and a recompute for later review or other accounting activity is flat impossible.
3. Your sales reps have some discretion in the discount rate to apply to an otherwise solid price, but both the price and the list of possible discount rates are rock-solid other than changing on some regular schedule. In that case, you must store the chosen discount code (or codes) that were applied. The discount and price tables must then be time-tagged for dates during which those prices and discounts were applicable. You could compute the sales price for each item from the combo of the item number, base price, discount codes, and dates.
In all three cases, what you are shooting for is that you must store everything for which a CHOICE was available. Because in the implied model underlying your record-keeping, it is that CHOICE that depends on the date and invoice information. If the choice included raw unit price, you must store the price. If the choice included discount codes, you must store the discount codes. If the choice included the item number (which it probably does), you store THAT.
Now, as to queries:
A table and a query are ALMOST the same thing. Except that there is no way to store a formula in a table. You can store the result of a formula in a table, but not the raw formula itself. On the other hand, a query CAN hold a raw formula. It is possible to build forms, reports, and VBA code to manipulate recordsets based on queries. If you use SQL aggregate functions in a query, it is possible that you could build a query that cannot be used to update individual records, but other than that, anything a table can do, a query can do.
Now, the reason we say that you should not store the RESULT of a formula in a table depends on whether the formula does or does not involve an item for which a choice existed (as explained in the discussion above.) If there was no choice involved, there is no need to store anything. But where a choice occurred, the choice is implicitly PART of the information defined by the invoice number. And THERE is where the normalization rule creeps in. You don't store anything in a table that didn't depend on the prime key of that table. If the price wasn't a choice, it doesn't belong in the invoice table. If the discount code wasn't a choice, it doesn't belong in the invoice table. The items sold were a choice, so they have to be associated with the invoice number. The date was a choice (the customer CHOSE to make the purchase on that date) so you have to store that with the invoice.
I hope that helps clear up some of the confusion.
Having said that, there ARE some computations that CAN be stored in a table FOR CONVENIENCE. Like, the total sale price of an invoice can be stored with the invoice even if it could be recomputed by querying the line items associated with the invoice. Because that won't change over time. I.e. the total sale price won't change if you change component prices after the fact. BUT this is generally a wasteful practise and can lead you down to a wilderness of confusion very quickly. Which is why some folks don't even store invoice prices with the invoice. Me, I would store it as a sanity check just to be sure that my line-item query is properly built so that when I compute the invoice total for my report, it has to match what I stored when I first entered the invoice. Otherwise my logic is wrong. The mere fact that it makes certain reports easier to write is, as we say in south Louisiana, just a little lagniappe (Cajun Fr. = added benefit).
.