Solved

Querry Help

Posted on 2011-02-19
4
1,100 Views
Last Modified: 2012-05-11
I have a querry that I would like to condense to not as many rows... it is attached to the below database.

The end result is I would like it to condense all same BookingID's to one row. For instance, in bookingID 79 all columns are the same except for the last two columns. I would like the new querry to compbine those two columns to one row titled: "Description". That "description" column would combine all the DateAppintments with the appropriate Duration

The end result for BookingID 79 would be one row that looks like this:

BookingID       customerID       StartDate         TutorName      TotalPrice     StudentName             Description
79                  AnsevinChris         2/14/2011       Teve                200               Name                2/17/2011 for 1 Hour, 2/18 for 1 Hour, 2/19/2011 for 1 Hour, 2/20/2011 for 2 Hour

Does this make any sense? I hope so, it would be awesome if it works!

Thanks,

Chris



PracticeBMdb.mdb
0
Comment
Question by:cansevin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 6

Expert Comment

by:AkAlan
ID: 34933669
It can be done but not in a simple query. I would do this in VBA code using recordsets. First populate the Booking recordset with all the bookings then loop through it and create a second recordset with all the appointments. If you have not used recorsets before and need help, let me know and I'll help with the code. I'm going to be interested to see if anyone has a simpler solution.

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34933741
test this,  run Query1

see codes of function concatThem in module1

used in query1

SELECT Bookings.BookingID, Bookings.StartDate, Bookings.TutorName, Bookings.customerID, Bookings.TotalPrice, Appointments.StudentName, Bookings.TotalHours, concatthem([Bookings].[BookingID]) AS Expr1
FROM Bookings INNER JOIN Appointments ON Bookings.BookingID = Appointments.BookingID
GROUP BY Bookings.BookingID, Bookings.StartDate, Bookings.TutorName, Bookings.customerID, Bookings.TotalPrice, Appointments.StudentName, Bookings.TotalHours, concatthem([Bookings].[BookingID])
HAVING (((Bookings.BookingID)=79));


to get all bookings, just removed the criteria in BookingID




PracticeBMdb.mdb
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 34933776
Nice one Capricorn, I have never seen this method but I like it a lot!
0
 

Author Closing Comment

by:cansevin
ID: 34933925
Wow, thats awesome... thanks!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question