Car park database

[BSJoJaMAx4]

Hi all



I am trying to build a database to manage staff car parking. Im trying to figure out if the following is possible and how it would be done:

1. Am I best to build a database which shows all the available car parks then assign staff to those spots? Im figuring this would be the best approach as I have 90 spaces in 3 locations and I want to be able to run vacancy reports, check for double ups etc and I figure this is the right way to go about it but Im not sure if it will work and how I would do it.
2. Every year the car park rate goes up and at different times depending on the location. How would I be able to manage this without going into each record individually. I also want to record the previous rental cost for historical purposes.
3. At the end of the year I need to run a FBT report for all spots used during that period. I have a start date and end date field but start date may be from a previous time (ie start date 1/1/14 but the report is from 1/7/14 to 30/6/15).

Or am I best just to continue using excel?

[June7]

Access can do this.

If you want historical info, best to use Access.

For historical info, must save record for each parking space assignment then options for the rate:

1. save the rate with each assignment record - will probably require some code to accomplish

2. save only the RateID from Rates table and create a new record in Rates table when the rate changes and flag the old rate record as 'inactive'

If you need a better understanding of relational database principles, Access functionality, programming concepts, go through some tutorials. Start with http://www.rogersaccesslibrary.com/

[BSJoJaMAx4]

Thanks June but not quite sure what I need to do (I understand but not sure how to implement). Can I work through the things that I need to do with you?

If I can, I would like to start with the first thing. How can I indicate when a bay becomes free or is used? From what I gather of access, should I build a table with 3 fields (carparklocation, carparknumber and carparkrate) then in the form select the bay based on the car park location previosuly selected?

[JeroenMioch]

I would make a different table for each location. And like June said a different table for the rates.
Also when you assign staff to each number you might wanna store the vehicle's owner and licenceplatenumers in a seperate table.

The carpark space you assign a rate to, and then a vehicle.
In the rate table you can add a datefield to identity what rate was current at the specified date.

This setup requires some SQL instructions to make it work because you have multiple tables. Like June said some SQL and or VBA coding will be required.

[June7]

Jeroen, I would not recommend a different table for each location. What if they acquire other locations? Really should be another field in table to identify location.

[BSJoJaMAx4]

Thanks Jeroen - how do I assign the person to the spot? I already capture name, rego etc in a table?

[JeroenMioch]

Are you gonna assign licenceplate numbers or names to empty spots or would "Occupied" or "Vacant" notification suffice ?

[BSJoJaMAx4]

I would love to assign names but not sure if thats possible as some spots are shared during the week by people working part time etc so I guess vacant / occupied would need to do it unless you know of a way to allocate multiple names to a bay