Results 1 to 5 of 5
  1. #1
    mikethebass is offline Novice
    Windows Vista Access 2000
    Join Date
    Jun 2010
    Location
    England
    Posts
    12

    Combinig three tables with a query

    Hello Folks,
    I'm running Access 2007 on Windows Vista.
    I have some experience with Access but it is some time since I needed to do something like this and I'd appreciate some assistance.

    I'm a musician and I want to put together a database to hold details of Venues, Bookings and Comments about Venues.

    I have three tables Venues, Bookings and Comments. All related by the field Club ID

    For any Venue there may be several Bookings and Several Comments. There may be Bookings and no comments or there may be no Bookings and several comments.

    What I's like to do eventually is have a form showing the Venue details and a couple of subforms one show any bookings one showing any comments.



    My initial problem is that I can create a query that shows the Venue and Bookings. However, in datsheet and presumably form view it shows the venue name for each booking.

    What I'd like to see is something like this

    Venue A

    Venue B Booking 1
    Booking 2
    Venue C

    Venue D

    Venue E Comment 1

    Thanks in advance for your help.

    MIke

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    A single query joining the 3 tables would not be useful on forms for data entry. I think what you are describing is a report output. However, for a report to show data from all three tables, a single query will not work properly because Venues has many related records in the other two tables. This calls for report/subreport arrangement.
    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
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might also check out these MS templates..... the forms are rather ugly, but might help on structure, queries reports..

    Access 2010> Desktop Event Management http://office.microsoft.com/en-us/te...i:TC001225345|

    Access 2003 > Event management database http://office.microsoft.com/en-us/te...i:TC001017829|

  4. #4
    mikethebass is offline Novice
    Windows Vista Access 2000
    Join Date
    Jun 2010
    Location
    England
    Posts
    12
    Thank you for the Report / Subreport suggestion.

    It works well for displaying the data. I yet have some cosmetic work to do on it.

    I'll be trying to add a couple of controls that will allow me to Add a booking or Add a comment when in the report.

    Thank you

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    You can have controls on report that will open form but they won't work in PrintPreview. And the new data won't display on the already open report.
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-05-2012, 12:04 PM
  2. 1 query 2 tables help?
    By mejia.j88 in forum Queries
    Replies: 4
    Last Post: 11-22-2011, 03:23 PM
  3. Trying this query from 2-Tables
    By djclntn in forum Queries
    Replies: 6
    Last Post: 11-13-2011, 09:25 AM
  4. Query on 3 tables
    By aveit in forum Access
    Replies: 0
    Last Post: 04-26-2011, 12:01 PM
  5. Query -2 tables. Help.
    By zuli007 in forum Queries
    Replies: 1
    Last Post: 04-15-2010, 10:02 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