Summerising by date calculated fields

[markweb]

Hi I have created a simple booking DB, i am try to summarize the booking by centre and date so that when a user checks if there is availability the will be able to see how many have booked an activity and how many spaces are left, i have created a new field that calculates the spare places

Spaces: IIf([Totalbookings]=[MaxPeople]"Full"[MaxPeople]-[TotalBookings])


I have also added a link to the main file

filedropper.com/ddc3-final


Help is really appreciated, this has been driving me nuts for three days

Mark

 

[plog]

When posting an issue, you need to tell us what is going wrong: Error message? Unexpected data being returned? Wife being unfaithful when you execute the code? Give us more insight to what makes you think you have an issue.

With that said, you've made an invalid IIF statement. Check out this link for how it needs to be set up: http://www.techonthenet.com/access/functions/advanced/iif.php

Hint: You're missing some commas.

 

[markweb]

Hi, thankyou for replying, I am trying summarise the availability report by centre and date, so for arguments sake all all the zumba bookings that are booked for the same date are totaled up and then taken away from the available spaces on the activity and when the finaly reach the maximun ammount allowed, i want it show 'full' , what is happening is each booking is listed separate and is taking 1 off the maximun ammount as it should, what i want all the bookings for a particular date to be summerised so that all the bookings are totaled up and then taken away from one maximun figure.

this is how it is:

Centre: Worcester
Activity

Id title date total maximun spaces

10 GroupCycling 01/06/2013 1 10 9
10 GroupCycling 01/06/2013 1 10 9
10 GroupCycling 01/06/2013 1 10 9
10 GroupCycling 01/06/2013 1 10 9
10 GroupCycling 01/06/2013 1 10 9
10 GroupCycling 01/06/2013 1 10 9

this is how i would like it to show in the report

Id title date total maximun spaces

10 GroupCycling 01/06/2013 6 10 4


I have grouped by centre and the activity, checked the sum box for total, i have tried going into the query created by the report and tried summing up that way as well and without checking the box to sum up the totals when creating the query but the shape changes of the report but the same data shows.

I have also changed the Spaces field calculation to commas, it calculates in exactly the same way and but still no summing up, help is really appreiated

Mark

 

[plog]

First, 'date' is a poor choice for a field name because its a reserved word which will give you trouble when writing queries or code that uses it. You should rename it by adding a prefix to it that helps describe what the date is for (e.g. 'ActivityDate').

It seems you've changed the issue. If you want me to help generate the data you want I can do that, but you initially posted a question about an IIF statement. Your IIf statement was not properly formed.

If you need help writing an entire query I'll need 2 distinct sets of data--starting sample data (including table and field names) and then I will need what your query should produce based on that starting sample data. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
Sally, 13, 4/30/2012
David 3, 5/12/2009
Terry, 45, 7/7/2008

 

[sumox]

your IIF statm shu look like this :
=IIf([TotalBookings]=[MaxPeople],0,[MaxPeople]-[TotalBookings])

but if you're looking for Sorting/Grouping on Fields
Search AWF or Google for "Grouping Query" words like that

And thanks for reading.

 

[stopher]

The problem you have is you have to much detail being fed to your report. So you need to refine your source query a bit more. In fact if you have a look at your CheckFull query and remove the MemberID field completely you will see that the query produces almost exactly the results you want i.e. summarised by activity/date. In the same query if you show CentreName (which is currently set to hidden) then this query has everything your report needs so base your report on this (you may want to save it as a new query if you are using CheckFull elsewhere)

hth
Chris