Hi, please forgive my ignorance as my knowledge is very limited on Access. Basically Im creating a relational database using Access... I have 3 tables, the tables below are not exact but should give you enough information as the fields and tables that are required to create the query are below.
Holiday on date
Holiday off date
Basically i want to run a query to find all guard who are available for a booking who are not on holiday and who are not already booked - guards that would be available should hopefully be available 7 days before any booking dates. Im sorry, I know this isnt very technical but i really would appreciate any help. You may message me on MSN messenger from this addy firstname.lastname@example.org Thankyou in advance for any help.
I think you are perhaps struggling with the some of the basic theory concepts behind realtional theory. You can find a usefull article here which may help.
It is possible to write a query which can identify which guards are not on holiday for specific jobs. But I don't think your data model is there yet to usefully wriute that query.
Its difficult to understand precisely what your model is trying to represent. I can see no way of how you identify that a guard is booked out, and to which job. Offhand I would expect another table which links 2 tables so you can see which guard(s) is booked out to which job(s). I'm also doubtfull that your model accurately represents a holiday (effectively your model only allows for one holiday per employee - it may make more sense to have a sub table containing details of each guards holidays. An advantage of this design is that you could theoretically work out what holidays a guard had taken or was owed in the future (assuming that you know how many holidays a person is entitled to this year)
Just as an aside - can I suggets you remove the spaces in you variable names Having spaces works ok in Access / JET but it can cause problems in writing queries and accessing eleemnts in forms. It will however cause problems if you use another SQL engine.
I'd also suugest using a naming covnention to reduce the length of your column names. what does Adx represent (I'm guessing Address) - maybe Addr or Addressx is perhaps more appropriate - on first reading I interpreted it as Advertx. Holiday on date & Holiday Off date could perhaps be renamed HolsStart & HolFinish
Thanks for your help creating a new table and adding holiday entitlement seems like a good idea as i need to include an update query. The adx prefix was just an example - sorry for my bad description the field name is actually Address 1, Address 2 etc, in theory if I had a subform in the Bookings form could I base it on the query which sees which guards are available? and obviously the holiday entitlement could be seen on the Guard form. Thankyou for your help, you've really helped - I considered making a new table but I kept trying to put it off because I've already made a log of how I implemented my system.
if you are making a log, then I don't think what you've done so far is wasted - after all you are demonstrating how you got to your final design - Some real propellor heads on this forum may well get to a final design in the first iteration, but they are few and far between, most of us in the trenches take several iterations to get to the final design. In many ways its better to go down a few blind alleys so that you can understand the process of coming to a normalised db design. After all it demonstrates your understanding, rather than just grabbing a design from someone else and passing it off as yours.
Can I restate - the article on Rudy (r937's site) is a really good introduction into db design - well worth a read.
I think you are potentailly still missing a table which identifies which guard(s) are assigned to which job(s). Bear in mind that a single job could have multiple guards, and a single guard may cover multiple jobs (eg if its a mobile patrol). In anyevetn you need soem way of identfying job assignments - itherwise you intiial request will fail. At present your model allows you to identify which guards are / are not on holiday - it doesn't identify which guards are currently assigned, and to which job(s)