?
Solved

Querry Help

Posted on 2011-02-19
4
Medium Priority
?
1,202 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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 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