Best way to do this? (2 Viewers)

Singh400 · Jan 29, 2009

Currently we only use the database to store information about bookings that have actually been booked. And anything that has yet to be booked remains on paper and in a folder.

Ideally we want to move to a paperless system, so as soon as we get the booking (via email/fax/telephone) it goes onto the system (where as now we take the booking and put it in the folder and book it nearer the time).

Problem I'm having is how do I implement something like this?

Do I mix the "ToBeBooked" data with the "AlreadyBooked" data? And put some sort of marker against the "ToBeBooked" data so it doesn't show up/intefere in other queries/reports that pull information from "AlreadyBooked" data.

Or do I create a new table and accompanying forms and queries for the "ToBeBooked" data but store it in the same database and then just use a macro to move the record over once booked?

FYI we work in the public sector providing a service to health professionals.

Thank you!

John Big Booty · Jan 29, 2009

I'd go with your first idea, and use an option group to up date it, that way you could have a number of options, for example "To be booked", "Booked", "Completed", "cancelled", for example.

The_Doc_Man · Jan 29, 2009

I'm with John here. The less data movement, the better off you are. Define a booking status "tentative" or whatever is the preferred term and then make that the default state for a "booking on paper" entry. Update it when it needs status updates.

ACMAIN_CHM · Jan 30, 2009

hi Singh400 ,

If the booked and to-be-booked has all the same column structure, it is recommended to keep them in the same table, and add a new column as status/flag to distinguish it.

best regards
ACMAIN

dkinley · Jan 30, 2009

Me, third here - all the same table because your status is the differentiator. For the status, I (not a formal recommendation - just based on what I know) would probably implement a combo box on the form since a room could have more than one status. In this manner it will be easy to group and count on the output side and run reports.

-dK

Simon_MT · Jan 30, 2009

Bookings should be treated like any othe process:

Provisional (tentative)
Active (Active)
Completed (Finalised)
Posted (Processed into Accounts)
History (Archived)

Simon

Singh400 · Feb 11, 2009

Looks like I'll be creating a new status field then. Cheers lads/ladettes.

Balr14 · Feb 11, 2009

At some point, you will need to archive old bookings off to another table/database; so make sure you have a date/timestamp of last activity or status change.

Singh400 · Feb 11, 2009

We create a new database every finanical year, so that shouldn't be a problem.

Simon_MT · Feb 12, 2009

I don't necessarily agree that you need to archive after each financial year but I fully understand why you do. You could consider Current Year ignoring anything archived. If a Client has previous bookings kepping them in one file needs that these old bookings can be reviewed against the Client. You could even produce a paper copy of the Booking.

Singh400 · Feb 12, 2009

Just been playing about with this. Trying an option group and dropping several check boxes in there. Just trying to enable multiple selection within the option group. Just can't figure out how

Driving me nutty, I swear I've done it before.

Any ideas?

Simon_MT said:
I don't necessarily agree that you need to archive after each financial year but I fully understand why you do. You could consider Current Year ignoring anything archived. If a Client has previous bookings kepping them in one file needs that these old bookings can be reviewed against the Client. You could even produce a paper copy of the Booking.

We have too much important information stored on paper, hence why I am pushing for it all be computerised.

Simon_MT · Feb 12, 2009

If you have Years and Periods (Months) Combiboxes on Year and depending upon your reporting requirements Start Period and End Period otherwise Start Date and End Date.

Options boxes may not be the solution.

Simon

Singh400 · Feb 13, 2009

Singh400 said:
Just been playing about with this. Trying an option group and dropping several check boxes in there. Just trying to enable multiple selection within the option group. Just can't figure out how Driving me nutty, I swear I've done it before.

Simon_MT said:
Options boxes may not be the solution.

Simon

I’m guessing by the unanimous silence, this can’t be done and I’m talking poppycock. So I guess I have to make a status field for each status and do something fancy with a sub form.

If anyone else has any other ideas, I’d be happy to hear them. Just list of status that can apply to any one booking, and these aren’t mutually exclusive (so all of them could apply to one booking)

PT DNA
SU DNA
INT DNA
INT Late
SU Late
PT Late
C
LC
OoH
WE

Blimey that’s quite a list, would it be easier just to create tblStatus and have them linked up in relationship manager with tblBookings.fldStatus?

Simon_MT · Feb 13, 2009

Absolutely, then combis.

Simon

Singh400 · Feb 13, 2009

Simon_MT said:
Absolutely, then combis.

Simon

Yeah I've already done it, not gone down the combi root though. Just sticking with option groups.

wazz · Feb 13, 2009

Singh400 said:
Just list of status that can apply to any one booking, and these aren’t mutually exclusive (so all of them could apply to one booking)

PT DNA

SU DNA

INT DNA

INT Late

SU Late

PT Late

C

LC

OoH

WE

Blimey that’s quite a list, would it be easier just to create tblStatus and have them linked up in relationship manager with tblBookings.fldStatus?

definitely create a new status table. if you can have more than one status applied at the same time you'll need a third, junction table.

Simon_MT · Feb 13, 2009

Each option within the group has a number, you have to identify the number and then:

if option = 1 then
DoCmd ...
elseif option = 2 then
DoCmd ...
etc
End if

Simon

Singh400 · Feb 13, 2009

wazz said:
definitely create a new status table. if you can have more than one status applied at the same time you'll need a third, junction table.

So what fields would my junction table contain? I already have tblBookings and tblStatus linked via fldBookingNo. Gah, who thought given a booking a status would be so complicated!

Simon_MT said:
Each option within the group has a number, you have to identify the number and then:

if option = 1 then
DoCmd ...
elseif option = 2 then
DoCmd ...
etc
End if

Simon

Blimey, then I would go crazy if I had to do that, thank god the option groups work fine as they are.

wazz · Feb 13, 2009

you just need BookingID and StatusID for the junction (plus optional description, and dates etc). the primary key should be both ID fields (two fields make up the PK) so duplicates are impossible:

Code:

tblBookingStatus
 
BookingID StatusID 
23           3
23           5
31           1
31           7
31           1     <-- duplicate; automatic error (which [I]is[/I] what you want).

Singh400 · Feb 16, 2009

wazz said:
you just need BookingID and StatusID for the junction (plus optional description, and dates etc). the primary key should be both ID fields (two fields make up the PK) so duplicates are impossible:

Code:

tblBookingStatus BookingID StatusID 23 3 23 5 31 1 31 7 31 1 <-- duplicate; automatic error (which [I]is[/I] what you want).

Ahhh I've seen what you've done there. Nice idea. Having a little trouble implementing this at this moment. Think I've been staring at it too long and I'm just stuck in the same line of thought!

Probably be back later today to pick your brains if I don't have any luck. Thanks for all the help guys.

Best way to do this? (2 Viewers)

Registered User.

AWF VIP

Immoderate Moderator

Nothing is Impossible

Access Hack by Choice

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread