Results 1 to 13 of 13
  1. #1
    bellevue is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    61

    Counting Occupants on a Booking Form

    I have a database set up which holds the names of guests coming on holiday.



    I have a separate field in which I want to have the total number of occupants coming on holiday; i.e. if the occupants are Sarah, Andrew and Joyce, I want access to automatically work out that there are three people and put a "3" in the "occupancy field. I've attached a screen print to show what I mean.

    I've tried to write an argument using the iif function but it didn't work; can anyone help please ?
    Attached Thumbnails Attached Thumbnails Occupancy Issue.JPG  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is Guests a subform in Continuous view? Have a textbox in form footer with calc: = Count([Name]). Can set it not visible.

    Then have the Occupancy textbox refer to the subform textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bellevue is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    61
    Thanks for that.

    The forms ([Customer Database] and [Reservations]) are separate tables within the same database; so I guess the answer is one is not a subform of the other.

    I didn't understand "Have a textbox in form footer with calc: = Count([Name]). Can set it not visible.", I've never done anything in the footer before.

    Hope you can help.

    Thanks

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How are these "Guests" linked/associated/related to the "Booking"?

    If a Booking can involve 1 or more Guests/Occupants, then I suspect you have a Booking Table and a related table for Occupants, and these are linked on BookingID.
    If you don't use Form/subform as June7 says, then you could use something like (untested)

    Occupancy = DCount("Occupant","OccupantTable","BookingId = & Me.BookingID & ")"

    Perhaps you could show us your tables and relationships as a jpg.

  5. #5
    bellevue is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    61
    I think I understand what you're asking but apologies if I'm geting it wrong.

    The record in the Customer Database table and record in the Reservation Table are linked by a relationship between the Telephone number fields (a field present in both tables).

    I have a table called Reservations in which are fields for the occupants details (name, age and gender) per the attached image sent with my first post.

    Sorry to sound thick but I'm sure I'm not using form/subform.

    I'd love to show you the relationships as a jpeg but am not sure how to find what you want uploaded as an image.

    Thanks

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of your mdb? You can remove most of the data, I would just need enough records to illustrate the problem. Remove anything confidential.I'd take a look -- no promises on a solution - but I will look at it.

    Do you have Primary keys on your tables? I'd be reluctant to use phone numbers as my linkage -- not wrong, butI'd use autonumbers and let Access do its thing.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How can you NOT be using subforms on a form as complex as shown? There are multiple payments and multiple guests and multiple services. We need to see your relationships diagram or the project itself.

    I never used A2000 but must be a menu item somewhere to open the Relationships builder. If you had built relationships you would know where that is. If you have then open the Relationship builder and do a screen shot.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    bellevue is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    61
    I hope this is what you need.

    Thanks
    Attached Thumbnails Attached Thumbnails Relationships.JPG  

  9. #9
    bellevue is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    61
    Can anyone help please ?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Helps to expand the table boxes so names and fields can be seen.

    Looks like you are linking Reservations2... and Reservations2008 on names. Bad idea. What if there are two Joan Smith? Why these two tables and not one?

    That's all I can do. I need to analyse database.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    bellevue is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    61
    I've removed the relationship between Reservations 2 and Reservations 2008.

    I've expanded the table boxes so all fields are shown; I can't show all fields in Reservations 2 as there are so many and it won't fit on the screen but have shown the only relationship between Reservations 2 and Customer Database.

    I hope this helps.
    Attached Thumbnails Attached Thumbnails DatabaseWindow.JPG  

  12. #12
    bellevue is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    61
    I've sorted this myself now thanks to another forum.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Glad you got it sorted out. Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with making airport booking system
    By matthewjones108 in forum Access
    Replies: 3
    Last Post: 03-13-2012, 08:10 AM
  2. Appointment Booking Database
    By richie2837 in forum Access
    Replies: 1
    Last Post: 07-16-2011, 01:39 AM
  3. Automatic Price for Booking - Access 2007
    By doquan0 in forum Queries
    Replies: 1
    Last Post: 08-27-2010, 11:34 AM
  4. booking in form
    By yoma in forum Forms
    Replies: 1
    Last Post: 07-31-2009, 05:40 PM
  5. Make a booking availibility checker - please see pseudocode!
    By sirantonycartwright in forum Programming
    Replies: 0
    Last Post: 09-03-2007, 10:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums