Totals only from calculated fields
I'm a bit stumped, but will continue trying to figure this out. If you have a solution, please post (I am always willing to learn).
I have a database of clients with a field "First date" consisting of month, day, year.
There are also fields "Number of Days", and "MealType". The number of days is from 1 to 5, the meal type is "regular", "vegetarian" or "other". What I am needing is a report recapping the meals needed each day:
Day 1 Day 2 Day 3 Day 4 Day 5
Regular 4 5 7 7 3
Veg 3 3 5 5 5
Other 1 1
So, my current thought is to create a temporary table from the existing, breaking each member's request into something like
memberID, day 1, day 2, day 3, day 4, day 5
Where day # is the choice they made, or nil if they are not included in the date and number of days range. Say member 1 is coming the 2nd day, for two days, and selects "other". That member would result in the temporary row of
memberID, , "other", "other", ,
which would result in the OTHER total count listed above. To print the recap total only, would I use the tfrUserDataSource, or would I need to do an SQL Query against the temporary table? If so, what would the <psuedo> code look like from a button click to set this up? Sorry for asking so much, but I'm stumped right now.
Thanks.
I have a database of clients with a field "First date" consisting of month, day, year.
There are also fields "Number of Days", and "MealType". The number of days is from 1 to 5, the meal type is "regular", "vegetarian" or "other". What I am needing is a report recapping the meals needed each day:
Day 1 Day 2 Day 3 Day 4 Day 5
Regular 4 5 7 7 3
Veg 3 3 5 5 5
Other 1 1
So, my current thought is to create a temporary table from the existing, breaking each member's request into something like
memberID, day 1, day 2, day 3, day 4, day 5
Where day # is the choice they made, or nil if they are not included in the date and number of days range. Say member 1 is coming the 2nd day, for two days, and selects "other". That member would result in the temporary row of
memberID, , "other", "other", ,
which would result in the OTHER total count listed above. To print the recap total only, would I use the tfrUserDataSource, or would I need to do an SQL Query against the temporary table? If so, what would the <psuedo> code look like from a button click to set this up? Sorry for asking so much, but I'm stumped right now.
Thanks.
Comments
look at the 2crosstab report in frdir\demos\reports demo project
apply a filter to your table or use a query to select records for the period to summarize and use a crosstab component.
regards