Dynamic Crosstab

[spartansFC]

Hi

So with Duane's previous help and his "recurring.mdb" database, i've been able to populate a table from a "start date to end date" to show a child's attendance on a daily basis at a certain childcare provider, the table design is:

tblOccupancyBuildNewPerm
lngOccupancyPermID
dteSessionDates
lngOccupancyMonthYear
lngChildID
lngClubsID
lngDailySession (this is just a 1, which will be used in a count)
lngDailySessionActive (can be either a "A", "X" to represent if a child is active etc)

I've even been able to create a crosstab query based off this table:

TRANSFORM Count(qrySessionAvailability.lngChildID) AS CountOflngChildID
SELECT qrySessionAvailability.lngChildID, qrySessionAvailability.dteChildDOB, qrySessionAvailability.ChildAge2, qrySessionAvailability.strGenderDesc, qrySessionAvailability.FullName, qrySessionAvailability.strClubName, Count(qrySessionAvailability.lngChildID) AS [Total Of lngChildID]
FROM qrySessionAvailability
GROUP BY qrySessionAvailability.lngChildID, qrySessionAvailability.dteChildDOB, qrySessionAvailability.ChildAge2, qrySessionAvailability.strGenderDesc, qrySessionAvailability.FullName, qrySessionAvailability.strClubName
PIVOT qrySessionAvailability.dtedays;

but my problem is, the user needs to be able to select a month and club name via 2 combo boxes and the column headings, data needs to change accordingly to show each weekday for that month. I've seen another of Duane's amazing example databases "DynamSubFrm.mdb" so i guess i need to see about changing the code in that, my question are:

1. Can i have column totals for each day of each month?
2. Does the crosstab subform as in dynamSubFrm.mdb need to be in the datasheet format or can it be a continuous form?
3. Can i create different column totals for each day .ie. Number of male children, number of male chidlren under 8, number of female children..... (i know i can as i've managed it on other forms, i'm just not sure if it's possible on a dynamic crosstab form.
4. I need to link the child crosstab with a staff rota crosstab and somehow link the days together which i can do via the child and master links i believe. Say for example on 18 July there are 10 under 9 children, and there is 1 staff member working that day, the ratio for staff is 1 staff member per 8 under 8's, so i need to somehow do a calculation per day to see if there is enough cover

I think i'm reaching my limitations here, this is by far the most complicated form i've been asked to design.

Can anyone help

Michael

 

[dhookom]

Michael,
The DynamSubFrm.mdb example takes advantage of datasheet views only.

What is the data type of dteDays? Is this a date field?

Can you use a report? Have you looked at my FAQ in the reports forum about dynamic monthly crosstabs? I know you can change the month interval to days. I haven't given much thought to presenting the information in a form but I expect it's possible.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

dteDays is made from dteSessionDates (which is a date field) in qrySessionAvailability, i've created a query to pull though all the data, then created a crosstab query "qrySessionAvailability_Crosstab" based of that query.

so in qrySessionAvailability_Crosstab i have in one of the columns

dtedays: Format([dteSessionDates],"dd")

the reason why i've done that is because hopefully the user will select the month and i want the column headings to just show the days, ie. "01, 02, 03, 04...."

I will probably need to print out the crosstab but i need to have it as a form first, I have looked at your dynamic monthly crosstab report, i'll have to have a better look i guess, i know it's possible to do what i want, just need to work it out.

Michael