CODEWITHSUNDEEP
sqlms-accesscount
Fayyaz Ali
Fayyaz Ali

Reputation: 787

Count with muliple where conditions in ms access

I have the query below;

Select count(*) as poor
from records where deviceId='00019' and type='Poor' and timestamp between #14-Sep-2012 01:01:01# and #24-Sep-2012 01:01:01#

table is like; id. deviceId, type, timestamp

data is like;

data is like; 

1, '00019', 'Poor', '19-Sep-2012 01:01:01'
2, '00019', 'Poor', '19-Sep-2012 01:01:01'
3, '00019', 'Poor', '19-Sep-2012 01:01:01'
4, '00019', 'Poor', '19-Sep-2012 01:01:01'

i am trying to count the devices with a specific specific type.

Please help.. access always returns wrong data. it is returning 1 while 00019 has 4 entries for poor

Upvotes: 1

Views: 757

Answers (2)

HansUp
HansUp

Reputation: 97101

Type and timestamp are both reserved words, so enclose them in square brackets in your query like this: [type] and [timestamp]. I doubt those reserved words are the cause of your problem, but it's hard to predict exactly when reserved words will cause query problems, so just rule out this possibility by using the square brackets.

Beyond that, stored text values sometimes contained extra non-visible characters. Check the lengths of the stored text values to see whether any are longer than expected.

SELECT
    Len(deviceId) AS LenOfDeviceId,
    Len([type]) AS LenOfType,
    Len([timestamp]) AS LenOfTimestamp
FROM records;

In comments you mentioned spaces (ASCII value 32) in your stored values. I had been thinking we were dealing with other non-printable/invisible characters. If you have one or more actual space characters at the beginning and/or end of a stored deviceId value, the Trim() function will discard them. So this query will give you different length numbers in the two columns:

SELECT
    Len(deviceId) AS LenOfDeviceId,
    Len(Trim(deviceId)) AS LenOfDeviceId_NoSpaces
FROM records;

If the stored values can also include spaces within the string (not just at the beginning and/or end), Trim() will not remove those. In that case, you could use the Replace() function to discard all the spaces. Note however a query which uses Replace() must be run from inside an Access application session --- you can't use it from Java code.

SELECT
    Len(deviceId) AS LenOfDeviceId,
    Len(Replace(deviceId, ' ', '')) AS LenOfDeviceId_NoSpaces
FROM records;

If that query returns the same length numbers in both columns, then we are not dealing with actual space characters (ASCII value 32) ... but some other type of character(s) which look "space-like".

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

If you want to count devices with specific type irrespective of deviceids then use this:

Select count(*) as excellent
from records where type='Poor'

If you want to count devices with specific deviceid irrespective of types then use this:

Select count(*) as excellent
from records where deviceId='00019'

Upvotes: 0

Related Questions