CAN SOMEONE PLZ HELP ME IN ACCESS OFFLINE??
10 April 2002, 06:43 PM
#1
Scooby Regular
Thread Starter
Join Date: Jul 2001
Posts: 1,795
Likes: 0
Received 0 Likes
on
0 Posts
I would be grateful if someone can help me offline (E-Mail). I am working on my final year project. It is on Access 2000 and the project is based on a Video Shop. I am 90% finished, but I require help on two functions.
1- Limit the each customer with MAX of 3 videos
2- Cross check reserved item against rentals. This will prevent reserved videos being taken out.
If someone is willing to look at my database via e-mail I would be very grateful.
Vinesh
1- Limit the each customer with MAX of 3 videos
2- Cross check reserved item against rentals. This will prevent reserved videos being taken out.
If someone is willing to look at my database via e-mail I would be very grateful.
Vinesh
10 April 2002, 07:05 PM
#2
Scooby Regular
Join Date: Jun 2000
Posts: 3,783
Likes: 0
Received 0 Likes
on
0 Posts
I imagine you'd have:
customers table (cust_ref, name, address)
videos table (video_ref, title, num_copies)
rentals table (cust_ref, video_ref, return_date)
LIMIT EACH CUSTOMER
To limit each customer to 3 vids, cross-ref their "cust_ref" (customers table) against "cust_ref" (rentals table) and count how many occurences - if its less than 3, let them have it.
RESERVE VIDEO
To reserve a video, treat it like a rental, but put reserved as the return date (or some daft return date). Downside to this is titles can be reserved indefintely, or you could add another field to rentals table like reserved_? with YES/NO field type.
CHECK IF RESERVED
To check if video is reserved cross-ref "video_ref" (videos table) with "video_ref" (rentals table) and count how many occurences - if its less than "num_copies" (videos table)
I'm sure there's lots of ways of doing it - but as for Access / Visual Basic - I'm too rusty to remember, sorry!
customers table (cust_ref, name, address)
videos table (video_ref, title, num_copies)
rentals table (cust_ref, video_ref, return_date)
LIMIT EACH CUSTOMER
To limit each customer to 3 vids, cross-ref their "cust_ref" (customers table) against "cust_ref" (rentals table) and count how many occurences - if its less than 3, let them have it.
RESERVE VIDEO
To reserve a video, treat it like a rental, but put reserved as the return date (or some daft return date). Downside to this is titles can be reserved indefintely, or you could add another field to rentals table like reserved_? with YES/NO field type.
CHECK IF RESERVED
To check if video is reserved cross-ref "video_ref" (videos table) with "video_ref" (rentals table) and count how many occurences - if its less than "num_copies" (videos table)
I'm sure there's lots of ways of doing it - but as for Access / Visual Basic - I'm too rusty to remember, sorry!
10 April 2002, 11:04 PM
#6
Scooby Regular
Join Date: Jul 2000
Location: Islington
Posts: 2,145
Likes: 0
Received 0 Likes
on
0 Posts
to find the number of vids a customer has
select count(customer) from rentals_table where returned_date is null group by customer;
assuming that you have a rentals_table and you're operating a video is rented to a customer when there is an unended row against them on that table of course
select count(customer) from rentals_table where returned_date is null group by customer;
assuming that you have a rentals_table and you're operating a video is rented to a customer when there is an unended row against them on that table of course
Trending Topics
10 April 2002, 11:10 PM
#9
Scooby Regular
Join Date: Jul 2000
Location: Islington
Posts: 2,145
Likes: 0
Received 0 Likes
on
0 Posts
Is there a reserved table?
select count(a.vid_id), count(b.vid_id)
from rented_table a, reserved_table b
where a.vid_id = [Entered video's ID]
and a.vid_id = b.vid_id
and a.return_date is null
group by a.vid_id, b.vid_id;
assumes that an unended row present on a rented table means it's rented and a row present on the reserved table means reserved (to be deleted when it's rented or put back on the shelf)
add the two fields together and if they're = select count(vid_id) from videos_table where vid_id = [Entered video's ID] then there ain't any more to rent or reserve.
select count(a.vid_id), count(b.vid_id)
from rented_table a, reserved_table b
where a.vid_id = [Entered video's ID]
and a.vid_id = b.vid_id
and a.return_date is null
group by a.vid_id, b.vid_id;
assumes that an unended row present on a rented table means it's rented and a row present on the reserved table means reserved (to be deleted when it's rented or put back on the shelf)
add the two fields together and if they're = select count(vid_id) from videos_table where vid_id = [Entered video's ID] then there ain't any more to rent or reserve.
Thread
Thread Starter
Forum
Replies
Last Post