By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,661 Members | 1,917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,661 IT Pros & Developers. It's quick & easy.

query from 2 tables list of items just once

P: 52
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.

Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
  3.         ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
  4.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  5.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  6.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  7.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
  8.         "ORDER BY tbl_Bookings.EventID"
  9.  
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?
Attached Images
File Type: jpg example1.jpg (40.3 KB, 143 views)
Mar 2 '17 #1

✓ answered by jforbes

This might be closer:
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
  3.         ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
  4.         ", concatRelated('Room', 'tbl_RoomBookings', 'EventID=' & [EventID] & ') AS Rooms " & _
  5.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  6.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  7.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  8.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
  9.         "ORDER BY tbl_Bookings.EventID"
I haven't tested it or anything, so you may have to tweak it.

Share this Question
Share on Google+
14 Replies


jforbes
Expert 100+
P: 1,107
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.
Mar 3 '17 #2

P: 52
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.

Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
  3.         ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
  4.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  5.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  6.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  7.         "SELECT Room, concatRelated("Room", "tbl_RoomBookings", "EventID = " & [EventID])" " & _
  8.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
  9.         "ORDER BY tbl_Bookings.EventID"
Mar 3 '17 #3

jforbes
Expert 100+
P: 1,107
This might be closer:
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
  3.         ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
  4.         ", concatRelated('Room', 'tbl_RoomBookings', 'EventID=' & [EventID] & ') AS Rooms " & _
  5.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  6.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  7.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  8.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
  9.         "ORDER BY tbl_Bookings.EventID"
I haven't tested it or anything, so you may have to tweak it.
Mar 3 '17 #4

P: 52
everything just goes blank in the box no headers no data

but also no errors??
Mar 6 '17 #5

jforbes
Expert 100+
P: 1,107
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
Mar 6 '17 #6

P: 52
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.
Mar 6 '17 #7

P: 52
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?
Mar 6 '17 #8

P: 52
found it.

Expand|Select|Wrap|Line Numbers
  1. lstEvents.RowSource = strSQL2
  2. Debug.Print strSQL2
  3.  
So code for SQL comes out at
Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
Mar 6 '17 #9

P: 52
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
Mar 6 '17 #10

jforbes
Expert 100+
P: 1,107
I did notice a typo. The Highlighted portion in the following should be removed:
Expand|Select|Wrap|Line Numbers
  1. ", 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.
Mar 6 '17 #11

P: 52
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

Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start], tbl_Bookings.EndTime As [End] " & _
  3.         ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
  4.         ", concatRelated('Room', 'tbl_RoomBookings', 'EventID=' & [tbl_Bookings.EventID] ) AS Rooms " & _
  5.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  6.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  7.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  8.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext, Rooms " & _
  9.         "ORDER BY tbl_Bookings.EventID"
  10.  
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?
Mar 7 '17 #12

P: 52
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

Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start], tbl_Bookings.EndTime As [End] " & _
  3.         ", tbl_Bookings.Contact, Security, IIF(Count(CateringRequests.EventID)>0, 'Yes', 'No') As Catering, tbl_Bookings.Int_Ext As [Booking] " & _
  4.         ", ConcatChr('Room', 'tbl_RoomBooking', 'EventID=' & [tbl_Bookings].[EventID] ) AS [Rooms] " & _
  5.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  6.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  7.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  8.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security, Int_Ext " & _
  9.         "ORDER BY tbl_Bookings.EventID"
  10.  
  11.  
Mar 7 '17 #13

jforbes
Expert 100+
P: 1,107
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.
Mar 7 '17 #14

P: 52
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.
Mar 7 '17 #15

Post your reply

Sign in to post your reply or Sign up for a free account.