Daniel- Still not clear.
First, what's in the Session table? If a SessionId also defines a facility and date/time, then those fields are redundant in your Booking table. Do you want to ensure that a member hasn't double-booked into a session? Can a "session" have more than one member booked? Can a "facility" handle more than one session? John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ P.S. How are you posting replies to this thread? If I try to send a normal reply, the return address is [EMAIL PROTECTED], which Yahoo bounces as invalid. When I reply to any other post in the group, it returns to [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Daniel Sent: Wednesday, December 14, 2005 9:17 PM To: John Viescas Subject: Re: Form help request John, any member can have a booking for the same date, time, facility and session. i want to make it so that there can only be one booking for a certin facility at a certin time on any given date. this is due tomorrow, any help you can give me would be really appreciated. Im up all night. Daniel. --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Daniel- > > And what constitutes a "duplicate" booking? Same member at the same > facility at the same date and time? Same member in the same session? > > John Viescas, author > "Building Microsoft Access Applications" > "Microsoft Office Access 2003 Inside Out" > "Running Microsoft Access 2000" > "SQL Queries for Mere Mortals" > http://www.viescas.com/ > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of > Daniel > Sent: Wednesday, December 14, 2005 6:54 PM > To: John Viescas > Subject: Re: Form help request > > Bookings table - BookingId (indexed no duplicates), Memberid, > FacilityId, SessionNo, SessionDate, BookingDate, Cost > > BookingId is primary key > > MemberId, FacilityId and SessionNo are foriegn keys > > Data Types BookingId - Autonumber > MemberId - Text > FacilityId - Text > SessionNo - Number > SessionDate - Date/Time > BookingDate - Date/Time > Cost - Currency > > Thanks > > Daniel. > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > Daniel- > > > > Well, you would write code in the BeforeUpdate event of the form > for > > entering new bookings to do a lookup on other bookings and make > sure there's > > not a duplicate. But as I indicated earlier, you might be able to > > accomplish the same thing with a Unique key. I would need to know > the > > structure of your "bookings" table to tell for sure. > > > > John Viescas, author > > "Building Microsoft Access Applications" > > "Microsoft Office Access 2003 Inside Out" > > "Running Microsoft Access 2000" > > "SQL Queries for Mere Mortals" > > http://www.viescas.com/ > > > > P.S. Had to post back to the group - your email is being returned > as invalid > > from Yahoo > > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of > > Daniel > > Sent: Wednesday, December 14, 2005 5:32 PM > > To: John Viescas > > Subject: Re: Form help request > > > > Hi John, everything is working now in my database except the > double > > bookings problem, i.e. i can book the same court for the same time > > and date. I was told this has to be dealt with in the VB code. do > > you have any ideas? > > > > Thanks > > > > Daniel. > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > wrote: > > > > > > Well, temporarily remove the relationships, define your indexes, > > then put > > > the relationships back. But I'm having a hard time envisioning > > how adding a > > > multiple-field unique index has anything to do with existing > > relationships. > > > > > > John Viescas, author > > > "Building Microsoft Access Applications" > > > "Microsoft Office Access 2003 Inside Out" > > > "Running Microsoft Access 2000" > > > "SQL Queries for Mere Mortals" > > > http://www.viescas.com/ > > > > > > > > > -----Original Message----- > > > From: [email protected] > [mailto:[EMAIL PROTECTED] > > On Behalf > > > Of Daniel > > > Sent: Tuesday, December 13, 2005 7:03 PM > > > To: [email protected] > > > Subject: [ms_access] Re: Form help request > > > > > > Hi, i cannot change the indexing as this is not allowed due to > the > > > relationships of the tables. > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > > wrote: > > > > > > > > Daniel- > > > > > > > > I'd need to know more about the structure of your table, but > > > perhaps you can > > > > define a Unique index on the relevant fields to prevent the > > > duplicates. > > > > > > > > John Viescas, author > > > > "Building Microsoft Access Applications" > > > > "Microsoft Office Access 2003 Inside Out" > > > > "Running Microsoft Access 2000" > > > > "SQL Queries for Mere Mortals" > > > > http://www.viescas.com/ > > > > > > > > > > > > -----Original Message----- > > > > From: [email protected] > > [mailto:[EMAIL PROTECTED] > > > On Behalf > > > > Of Daniel > > > > Sent: Tuesday, December 13, 2005 6:48 PM > > > > To: [email protected] > > > > Subject: [ms_access] Re: Form help request > > > > > > > > Thanks for the help last night John. I got everything working, > > now > > > > im having problems with the booking form making double > bookings > > > i.e. > > > > i have a court which i can book for a session time and date ( > > this > > > > can be double booked). any ideas? > > > > > > > > Thanks > > > > > > > > Daniel. > > > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > > > wrote: > > > > > > > > > > Daniel- > > > > > > > > > > Why a new query? What's the SQL of your new query? It > sounds > > > > like what you > > > > > constructed is no longer updatable - hence the beeps. Can > you > > > > open the > > > > > query and update records? > > > > > > > > > > John Viescas, author > > > > > "Building Microsoft Access Applications" > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > "Running Microsoft Access 2000" > > > > > "SQL Queries for Mere Mortals" > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > -----Original Message----- > > > > > From: [email protected] > > > [mailto:[EMAIL PROTECTED] > > > > On Behalf > > > > > Of Daniel > > > > > Sent: Monday, December 12, 2005 8:39 PM > > > > > To: [email protected] > > > > > Subject: [ms_access] Re: Form help request > > > > > > > > > > Hey John, done what you said and its still not working. Made > a > > > new > > > > > qry from scratch and tried a brand new frm using that qry. > Now > > i > > > > get > > > > > a list of names and i cant select any of them, just get the > > > > windows > > > > > error beep in my ear when i click the options. > > > > > > > > > > Daniel. > > > > > > > > > > --- In [email protected], "John Viescas" > <[EMAIL PROTECTED]> > > > > wrote: > > > > > > > > > > > > Daniel- > > > > > > > > > > > > You need to include Booking.MemberID and > Booking.FacilityID > > in > > > > > your query. > > > > > > For members, set Booking.MemberID as the Control Source of > > > your > > > > > combo box > > > > > > (that's the Foreign Key), and as soon as you pick a value > in > > > the > > > > > combo, > > > > > > Access should autolookup the member's address info. Ditto > > > > > FacilityID. > > > > > > > > > > > > John Viescas, author > > > > > > "Building Microsoft Access Applications" > > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > > "Running Microsoft Access 2000" > > > > > > "SQL Queries for Mere Mortals" > > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > From: [email protected] > > > > [mailto:[EMAIL PROTECTED] > > > > > On Behalf > > > > > > Of Daniel > > > > > > Sent: Monday, December 12, 2005 7:29 PM > > > > > > To: [email protected] > > > > > > Subject: [ms_access] Re: Form help request > > > > > > > > > > > > Ok, Here is the layout > > > > > > > > > > > > > > > > > > Member: MemberID, Name, Address1, Address2, > PostCode, > > > > > > DateofBirth, CategoryCode > > > > > > > > > > > > Facility: FacilityID, FacDescription, Price (per session) > > > > > > > > > > > > Session: SessionNo, TimeRange > > > > > > > > > > > > Category: CategoryCode, CatDescription, AnnualCost, > Discount > > > > > > > > > > > > Booking: BookingId, MemberID, FacilityID, SessionNo, > > > > > SessionDate, > > > > > > BookingDate, Cost > > > > > > > > > > > > > > > > > > **SQL** > > > > > > > > > > > > > > > > > > SELECT Member.Name, Member.Address1, Member.Address2, > > > > > > Member.PostCode, Member.DateOfBirth, > > Category.CatDescription, > > > > > > Facility.FacDescription, Session.SessionNo, > > Session.TimeRange, > > > > > > Booking.SessionDate, Booking.BookingDate, Facility.Price, > > > > > > Category.Discount, Booking.Cost > > > > > > FROM [Session] INNER JOIN ((Category INNER JOIN Member ON > > > > > > Category.CategoryCode = Member.CategoryCode) INNER JOIN > > > > (Facility > > > > > > INNER JOIN Booking ON Facility.FacilityId = > > > Booking.FacilityId) > > > > ON > > > > > > Member.MemberId = Booking.MemberId) ON Session.SessionNo = > > > > > > Booking.SessionNo; > > > > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > Daniel. > > > > > > > > > > > > > > > > > > > > > > > > --- In [email protected], "John Viescas" > > <[EMAIL PROTECTED]> > > > > > wrote: > > > > > > > > > > > > > > Daniel- > > > > > > > > > > > > > > What's the structure of your tables? And what is the > > > current > > > > > > Record Source > > > > > > > of the form (the SQL)? And please include the previous > > text > > > > > from > > > > > > the thread > > > > > > > in your replies. If I hadn't just answered your > question, > > I > > > > > > wouldn't have a > > > > > > > clue what you're talking about. > > > > > > > > > > > > > > John Viescas, author > > > > > > > "Building Microsoft Access Applications" > > > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > > > "Running Microsoft Access 2000" > > > > > > > "SQL Queries for Mere Mortals" > > > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > > From: [email protected] > > > > > [mailto:[EMAIL PROTECTED] > > > > > > On Behalf > > > > > > > Of Daniel > > > > > > > Sent: Monday, December 12, 2005 7:10 PM > > > > > > > To: [email protected] > > > > > > > Subject: [ms_access] Re: Form help request > > > > > > > > > > > > > > Hi John, im using a multi table query for the frm and i > > have > > > > the > > > > > > combo > > > > > > > box set for Name from Members tbl for risk of sounding > > > stupid > > > > > > (where > > > > > > > do i set the foreign key)? > > > > > > > > > > > > > > p.s. i just bought your book on Sunday "Inside Out 2003" > > > > > > > > > > > > > > Daniel. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/KIlPFB/vlQLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
