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
6  1766  Rabbit 12,516
Recognized Expert Moderator MVP
Add a Count to your query grouping by Staff.
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 12,516
Recognized Expert Moderator MVP
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.
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 12,516
Recognized Expert Moderator MVP
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 32,568
Recognized Expert Moderator MVP
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 : - Pass a WhereCondition parameter to the report on open when using DoCmd.OpenRepor t().
- Change the WHERE clause in your query.
- Edit the report itself to add a Filter condition (Property of the report object).
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: deko |
last post by:
I have a number of queries that pull totals from different tables. How do I
sum the different total values from each query to get a grand total? I
tried using a Union query like this:
SELECT cTotal As bct FROM qryCbank1
UNION SELECT cTotal As bct FROM qryCbank2
UNION SELECT cTotal As bct FROM qryCbank3
UNION SELECT cTotal As bct FROM qryCbank4
UNION SELECT cTotal As bct FROM qryCbank5
UNION SELECT cTotal As bct FROM qryCbank6;
|
by: New Guy |
last post by:
I'm trying to work with a system that somebody else built and I am
confounded by the following problem:
There is a table of payments and a table of charges. Each client has
charges and payments during the month.
I'd like to get the totals of the payments and of the charges for each
client. When I run the following query, I get huge numbers that
appear as if the join is not working correctly.
|
by: Joost |
last post by:
I have a form based on a totals query, with one of the fields as the
sum of the quantity of an article of the grouped records. I get an
#error# in a control on the form where I want to display the sum of
this sum. TxtExample =sum(SumOfQuantity).
I understand that I can't sum a calculated control but I would argue
this is not a calculated control. I have been wrong before though....
Any help would be very much apprciated, thanks in advance
|
by: BerkshireGuy |
last post by:
I have the following code:
Dim strSQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined,
intNumOfWasted, intNumOfApproved As String
Dim QDF As QueryDef
Dim PARAM As Parameter
|
by: brm6546545 |
last post by:
I have a tblTax. It has fields InvoiceNum, TaxAuthority, TaxableTotal,
NonTaxableTotal, TaxCollected.
Sample data
1,county,10.00,0.00,0.40
1,city,10.00,0.00,0.10
2,state,0.00,15.00,0.15
When totaling invoice 1 should have totals of 10.00,0.00,0.50 because
| | |
by: MikeSA |
last post by:
Hi
I a trying to create a chart that reflects monthly cumulative totals from a query.
The query fields show sales opportunities forecasted invoice date (OppForInvDate), Opportunity Description and Estimated Value of the opportunity (OppEstVal). All I want is to chart, cumulatively for each month, the totals for the month?
Why is it so difficult, or is just me?
Mike
|
by: JJM0926 |
last post by:
I'm trying to create a running totals query in access 97. I have
followed the directions on how to do it from Microsofts website
article id 138911. I took their code they had and replaced it with my
fields. When I try to run it I get #errors in my RunTot column. I'm
kinda new to this. Not sure if maybe I mistyped something wrong or is
there a better way to do this? I have pasted the code. Any help would
be greatly appreciated....
|
by: Micheal |
last post by:
Greetings Access Group,
Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days (total 10 hours) and I have yet to resolve it. please excuse me for my lack of terminology, I will try to provide you with the best of information.
I currently have a report that contains 2 totals and the percentage of the difference of the 2...
|
by: elias.farah |
last post by:
Hello Everyone,
I'm having some very weird behavior on a couple of Access forms. (Not
all forms, just some of them).
The forms have been working for years, under Access XP/2003 etc, and
last week upgraded from Windows XP/Office 2003 to Vista x64/Office
2007.
Under Access 2007, a couple of forms are now taking 60 seconds to
|
by: colin spalding |
last post by:
How do I design a totals query that instead of the following result
YearOfAcc PremiumGBP
2004 9,142,306.95
2004 1,481,153.21
2005 11,981,987.85
2006 20,653,195.20
2006 -521,140.08
2006 -455,788.20
2006 -212,507.35
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
