Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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
0
YZlat
Asked:
YZlat
  • 5
  • 3
  • 3
1 Solution
 
rockiroadsCommented:
say your table is called xxx

select count(FileNumber) from xxx a where validated=false and recordid not in (select recordid from xxx where xxx.filenumber = a.filenumber and validated=true)


0
 
arcrossCommented:
SELECT * FROM yourtablename WHERE [FileNumber] = TRUE
0
 
arcrossCommented:
im crap. forget my comment. I had a brain attack
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arcrossCommented:
select  count(FileNumber) as  MyFileNumber from table1 WHERE FileNumber = TRUE and Validate=FALSE
0
 
YZlatAuthor Commented:
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)



0
 
arcrossCommented:
jaja veo que controlas espaƱol YZlat
0
 
rockiroadsCommented:
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
0
 
arcrossCommented:
jajaja was he your real friend?

hmmm, or maybe (HE) was (SHE) what it makes the difference ;)
0
 
YZlatAuthor Commented:
yo creo que no:)
0
 
rockiroadsCommented:
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

0
 
YZlatAuthor Commented:
that's how I learned:)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now