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 6975
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.
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
| |