I made a report concept from a simple query that contain as follow - SELECT [Table1].[Date], [Table1].Reason
-
FROM [Table1]
-
GROUP BY [Table1].[Date], [Table1].Reason
-
HAVING ((([Table1].[Date])>=[FirstDate] And ([Table1].[Date])<=[LastDate]) AND (([Table1].Reason) Is Not Null));
When I click the view button, the query shows the date and the reasons record. Well, I assume the query works well.
Now, I want to make a report using that query, but is not a regular kind of report, I wanted to be like this : - No Date On Leave Sick Present Business Trip
-
1 October'09 1 4 50 5
-
2 November'09 2 10 48 0
To get the Reason value, I enter this to it control source =Count(IIf([Reason]="On Leave",0)), that also for all 4 kind of reason type.
The strange is for the other report using this model I have no problem, but somehow for this one it came with warning like this :
"
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
Please help me on this one. I try to remake the report that's work but still not working for this 2 table.
1 2450
Hi
To achieve what you have illustrated I think something like this should do it - SELECT Format([Table1].[Date],"mmm yy") as [Date], Sum(IIF([Reason]="On Leave",1,0) as [On Leave], Sum(IIF([Reason]="Sick",1,0) as [Sick], Sum(IIF([Reason]="Present",1,0) as [Present], Sum(IIF([Reason]="Business Trip",1,0) as [Business Trip]
-
FROM [Table1]
-
GROUP BY Format([Table1].[Date],"mmm yy")
-
HAVING ((([Table1].[Date])>=[FirstDate] And ([Table1].[Date])<=[LastDate]) AND (([Table1].Reason) Is Not Null));
??
I would suggest (if you havn't done it this way) is to have a Lookup table containing the 'Reasons' for the absences and create a relationship with Table1 based on the Reason_ID (long integer) contained in the new table. This would change the query to look something like this. - SELECT Format([Table1].[Date],"mmm yy") as [Date], Sum(IIF([Reason_ID]=1,1,0) as [On Leave], Sum(IIF([Reason_ID]=2,1,0) as [Sick], Sum(IIF([Reason_ID]=3,1,0) as [Present], Sum(IIF([Reason_ID]=4,1,0) as [Business Trip]
-
FROM [Table1]
-
GROUP BY Format([Table1].[Date],"mmm yy")
-
HAVING ((([Table1].[Date])>=[FirstDate] And ([Table1].[Date])<=[LastDate]) AND (([Table1].Reason_D) Is Not Null));
The other thing is that it is a VERY bad idea to use "Date" as a field name (or any other keyword). Believe me; this advice is based on experience (having ignored vague recollection of this advice previously!!!).
MTB
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: mark |
last post by:
How do I get all fields on one page of a report?
I have a report that has a column for each day of the week and 6 records for
each day. I need each weekday's records returned on only one detail page.
Instead I am getting a new table layout for each day of the week. I have tried
grouping on every record and combination I can, manipulating the Group
Properties but can't get it right. I have the entire report in the Detail
Section, with...
|
by: Paul |
last post by:
I have a query with a numeric field called BALBOOK. I have a form
(we'll call it Form1) that runs this query. On this form is a text
box (we'll call it Text1). When the query is run, I want is to use
all the records from a file where BALBOOK is equal to Text1. However,
if Text1 is blank (or null) I want the query to use all the records.
I don't know how to write the IIf statement. It would look something
like this:
...
|
by: Terri |
last post by:
I have an A2K database with linked tables to SQL Server 2000.
I am trying to use an iif statement in a query.
IIf(!! Is Null,
"*",!!)
This will return records when !! is not null
but will not return any records when (!! is
null. I want to return all records when (!!
|
by: Sheldon |
last post by:
I have a query (query1) that contains a number of result fields. One
of those fields (field1), can contain any or all of the following
values: 1, 2, 3, 4, 5, 6, 7 or 8. I also have a field (field2) in a
one record table that is one of tables I'm linking in query1. If the
value in field2 equals 1 or 3 or 5 or 7 then I only want to show the
records in query1 that have a 1 (or 3 or 5 or 7) in field1. If field2
equals 2 or 4 or 6 or 8,...
|
by: Michael R |
last post by:
Hello all.
I'm currently writting a query that would be able to filter a table by a form's control value. If the value is null, the query should select all the rows of the table.
The condition I came up with is:
IIf(IsNull(!!),Like "*",!!)
It filters the data correctly when Forms!printqry!farea has a value, howerever it doesn't choose anything when Forms!printqry!farea value is null.
What is the correct syntax to do it?
| |
by: Bernice J |
last post by:
I'm trying to count the number of records in a table with the field STATUS that have a Y value. I'm using the expression "=Count(IIf(="Y",1,0))" . It counts all the values not just the Y's. Any ideas where I'm going wrong? The field has text values in the table.
I'm using this expression in a textbox in a report.
|
by: plaguna |
last post by:
I’m creating a Microsoft Access Report of 6 different questions with “Yes” and “No” answers. I have no problem to count the Yes and Nos with the following formulas for each question in the Report Footer:
=Count(IIf(=”YES”,0))
=Count(IIf(=”NO”,0))
My question is: how can I get a percentage of NOs for each question? I tried to create a new field (Total of Yes) and (Total of No) in my query with the above formulas, that way I can manipulate...
|
by: cwby1966 |
last post by:
Hi
I have a form that has a sub form. Want to add a text box that would didplay a message depending on different cirteria. The sub form list items to be followed up on, so if there was an item that was past due and not complete I want to diplay a message that there are past due items, same if there was an item that was due in the next 30 days.
I have tried creating a text feild with a control coure using dcount but had not luck. Can anyone...
|
by: RonnieG |
last post by:
I'm trying to create a function in a query but not sure how or if it's the most effecient way. Here is what I'm wanting to do:
If the = Yes and the > use the , if the is not null and the > use the , if the is null and the > use the , if the is null use the , if the is null use the , if the and the is null use the greater of the two.
Here is what I've tried but it's not pulling in the correct info:
Event Date: IIf(="Prosp" And
...
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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.
| |