Combinig three tables with a query

[mikethebass]

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

[June7]

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.

[ssanfu]

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|

[mikethebass]

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

[June7]

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.