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

 

[dhookom]

I'm not sure why I added "+2" in the DateSerial for the year. If you remove it, the dates should display correctly with the first column being a Sunday.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

i did what you said, removed the "+2" but it didn't seem to work, the days and dates still didn't match

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

the query now looks like the above, if you run the frmActivitySchedule and use 2010 as the year, it places the 1 Jan 2010 as a wednesday, the 1st Jan 2010 is a Friday so i have a general idea of what the expresssion is doing so i played around with it, i counted back from the wednesday to the friday and the difference was "4" so i changed the expression to the one below and it works now, i've checked every month from 2010 onwards and they all match (day = date) now

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

So does the expression tell the query what the starting day/date should be and by changing it to "-4", it now knows that it should start on the Friday.

Please tell me i'm right

Mikie

 

[MajP]

Not to distract from what you are working, but here is a third example of building a year calendar FYI. This uses a more traditional form, but kind of a convoluted process. This requires a lot less labor than the original form I made, but there a many small moving parts. It is easier to see than explain. But basically you build a table to serve as a "grid". In this case 37 fields for labels, a field for the name of the month, and other fields for storing information about that row (but not added to the form). Bind this table to the form and you have an empty grid. Now you can populate the "cells" by looping the records (rows) and the fields (columns).

http://www.4shared.com/file/_M80HSfu/MajPYearCalendar_Ver2.html

Look at the "tblGrid" and then the "frmYearCalendar" in design view to see how it is a bound form.

 

[dhookom]

Actually, if you want to days across the top to begin with a Saturday, I found -5 to work as desired

Expr1: [TheDate]-DateAdd("d",-Weekday(DateSerial(Year([TheDate]),Month([TheDate]),-5)),DateSerial(Year([TheDate]),Month([TheDate]),1))

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Thanks Majp for the updated version of the calender.

When i try to open it i get a few error messages.

There is no object in this control

FillMonthLabels Me, Year(dtpYear.Value)

When i switch to VBA and then look at the references, i have a missing one

Microsoft Windows Common Controls -2.6.0

 

[MajP]

I believe that is because I used a date time picker. Can you add the reference?

 

[spartansFC]

Hi Majp, so i managed to install the Microsoft Windows Common Controls, and i've checked the references, none are missing.

Do i just scroll down the list of references, find the date/picker active x and tick it. I can't see it in the list

 

[spartansFC]

Hi Duane, i tried changing the the expression to "-5" but the dates didn't match for me, but they do for "-4

 

[dhookom]

If -4 works then use it.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I knew i hadn't quite finished with this form, i've got totals in the footer section of my form, i know it's probably not the best way to do but it works.

To calculate if a session is "booked" or "predicted" i now have:

=Sum(IIf(InStr([4],"Pr") Or (Abs(InStr([4],"X")>0)),1,0))

To calculate if a session is booked and the child gets picked up from certain schools i have:

=Sum(IIf([PickupGrp]="School Pickup 1" And (Abs(InStr([4],"X")>0)),1,0))

I now need to combine the 2 statements so i thought the following would work:

=Sum(IIf(InStr([4],"Pr") Or (Abs(InStr([4],"X") And [PickupGrp]="School Pickup 1">0)),1,0))

The total i get from the above is 14, it should be 8, it seems to count every row where there is a "Pr" or "X" and forgets about the pickupgrp condition.

Anyone have a better solution.

Mikie

 

[dhookom]

The first expression should be:
[CODE expression]
=Sum(IIf(InStr([4],"Pr")[highlight #FCE94F]>0[/highlight] Or (Abs(InStr([4],"X")>0)),1,0))
[/code]

=Sum(IIf([PickupGrp]="School Pickup 1" [b][COLOR=#A40000][highlight #FCE94F]>0[/highlight][/color][/b] And (Abs(InStr([4],"X")>0)),1,0))

Tell us exactly what you mean by "[highlight #FCE94F]combine the 2 statements[/highlight]".

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

So when i mean combine the 2 statements i need the sum if statement to:

If [4] = Pr or X and PickupGrp = "School Pickup 1"

I thought the statement

=Sum(IIf(InStr([4],"Pr")>0 Or (Abs(InStr([4],"X")>0 And [PickupGrp]="School Pickup 1")>0),1,0))

would work but it only half works, i've tried testing it to see what the outcome is:

from Apr 2013 to July 2013, the text boxes Numbered [1], [2], [3], [4]... etc all look like

4
B
X
0

the result is 8 which is correct

from Sept 2013 to July 2014, the text boxes Numbered [1], [2], [3], [4]... etc all look like

4
Pr
Pr
0

the result is 12 which is incorrect, it looks like the statement is counting all the children who have

4
Pr
Pr
0

but isn't looking at the school pickup group part.

I'm not sure why it's only working half the time

 

[PHV]

You have to use parenthesis when you mix OR & AND, eg:
IIf([!]([/!]sometest Or othertest[!])[/!] And moretest, ..., ...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[spartansFC]

So i've been trying to amend/create a report per each club to show each day the total number of children attending each day, i've amended the crosstab query and came up with this:

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

the code above works but i only need the count where strSessionGrp = "X"

 

[dhookom]

Try:

Val: Max(Day([TheDate])) & Chr(13) & Chr(10) & [COLOR=#A40000]Abs(Sum(strSessionGrp="X"))[/color] & Chr(13) & Chr(10) & Min([strSessionGrp]) & Chr(13) & Chr(10) & Min([IsHoliday])

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Thanks again Duane, how come you make things look so simple

 

[dhookom]

It thrive on simple and hate complex.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

New problem with this query:

Val: Max(Day([TheDate])) & Chr(13) & Chr(10) & Abs(Sum([strPickupDesc]="B")) & Chr(13) & Chr(10) & Min([strPickupDesc]) & Chr(13) & Chr(10) & Min([CurDailyCost]) & Chr(13) & Chr(10) & Min([IsHoliday])

so the above shows for example

4
2
B
95
0

or

16
2
B
102
0

there are 6x£95 and 12x£102 so the total that i need is £1,794. I can set up a sum([curDailyCost] but this equals £3,3363 which is sort of correct and i know why it's doing it. I just need to somehow in the query to summ all the curDailyCost when strpickupDesc = "B".

i've tried a few variations

MnthCost: Sum(IIf(Abs(inStr([Val],"B",[curDailyCost],0)) but this didn't work

Anyone have any ideas

 

[dhookom]

Try something like:

MnthCost: Sum(Abs(inStr([Val],"B")>0)*[curDailyCost])

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Thanks again for your help Duane, i tried your suggestion but it's bringing up the error, the column of the query is set to:

Field: MnthCost: Sum(Abs(inStr([Val],"B")>0)*[curDailyCost])
Table: blank
Total: Expression
Crosstab: Row Heading

with all the above i get the following message:

"Subqueries cannot be used in the expression (Abs(InStr(& & &Abs() & & & & & & & & &, "B")>0)*[curDailyCost])"

i tried changing it to MnthCost: (Abs(Sum(InStr([Val],"B")>0)*[curDailyCost])) but then i get

"You tried to execute a query that does not include the specified expression 'Abs(Sum(InStr(& chr(13) & chr(10) & Abs() & Chr(13) & Chr(10) & & Chr(13) & Chr(10) & & Chr(13) & Chr(10) &,"B")>0*[curDailyCost])"

I even thought about trying to create another seperate query and linking it but i'm sure we're nearly close with the above