I have a cross tab query with Date as a row heading and a series of Names as column headings. The Value for each Date/Name intersection can either be -1, 0 or 1.
For example: - Date, Name1, Name2, Name3, Name4
-
1/1/2000, -1, -1, 0, 1
-
1/2/2000, 1, 1, 0, 0
I would like to have a column ("Members") that counts the non-zero values for each row. This would give something like the following: - Date, Members, Name1, Name2, Name3, Name4
-
1/1/2000, 3, -1, -1, 0, 1
-
1/2/2000, 2, 1, 1, 0, 0
I am having trouble using the count function to achieve this. In the cross tab query builder I am defining the following, where "Value" is the field that holds either a -1, 0 or 1. - Field: Members: Count([Value]<>0)
-
Table: <this is blank>
-
Total: Expression
-
Crosstab: Row Heading
-
Criteria: <this is blank>
But this always returns the count of all of the names. So in the above example I get the following: - Date, Members, Name1, Name2, Name3, Name4
-
1/1/2000, 4, -1, -1, 0, 1
-
1/2/2000, 4, 1, 1, 0, 0
as there are four names. For the first row it would ideally return 3, followed by 2 in the second row.
I would appreciate any help you all can give me on this one.
5  7028  FishVal 2,653
Recognized Expert Specialist
Try to use Sum(Abs(<.. fieldname ..>)) instead of Count(<.. fieldname ..>).
Or write a custom aggregate function like in that thread.
Regards,
Fish.
Thanks - that's a great solution. I guess I would have to write a custom function if it generated values other than -1 and 1, though.
FishVal 2,653
Recognized Expert Specialist
I think it is not yet a case to write a custom function.
You can: - Use an intermediate query to replace zeroes with Nulls and then perform the crosstab query.
- Use Sgn() function which returns -1 for negative, 0 for zero and +1 for positive numbers.
I want something similar, but the contents of the fields within the row are either null or not null. I want to count the number of cells in the row that are not null.
In your query if you do:
Field: Name: iif([Names]=0,Null,[Names])
Total: Count
Crosstab: Row Heading
Just did this on my Crosstab query and it works.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Paxton |
last post by:
Hi,
I'm trying to display the total page views per page within a given date
range, but the correct SQL is seemingly beyond me. I get the correct result
with a straightforward Group By and Count clause eg
SELECT DISTINCT tblPageViews.PageVisited,
Count(tblPageViews.PageVisited) AS CountOfPageVisited
FROM tblPageViews
GROUP BY tblPageViews.PageVisited;
|
by: randi_clausen |
last post by:
Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!
|
by: RCS |
last post by:
I've been running into more and more complexity with an application, because
as time goes on - we need more and more high-level, rolled-up information.
And so I've created views, and views that use other views.. and the queries
are getting slower and slower.
This morning, I'm working on something like this:
select
<some columns>,
"calculatedcolumn" = (select top 1 crap from stuff where
|
by: Tim Pollard |
last post by:
Hi
I'm hoping someone can help me with an access problem I just can't get
my head around. I normally use access as a back end for asp pages,
just to hold data in tables, so queries within access are a mystery to
me, but I can't think of any other way of dealing with the problem.
I have six tables in my db:
tblCompanies (list of companies, primary key CompanyID)
tblOffices (list of office buildings including what company owns/uses
|
by: amanda |
last post by:
Hope someone can help me with this - I've been staring at it stupidly
for hours now, convinced there must be an easy way to achieve the
results I want:
I have a very large table recording every sale made by one of 90
salespeople (2 years of data), and the date the sale was made. I need
a report that shows a list of the salespeople down the left hand side
and then for each month the total number of sales and the total value
of sales. I...
| | |
by: Aaron West |
last post by:
Try this script to see what queries are taking over a second.
To get some real output, you need a long-running query. Here's one
(estimated to take over an hour):
PRINT GETDATE()
select count_big(*)
from sys.objects s1, sys.objects s2, sys.objects s3,
sys.objects s4, sys.objects s5
PRINT GETDATE()
|
by: Rob Woodworth |
last post by:
Hi,
I'm having serious problems getting my report to work. I need to
generate a timesheet report which will contain info for one employee between
certain dates (one week's worth of dates). I have a table containing
records for each job done, the records contain date, employee name, job done
(a code representing the type of job), cost code (another code), regular
hours, and overtime hours. The tricky part is that more than one job can...
|
by: davidwelli |
last post by:
Hello,
All help is appreciated, I'm struggling with the following.
Using Access 2003 (front end) connecting to Oracle 7 database (back end). I'm trying to create a query in access that will show how many instances of something happens.
Within the query I am using a calculated filed called "registered".The syntax is
Registered: DateDiff("w",,)
|
by: ramprat |
last post by:
Hi,
I'm trying to use the results from a select query as a way to limit the records that I update with my update query and it seems like it should work but I keep getting a "Operation must use an updateable query" error. Essentially the entire update query works fine until I add the select query as a way to limit the records. If I replace the select query with an actual table of results from the select query it works fine but I prefer the...
|
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: 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: 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: 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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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.
| |
