Daniel- Then add FacilityID to your Unique key. Or create another Unique key on FacilityID, SessionNo, and Date. Think logically about what cannot be duplicate, then ask Access to restrict it for you.
Not too bad for 5 days! <s> You in the U.K.? I'm in Texas right now and about to retire for the night. 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 10:34 PM To: John Viescas Subject: Re: Form help request John, i've only been using access for 5 days :) (try not to laugh at me too much, plus its 4:30am here). Ok now that worked i cannot make a duplicate booking for a facility with the same member, however i can double book a facility with another member using the same time date and session number. any ideas? Daniel. --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Well, duh. You have duplicate bookings! You can find the duplicates by > running a Totals query on the duplicate fields and finding the ones where > Count(*) > 1. > > Or, you can copy your table structure to a new table, define the unique key, > and then append the records from the old tables and ignore errors. > > 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 10:02 PM > To: John Viescas > Subject: Re: Form help request > > John, Im getting this error message now. > > " The changes your requested to the table were not sucessful because > they would create duplicate values in the index, primary key, or > relationship. Change the data in the field or fields that contain > duplicate data, remove the index, or redefine the index to permit > duplicate entries and try again". > > Daniel. > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > Daniel- > > > > Then I would think a Unique index on memberId, SessionNo, and > SessionDate > > would avoid the duplicates. You'll need to define that in the > Indexes > > window. Open the table in Design view and choose Indexes from the > View > > menu. Enter an Index Name such as DupBooking and choose MemberId > as the > > first field. In Index Properties in the lower part of the window, > set > > Unique to yes. On the next line down, select the SessionNo field, > and on > > the line below that, the Session Date. > > > > 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 9:43 PM > > To: John Viescas > > Subject: Re: Form help request > > > > Session Table: SessionNo (number) also (foreign key for bookings > > table) TimeRange (text). > > > > Can a "session" have more than one member booked? - yes (dont want > > this to happen though unless the time and date are different). > > > > Can a "facility" handle more than one session - yes (i want this) > > > > Thanks > > > > Daniel. > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > wrote: > > > > > > 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 --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/2jUsvC/tzNLAA/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/
