keep track of free bays

[Guest]

Hi!

I'm making a database for a project at uni modelled on a boat company that
hires out and repairs boats. i'm almost near the end (thanks to the mvps who
helped me!). my last foreseeable problem is this.

the company has 5 boats and five locations. a repair can only be carried out
if a location is free. is there anyway i can make it automatic that if a boat
is hired out then a location becomes available and so a repair can be carried
out.

let me know if you need more info.

thanks

sf

 

[Connie Kamrowski via AccessMonster.com]

So do you want an option when you hire a boat to ask the user if they want
to allocate this bay to a boat needing repair?and then run an event on OK

or do you want to automate the process and then advise the user after the
process is started?

 

[John Vinson]

Hi!

I'm making a database for a project at uni modelled on a boat company that
hires out and repairs boats. i'm almost near the end (thanks to the mvps who
helped me!). my last foreseeable problem is this.

the company has 5 boats and five locations. a repair can only be carried out
if a location is free. is there anyway i can make it automatic that if a boat
is hired out then a location becomes available and so a repair can be carried
out.

let me know if you need more info.

thanks

sf

It would seem that you have a many-to-many relationship from boats to
locations (any boat may be in any of the five locations over time,
and any location may serve any boat over time... right?)

If so you need a Where table with the BoatID and LocationID.

It would then be simple to create an Unmatched Query wizard query to
find Locations which have no Boat.

John W. Vinson[MVP]

 

[Guest]

I want to automate the process. for example, if a boat is hired out on a
particular date then the location will be marked as available and then when a
boat needs repair, a lookup of available locations can be done to see whether
the repair can be booked.

Thanks

SF

 

[Guest]

John.

Your assumption is correct about the many to many.

At the moment, i have a table with the boat id and location code, but it
contains other fields as well. are you talking about creating a junction
table?

i think i get what you are saying about creating an unmatched query wizard
but how on earth would i get the availability of a location to change when a
boat is hired out? am i making sense?

Sf

 

[Guest]

some more info from my briefing sheet.

The boatyard has a single slipway, and moorings for up to five boats at any
one time. The available capacity imposes a constraint on the number of
repair/refit jobs which can be undertaken at any one time (a theoretical
maximum of two boats out of the water and a further five either being worked
on at their moorings if the repairs are minor or waiting to be taken out of
the water). This theoretical maximum assumes all MSE’s boats are out on hire
and therefore not needing to use the moorings. The system, therefore, will
have to keep track of which repair bays (on land) and/or moorings are
available at any given time.

 

[John Vinson]

John.

Your assumption is correct about the many to many.

At the moment, i have a table with the boat id and location code, but it
contains other fields as well. are you talking about creating a junction
table?

If you have a table with boat ID and location code, that IS a junction
table. Junction tables often have other fields! In this case, since
the many to many is over time, you would indeed need at least fields
for date in and date out.

i think i get what you are saying about creating an unmatched query wizard
but how on earth would i get the availability of a location to change when a
boat is hired out? am i making sense?

I'm sure you are - but in terms of your business, which I guess I
don't understand! What in your table structure indicates that a boat
is "hired out"? What (if any) is the relationship between a boat's
hired status and its location? If the boat is out on the water, what
would be in this location junction table (or would that even apply)?

John W. Vinson[MVP]