Need query to return absence of record

Hello all, hope everyone is doing well this fine morning!

I need some help in constructing a query.  I have three tables,
designed as so:

Personnel
    FullName(PK)
    Department

Classes
    ClassName(PK)

TrainingHistory *Junc table between Personnel and Classes*
    FullName(FK)
    ClassName(FK)
    DateTaken

TrainingHistory records every instance of a class taken by all
personnel.  I need to be able to find people who HAVE NOT taken a
given class.  I need to be able to pick what class I search for each
time I do the query.  For example, I need to know who in the
organization has NOT taken "Network Security" so that I can sign them
up.  Any help on this would be much appreciated.

TIA,
George

0
sword856
7/25/2007 2:07:28 PM
access.queries 6343 articles. 1 followers. Follow

10 Replies
1088 Views

Similar Articles

[PageSpeed] 44

sword856@yahoo.com wrote:

>Hello all, hope everyone is doing well this fine morning!
>
>I need some help in constructing a query.  I have three tables,
>designed as so:
>
>Personnel
>    FullName(PK)
>    Department
>
>Classes
>    ClassName(PK)
>
>TrainingHistory *Junc table between Personnel and Classes*
>    FullName(FK)
>    ClassName(FK)
>    DateTaken
>
>TrainingHistory records every instance of a class taken by all
>personnel.  I need to be able to find people who HAVE NOT taken a
>given class.  I need to be able to pick what class I search for each
>time I do the query.  For example, I need to know who in the
>organization has NOT taken "Network Security" so that I can sign them
>up. 


SELECT P.FullName
FROM Personnel As P LEFT JOIN TrainingHistory As T
	ON P.FullName = T.FullName
WHERE T.ClassName = [Enter Class Name]
	AND T,ClassName Is Null

-- 
Marsh
MVP [MS Access]
0
Marshall
7/25/2007 3:21:19 PM
On Jul 25, 10:21 am, Marshall Barton <marshbar...@wowway.com> wrote:
> sword...@yahoo.com wrote:
> >Hello all, hope everyone is doing well this fine morning!
>
> >I need some help in constructing a query.  I have three tables,
> >designed as so:
>
> >Personnel
> >    FullName(PK)
> >    Department
>
> >Classes
> >    ClassName(PK)
>
> >TrainingHistory *Junc table between Personnel and Classes*
> >    FullName(FK)
> >    ClassName(FK)
> >    DateTaken
>
> >TrainingHistory records every instance of a class taken by all
> >personnel.  I need to be able to find people who HAVE NOT taken a
> >given class.  I need to be able to pick what class I search for each
> >time I do the query.  For example, I need to know who in the
> >organization has NOT taken "Network Security" so that I can sign them
> >up.
>
> SELECT P.FullName
> FROM Personnel As P LEFT JOIN TrainingHistory As T
>         ON P.FullName = T.FullName
> WHERE T.ClassName = [Enter Class Name]
>         AND T,ClassName Is Null
>
> --
> Marsh
> MVP [MS Access]- Hide quoted text -
>
> - Show quoted text -

Thanks, Marsh, for replying!

I put in the SQL (except I changed T,ClassName is Null to T.Classname
is Null) just as you wrote, but the query is returning no names for
the classes I put in.  I know not everyone has taken every class, so
there should be names showing up.  The SQL makes it seem like the
query is looking for a value ([Enter Class Name]) and "Is Null" in the
same field, which seems like a contradiction.  I may not have
explained well enough about my TrainingHistory table.  Every time
someone takes a class, I enter their name, class name, and date in the
table.  My explanation earlier made it seem like I put classes in that
everyone took.  So, basically I am looking for the lack of records in
that table, i.e. records that would be [given class] and personnel
names.  I hope that clears things up, and thanks for reading this!

0
sword856
7/25/2007 4:07:33 PM
PARAMETERS [Enter ClassName] Text ( 255 );
SELECT Personnel.*, [Enter ClassName] AS [Missing Class]
FROM Personnel
WHERE Personnel.FullName Not In 
      (SELECT TrainingHistory.FullName
       FROM TrainingHistory
       WHERE TrainingHistory.ClassName In
            (SELECT Classes.ClassName
             FROM Classes
             WHERE Classes.ClassName = [Enter ClassName]));
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"sword856@yahoo.com" wrote:

> Hello all, hope everyone is doing well this fine morning!
> 
> I need some help in constructing a query.  I have three tables,
> designed as so:
> 
> Personnel
>     FullName(PK)
>     Department
> 
> Classes
>     ClassName(PK)
> 
> TrainingHistory *Junc table between Personnel and Classes*
>     FullName(FK)
>     ClassName(FK)
>     DateTaken
> 
> TrainingHistory records every instance of a class taken by all
> personnel.  I need to be able to find people who HAVE NOT taken a
> given class.  I need to be able to pick what class I search for each
> time I do the query.  For example, I need to know who in the
> organization has NOT taken "Network Security" so that I can sign them
> up.  Any help on this would be much appreciated.
> 
> TIA,
> George
> 
> 
0
Utf
7/25/2007 4:20:04 PM
Simplest query with minimal records would be the following

SELECT Personnel.FullName
FROM Personnel
WHERE FullName Not IN
(SELECT FullName
 FROM TrainingHistory
 WHERE ClassName = [What Class])

If you have a large number of records, NOT IN can be slow.  You MIGHT be 
able to use the following or it might fail.

SELECT Personnel.FullName
FROM Personnel LEFT JOIN
(SELECT FullName
 FROM TrainingHistory
 WHERE ClassName = WhatClass) as TH
ON Personnel.FullName = TH.FullName

You can solve this with a two query approach.
Construct a query with everyone that HAS taken the class and save that 
query.
Now use the unmatched query wizard to find everyone in the personnel table 
that is not in the saved query.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

<sword856@yahoo.com> wrote in message 
news:1185372448.441808.277300@k79g2000hse.googlegroups.com...
> Hello all, hope everyone is doing well this fine morning!
>
> I need some help in constructing a query.  I have three tables,
> designed as so:
>
> Personnel
>    FullName(PK)
>    Department
>
> Classes
>    ClassName(PK)
>
> TrainingHistory *Junc table between Personnel and Classes*
>    FullName(FK)
>    ClassName(FK)
>    DateTaken
>
> TrainingHistory records every instance of a class taken by all
> personnel.  I need to be able to find people who HAVE NOT taken a
> given class.  I need to be able to pick what class I search for each
> time I do the query.  For example, I need to know who in the
> organization has NOT taken "Network Security" so that I can sign them
> up.  Any help on this would be much appreciated.
>
> TIA,
> George
> 


0
John
7/25/2007 4:36:44 PM
On Wed, 25 Jul 2007 07:07:28 -0700, sword856@yahoo.com wrote:

>Hello all, hope everyone is doing well this fine morning!
>
>I need some help in constructing a query.  I have three tables,
>designed as so:
>
>Personnel
>    FullName(PK)
>    Department
>
>Classes
>    ClassName(PK)
>
>TrainingHistory *Junc table between Personnel and Classes*
>    FullName(FK)
>    ClassName(FK)
>    DateTaken
>
>TrainingHistory records every instance of a class taken by all
>personnel.  I need to be able to find people who HAVE NOT taken a
>given class.  I need to be able to pick what class I search for each
>time I do the query.  For example, I need to know who in the
>organization has NOT taken "Network Security" so that I can sign them
>up.  Any help on this would be much appreciated.

A NOT EXISTS query can do this for you:

SELECT FullName, Department FROM Personnel
WHERE Not Exists(SELECT FullName FROM TrainingHistory
WHERE TrainingHistory.FullName = Personnel.FullName
AND TrainingHistory.ClassName = [Enter class:]);

One big flaring warning here: FullName is NOT suitable as a primary key. A PK
should have three characteristics: it should be unique; it should be stable;
and it should (ideally) be short. People's names fail on all three counts! I
once worked with Dr. Lawrence David Wise, Ph.D. and his colleage, Dr. Lawrence
David Wise, Ph.D.; they're not stable, as people marry or otherwise change
their names; and they can be pretty long. A unique EmployeeID is a much better
choice as a PK.

             John W. Vinson [MVP]
0
John
7/25/2007 4:52:21 PM
On Jul 25, 11:20 am, Jerry Whittle
<JerryWhit...@discussions.microsoft.com> wrote:
> PARAMETERS [Enter ClassName] Text ( 255 );
> SELECT Personnel.*, [Enter ClassName] AS [Missing Class]
> FROM Personnel
> WHERE Personnel.FullName Not In
>       (SELECT TrainingHistory.FullName
>        FROM TrainingHistory
>        WHERE TrainingHistory.ClassName In
>             (SELECT Classes.ClassName
>              FROM Classes
>              WHERE Classes.ClassName = [Enter ClassName]));
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
>
> "sword...@yahoo.com" wrote:
> > Hello all, hope everyone is doing well this fine morning!
>
> > I need some help in constructing a query.  I have three tables,
> > designed as so:
>
> > Personnel
> >     FullName(PK)
> >     Department
>
> > Classes
> >     ClassName(PK)
>
> > TrainingHistory *Junc table between Personnel and Classes*
> >     FullName(FK)
> >     ClassName(FK)
> >     DateTaken
>
> > TrainingHistory records every instance of a class taken by all
> > personnel.  I need to be able to find people who HAVE NOT taken a
> > given class.  I need to be able to pick what class I search for each
> > time I do the query.  For example, I need to know who in the
> > organization has NOT taken "Network Security" so that I can sign them
> > up.  Any help on this would be much appreciated.
>
> > TIA,
> > George- Hide quoted text -
>
> - Show quoted text -

Brilliant, Jerry!  Works great!  Thanks for your help!

0
sword856
7/25/2007 4:54:52 PM
On Jul 25, 11:54 am, sword...@yahoo.com wrote:
> On Jul 25, 11:20 am, Jerry Whittle
>
>
>
>
>
> <JerryWhit...@discussions.microsoft.com> wrote:
> > PARAMETERS [Enter ClassName] Text ( 255 );
> > SELECT Personnel.*, [Enter ClassName] AS [Missing Class]
> > FROM Personnel
> > WHERE Personnel.FullName Not In
> >       (SELECT TrainingHistory.FullName
> >        FROM TrainingHistory
> >        WHERE TrainingHistory.ClassName In
> >             (SELECT Classes.ClassName
> >              FROM Classes
> >              WHERE Classes.ClassName = [Enter ClassName]));
> > --
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
> > "sword...@yahoo.com" wrote:
> > > Hello all, hope everyone is doing well this fine morning!
>
> > > I need some help in constructing a query.  I have three tables,
> > > designed as so:
>
> > > Personnel
> > >     FullName(PK)
> > >     Department
>
> > > Classes
> > >     ClassName(PK)
>
> > > TrainingHistory *Junc table between Personnel and Classes*
> > >     FullName(FK)
> > >     ClassName(FK)
> > >     DateTaken
>
> > > TrainingHistory records every instance of a class taken by all
> > > personnel.  I need to be able to find people who HAVE NOT taken a
> > > given class.  I need to be able to pick what class I search for each
> > > time I do the query.  For example, I need to know who in the
> > > organization has NOT taken "Network Security" so that I can sign them
> > > up.  Any help on this would be much appreciated.
>
> > > TIA,
> > > George- Hide quoted text -
>
> > - Show quoted text -
>
> Brilliant, Jerry!  Works great!  Thanks for your help!- Hide quoted text -
>
> - Show quoted text -

>SELECT FullName, Department FROM Personnel
>WHERE Not Exists(SELECT FullName FROM TrainingHistory
>WHERE TrainingHistory.FullName = Personnel.FullName
>AND TrainingHistory.ClassName = [Enter class:]);


>One big flaring warning here: FullName is NOT suitable as a primary key. A PK
>should have three characteristics: it should be unique; it should be stable;
>and it should (ideally) be short. People's names fail on all three counts! I
>once worked with Dr. Lawrence David Wise, Ph.D. and his colleage, Dr. Lawrence
>David Wise, Ph.D.; they're not stable, as people marry or otherwise change
>their names; and they can be pretty long. A unique EmployeeID is a much better
>choice as a PK.

> John W. Vinson [MVP]


Wow.  That's a lot of ways to do this.  John V., I tried yours cause
it seemed simplest and it works well.
The reason FullName is the PK is because when I started this DB, I
knew next to nothing about Access and was told by higher up to do so.
Now it is in it's later stages and has real data inside it.  Would it
be possible to change the PK to an ID number and still keep all the
functionality it already has?  Personnel has relationships on FullName
to 4 different tables, all of which have forms for input.

0
sword856
7/25/2007 5:05:26 PM
sword856@yahoo.com wrote:

>On Jul 25, 10:21 am, Marshall Barton <marshbar...@wowway.com> wrote:
>> sword...@yahoo.com wrote:
>> >Hello all, hope everyone is doing well this fine morning!
>>
>> >I need some help in constructing a query.  I have three tables,
>> >designed as so:
>>
>> >Personnel
>> >    FullName(PK)
>> >    Department
>>
>> >Classes
>> >    ClassName(PK)
>>
>> >TrainingHistory *Junc table between Personnel and Classes*
>> >    FullName(FK)
>> >    ClassName(FK)
>> >    DateTaken
>>
>> >TrainingHistory records every instance of a class taken by all
>> >personnel.  I need to be able to find people who HAVE NOT taken a
>> >given class.  I need to be able to pick what class I search for each
>> >time I do the query.  For example, I need to know who in the
>> >organization has NOT taken "Network Security" so that I can sign them
>> >up.
>>
>> SELECT P.FullName
>> FROM Personnel As P LEFT JOIN TrainingHistory As T
>>         ON P.FullName = T.FullName
>> WHERE T.ClassName = [Enter Class Name]
>>         AND T,ClassName Is Null
>>
>> --
>> Marsh
>> MVP [MS Access]- Hide quoted text -
>>
>> - Show quoted text -
>
>Thanks, Marsh, for replying!
>
>I put in the SQL (except I changed T,ClassName is Null to T.Classname
>is Null) just as you wrote, but the query is returning no names for
>the classes I put in.  I know not everyone has taken every class, so
>there should be names showing up.  The SQL makes it seem like the
>query is looking for a value ([Enter Class Name]) and "Is Null" in the
>same field, which seems like a contradiction.  I may not have
>explained well enough about my TrainingHistory table.  Every time
>someone takes a class, I enter their name, class name, and date in the
>table.  My explanation earlier made it seem like I put classes in that
>everyone took.  So, basically I am looking for the lack of records in
>that table, i.e. records that would be [given class] and personnel
>names.


Sorry about all the typos.  Try this

SELECT P.FullName
FROM Personnel As P LEFT JOIN TrainingHistory As T
     ON P.FullName = T.FullName
WHERE T.ClassName = [Enter Class Name]
     AND T.FullName Is Null

-- 
Marsh
MVP [MS Access]
0
Marshall
7/25/2007 10:23:00 PM
On Wed, 25 Jul 2007 10:05:26 -0700, sword856@yahoo.com wrote:

>The reason FullName is the PK is because when I started this DB, I
>knew next to nothing about Access and was told by higher up to do so.

Sometimes the higher-ups are idiots. Often they're quite bright... but still
wrong. This is such a case. Note that I don't say WHICH is the case... <g>

>Now it is in it's later stages and has real data inside it.  Would it
>be possible to change the PK to an ID number and still keep all the
>functionality it already has?  

Possible, but tedious and not tremendously easy.

>Personnel has relationships on FullName
>to 4 different tables, all of which have forms for input.

To do this, you'ld need to do several steps. Add the new (autonumber or
numeric or short unique text) Primary key to the Personnel table, and matching
foreign key fields (Long Integer if you use an autonumber) to the other four
tables.  Populate the primary key field in the Personnel table, somehow - in
code, with an update query, depends on what you need.

Then run four Update queries; join Personnel to each of the other tables in
turn, joining on Fullname. Update the new foreign key field to

[Personnel].[newprimarykeyfieldname]

using your new primary key field name. Then break all the relationships on
Fullname, and establish new relationships on the new fields. You'll also
probably need to change the master/child link fields on all subforms which now
use Fullname.

             John W. Vinson [MVP]
0
John
7/26/2007 12:10:28 AM
On Jul 25, 7:10 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Wed, 25 Jul 2007 10:05:26 -0700, sword...@yahoo.com wrote:
> >The reason FullName is the PK is because when I started this DB, I
> >knew next to nothing about Access and was told by higher up to do so.
>
> Sometimes the higher-ups are idiots. Often they're quite bright... but still
> wrong. This is such a case. Note that I don't say WHICH is the case... <g>
>
> >Now it is in it's later stages and has real data inside it.  Would it
> >be possible to change the PK to an ID number and still keep all the
> >functionality it already has?  
>
> Possible, but tedious and not tremendously easy.
>
> >Personnel has relationships on FullName
> >to 4 different tables, all of which have forms for input.
>
> To do this, you'ld need to do several steps. Add the new (autonumber or
> numeric or short unique text) Primary key to the Personnel table, and matching
> foreign key fields (Long Integer if you use an autonumber) to the other four
> tables.  Populate the primary key field in the Personnel table, somehow - in
> code, with an update query, depends on what you need.
>
> Then run four Update queries; join Personnel to each of the other tables in
> turn, joining on Fullname. Update the new foreign key field to
>
> [Personnel].[newprimarykeyfieldname]
>
> using your new primary key field name. Then break all the relationships on
> Fullname, and establish new relationships on the new fields. You'll also
> probably need to change the master/child link fields on all subforms which now
> use Fullname.
>
>              John W. Vinson [MVP]

Ouch.  That would mean I would also have to change forms and reports
because they would then show the employee ID numbers instead of
names.  I trying to decide if it is worth it.  Since the names wont be
in the tables on which the subforms are based, will I have to do
something complicated to display the names in the subform? or could I
maybe just keep the names in the tables and use them?  Thanks so much
for your input, John.

0
sword856
7/26/2007 5:26:51 PM
Reply:

Similar Artilces:

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Need to import EDI file
Hello. I have an EDI TS-130 file (academic transcript) that I would like to import into Access 2003. Is this possible to do for free? If so, how? ...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

You wont believe this! SERVER DOWN
I was helping the operations/it director to get RPC over HTTP going on their exchange server for a company I use to be the sys admin for. The ops/it director was doing the add/remove programs portion to add rpc over http there, somehow in the process of doing this he managed to uninstall IIS -or- just NNTP and SMTP (I can't figure out which and he claims he did neither). Long story short, I've reinstalled NNTP and SMTP, reconfigured SMTP to the best recollection I have of the previous config and the server still refuses to send/receive email, even internally. All services are s...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

need custom cut and paste functions
Hello, I once wrote here about a problem I had cutting and pasting where columns would turn to "REF!" after a cut and paste. I would work around it by copying, pasting and then manually deleting instead. I thought turning everything in the sheet to absolute references would solve the problem but it didn't so now I am thinking of a different solution. Could someone tell me what I need to do to write my own cut and paste functions which would basically copy the selection and then on a paste it would paste and then delete the original selection from where it was copied from...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

RPC over HTTP/S on Exchange 2003
I have been configuring my single server with exchange to use RPC over https I have followed the instructions in MS guide and another simplified guide at http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm Server spec is: Server 2003 standard SP1, Exchange 2003 SP1, XP client SP2 with outlook 2003 sp2 The bottom line is that when testing from the WAN, the outlook client will not connect and say that the exchange server is unavailable. I have a lot of experience configuring rpc over http/s with sbs2003 but this is the first time for server 2003 standard. I have outlook ...

Mailbox needs reducing
We have this mailbox that has been left for months and with no limits, it's now 11GB!! Ho can I trim in down, I don't really want to go into the mailbox and do it, can Exchange do it? In news:euNRN1iPHHA.404@TK2MSFTNGP02.phx.gbl, Gonzo <no@no.com> typed: > We have this mailbox that has been left for months and with no > limits, it's now 11GB!! > > Ho can I trim in down, I don't really want to go into the mailbox and > do it, can Exchange do it? Presuming you're not on 5x or earlier, you can use mailbox management policies to get rid of items ol...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

I need help restoring Public Folders
Hi I've been having issues getting Public Folder replication going between 2 Exchange 2003 servers. Prior to doing any work I performed a Backup using Veritas Backup Exec and this completed and verified correctly. I screwed things up and managed to delete some of the public folders on my original server. I'm not 100% sure what I did but it is a mess and the only backup I have is the Exchange backup I created before doing any work. The public folders contain a number of Contact Lists and a very important Event Calendar. I have restored the public folders but when the restore c...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Public folder issue, "messaging interface has returned an unknown error"
I'm running a SBS2003 domain and just added a public folder and a customized form for entry into the public folder. All of our clients (all XP Pro, Outlook 2003 with all updates) can get to that folder and use the form without any difficulty except for one. On one computer I get the classic "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." error message. I have tried a repair install, no change. I have tried making a new profile, no change. I have tried adding / removing his PST file (currently he's not even using a pst file...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...

Need to retrieve a deleted Excel File
On Saturday I hide an excel sheet and then protected the workbook. When I got into work today to open the file it was non-existent. What do I have to do to retrieve the file? Does it make a difference if the file extension ended with .csv? Thanks in advance! Search in Windows to make sure it's still on your compute -or go to the folder the file is in and look to make sure it's still ther -or If you didn't do this, you might need to to text, which includes *.cs Excel is not set to open this type of file by defaul tj Did you (try to) open the file with Excel. If so : did ...

need some XL URL's please
I would appreciate some good site links for XL how to's and working examples aimed at Novice - Intermediate. Many thanks, Steve Hi Steven: See: Dave McRitchie: http://www.mvps.org/dmcritchie/excel/excel.htm Go to the heading: Excel Lessons & Tutorials (#tutorials) It's just after halfway down. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au It is imperative that the patches provided by Microsoft in its April Security Release be applied to Systems as soon as possible. It is believed that the likelihood of a worm being released SOON that ...

Excel for Customer History Records
Hello, can anyone direct me to an existing template that can be used to track customer enquiries and feedback. It needs an efficient way of recording follow up & response conversations. In a standard workbook layout if you type loads of text into a column (say 'follow up' for instance), it pushes all the rows so far down the spreadsheet it quickly becomes unwieldy. If anyone can help I could e mail an example of what we're using now (awful) and after having a chuckle at our expense maybe you can suggest something more appropriate. We're running 2003. Many thanks --...

ebay - ereseller stook my money
a terrible thing happenned to me on ebay. i am a disabled woman in and eresellers took my money and never sent my washer. his email address is david@tropivision.com. anything that you can do to help would be nice. ebay is doing nothing. i can give a reward This group is for discussing the Microsoft Money computer program. Does it really sound like an eBay buyer complaint forum? Do a google search on ebay and you will find a couple of groups, one of which may reply to your problem. That ebay seller has been selling on ebay for over 5 years. arthur -- On 1 Jun 2004 13:50:08 -0700, (An...

UDF that returns a Hyperlink
Is it possible in Excel 2007 using VBA to create a UDF that returns a hyperlink? Ideally I want a hyperlink where the display name is different to the linked URL. Any ideas? TIA First, if you mean you want the tooltip to show a different address, then I don't think that it's possible. Second, if you meant you want to see something in the cell that isn't the address of the link, then... There's an =hyperlink() worksheet function that may do exactly what you want: =hyperlink("http://www.microsoft.com","Click me!") Third, If you wan...

Need Help With GP Item Pricing
GP ITEM PRICING I need to use “Bucket Pricing” for items in GP. For example below is the pricing schedule that I need to use. First 20 …………….$ 1.75 Next 280 ……………… 1.36 Next 700 ……………… 1.23 Next 2,000 ……………… 1.09 Over 3,000 ……………… .82 So if I had a Qty. of 350 it would be as follows 20 * 1.75 = 35 280 * 1.36 = 380.80 50 * 1.23 = 61.50 Total : $477.30 In GP I can only set it up to do a “Quantity Range” in the Item Price List Maintenance window. Problem is if I set it up that way and enter a quantity of 350 it will default to the $1.23 ...