Re: Designing a Booking System
From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 11/19/04
- Next message: Thang Long: "Replication error"
- Previous message: Duane Hookom: "Re: link question"
- In reply to: Olly Bowman: "Re: Designing a Booking System"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 19 Nov 2004 10:44:54 +0200
Olly,
What are the numbers in the matrix? Client_ID's? Anyway, yes, it is
possible. What you need is a crosstab query. Start by making a simple select
query with all the required fields (Instr_ID, Client_ID, BookingDate,
DayPart) and then go Quriy > Crosstab to change it to a crosstab query. In
the Crosstab line in the grid, set BookingDate and DayPart to Row Heading,
Instr_ID to Column Heading and Client_ID to Value. The Total setting should
be Group By for all but the Value column, which should be set to
First/Last/Min/Max/Sum depending on what you want (if there's only one row
for each day/part/instructor combination in the original table, like it
should be, then it doesn't make any difference which one you use).
The tricky part is to force it to display all possible dates/dayparts in a
range. As it is, if no record exists for a date/part it will not be
displayed at all. One way around it that I can think of is to have a
separate table with all the dates/parts in the period in question and
combine that with the bookings table in the query with a right join so that
it returns all records from the auxiliary table, and the matching records
from the bookings table. If your application involves a form on which the
user selects a date range to display, then you could use some simple VB code
behind the "Show Bookings" button to clear and populate the auxiliary table
for the required date range.
Assuming the table is called tblAux, with fields fDate and fPart (make the
table manually), the form is called MyForm, the two text boxes for the date
range are called txtFrom and txtTo, and the command button is called
Command0, your code would look something like:
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
strSQL = "DELETE * FROM tblAux"
Set db = CurrentDb
db.Execute strSQL
dfm = Forms![MyForm]![txtFrom]
dto = Forms![MyForm]![txtTo]
Set rst = db.OpenRecordset("tblAux")
vdat = dfm
Do Until vdat > dto
rst.AddNew
rst.Fields(0) = vdat
rst.Fields(1) = "AM"
rst.Update
rst.AddNew
rst.Fields(0) = vdat
rst.Fields(1) = "PM"
rst.Update
vdat = vdat + 1
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
DoCmd.OpenQuery "QueryName", acViewNormal
End Sub
Just change the names to the actual ones and you're there.
HTH,
Nikos
"Olly Bowman" <OllyBowman@discussions.microsoft.com> wrote in message
news:4000B981-F53E-44BB-8F1F-736A3EA0BA18@microsoft.com...
> Thanks, that helps.
>
> I've been trying to take the information in this table and produce a query
> that contains all possible dates, day part and a field for each instructor
> containing the relevant booking reference.
>
> My aim is to then create a from from this query looking a bit like this.
>
> Date Part Ins1 Ins2 Ins3 Ins4
Ins4
> Ins5
> 1/1/2005 am 0 0 2 0
4
> 0
> 1/1/2005 pm 0 3 2 0
1
> 0
> 1/1/2005 am 5 0 0 0
0
> 0
>
> etc.
>
> Is this possible, and i can't seem to work out how to do it?
>
> Thanks again.
>
> Olly Bowman
>
>
> "Nikos Yannacopoulos" wrote:
>
> > Olly,
> >
> > Separate tables for similar data are a recipe for disaster. You need ONE
> > booking table, with fields (at least):
> >
> > BookingID (autonumber)
> > Instr_ID
> > Client_ID
> > BookingDate
> > DayPart
> >
> > Note: you could work with either two or three possible values in
DayPart, AM
> > / PM or AM / PM / WD (for whole day). In the former case, a whole day
> > booking would create two records. Personally, I would opt for this
approach.
> >
> > If you make a composite primary key including fields Instr_ID,
BookingDate
> > and DayPart you will avoid double bookings without the need for code to
do
> > it for you (only works with the first option above, i.e. two records for
> > whole day).
> >
> > HTH,
> > Nikos
> >
> >
> >
> > "Olly Bowman" <OllyBowman@discussions.microsoft.com> wrote in message
> > news:896EBAFF-9155-4378-8CFF-4239925234F0@microsoft.com...
> > > Hi, i'm trying to create a booking system for a ski school.
> > > There are about ten instructors, each of which can be booked for
either a
> > > morning, afternoon or whole day, on any day of the week. This means
that
> > at
> > > most there could be 4000 different bookings.
> > >
> > > I already have a table for instructor information and a table for
client
> > > information, but am unsure how to store the dates for each booking.
> > Should I
> > > have a table for each instructor with a field for every date?
> > >
> > > Hope someone can help.
> >
> >
> >
- Next message: Thang Long: "Replication error"
- Previous message: Duane Hookom: "Re: link question"
- In reply to: Olly Bowman: "Re: Designing a Booking System"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
