Thinking Outside of the Box

[DreamGenius]

Okay, having ascertained that RowID and RowNum isn't available in Access via the Search facility, I'll throw my quandry open to the combined wisdom and collective minds of this forum.

I have a database which tracks Training Requests and the Dates booked are records in tblBookDays, with BookDayId (Autonumber), BookingID (which is the Foreign Key to tblBookings), Employee (Text), Program (Number), BookDate (Date), Attended (Boolean), Canceled (Boolean) plus other text fields.

For any given Booking(ID), there may be multiple entries in this table, but the Unique Key is on Employee + Program + BookDate, meaning no person can be booked on the same program on the same date.

For the purposes of reporting, I'm trying to get the dates concatenated into a string, using a query like this:

TRANSFORM Max(BookDate) AS MaxDate
SELECT BookingID, 
COUNT (AssociateID) AS Associates
FROM qryAttended
GROUP BY BookingID
ORDER BY BookingID
PIVOT "Date0" & Right(CStr([BookDaysID]),1);

where qryAttended is simply

SELECT BookDaysID, BookingID, AssociateID, ProgramID, CourseID, BookDate, Notes, Attended, Canceled, Replaced, Charged, Reason, UpdatedBy, UpdatedOn
FROM tblBookDays
WHERE Attended = True
AND Canceled = False
ORDER BY BookingID, BookDate;

the output of which is concatenated using the following query

SELECT BookingID, 
Replace(
 Trim(
  IIf([Date00] Is Null,Null,[Date00] & "   ") & 
  IIf([Date01] Is Null,Null,[Date01] & "   ") & 
  IIf([Date02] Is Null,Null,[Date02] & "   ") & 
  IIf([Date03] Is Null,Null,[Date03] & "   ") & 
  IIf([Date04] Is Null,Null,[Date04] & "   ") & 
  IIf([Date05] Is Null,Null,[Date05] & "   ") & 
  IIf([Date06] Is Null,Null,[Date06] & "   ") & 
  IIf([Date07] Is Null,Null,[Date07] & "   ") & 
  IIf([Date08] Is Null,Null,[Date08] & "   ") & 
  IIf([Date09] Is Null,Null,[Date09])
 ),"   ",", "
) AS Bookings
FROM tabAttended;

The challenge is that, by using the right-most digit from BookDaysID to separate out the dates, the dates aren't always in chronological order. You need to see the attached file to understand what I mean by that - see the rows where BookingID = 1738. The first block of data is from the Cross Tab query, the second from the concatenation query.

The reason for concatenating the dates is to simply display them on one line of a report but I'm stumped - I'll listen to any solution, however wacky, if it gets me where I want to go.

 

[HiTechCoach]

Alan,

I am really not sure what you want.

My first guess is the that you could use this:

Return a concatenated list of sub-record values

Hope this helps ...

 

[DreamGenius]

Far enough, it was late when I posted it and it was a lot of words which I now see don't really come to a point! I'll try to clarify.

The concatenated strings of dates produced for the report is not always in chronological order and I need them to be. I cannot see a way of achieving that, by adapting the method I'm using or by any other method.

I'm hoping that somebody here might have a fresh approach or good idea. Please refer to the attached, particularly the lines where the BookingID is 1738, for an example of where I'm failing.