Recurring Data and Yearly Calender Summary 1

[spartansFC]

Hi

I've used Duane Hookems recurring database example and created a form that populates booking information for a child's attendance from April 14 2013 to March 31 2014, the fields are:

dteSessionDates
ysnCurrentSession
lngChildID
lngClubID
lngPickedupFrom
lngSessionType
curSessionCost

and the data looks like

14/04/2013 True 100 100 100 1 10.00
15/04/2013 False 100 100 100 1 10.00
16/04/2013 False 100 100 100 1 10.00

so i can do the above, i've seen a great example of a yearly calender which i've attached where you can see which sessions the child is booked into. I know how to do crosstab queries but i'm not sure how to:

A: get the form to show all dates as days (Su, Mo, Tu, We... etc) along the top even when the child hasn't booked in for that day

I thought i might need a seperate table and connect it via dteSessionDates but that doesn't seem to work, it only shows the sessions the child is booked in for.

Has anyone ever tried to do a summary form like the one attached?

Any ideas

thanks

Mikie

 

[spartansFC]

Finally figured out why the conditional formatting wasn't working, the conditional formatting expressions is:

Right([11],2)="-1"

i didn't realise the [11] was the name of each of the text boxes, i'm so stupid. I've been trying to set up a second conditional format based off the session type (lngAMPMSession), i thought
if i changed the expression to:

Right([11],1)="1"

but it doesn't work, the number after the comma does relate to each row, for example:

18 (Date)
1 (lngAMPMSession)
-1 (isholiday)

so if i wanted to reference the date it would be

Right([11],0)="1"

, the session type

Right([11],1)="1"

, the isnumber

Right([11],2)="-1"

I'm using Access 2010 and i think you can add more conditional formatting expressions, just need to figure this out and the forms sorted. Does it matter what order the conditional formatting is in.

 

[dhookom]

Did you look up the function Right() in Help or on-line? The lngAMPMSession will be some place in the middle of the string of characters. You could use Mid() but keep in mind some of your values will have 1 digit for the date and some will have 2.

I would probably change the lngAMPMSession to either "A" or "P" and then use the InStr() function to test if A or P is anywhere in the string of characters.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

Sorry for asking all the questions without searching for the solution myself although i did google search the right function but couldn't understand it to be honest, i'm more of a visual person so even if i read something it takes me a while to figure things out.

I even tried to figure out having lngAMPMSessions show either "B", "X", "T" .... but couldn't do that but i eventually figured it out and then by using your solution of using the InStr function, the form works perfectly.

I think this form and the yearly calender are finally complete now.

Thanks again to you and MajP's help and patience.

Mikie

 

[spartansFC]

So i've got the form working, i just need to add some totals in the footer section of the subform. I thought a simple sumIf statement would work, but it didn't, then i googled around and found this

=(Len([6])-Len(Replace([6],"B","")))/1

that didn't work, then i was reading about maybe having to loop through the text box, does it make a difference that the text boxes have carriage returns etc and that's why a simple sumif won't work

So is there a way to sum on a continuous subform and add it to the footer of the subform the number of times a letter, in this case "B" appears, bearing in mind the text box isn't the usual text box,

1
B
0

or it could be

2

-1

I've done footer totals before, it's just this text box is a bit different to the ones i'm used to

 

[dhookom]

You can use an expression like:
Instr([6],"B")
to return the position of the B in the [6] field. If it's not found, the expression will return 0. So:
Instr([6],"B")>0
will return either 0 if not found or -1/True if found. Add the absolute function to convert the -1 to 1:
Abs(Instr([6],"B")>0)
Then sum the results to get the number of records with a B in the values.
Sum(Abs(Instr([6],"B")>0))

So try something in the Control Source property like:
=Sum(Abs(Instr([6],"B")>0))




Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

thanks again for all your help Duane, i even managed on my own to get the sum to work with 2 conditions

=Sum(IIf([PickupGrp]="St Michaels & Hawthorn Primary" And (Abs(InStr([1],"B")>0)),1,0))

 

[spartansFC]

so i thought i'd cracked the form footer totals, then i realised that i would have to total if the value was either "B" or "Ex" or "ST", i've googled around and even found someone who asked a similar questions, Duane's answer to the similar question was to put:

=sum(abs(Instr("EXT~LOA",[EMSTCD])>0))

so i changed the code to:

=Sum(Abs(InStr("B~Ex~ST",[7])>0))

but it doesn't total anything, just a zero

 

[dhookom]

Think about it. You are looking in the string "B~Ex~ST" for a value like "1 B -1". It doesn't work that way. You might consider creating an expression that looks for B then Ex then ST of putting some value in your table in the records for B Ex and ST records.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Ok, so i think i understand what you mean, so what i've done is in the tblSessionTypes

lngSessionType (Autonumber)
strSessionType (can be B, C, N, SF, D, T, H, Ex, ST, R, PR)
strSessiongFull (can be Booked, Cancelled, Cancelled 4 weeks notice, Swapped session.....)
StrSessionCharge (can be either Y or N
strSessionGrp (can be "BExSt" or any of the other individual types)

the expression in the query now looks like

Val: Max(Day([TheDate])) & Chr(13) & Chr(10) & Max([strSessionGrp]) & Chr(13) & Chr(10) & Min([IsHoliday])

and the text boxes look like

2
BExSt
0

i've then used the

=Sum(Abs(InStr("BExST",[7])>0))

in the footer to sum up the boxes but it doesn't seem to want to work either.

Is this what you meant?

 

[dhookom]

Try reverse since the expressions that worked had the field name as the first argument in the function.

=Sum(Abs(InStr([7],"BExST")>0))

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Thanks again for your help Duane, i've been thinking more about your answer about adding a value to the table which i did with "BExST", but that lead to some problems so what i did was for all
session types "B", "Ex", "ST" i added a "X" to strSessionGrp.

I then changed the value expression in the query to:

Val: Max(Day([TheDate])) & Chr(13) & Chr(10) & Max([strSessionKey]) & Chr(13) & Chr(10) & Max([strSessionGrp]) & Chr(13) & Chr(10) & Min([IsHoliday])

so in the text boxes i now have

2
B
X
0[code]

or

[code]2


-1

i thought everything was working perfectly and i've been creating the forms when i realised the forms were being populating incorrectly.

So any child (lngChildID) can go to any of the 6 clubs (lngClubID], some children can go to more then one club, a breakfast or after school club. Each club does have a unique ID number and so does each child.
the problem is that the form is pulling information from both clubs for some reason.

test date
Club ID, ChildID, dteSessionDates, lngBookingID, Surname, Forename, strSessionKey, strSessionGrp
376, 605, 24/04/2013, 47491, Doe, John, B, X
377, 605, 23/04/2013, 47490, Doe, John, B, X

my version of your qcarDateShipTo is

SELECT Format([TheDate],"mmm-yy") AS expr2, tblDates.TheDate, tblDates.isHoliday, Year([TheDate]) AS Expr1, tblSessionsBuildNewPerm.lngChildID, tblSessionsBuildNewPerm.lngClubsID
FROM tblDates, tblSessionsBuildNewPerm
GROUP BY Format([TheDate],"mmm-yy"), tblDates.TheDate, tblDates.isHoliday, Year([TheDate]), tblSessionsBuildNewPerm.lngChildID, tblSessionsBuildNewPerm.lngClubsID
HAVING (((Format([TheDate],"mmm-yy"))=Forms!frmAvailability4StCathBCMain!cmbMonthYear) And ((tblSessionsBuildNewPerm.lngChildID)=Forms!frmAvailability4StCathBCMain!cmbChildSelectt) And ((tblSessionsBuildNewPerm.lngClubsID)=Forms!frmAvailability4StCathBCMain!cmbClubSelect))
ORDER BY tblDates.TheDate;

results from using "Apr-13", ChildID 605, ClubID 376:

expr2, TheDate, isHoliday, Expr1, lngChildID, lngClubsID
Apr-13, 01/04/2013, Yes, 2013, 605, 376
Apr-13, 02/04/2013, Yes, 2013, 605,376
.....
Apr-13, 23/04/2013, No, 2013, 605, 376
Apr-13, 24/04/2013, No, 2013, 605, 376

my version of the qxtbActivitySchedule crosstab is

TRANSFORM Max(Day([TheDate])) & Chr(13) & Chr(10) & Max([lngAMPMSession]) & Chr(13) & Chr(10) & Min([IsHoliday]) AS Val
SELECT Year([TheDate]) AS YR, Month([TheDate]) AS MTH, qryAvailability5WestJesMain.lngClubsID, qryAvailability5WestJesMain.lngChildID
FROM qryAvailability5WestJesMain LEFT JOIN tblSessionsBuildNewPerm ON (qryAvailability5WestJesMain.lngChildID = tblSessionsBuildNewPerm.lngChildID) AND (qryAvailability5WestJesMain.TheDate = tblSessionsBuildNewPerm.dteSessionDate)
GROUP BY Year([TheDate]), Month([TheDate]), qryAvailability5WestJesMain.lngClubsID, qryAvailability5WestJesMain.lngChildID
ORDER BY Year([TheDate]), Month([TheDate]), qryAvailability5WestJesMain.lngClubsID, qryAvailability5WestJesMain.lngChildID
PIVOT [TheDate]-DateAdd("d",-Weekday(DateSerial(Year([TheDate])+2,Month([TheDate]),1)),DateSerial(Year([TheDate]),Month([TheDate]),1)) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37);

results from "Apr-13", lngChildID: 605, lngClubsID: 376 are:

YR, MTH, lngClubsID, lngChildID,1,2,3,4,5,6.....
2013, 4, 376, 605,

when i run the form based off the queries, the form pulls the data from both clubs lngClubsID 376 and 377. Even if i put the clubID, childID, month-yr in the criteria of the query, it will still display on the form information from both clubs.

Any ideas where i'm going wrong

 

[dhookom]

I don't see where your query SQL filters by lngClubsID.

Have you considered creating a calendar report rather than a form? What's the advantage of a form?

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I guess the reason for the form is that it's easier to have it on screen, then users can then filter it using the combo box. You're right about not seeing where lngClubsID not querying, i've just had a look at your query and it has

HAVING (((Orders.ShipName)=[Forms]![frmActivitySchedule]![cboShipName]) AND ((Year([TheDate]))=[Forms]![frmActivitySchedule]![cboYear]))

mine does not have this part of the code in the SQL, hmmmm. So i clicked on the totals icon to revert the query back to a SELECT query, then clicked it again and when i looked at the SQL the HAVING part was there:

SELECT Format([TheDate],"mmm-yy") AS expr2, tblDates.TheDate, tblDates.isHoliday, Year([TheDate]) AS Expr1, tblSessionsBuildNewPerm.lngChildID, tblSessionsBuildNewPerm.lngClubsID
FROM tblDates, tblSessionsBuildNewPerm
GROUP BY Format([TheDate],"mmm-yy"), tblDates.TheDate, tblDates.isHoliday, Year([TheDate]), tblSessionsBuildNewPerm.lngChildID, tblSessionsBuildNewPerm.lngClubsID
HAVING (((Format([TheDate],"mmm-yy"))=[Forms]![frmAvailability5WestJesASCMain]![cmbMonthYear]) AND ((tblSessionsBuildNewPerm.lngClubsID)=376))
ORDER BY tblDates.TheDate;

To avoid confusion i added the Club ID in the criteria "376". So when i run the form, it still puts the 23/04/2013 is After school club only ID 376, 23/04/2013 is breakfast club only ID 377 on the same form

there is something i'm missing, it only happens though when a child is in 2 different clubs... i'm so confused, been working on this problem all afternoon.

 

[dhookom]

I'm not sure which query has what SQL. I noticed an LEFT JOIN but don't know if this should be changed to INNER JOIN.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I finally figured out the problem at 11.30pm last night. It was to do with the relationships in the crosstab query, in your database you had the 2 tables connected via:

qxtbActivitySchedule table relationships

ShipName > ShipName
TheDate > OrderDate

i assumed i only needed the 2 fields linked but i needed 3 so i've linked:

lngChildID > lngChildID
lngClubsID > lngClubsID
theDate > dteSessionDate

the forms all work now, can't believe it.

I don't want to jinx it but i think we can finally close this thread

Thanks again for Duane and MajP's help.

 

[spartansFC]

Hi Duane

I knew i'd spoke to soon. All the forms work which is great, i was even creating a yearly calender based off 1 child, 1 club, 1 year when i noticed something. February 28, 2014 is a friday, so therefore March 1, 2014 should be a Saturday but when i run the form it fills in the "1" on the Sunday instead. I thought i'd done something wrong but i checked your amazing TT_attendance database you created and it happens on that too.

Is it because February 2014 is a leap year?

 

[dhookom]

I didn't know February 2013 was a leap year.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

You're right February 2014 isn't a leap year, i was just trying to think why the form was populating the dates in the wrong boxes.

Is it the query that needs amending somehow or the form?

 

[dhookom]

I expect the query expression is somehow wonky.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Is it this part of the query that is wonky?

Expr2: [TheDate]-DateAdd("d",-Weekday(DateSerial(Year([TheDate])+2,Month([TheDate]),1)),DateSerial(Year([TheDate]),Month([TheDate]),1))