Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Querry Help

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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 …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

610 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