Best way to do this?

[Singh400]

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]

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]

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]

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]

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]

Bookings should be treated like any othe process:

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

Simon

 

[Singh400]

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

 

[Balr14]

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]

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

 

[Simon_MT]

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]

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?

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]

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]

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.

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]

Absolutely, then combis.

Simon

 

[Singh400]

Absolutely, then combis.

Simon

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

 

[wazz]

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]

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]

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!

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]

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:

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

 

[Singh400]

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:

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.