Double bookings

[ICTkirsten]

Hello,

Im trying to follow this guide but i dont understand how to do some stuff which i will underline... also how do i subtract one from the other?

Although the idea of the query - to find available slots on a given day - sounds quite simple, it's not as easy as it first appears. This is because you're looking not just for bookings that don't exist, but bookings that don't exist on a particular day. My solution, therefore, uses two queries - one that finds the appointments that have been made for a particular doctor on a given day (straightforward enough), and then a second one that effectively subtracts these from the complete list of available slots (i.e. those in tbl_available), to give the available appointments for the chosen day




thank you

 

[plog]

Sounds like you don't want the answer but a hint, so here you go:

I'd do this in 3 queries:

A = all doctors appointments.
B = all possible appointments.
C = B - A

I think you said you had a handle on A. B is probably going to be a cartesian product (http://en.wikipedia.org/wiki/Cartesian_product) and C is going to be a LEFT JOIN from B to A (include all from B, only those in A that match).

B is the key to this. If you need more of a hint let me know.

 

[ICTkirsten]

Hiya sorry i do not understand any of that haha

please could you bare with me and make it clearer for me hehe

 

[ICTkirsten]

My solution, therefore, uses two queries - one that finds the appointments that have been made for a particular doctor on a given day

its mainly this bit that i dont get

 

[jdraw]

Perhaps you could show us some code or database or relationships window.
It's hard to visualize and deduce some options.
There is a sample here re Cartesian product to resolve bookings.

 

[plog]

Can you post your database?

 

[ICTkirsten]

i would but the issue is its my coursework so im not allowed.
im trying to follow this link
http://www.advanced-ict.info/databases/clash.html

under the heading discrete appointments

ive also tried this
http://www.datawright.com.au/access_resources/bookings_without_clashes.htm

 

[jdraw]

Take a look at this link suggested in post #5