Access 2003 Help

[thecoolguy]

Hi Guys,

I wonder could anyone help me?

I have a college assignment for an access project.

The database is about renting cars out to customers.

I have build the tables up and how they link to each other (1 To M), the bit I could do with some help on is one the user requirement is to show a list of dates free for a certain date.

So for example the user inputs [1/03/2009] till the [4/03/2009] and a list should appear allowing the user to see what is free and what is not.

To do this would this be a query??, and using a long SQL command?? to select the tables?

Also, when a user uses access form to input the data will the query stop people putting in a car that is allready being used by someone else? i.e to stop one car from being used by another customer in the same time frame.

I could really do with some help / advice on how to do this!

Each car has its own class brand (A to E) and, a hire table which shows the car reg number and the first day of hire and last day of hire. Also a car table which shows which user is using what car.

Please Help!

Cheers

If you need picture of the ER model of the table let me no... might show you what I mean. I am quite new to access as well.

[thecoolguy]

Anyone any ideas?, or have a clue what i'm on about??

[rendrag]

can you post a screenshot of your database diagram?

it seems like you would need a "reserved date" table, or something similar that you could run a query against, that for a certain car (or any car depending on the requirements of the project) which dates have been used up out of the total date list.

Edited March 29, 2008 by rendrag

[thecoolguy]

Sure, I cant add any tables to it.

What the requirement asks for is the following :-

1. Create a hire booking

When a customer contacts Havacar with a hire request the bookings team will check the hire records to check on the availability of a car in the required class on the required date.

If no car is available an alternative class of car will be offered or an alternative date. If the customer wishes to continue with the booking the details of the hire are recorded in a hire record.

If the customer is a new customer then the customer details are noted along with the hire record.

2. Update hire bookings

Customers may contact the company after making a booking, requesting changes to the booking or making a cancellation. If the customer requests a change to the original hire booking the hire records will be checked to ensure that the change can be accommodated, appropriate changes are then made to the hire record.

So, i'm puzzled on how i could do that. I assume it would work with a query and the query allows the user to select first day of hire and the last day of hire and shows a list of the dates in betwen as well (for example first date hire 1.03.09 to the 10.03.09) and it would find any cars with those dates as free.

However can the query link with a form to stop the same car from being hired or that date? - like a look up?

ER table below.