UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Count Negative Numbers Is A Query, Access 2016    
 
   
aligator510
post Jun 13 2018, 03:15 AM
Post#1



Posts: 63
Joined: 23-August 17



Hi everyone,

I have a query like this below:

ID (OCTOBER) (NOVEMBER) (DECEMBER)
1 -1 -100 -2
2 3 0 -1
3 -1 -6 15
with positive and negative numbers

I want to count negative numbers for every row and have result similiar like table below:

ID (OCTOBER) (NOVEMBER) (DECEMBER) (COUNT OF NEGATIVE NUMBERS)
1 -1 -100 -2 3
2 3 0 -1 1
3 -1 -6 15 2

Any help would to make this work would be great.
(Please find attached photos of version one and desired result)

Thanks!
This post has been edited by aligator510: Jun 13 2018, 03:18 AM
Attached File(s)
Attached File  ver1.PNG ( 3.21K )Number of downloads: 1
Attached File  ver2.PNG ( 3.5K )Number of downloads: 2
 
Go to the top of the page
 
BruceM
post Jun 13 2018, 07:16 AM
Post#2


UtterAccess VIP
Posts: 7,683
Joined: 24-May 10
From: Downeast Maine


Is that a crosstab query, or do you have a table with fields named October, etc., and a Select query based on that table?
Go to the top of the page
 
Jeff B.
post Jun 13 2018, 07:18 AM
Post#3


UtterAccess VIP
Posts: 10,140
Joined: 30-April 10
From: Pacific NorthWet


You posted in an Access-related forum. The image you provided suggests that you're using a spreadsheet. If you are using MS Access, you (& Access) will have to work hard to apply Access' relationally-oriented functions and features to 'sheet-oriented data.
Go to the top of the page
 
aligator510
post Jun 13 2018, 08:02 AM
Post#4



Posts: 63
Joined: 23-August 17



This is a table with with fields named October, etc.
Images are just samples of what I have and what results I need.(And are not spreadsheet)
This post has been edited by aligator510: Jun 13 2018, 08:04 AM
Go to the top of the page
 
BruceM
post Jun 13 2018, 09:12 AM
Post#5


UtterAccess VIP
Posts: 7,683
Joined: 24-May 10
From: Downeast Maine


The point about spreadsheet is that including data (in this case the month) in a column (field) name is spreadsheet construction.

The functionality of Access cannot be of much help here. The only thing that occurs to me is something like this:

NegCount: IIf([October] < 0,1,0) + IIf([November] < 0,1,0)...etc.

If the data were in individual records you could construct a crosstab query to show each month. I don't use crosstab queries often, so cannot offhand describe how to obtain the count, but if you decide to go that route I expect somebody with more experience than I will weigh in on that detail.
Go to the top of the page
 
Daniel_Stokley
post Jun 13 2018, 09:35 AM
Post#6



Posts: 273
Joined: 22-December 14
From: Grand Junction, CO, USA


Please take a look at this image and SQL.
Attached File  NegCountCrossTab.gif ( 18.65K )Number of downloads: 5

Attached File  NetCountQuery.gif ( 12.43K )Number of downloads: 2

SQL
TRANSFORM First(tbl_IDMonthValues.[lngValue]) AS FirstOflngValue
SELECT tbl_IDMonthValues.[lngID], Sum(IIf([lngValue]<0,1,0)) AS [Neg Count]
FROM tbl_IDMonthValues GROUP BY tbl_IDMonthValues.[lngID]
PIVOT tbl_IDMonthValues.[txtMonth];


Is that what you want?
Go to the top of the page
 
aligator510
post Jun 14 2018, 02:30 AM
Post#7



Posts: 63
Joined: 23-August 17



Hi again,

I have the impression that we are not understood.
Please look images below.
There is a table in datasheet and design view.

I tried the formula in the query ( countNegative: Sum(IIf([May13]<0,1,0)+IIf([June13]<0,1,0)) )
But I am geting error message like in the image below.

All I need is to count just negatative numbers for every ID.


Attached File(s)
Attached File  tblmonths.PNG ( 86.14K )Number of downloads: 6
Attached File  tblmonths_design_view.PNG ( 11.4K )Number of downloads: 0
Attached File  tblMonth_Query.PNG ( 12.63K )Number of downloads: 4
Attached File  Query_Error.PNG ( 15.97K )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Jun 14 2018, 06:04 AM
Post#8


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but just remove Sum from the calculation. Sum is used in a Totals query (grouping), not in a regular query. The components will add together simply by using the + sign.

And you really need to reevaluate your table design. We call what you have done "committing spreadsheet" and is not appropriate db design, as others have indicated, leading to difficulties.

Think tall and thin tables, not short and wide. And no content data in field names (such as dates).

HTH
Joe
Go to the top of the page
 
Jeff B.
post Jun 14 2018, 07:47 AM
Post#9


UtterAccess VIP
Posts: 10,140
Joined: 30-April 10
From: Pacific NorthWet


I believe the point several responders are making is that your table structure, with "repeating fields" (i.e., Month1, Month2, ...) is NOT well-normalized. What are you going to do when Month99 comes along? With your table design, you will need to add a new field, modify any queries that inspect fields, and any forms that use fields, and any reports that use fields, and any macros that ... get the idea?

A more normalized design would have a single field to hold "month", and a second field to hold the related value for that month.
Go to the top of the page
 
BruceM
post Jun 14 2018, 07:57 AM
Post#10


UtterAccess VIP
Posts: 7,683
Joined: 24-May 10
From: Downeast Maine


Maybe like this:

CODE
tblMain
  MainID (autonumber PK)
  NIPTI
  KODI

tblMonthData
  MonthDataID (autonumber PK)
  MD_MainID  (linking field to tblMain)
  MonthNumber
  YearNumber

Maybe there is a specific date instead of MonthNumber and YearNumber, which would be fine as an alternative. With the month number and the year you can use DateSerial to assemble a date (the first of the month, for instance), and so get everything sorted properly.

Without knowing more about the data this is vague, but describes the general idea, I think.
Go to the top of the page
 
aligator510
post Jun 14 2018, 08:47 AM
Post#11



Posts: 63
Joined: 23-August 17



The number in monthname represent the year value.
You think is better to have two separated tables one for the months and one for the years values?

Anyway now is solved. The problem was Sum used in a query.

Thanks !!!
Go to the top of the page
 
RJD
post Jun 14 2018, 08:57 AM
Post#12


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


QUOTE
Anyway now is solved. The problem was Sum used in a query.

You are welcome. Glad you got that solved, however ...

QUOTE
You think is better to have two separated tables one for the months and one for the years values?

Not at all, if you really mean "tables." Please study what Bruce and Jeff are telling you about design. Good design dictates that content (such as the month name) should not be used as field names, but as content within a field.

If you mean "fields" for month and year, in a table, then you can combine these (as Bruce said), depending on what you are doing.

Regards,
Joe
Go to the top of the page
 
RJD
post Jun 14 2018, 09:26 AM
Post#13


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Thinking you might benefit from a demo, I created one using some of your data, with tables as indicated by Bruce. See the tables (with some of your data), how the negative counts are handled in the queries, and how the layout works in the crosstab. Much simpler and MUCH more flexible and easy to get the results you seem to want.

HTH
Joe
Attached File(s)
Attached File  CountNegatives.zip ( 19.89K )Number of downloads: 4
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th October 2018 - 05:33 AM