item daily location access database- 500 items, 100 locations ?

[Jack]

I'm trying to compose a datbase that will track about 500 pieces of equipment
daily across up to 100 locations and be able to assign weekly and monthly
costs to each location based on daily rates of hire for each piece of
equipment.

The only method that I can think of to track each piece of equipment daily
is a table per piece of equipment with a record for each day but this feels
too bulky and too long to set up and maintain. I'm sure there's a simpler way
of doing this but I just can't think around this corner.

Any help would be much appreciated-

 

[David Cox]

Why do you need a record for each day? Why not one record to cover a period
of hire with a start date and an end date? It would be possible to recover
day by day data with a query.

Do the hire rates vary by length of hire, or by day of the week, or by
location?

 

[Jack]

Hire rates do not vary upon day of the week, or locaton, and are updated
perhaps once a quarter.

If i had a record for period of hire I'm thinking i could query current
location against the record with equipment id and (end date="") so that makes
sense.

A 5 field table (record_id),(equipment_number), (location),
(start_date),(end_date) would also only require initialisation with current
location values then updates could easily be added; thus far everythings
looking good-

however-

there are two kinds of equipment -

Equipment type A which is measured on a daily basis -

And Equipment type B which is measured on a % of the month basis over
different locations, individual days not being specified. Input figures are a
monthly % split per equipment_number at the end of the month. Don't know how
to factor this into start date and end date other than to have another table
with (equipment_number),(location),(month),(%split) and query the two tables
seperately to come up with a report that combines location charges-


???

 

[David Cox]

there is just a small change ... ( :-<)

It now looks like 4 tables locations, equipment, cost_A records, cost_B
records.
The cost_A and cost_B records link equipment to location.

If I have it right there would usually only be one cost_B record per
equipment at location per month, and one cost_A record for each period of
equipment at a location.

It appears to me that one query should be sufficient to extract the costs
per location per month.

I hope that I have this right.