Access database Design

[boyleyc]

Hi

I am working on a Access database and have run into a design problem.

Bit of background - This is how a booking works.

A booking is taken from a client and a date/time begining of booking
and a date/time end of booking. A staff member is then sent to the
client. This is stored in a bookings table.

BookingID (Pkey)
clientID
StaffID
dateBegin
timebegin
dateend
timeend

my main problem is with charge rates.

If for example a booking is taken on a friday from 12 noon till 10am
the next day. Charge rates may be from 10am till 5pm at one rate then
increase for the night time and then reduce again at 7am in the
morning.

My other problem is this information needs to be stored incase the
charge rates change and I want to look back over previous bookings but
obviously want to see the price i charged not the price with the
updated charge rates.

Now I know how to calculate the bookings on the fly using a function /
queries etc. My problem is I dont know how to contruct my tables with
out storing masses of data

Help

Thanks in advance

Boyley

[Phil Stanton]

Suspect you want another table TblCharges

ChargeID Auto PK
BookingID (Fkey)
dateBegin
timebegin
dateend
timeend
ChargeRate

Then you can have as many logs on and off and as many rates as you like for
each booking.

You wont need the date and time information in your TblBookings

Input the info using Booking information (witthout the dates and times) on
the main form and the ons, offs and rates on a subform

HTH

Phil
<bo*****@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...

Hi

I am working on a Access database and have run into a design problem.

Bit of background - This is how a booking works.

A booking is taken from a client and a date/time begining of booking
and a date/time end of booking. A staff member is then sent to the
client. This is stored in a bookings table.

BookingID (Pkey)
clientID
StaffID
dateBegin
timebegin
dateend
timeend

my main problem is with charge rates.

If for example a booking is taken on a friday from 12 noon till 10am
the next day. Charge rates may be from 10am till 5pm at one rate then
increase for the night time and then reduce again at 7am in the
morning.

My other problem is this information needs to be stored incase the
charge rates change and I want to look back over previous bookings but
obviously want to see the price i charged not the price with the
updated charge rates.

Now I know how to calculate the bookings on the fly using a function /
queries etc. My problem is I dont know how to contruct my tables with
out storing masses of data

Help

Thanks in advance

Boyley

[boyleyc]

Hi Phil cheers for the reply.

The problem is that charge rates will probably be set for around 3
months at a time.

A booking will come in and startdate/Time finishdate/time be issued.

Staff entering this have no access to the charge rates etc. It needs to
be kept as simple as possible.

I was thinking that per each booking once confirmed, trigger a function
that calculates the charge to the client and payment to the staff
member, and store in the bookings form for later query. (ClientCharge,
StaffPayment)

However I know that no calculations are supposed to be stored in a
table and know that this is not the right way.

Cheres in advance

Chris

Phil Stanton wrote:

Suspect you want another table TblCharges

ChargeID Auto PK
BookingID (Fkey)
dateBegin
timebegin
dateend
timeend
ChargeRate

Then you can have as many logs on and off and as many rates as you like for
each booking.

You wont need the date and time information in your TblBookings

Input the info using Booking information (witthout the dates and times) on
the main form and the ons, offs and rates on a subform

HTH

Phil
<bo*****@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...

Hi

I am working on a Access database and have run into a design problem.

Bit of background - This is how a booking works.

A booking is taken from a client and a date/time begining of booking
and a date/time end of booking. A staff member is then sent to the
client. This is stored in a bookings table.

BookingID (Pkey)
clientID
StaffID
dateBegin
timebegin
dateend
timeend

my main problem is with charge rates.

If for example a booking is taken on a friday from 12 noon till 10am
the next day. Charge rates may be from 10am till 5pm at one rate then
increase for the night time and then reduce again at 7am in the
morning.

My other problem is this information needs to be stored incase the
charge rates change and I want to look back over previous bookings but
obviously want to see the price i charged not the price with the
updated charge rates.

Now I know how to calculate the bookings on the fly using a function /
queries etc. My problem is I dont know how to contruct my tables with
out storing masses of data

Help

Thanks in advance

Boyley

[Phil Stanton]

OK, Chris

Another Table

TblChargeRates

ChargeRatesID Auto
ChargeRate Currency
AppliesFrom Date
AppliesTo Date

Then on the subform you need to set the OnInsert event to something like

ChargeRate.DefaultValue = DLast("ChargeRate","ChargeRates")

This will put the latest charge rate in as the last rate found in the
ChargeRate Table, but can be overridden.

I,m off sailing now and won't be back till Tuesdat

Phil

<bo*****@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

Hi Phil cheers for the reply.

The problem is that charge rates will probably be set for around 3
months at a time.

A booking will come in and startdate/Time finishdate/time be issued.

Staff entering this have no access to the charge rates etc. It needs to
be kept as simple as possible.

I was thinking that per each booking once confirmed, trigger a function
that calculates the charge to the client and payment to the staff
member, and store in the bookings form for later query. (ClientCharge,
StaffPayment)

However I know that no calculations are supposed to be stored in a
table and know that this is not the right way.

Cheres in advance

Chris

Phil Stanton wrote:

>Suspect you want another table TblCharges

ChargeID Auto PK
BookingID (Fkey)
dateBegin
timebegin
dateend
timeend
ChargeRate

Then you can have as many logs on and off and as many rates as you like
for
each booking.

You wont need the date and time information in your TblBookings

Input the info using Booking information (witthout the dates and times)
on
the main form and the ons, offs and rates on a subform

HTH

Phil
<bo*****@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegr oups.com...

Hi

I am working on a Access database and have run into a design problem.

Bit of background - This is how a booking works.

A booking is taken from a client and a date/time begining of booking
and a date/time end of booking. A staff member is then sent to the
client. This is stored in a bookings table.

BookingID (Pkey)
clientID
StaffID
dateBegin
timebegin
dateend
timeend

my main problem is with charge rates.

If for example a booking is taken on a friday from 12 noon till 10am
the next day. Charge rates may be from 10am till 5pm at one rate then
increase for the night time and then reduce again at 7am in the
morning.

My other problem is this information needs to be stored incase the
charge rates change and I want to look back over previous bookings but
obviously want to see the price i charged not the price with the
updated charge rates.

Now I know how to calculate the bookings on the fly using a function /
queries etc. My problem is I dont know how to contruct my tables with
out storing masses of data

Help

Thanks in advance

Boyley