Multiple date restrictions

[DougS]

My database is used to store data for internal rejects, and to calculate the cost of each new entry I created a query, which takes the [weight] from the entry form, and multiplies that by the [film type](from a seperate tbl that holds all the different film types and their price per kilo). The result of the calculation is dispalyed on the entry form, and all this works fine. My problem is that at the start of each year the price per kilo of each [film type] changes, but I need to keep the integrity of the previous year's entries with theirr values according to that year's prices. What I did was to create a seperate table for the Film types for each year, with their seperate price per kilos, and a corresponding query to work out the value of each entry. Each query has date limits to restrict it to dealing with records for that year only. I want a query that will display all records and their proper values (£), but the query will not display any records at all because of the date restrictions put on to the queries.

In response to the answer, I cannot do it like that, because I need to have an individual costing for each record all the time for reports purposes. There is probably a better way to have done it all from the start, but I cannot think of it at all, Thanks.


[This message has been edited by DougS (edited 01-30-2002).]

 

[Rich]

It's easier to store the price per kilo per transaction and calculate the total price from that.
HTH

 

[KKilfoil]

You can store time-specific values without violating the sacred normalization rules.

You could write a bit of code in the Afterupdate event of your form to look up the proper price from your existing table, and STORE it in each Reject record.

OR

You could construct a query to show the [film type], [Price] and [PriceYear]{calculated} from your reference table.

Make another query to show the useful Reject data, with the [RejectYear] {calculated} from the [rejectdate] or whatever.

Make a third query that relates the two ...Year fields above, and use THAT one in your reports.

 

[KKilfoil]

Sorry, I just reread your post and realized I left something out.

I wouldn't use a separate table for each year for your prices. Use a single table including FilmType, UnitPrice, etc, but add a field like [PriceYear] to track the appropriate year.

NOW read my prior post.

 

[DougS]

A brilliant answer.

I have created in my original table for [film type] a seperate field for each years and the respective price per kilo, and deleted all thee other tables I created that are no longer in use. In my original calculation query I have deleted the date restriction, and created a new calculation for each year, pointing the query to the correct cloumn for price per kilo needed. Each calculation adds up all records, but that does not matter, because all of my reports are creaed from parameter queries, and therefore require a date range to work, so the value per record shown is from the calculation from that year, if you get my drift. I have also been able to delete about 20 queries that are now redundant.
a slight correction, the database is working a lot better, but how do I tell a report to display the results of the calculation depending on the dates selected in the parameter query, and what if I do a report that spans more than 1 year. Is it possible to do just 1 calculation in my query that uses some sort of IF statement depending on what date is entered on to the data entry form.
My film table is as thus
FilmID - links table to main table
Film Type - description of films
Value2001 - price per kilo for that year
Value2002
Value2003 etc.
Thanks.

Thanks very much, Dougs.


[This message has been edited by DougS (edited 01-31-2002).]

 

[KKilfoil]

I was actually suggesting a different structure.

Don't add a new field to each 'film type' for each year, or you'll have a hideous structure if this thing is still in use in 20 years!

Create a new RECORD for each film type for each year, with a single field called, say, 'CostYear'. Then, for a given reject, there should be a matching record for 'film type' and for 'year'. Your calculations, etc will be simpler, because the same field for the cost is always used.

If 'weight' varies by year, you can change it as well.

Parametric calculations should not be needed.

 

[DougS]

Probably my fault, but I don't think that I have explained my database properly
The user form has these fields
RecordId, Date, Week No.,Reject Note No., Weight, Customer, Width, Film Type, Department, General Fault and Specific Fault.
A seperate query works out the value of each individual entry (about 2,500 each year), by multiplying the [weight] by the [film type](which has price per kilo)
The individual price per kilo for each film type changes each year, but I need to keep a record of all previous entries with their correct individual values.
My reports run from parameter queries because different info is required at different times, and from different reports.
Thanks.