Solved

Querry Help

Posted on 2011-02-19
4
1,029 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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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