[Jman08]

[font=Trebuchet Ms]Hi all, I could do with bit of help / advice.

I have an assignment on access.

I have to build a database about a car shop which allows customer to rent a car for X amount of days.

I’ve built all the tables and made the relationships.

The bit I could do with some advice for is I’m trying to make a query that will show all the available cars which I can do but the bit I’m not sure on is how to stop someone say using car 1 for the 2/2/07 and then another user books car 1 for the same date. - Would an If statement work? Like iff(date=2/2/07)??


Also, all the cars are in classes – A to E would there be a way to offer a user another class to use such as if all class A was used up for that date to allow them to look at Class B rather – would a combo box link work???

On and fianly is there any way to get the search results to appear on a form? I've seen on example but I had no idea how they did it. - I think its something to do with sub forms??


Cheers for any help. I'm not looking for you guys do to do everything. Its just really getting me started on how solve that problem! [font]

This post has been edited by Jman08: 27 March 2008 - 02:59 AM

[baavgai]

You'd presumably have something like:

Car(CarId, Name, CarClass, ...)
CarRental(CarId, Day, CustomerId, ...)

To check cars against days, you'd do something like:

select *
	from Car c
		inner join CarRental r on r.CarId=c.CarId

To find if a car was free on a given day, you'd do something like:

select c.*
	from Car c
		left outer join CarRental r 
			on r.CarId=c.CarId
				and Day=#02/02/2007#
	where r.CarId is null

For the second question, you want a summary of available car types for a given day:

select c.CarClass, count(c.CarId) as Available
	from Car c
		left outer join CarRental r 
			on r.CarId=c.CarId
				and Day=#02/02/2007#
	where r.CarId is null
	group by c.CarClass

Note, a span of days get's more complex.

As to the GUI of access, I'm afraid I can't help. If you're doing anything beyond a simple form, you're honestly better off using a real programming language for a front end. Access VBA is trully unfriendly and causes hair loss.

Hope this helps.

[Jman08]

Hey thanks for the help

I'm stuck to access , I just thought a query would be the easy way of doing what I wanted - I'm trying keep as simple as possible!

I let you know how I get on

One way I just thought of tho is a function to compare what the user date enters with the hire table with the correct car! .

[baavgai]

Jman08, on 27 Mar, 2008 - 01:57 PM, said:

I'm stuck to access , I just thought a query would be the easy way of doing what I wanted - I'm trying keep as simple as possible!
One way I just thought of tho is a function to compare what the user date enters with the hire table with the correct car! .

The table examples offered are as simple as possible.

I know Access writes all the scary SQL for you, and the SQL it writes is plenty scary. However, if you understand how to write queries, and the syntax that goes into them, you'll have a much easier time in Access or any other environment that uses SQL.

As a general rule, make the database do as much work as it can, avoiding writing functional code whenever possible. Databases are dumb, they have a limited number of things they can do and a limited amount of ways to express their data. However, they're really good at what they can do, so you should always try to make them do the work.

Good luck.

[Jman08]

Hey thanks , i'm sort of getting there now lol , its just coding SQL in the query but working out how all 3 tables will link with each other =p