Solved

Querry Help

Posted on 2011-02-19
4
949 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 119

Accepted Solution

by:
Rey Obrero 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now