Solved

Access Query

Posted on 2004-08-04
11
250 Views
Last Modified: 2012-05-05
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
Comment
Question by:YZlat
  • 5
  • 3
  • 3
11 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 50 total points
ID: 11716346
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
 
LVL 8

Expert Comment

by:arcross
ID: 11716359
SELECT * FROM yourtablename WHERE [FileNumber] = TRUE
0
 
LVL 8

Expert Comment

by:arcross
ID: 11716374
im crap. forget my comment. I had a brain attack
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 8

Expert Comment

by:arcross
ID: 11716490
select  count(FileNumber) as  MyFileNumber from table1 WHERE FileNumber = TRUE and Validate=FALSE
0
 
LVL 35

Author Comment

by:YZlat
ID: 11716543
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
 
LVL 8

Expert Comment

by:arcross
ID: 11716661
jaja veo que controlas español YZlat
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11716683
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
 
LVL 8

Expert Comment

by:arcross
ID: 11716781
jajaja was he your real friend?

hmmm, or maybe (HE) was (SHE) what it makes the difference ;)
0
 
LVL 35

Author Comment

by:YZlat
ID: 11717006
yo creo que no:)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11717177
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
 
LVL 35

Author Comment

by:YZlat
ID: 11718590
that's how I learned:)
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question