Totals in a query help please

[leeg]

Help please. Apologies but I am poor in access and programming :o) and am having trouble getting my head around this one!!...again!!!

I need to have a query or report to flag up someone who has been absent for over 8 days + in a 12 month period.

the relationship between tables is staffID. My sub table stores the absent occurences against my main Staff Names table. Obviously staff can have one or more occurences of being absent.

In my query I have a "dateStart" field which I have an expression which says > date -365 which filters my query to pull records within the last 12 months, so that's fine. I also have an expression field which tells me the number of days they've been absent =[enddate]-[startdate], so that's fine.

How on earth do I get to filter the query to show >8 days within last 12 months for one or more members of staff? If I do an expression of >8 it will ignore anyhting under 8 days although a staff member may have several occurences totalling 8 days...which is what I need to calculate and query.

I don't care how it's done, i just need help before i go maaaaaaaad!

Does this make sense? I would be extremely grateful of any comments on this (polite ones of course :o)

Regards, Graeme

[Rabbit]

Add a Count to your query grouping by Staff.

[leeg]

Thanks, but can you elaborate a bit more? I have looked at all those options but count seems to add a 1 to everything?

REgards

Graeme

[Rabbit]

Sorry, forgot each occurence can be more than 1 day of absense, you'll want to sum the amount of days absent and group by their name.

[leeg]

THanks a lot. I said I was thick...

Do this in a report or query?

If it's in a report, how do I create a filter to filter data for over 8 days only for all staff?

MAny thanks in advance

Graeme

[Rabbit]

You do it in a query, the same query that your report is based on. Click on View > Totals and it'll bring up the new options you need.

[NeoPa]

THanks a lot. I said I was thick...

Do this in a report or query?

If it's in a report, how do I create a filter to filter data for over 8 days only for all staff?

MAny thanks in advance

Graeme

You can do this in one of three ways :

1. Pass a WhereCondition parameter to the report on open when using DoCmd.OpenReport().
2. Change the WHERE clause in your query.
3. Edit the report itself to add a Filter condition (Property of the report object).