By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Totals in a query help please

P: 14
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
Mar 13 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,324
Add a Count to your query grouping by Staff.
Mar 13 '07 #2

P: 14
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
Mar 13 '07 #3

Rabbit
Expert Mod 10K+
P: 12,324
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.
Mar 13 '07 #4

P: 14
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
Mar 14 '07 #5

Rabbit
Expert Mod 10K+
P: 12,324
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.
Mar 14 '07 #6

NeoPa
Expert Mod 15k+
P: 31,266
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).
Mar 15 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.