YZlat
asked on
Access Query
I have a table with three fields:
RecordID - Auto number
FileNumber - Numeric String
Validated - Boolean
I need to write a query to select the count of file numbers that were not validated i.e where Validated=False.
The trick is that there are could be multiple records for the same file number. And if a file number has a record where Validated=True it does not need to be counted. For example:
RecordID FileNumber Validated
1 123 True
2 123 False
3 123 True
4 456 False
5 678 False
6 678 False
The query should return 2
RecordID - Auto number
FileNumber - Numeric String
Validated - Boolean
I need to write a query to select the count of file numbers that were not validated i.e where Validated=False.
The trick is that there are could be multiple records for the same file number. And if a file number has a record where Validated=True it does not need to be counted. For example:
RecordID FileNumber Validated
1 123 True
2 123 False
3 123 True
4 456 False
5 678 False
6 678 False
The query should return 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT * FROM yourtablename WHERE [FileNumber] = TRUE
im crap. forget my comment. I had a brain attack
select count(FileNumber) as MyFileNumber from table1 WHERE FileNumber = TRUE and Validate=FALSE
ASKER
arcross, que pasa contigo?:)
rockiroads,
filenumber needs to be unique, so I changed your query to:
select count(FileNumber) from xxx a where validated=false and FileNumber not in (select distinct FileNumber from xxx where xxx.filenumber = a.filenumber and validated=true)
rockiroads,
filenumber needs to be unique, so I changed your query to:
select count(FileNumber) from xxx a where validated=false and FileNumber not in (select distinct FileNumber from xxx where xxx.filenumber = a.filenumber and validated=true)
jaja veo que controlas español YZlat
onions, thats the only spanish I know (sorry bad joke)
and one other line, which a mexican friend of mine told me
chupa me ver..... thats enough now
sorry people, my spanish aint that good
and one other line, which a mexican friend of mine told me
chupa me ver..... thats enough now
sorry people, my spanish aint that good
jajaja was he your real friend?
hmmm, or maybe (HE) was (SHE) what it makes the difference ;)
hmmm, or maybe (HE) was (SHE) what it makes the difference ;)
ASKER
yo creo que no:)
his name was Ernie, I know him when I used to stay in Banning in California (at school) one of the first guys I got to know when I started school there
he did tell me the meaning and also another slight variation to it, but thats it
he did tell me the meaning and also another slight variation to it, but thats it
ASKER
that's how I learned:)