SQL Query Help

[FJBoy]

i'm creating a project in vb.net with MS Access database, I've got a form to return a cd by just entering the cds id.

What i want it to do is add a fine to the user if the movie is late and then add it. So i know i need to times the price by dayslate then add it to. I have 4 tables and these are

Rent table contains:

cd_id, Member_id, date rented, datedueback, returnedDate, dayslate

cd table:

cd_id, title, price_band

price band:

price_band, price, daysRent

Member:

Member_id, name, fines, noof_cds_rented

what would the SQL be to do this?

 

[FJBoy]

Change of plan guys was making it to complicated for myself. What i'm going to do is have two SQL querys.

What i need to do is just get the the price of the cd being returned. For this i will need to look up the priceband of the cd in the cd table, then get the price of the priceband table. Then to work out the fine i will just create an int with the sum of # of dayslate by the price

Then what i plan to do is have an UPDATE sql that takes the member_id from the previous sql dataset i created and add the int i created to that member.

Thanks for any help in advanced guys

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886