Checkboxes and calculations

[Bob]

Hi there,

I was just wondering if anyone could help me. I have designed a database
for a conference. When someone books to attend the event they use
checkboxes to choose which days they are going to be in attendance. They
have the option of "All Days", "Day 1","Day 2","Day 3" and some other
things such as accommodation and dinner. What I was wondering was, how can
I assign a monetary value to each checkbox so that I can produce a report
with the value they are due to pay?

Many Thanks
Bob

 

[Tim Ferguson]

They
have the option of "All Days", "Day 1","Day 2","Day 3" and some other
things such as accommodation and dinner. What I was wondering was, how
can I assign a monetary value to each checkbox so that I can produce a
report with the value they are due to pay?

Depends on how you have defined the tables. If these fields are next to
each other as columns in one table, you have some major work ahead of you.
On the other hand, if you have done it right, with a tall thin table with
one record each for "Day1", "Day2" and so on, then it is very easy to
create a new table with Prices and join it to that one.

Post back if you need more info.


Tim F

 

[Bob]

Depends on how you have defined the tables. If these fields are next
to each other as columns in one table, you have some major work ahead
of you. On the other hand, if you have done it right, with a tall thin
table with one record each for "Day1", "Day2" and so on, then it is
very easy to create a new table with Prices and join it to that one.

Post back if you need more info.


Tim F

At the moment all the day boxes are fields in one table. Any chance you
could post how it is done for both methods.

Cheers
Jon

 

[Tim Ferguson]

At the moment all the day boxes are fields in one table. Any chance you
could post how it is done for both methods.

Well, you've got the bad one already, by the sound of it:

Bookings(
BookingNumber Autonumber Primary Key,
NameAndAddress Text [actually in different fields, etc.],
DayOne Boolean,
DayTwo Boolean,
DayThree Boolean,
etc...)


but the better way would be to do use a relational schema, something like:

Attendees(
AttNumber Autonumber Primary Key,
NameAndAddress Text [etc as above],
etc...)

Bookings(
AttNumber Long Int FK References Attendees,
DayNumber Int FK References DaysOfConference,
BookedOn Date

Primary Key (AttNumber, DayNumber)
)

Tbe bookings table consists of one row for each day booked for each
candidate. Remember a candidate may book one day and then ring up later
having found the funding for day two as well. It is easy to see who has
booked for each day (WHERE DayNumber=2) or which days a candidate has
booked (WHERE AttNumber=1093). You can link this table in turn to a
DaysOfConference table like this:

DaysOfConference(
DayNumber Int Primary Key,
Title Text,
Cost Currency,
ProvisProgramme Memo
)

and then you can add up the costs etc. by doing the join. Don't forget to
keep track of partial payments:

Payments(
AttNumber Long Int FK References Attendees,
DateRcvd Date,
Amount Currency,
ValidatedBy Text [who okayed the cc number?]

Primary Key (AttNumber, DateRcvd)
)


and so on.

As for the GUI, you'll need one form for managing the attendees, and
perhaps use a subform to add the days booked. You'll need a second form for
the finance people to (a) select an existing attendee and (b) log the
receipt of a cheque or credit card, etc. Remember the golden rule: one form
per process: i.e. one process = taking the booking; another process =
getting the money. If you need to book rooms, hotel accommodation and
transport, that might be another; and providing attendee information for
contributors; then there's printing lapel badges (you'd prolly use a report
for that) and so on.

Hope that's enough for you to get started. With best wishes


Tim F