Help please

[steph789]

I am pretty new to access and am making a database for my Alevel project. Its for a sports club that lend out equipment. I have a prices table which has the name of the equipment, equipment ID and price. Also I have a loans table and a equipment table. I have created an update query which updates the Cost of equipment field to the Cost of equipment values that are in the prices table. But I am really strugling on how to update the Cost of equipment values in the Loans table so the price of the equipment is automatically added. Anyone got any suggestions on how to do this? I am better to use an update query in the first place?

Thanks

 

[KjWhal]

I am pretty new to access and am making a database for my Alevel project. Its for a sports club that lend out equipment. I have a prices table which has the name of the equipment, equipment ID and price. Also I have a loans table and a equipment table. I have created an update query which updates the Cost of equipment field to the Cost of equipment values that are in the prices table. But I am really strugling on how to update the Cost of equipment values in the Loans table so the price of the equipment is automatically added. Anyone got any suggestions on how to do this? I am better to use an update query in the first place?

Thanks

You shouldn't have cost fields in each of these tables.

You should have

tableEquipment
equipmentID
equipmentName

tableCost
costID
equipmentID
cost

Not sure what your loans table is. Computed values should never be stored. Compute them at run time.

 

[pbaldy]

I would disagree with KjWhal in this instance. While you would generally not store a "looked-up" value from another table, prices are a little different. Because of their dynamic nature, if/when the price changes, you would get the wrong value for previous transactions if you got the price from the prices table later.

Steph, I'm not sure an update query is the correct solution, depending on what you're trying to accomplish. Generally, I'd have a form where the user was entering the transaction, and I'd get the current price from the prices table at that point.

Edit: I'd add that the price table is probably redundant, since you could simply add a price field to your equipment table.

 

[steph789]

Thanks for your help. Ill explain the my database in more detail. In the

Equipment table the fields are: Equipment ID, Name of Equipment, Size, Description, Colour, Condition and Cost of equipment (this is the cost to hire the equipment)

Loans table- Loan ID, Player ID, Equipment ID, Date borrowed, Return date, Return status, Cost of equipment (which needs to be the same as in the prices table) and Paid

Prices- Name of equipment, Equipment ID and Cost of equipment.

All the cost of equipments need to be the same as in the prices table.

A player borrows the piece of the equipment and they get charged the Cost of equipment price.

Im not very good with databases and have useless teachers at school so im probably going about it the wrong way and the final project has to be in less than 2 weeks.

 

[pbaldy]

I think the prices table is not necessary. Since you say the price in the equipment table needs to be the same, why have it in 2 places? This is a normalization issue, if you've covered that (which you should have, as it's fundamental to good database design). If you want to keep that table, I'd drop that field from the equipment table; you don't want to maintain the same piece of data in 2 places.

As an additional comment, even if that table were necessary, it would be redundant to store the name of the equipment in it. You would use the ID to get the name from the equipment table. This is also a normalization issue.

If your price never changed, then it would also be a normalization problem to store the price in the loans table. However, in the real world prices change over time, so it's appropriate to store the price for each particular transaction. Like I said, the way I would typically do that would be on the form where the user is entering the transaction.