473,323 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Using Count within a Cross Tab Query

119 100+
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:

Expand|Select|Wrap|Line Numbers
  1. Date, Name1, Name2, Name3, Name4
  2. 1/1/2000, -1, -1, 0, 1
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. Date, Members, Name1, Name2, Name3, Name4
  2. 1/1/2000, 3, -1, -1, 0, 1
  3. 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.

Expand|Select|Wrap|Line Numbers
  1. Field: Members: Count([Value]<>0)
  2. Table: <this is blank>
  3. Total: Expression
  4. Crosstab: Row Heading
  5. Criteria: <this is blank>
But this always returns the count of all of the names. So in the above example I get the following:

Expand|Select|Wrap|Line Numbers
  1. Date, Members, Name1, Name2, Name3, Name4
  2.  1/1/2000, 4, -1, -1, 0, 1
  3.  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.
Jan 31 '09 #1
5 6975
FishVal
2,653 Expert 2GB
Try to use Sum(Abs(<.. fieldname ..>)) instead of Count(<.. fieldname ..>).
Or write a custom aggregate function like in that thread.

Regards,
Fish.
Jan 31 '09 #2
billelev
119 100+
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.
Feb 2 '09 #3
FishVal
2,653 Expert 2GB
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.
Feb 2 '09 #4
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.
Oct 17 '10 #5
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.
Jul 3 '19 #6

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

Similar topics

2
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...
11
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...
6
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...
2
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...
3
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...
1
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...
1
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...
3
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...
1
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
0
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...
1
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....
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.