query from 2 tables list of items just once

[sheepchild]

i have this code below which pull the data from tbl_bookings for 1 event. each event has a room(s) allocated to it stored in tbl_roombookings. But they can have more than one room allocated say room 1 2 and 3

currenly this is shown in a window at the bottom as a quick view for the events on a certain day. i would like for them to be show next to them as well but when i try and add anything it then has the event listed multipul times as there are rooms.

strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
        ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
        ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
        "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
        "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
        "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
        "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
        "ORDER BY tbl_Bookings.EventID"
 

attached is also a picture showing what i mean, in this example APLS has about 5 rooms which link to this one event and the other 2 in a normal report that i can do it would APLS would show 5 times one for each room. which is not what i want. I would like it to show room in column and then just have room1, room2, room3, etc...

hope this makes sence?

[jforbes]

I think what your looking for is Concatenate values from related records

It can be used to take a Column over Multiple rows and make one Value out of it.

[sheepchild]

something like this? i'm getting a compile error so guessing that i put something in the wrong order.Still new with the whole VB scripting and SQL statements.

strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
        ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
        ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
        "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
        "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
        "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
        "SELECT Room, concatRelated("Room", "tbl_RoomBookings", "EventID = " & [EventID])" " & _
        "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
        "ORDER BY tbl_Bookings.EventID"

[jforbes]

This might be closer:

strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
        ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
        ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
        ", concatRelated('Room', 'tbl_RoomBookings', 'EventID=' & [EventID] & ') AS Rooms " & _
        "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
        "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
        "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
        "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
        "ORDER BY tbl_Bookings.EventID"

I haven't tested it or anything, so you may have to tweak it.

[sheepchild]

everything just goes blank in the box no headers no data

but also no errors??

[jforbes]

A guess from the picture you provided is that the SELECT statement isn't returning any records.

Typically, when I'm not getting records when I think I should, I put a break point in the Code, debug.print the SQL string (strSQL2), copy the SQL onto the Clipboard, open a new Query, flip over to SQL Mode, paste the SQL into the new query, attempt to run it and see what it does. This might be helpful: How to Debug SQL String

[sheepchild]

tried putting the debug.print in the VBA code in a couple of places but all with the same results.

put it right in front of the strSQL2 and in front of the lstevents.rowsource = strSQL2 and a few other places.

i either get blanks appearing or false i don't get the SQL code to copy into a query.

i have done that before when i was expanding the SQL last time, but cant get it to work this time to work on it.

[sheepchild]

looking thought some other information that would make sence for it being false, as thats the overall one saying false to no information, doesnt make it into a SQL to paste into the query and work that way. is that correct in my assumptions?

[sheepchild]

found it.

lstEvents.RowSource = strSQL2
Debug.Print strSQL2
 

So code for SQL comes out at

SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status , tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] , tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] , concatRelated('Room', 'tbl_RoomBookings', 'EventID=' & [EventID] & ') AS Rooms FROM tbl_Bookings LEFT JOIN CateringRequests ON tbl_Bookings.EventID = CateringRequests.EventID WHERE MeetingDate = 42801 GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext ORDER BY tbl_Bookings.EventID
 

[sheepchild]

getting a syntax error in sting

is this something do with the extra table now being called on, does this also need to be in the select, or left join like the other table that is also being called?

or is this an concatrelated, not transferring from VBA to SQL?

getting a litte lost to be fair

[jforbes]

I did notice a typo. The Highlighted portion in the following should be removed:

", concatRelated('Room', 'tbl_RoomBookings', 'EventID=' & [EventID] & ') AS Rooms " & _

• Your code is using VBA to Create SQL String.
• It then applies the SQL String to RowSource of the ListBox.
• The ListBox recognizes that it has a newly defined RowSource and Queries the database using the SQL String.
• Access, while performing the Query, will call the ConcatRelated() function, (which is a VBA call) for each row.

So there really isn't another table being added to the SELECT Statement. The table is being included in the Function call which will perform a small query and return a string with the Concatenated fields.

Again, usually the best way to troubleshoot something like this is to get the SQL String on to the Clipboard, create a Query and Paste the SQL into the new Query and let Access tell you what is wrong with it.

[sheepchild]

cool manage to get that into a query, had the error of: the '[EventID]' could refer to more than one table listed in the from clause of your SQL

so added in which table it was looking from which changed the error:

undefined function 'concatRelated' in expression

strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
        ", tbl_Bookings.StartTime As [Start], tbl_Bookings.EndTime As [End] " & _
        ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
        ", concatRelated('Room', 'tbl_RoomBookings', 'EventID=' & [tbl_Bookings.EventID] ) AS Rooms " & _
        "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
        "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
        "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
        "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext, Rooms " & _
        "ORDER BY tbl_Bookings.EventID"
 

do i need to define this somewhere in the VBA script for it to call it? or is it already a predefined script built into access?

[sheepchild]

a development, try the expression builder in access to see if ConcatRelated existed. it didn't but did find ConcatChr changed that and started to get some results. This then showed up an error of spelling which i corrected and then started populating one room in the table/Query

strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
        ", tbl_Bookings.StartTime As [Start], tbl_Bookings.EndTime As [End] " & _
        ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
        ", ConcatChr('Room', 'tbl_RoomBooking', 'EventID=' & [tbl_Bookings].[EventID] ) AS [Rooms] " & _
        "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
        "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
        "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
        "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
        "ORDER BY tbl_Bookings.EventID"
 
 

[jforbes]

You get the ConcatRelated() function from Allen Browne's website:
Concatenate values from related records
He has some instructions on how to put it into a module for you. When you have the function available, Access will stop complaining about it. I'm not sure what ConcatChr() is, but it sounds like a function to build one string out of many, which isn't exactly what your trying to do.

[sheepchild]

Think i have cracked it i was looking at the link before you sent, doesn't say what to name the module, i had named it the same as the function so it was confusing it and i assume stopping the function from working.