Combine Multiple Dates in Report

[Rabbit]

Dear all,

I am creating a training record which can accomodate "individual dates" and "continuous date", so that a training can be held in consecutive and non-consecutive dates. In order to do the above requirements, I have created 3 tables, table with continuous date (start & end date), table for individual date, and a training detail table. The first 2 tables are linked to the "Detail table" by the "Detail ID".
An entry form was built from the "Detail" table, 2 subforms (individual date & consecutive dates) were embeded in it.
Two queires were built: one for individual dates; second one is similar to first one except putting the start & end date into one field. Then I used SQL to union the above 2 queries into one big pool of data for report.
Problems when I try generate the report for a particular training:
For example:
Training Category Training Title Date
PC Training ACCESS for Advance User 1 Feb 2010
PC Training ACCESS for Advance User 3 Feb 2010
PC Training ACCESS for Advance User 10 Feb 2010
PC Training ACCESS for Advance User 12 Feb 2010-14 Feb 2010

My boss want me to combine all the dates together to have something like this:
Training Category Training Title Date
PC Training ACCESS for Advance User 1, 3, 10 Feb 2010
& 12 Feb 2010 - 14 Feb 2010

I tried to add an additional unbound text with the condition, but how do I know how many non-consecutive or consecutive dates of each different trainings?
Thank you in advance for you all valuable advices!

 

[vbaInet]

If you have a group ID then something like this should help:

http://allenbrowne.com/func-concat.html

Welcome to the forum.

 

[Rabbit]

Dear vbaInet,

Thank you very much, I have a group ID for the Course Date
It works well but I encounter some other problems:
1) The report produced multiple number of concatenated value of each same ID(different training categories).
2) The number is of duplication is = number of original single entries.

Source of the problem:
I listed only one training in the forum for easy reference.
Actually, I got 12 different types of Trainings, e.g. Professional, Career, PC related, Language, Staff development......
Each type of training has a group ID for its category.
e.g. (let's call the group ID as "ID"),
Professional ID consist of 1, 2, 3;
Career ID consist of 1, 2, 3;.......
When it come down to report, it grouped all the Course Date with same "ID" together.

It seems like that I have to go back to the very fundamental structure, and use the concatenate function to eliminate or say combine the record together before reporting!!!
But thanks anyway for your valuable advice!

 

[vbaInet]

No need to change your structure yet. Show me a screenshot of what it's doing and also one of what it should look like (using Excel maybe).

So basically you want to get the concatenated values based on TWO references, the Group ID AND the Course Type?

Also, do the Course Types have IDs too?

 

[Rabbit]

Dear vblInet,

Sorry to reply you mail so late. I was on night shifts, and I was too exhausted for the whole time.
First, thank you so very much for your very sincere assistance! You really did put yourself into my problem!
You are correct, I need to concatenated values based on TWO references, the Group ID and and Course Type. I've considered your suggestion also.

But I changed my mind, after I look at all the following reports involved:
1) Staff Training Report for the user to the pick the start and end day;
2) There are 5 ranks of Staff, user select the rank and the start/end period to pull the training report details;
3) Individual Staff Academic qualification and whole Training History x 2 (one with personal particulars, one without) for the manager;
4) Staff Training History with user select indivdual/multiple type of Training for a user selected period for Training Unit reference;
5) User select Training Course oriented details report.

Specially in item 3), I've used 14 subforms embeded into the each report, with 1 academic qualification, 13 types of training to be exact(not 12). (hold on to these reports, I got some new problems in here, will ask later).

So, if I change the basic structure, to have all the dates concatenated at the query and do a grouping. For each particular training course session (for a particular staff), it has only one entry of couse day (concatenated). For all the above reports, all I have to do is to redefine their sources to some newly built queries.
Actually, I rebuilt all my queries with your provided concatenated functions, what's left is redefine the source for the reports.
Try to provide you some screenshots if not succeed!
Thank you very much!

 

[vbaInet]

(hold on to these reports, I got some new problems in here, will ask later).

I'm trying to hold on to them but I keep losing connection. Help me rabbit, I can't feel them

Right, 13 subreports sound like an overkill. Is Grouping not getting you what you want? Maybe you can explain how the report is and maybe a sample of the data and screenshots as well.

With regards your original question, it would have been possible to conatenate based on those two IDs. It's only a few lines of code.

 

[Rabbit]

Ok!

Give me a little more time, I'll give you a full detail of my training db!

 

[Rabbit]

Dear vbaInet,

Sorry to keep you waiting, got shift duty which may me very busy!
The objective of my program is doing a summary for a department of sixty some people. Please forgive me I am trying to avoid some confidential details and information, so I will use some alias. Besides, please let me know if some of my program structure is foolish.
For example:
IT Training:
TABLE Structure
In order for a training to enter unlimited non-consecutive and consecutive days, I created 3 tables:
1) Non-consecutive day table : ITSingleDate (ITID, ITDate);
2) Consecutive day table : ITConsecDate (ITID, StartDate, EndDate);
3) ITDetail table, which the above 2 tables connect with the detail table with a ITID key: ITDetail (ITID, StaffName, Rank, ITCourse, Organization, Cost)

In this case, I embedded 2 subforms for user entry. see first screen shot on the attachment:
Query Structure
From the above information, 2 main streams of data, the non-consecutive and consecutive, for each I created one query:
1) Basic queries:
qryITSingleDate – which contain one date field
qryITConsecDate – which also contain one combined date field
(03 Jan 2009 to 05 Jan 2009)
2) Union records:
I used a UNION query to join them into one, which contain more than actual number of records. That means, for a training it has 2 single days and 1 consecutive period, it gives out 3 lines of records. Let’s call this qryIT
3) Grouping and apply build-in function:
I have to create a group query and use your concatenated function to combine dates, called qryFinalIT
4) Reconstruct the Date field for query, report sorting purpose:
Extract out the “day”, “month”, “year” out from the concatenated field, which is the very first day of the training and use it as a sorting index. In which I need one more query: qrySubIT (added a “Lining” field).
5) Union all the Trainings:
Joining all the Trainings together by qryAllFinal.
6) During all the reports generation, I have also use some pseudo queries, dynamic queries like let the user select single/multiple Training(s), then build a SQL statement according to his selection(s).

The above arrangement provides an easy report generation.
As you have suggested no need to change the original yet, you can use a few lines of codes to combine 2 groups ID together. How can you solve the multiple appearance problems?
Actually, before I made the above changes, I have saved an extra copy for in case.

It seems like it works fine now!
I have 2 more questions (hope it’s not too much to ask):
1) Every time I generate the report, the program size tends to get bigger. I’ve set compress before closing, but is there any way to compress every time I do query or report generation?

2) I have attached a report in design view which contains all the subreports, can I add a text “NiL” besides each “Training Title” when empty record.
example: IT Training: NIL

Hope I am not asking for too much!

Rabbit

 

[Rabbit]

Sorry try one more attachment!

 

[Rabbit]

If you have a group ID then something like this should help:

http://allenbrowne.com/func-concat.html

Welcome to the forum.

Dear All,

Last year, with the great help from vbaInet, I put the concat function into my queries, it worked fine with a few entries, but run like forever for > 3000 entries. Now I am in deep trouble. I have to force to erase the function from all the queries. Let's start over with your help!
I have a Detail Training Table and 2 supplementary tables (consecutive/non-consecutive days), in which I created an entry form for user. As a result, there will be all connected to a DetailID. For example:
The big pool:
ID Staff Course CourseDate TotalDays Cost
5 John ACCESS 1/2/2011 to 3/2/2011 4 200.00
5 John ACCESS 5/2/2011 4 200.00
5 John ACCESS 7/2/2011 4 200.00
With some queries help, I calculated total number of days to 4, and the cost is only 200.00 for the whole deal.
Before I used concat function in query, it gives me this:
The elegant pool:
ID Staff Course CourseDate TotalDays Cost
5 John ACCESS 1/2/2011 to 3/2/2011, 4 200.00
5/2/2011, 7/2/2011
Now I have a big table like the “Big Pool”, with >6000 records, and wanted to put it in a report and display it out like the “Elegant Pool”!
Can I use "Concat" function in Report Textbox, but how to avoid multiple display identical results??????


Hope I am not asking for too much!
Thank you in advance

Rabbit

 

[vbaInet]

Yes you can still use the Concat function in your report. To avoid duplication, Group By ID, move the fields to the Header section of that group and put the Concat function there.

 

[Rabbit]

Yes you can still use the Concat function in your report. To avoid duplication, Group By ID, move the fields to the Header section of that group and put the Concat function there.

Thanks, nice to hear from you again!
I will try that and keep up posted!

rabbit

 

[vbaInet]

No probs!!

I hope it goes well.

 

[Rabbit]

No probs!!

I hope it goes well.

Problem:
On the report, I created a DetailId group by and set up header, moved all the fields to that section, leaving the detail information section blank.
then I add the followin to the original CourseDate Row Source.
=ConcatRelated("CourseDate","qryNextGeneration3New","DetailID=" & [DetailID])

when I run it it gives me: Error 3061: Too few Parameter Expected 2

??????

Rabbit

 

[Rabbit]

Problem:
On the report, I created a DetailId group by and set up header, moved all the fields to that section, leaving the detail information section blank.
then I add the followin to the original CourseDate Row Source.
=ConcatRelated("CourseDate","qryNextGeneration3New","DetailID=" & [DetailID])

when I run it it gives me: Error 3061: Too few Parameter Expected 2

??????

Rabbit

Further from above: I found out that the original report: I used a form to let the user select the start date and end date (criteria no. 1 is set to the qryNextGeneration3New), then when I use the ConcatRelated function on the row source for the report text box (2nd Criteria is set in along), as a result: Error 3061: Too few Parameter Expected 2.
How can I putting this to work?
What if an addition criteria in the form e.g. Start, End date + staff name????


Rabbit

 

[vbaInet]

Further from above: I found out that the original report: I used a form to let the user select the start date and end date (criteria no. 1 is set to the qryNextGeneration3New), then when I use the ConcatRelated function on the row source for the report text box (2nd Criteria is set in along), as a result: Error 3061: Too few Parameter Expected 2.
How can I putting this to work?
What if an addition criteria in the form e.g. Start, End date + staff name????

The main problem here is that when opening a recordset, it doesn't like parameterised queries.

What you should do is:

1. Make a copy of the query and in the copy remove all the criteria that points to a control on a form
2. Save the copy and close.
3. In your original query add the new query you created in step 1 to the grid and delete all the tables from the grid
4. Look through the fields and amend any field that isn't pointing to the query you just added
5. Use the query in step 1 in the (air) code below:

public function ConcateRelated(intDetailID as long)
    dim rst as dao.recordset, strConcat as string

    strConcat = vbnullstring

    set rst = currentdb.openrecordset("SELECT * FROM QueryName WHERE [Field1] = #" & Me.txt1 & "# " & _
                                          "AND [Field2] = #" & Me.txt2 & "# AND " & _
                                          "DetailID = " & intDetailID & ";", dbOpenSnapshot)

    do while not rst.eof
        strConcat = strConcat & rst![DateField] & ", "
        rst.movenext
    loop

    if len(strConcat) <> 0 then
        ConcatRelated = left(strconcat, len(strconcat) - 2)
    end if
end function

 

[Rabbit]

The main problem here is that when opening a recordset, it doesn't like parameterised queries.

What you should do is:

1. Make a copy of the query and in the copy remove all the criteria that points to a control on a form
2. Save the copy and close.
3. In your original query add the new query you created in step 1 to the grid and delete all the tables from the grid
4. Look through the fields and amend any field that isn't pointing to the query you just added
5. Use the query in step 1 in the (air) code below:

public function ConcateRelated(intDetailID as long)
    dim rst as dao.recordset, strConcat as string
 
    strConcat = vbnullstring
 
    set rst = currentdb.openrecordset("SELECT * FROM QueryName WHERE [Field1] = #" & Me.txt1 & "# " & _
                                          "AND [Field2] = #" & Me.txt2 & "# AND " & _
                                          "DetailID = " & intDetailID & ";", dbOpenSnapshot)
 
    do while not rst.eof
        strConcat = strConcat & rst![DateField] & ", "
        rst.movenext
    loop
 
    if len(strConcat) <> 0 then
        ConcatRelated = left(strconcat, len(strconcat) - 2)
    end if
end function

Dear vbaInet,

I am kind of loss and having a headache on this!
Hope I did not give you too much troubles!

on my qryNextGeneration4New:
a field called: Lining (align records according to the first day of course);
I added:
"Between [Forms]![frmCountDayRange]![StartDate] And [Forms]![frmCountDayRange]![EndDate]"

on my report:
Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True
' Open form
DoCmd.OpenForm "frmCountDayRange", , , , , acDialog
' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmCountDayRange") = False Then Cancel = True
' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub
Which will call up the form to with 2 text boxs (pop-up calendar) to and an "OK" button to check the date entries.
The value from frmCountDayRange will transfer the dates criteria to the qryNextGeneration4New, which in term to provide details for the report.

I follow your previous instruction to move all the info to DetailID GroupBy header, create a qryNG4New, remove criteria from qryNG4New, embeded the qryNG4New into qryNextGeneration4New (which still contain the date parameter criteria), insert the Function but changed the sql to:

Public Function ConcateRelatedvba(intDetailID As Long)
Dim rst As dao.Recordset, strConcat As String
strConcat = vbNullString
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryNextGeneration4New WHERE [Lining] = Between [Forms]![frmCountDayRange]![StartDate] And [Forms]![frmCountDayRange]![EndDate]" & " AND " & "DetailID = " & intDetailID & ";", dbOpenSnapshot)
Do While Not rst.EOF
strConcat = strConcat & rst![DateField] & ", "
rst.MoveNext
Loop
If Len(strConcat) <> 0 Then
ConcatRelated = Left(strConcat, Len(strConcat) - 2)
End If
End Function

Put the Function into the row Source and run the report :
It gave me an infinite error messages...........!

Rabbit

 

[vbaInet]

I mentioned that this was the cause of the initial error -->

The main problem here is that when opening a recordset, it doesn't like parameterised queries.

This means, you must concatenate the value from the control to the SQL statement. You wrote this -->

Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryNextGeneration4New WHERE [Lining] = Between [Forms]![frmCountDayRange]![StartDate] And [Forms]![frmCountDayRange]![EndDate]" & " AND " & "DetailID = " & intDetailID & ";", dbOpenSnapshot)

(The green bit is incorrect by the way) But it should actually be like this:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryNextGeneration4New WHERE [Lining] Between #" & Me.[StartDate] & "# " & _
"And #" & Me.[EndDate] & "# AND " & "DetailID = " & intDetailID & ";", dbOpenSnapshot)

 

[Rabbit]

I mentioned that this was the cause of the initial error -->
This means, you must concatenate the value from the control to the SQL statement. You wrote this -->
(The green bit is incorrect by the way) But it should actually be like this:

I understand the recordset does not like parameterised queries!
after I put your suggestion into the Function, it gives me : invalid ME.
I've been thinking over the whole situation for the past few days.
I changed the report form a bit, leaving all the fields into the detail section. Do not display duplicate on staff's name and course name, which leave the Course Date intact. Add a total field in the DetailID group end section. The result is acceptable for my boss:

Course Staff Date Total Number of Days
ACCESS John 4 March 2011
7 March 2011
-------------------------------------------------- 2

The outlook is not perfect, but it's a way to work around the problem.

Thank you so much for all your help, you are good!

Rabbit

 

[vbaInet]

after I put your suggestion into the Function, it gives me : invalid ME.

What was the exact error message? My guess would be that you have put the code in a Module or a Class, but it should actually be in the Form's module.

I understand the recordset does not like parameterised queries!

Just for clarity, what you actually wrote was still a parameterised query. The red bits indicate that it is -->

Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryNextGeneration4New WHERE [Lining] = Between [Forms]![frmCountDayRange]![StartDate] And [Forms]![frmCountDayRange]![EndDate]" & " AND " & "DetailID = " & intDetailID & ";", dbOpenSnapshot)

Parameterised in the sense that you are reference a control from within the SQL statement.