How to ignore records with a duplicate ID based on a value

I hae searched the forum tono avail, so I'll ask for help.

I have a table [Scope Event Table] which records events with status changes. 
 The table uses an auto numbered primary key (not shown in the example 
below).  It has data like this:

DR_ID	Event_Date	Reason
12556	01/03/2008	Added
12556	01/24/2008	Closed
12874	01/05/2008	Added
14128	02/09/2008	Added

I am a novice at SQL.  I am trying to build a query that would pull only the 
DR_ID’s where the most recent Reason = “Added”.  So I would want results to 
look like this:

DR_ID	Event_Date	Reason
12874	01/05/2008	Added
14128	02/09/2008	Added

Thanks in advance for your assistance.

0
Utf
3/13/2008 2:02:02 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
593 Views

Similar Articles

[PageSpeed] 34

Firstly, open the table in design view, and make a unique index on the 
combination of DR_ID + Event_Date, so there cannot be duplicates. (Use the 
Indexes box on the toolbar.)

Now create this query:
    SELECT [Scope Event Table].DR_ID,
    Max([Scope Event Table].[Event_Date]) AS MostRecent,
    (SELECT Reason from [Scope Event Table] AS Dupe
    WHERE Dupe.DR_ID = [Scope Event Table].DR_ID
    AND Dupe.Event_Date = Max([Scope Event Table].[Event_Date]))
    AS LastReason
    FROM [Scope Event Table]
    GROUP BY DR_ID;

If subqueries are new, here's an introduction:
    http://allenbrowne.com/subquery-01.html

Further reading:
    http://www.mvps.org/access/queries/qry0020.htm

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Wiley" <Wiley@discussions.microsoft.com> wrote in message
news:09808991-FA2C-4D62-9D24-E4B0FFC85043@microsoft.com...
>I hae searched the forum tono avail, so I'll ask for help.
>
> I have a table [Scope Event Table] which records events with status 
> changes.
> The table uses an auto numbered primary key (not shown in the example
> below).  It has data like this:
>
> DR_ID Event_Date Reason
> 12556 01/03/2008 Added
> 12556 01/24/2008 Closed
> 12874 01/05/2008 Added
> 14128 02/09/2008 Added
>
> I am a novice at SQL.  I am trying to build a query that would pull only 
> the
> DR_ID’s where the most recent Reason = “Added”.  So I would want results 
> to
> look like this:
>
> DR_ID Event_Date Reason
> 12874 01/05/2008 Added
> 14128 02/09/2008 Added
>
> Thanks in advance for your assistance. 

0
Allen
3/13/2008 2:43:59 PM
In your query, the column that supports Added and Closed put "Added" in the 
criteria field. When you run the query it should only show the records with 
Added in the column and omit Closed.

"Wiley" wrote:

> I hae searched the forum tono avail, so I'll ask for help.
> 
> I have a table [Scope Event Table] which records events with status changes. 
>  The table uses an auto numbered primary key (not shown in the example 
> below).  It has data like this:
> 
> DR_ID	Event_Date	Reason
> 12556	01/03/2008	Added
> 12556	01/24/2008	Closed
> 12874	01/05/2008	Added
> 14128	02/09/2008	Added
> 
> I am a novice at SQL.  I am trying to build a query that would pull only the 
> DR_ID’s where the most recent Reason = “Added”.  So I would want results to 
> look like this:
> 
> DR_ID	Event_Date	Reason
> 12874	01/05/2008	Added
> 14128	02/09/2008	Added
> 
> Thanks in advance for your assistance.
> 
0
Utf
3/13/2008 2:52:02 PM
I think the following might work for you.

SELECT S.DR_ID, S.Event_Date, S.Reason
FROM [Scope Event Table] as S
WHERE S.Event_Date =
    (SELECT Max(Event_Date)
     FROM [Scope Event Table] as Dupe
     WHERE Dupe.DR_ID = S.DR_ID)
AND S.Reason = "Added"

In Design view
-- Add your table
-- add your field
-- Under Reason set the criteria to
    = "Added"
-- Under Event_Date set the criteria to
   = (SELECT Max(Event_Date)  FROM [Scope Event Table] as Dupe WHERE 
Dupe.DR_ID =[Scope Event Table].DR_ID)
-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Wiley" <Wiley@discussions.microsoft.com> wrote in message 
news:09808991-FA2C-4D62-9D24-E4B0FFC85043@microsoft.com...
>I hae searched the forum tono avail, so I'll ask for help.
>
> I have a table [Scope Event Table] which records events with status 
> changes.
> The table uses an auto numbered primary key (not shown in the example
> below).  It has data like this:
>
> DR_ID Event_Date Reason
> 12556 01/03/2008 Added
> 12556 01/24/2008 Closed
> 12874 01/05/2008 Added
> 14128 02/09/2008 Added
>
> I am a novice at SQL.  I am trying to build a query that would pull only 
> the
> DR_ID's where the most recent Reason = "Added".  So I would want results 
> to
> look like this:
>
> DR_ID Event_Date Reason
> 12874 01/05/2008 Added
> 14128 02/09/2008 Added
>
> Thanks in advance for your assistance.
> 


0
John
3/13/2008 3:24:27 PM
Allen,

Thansk for your response.  I changed the primary key as you instructed and 
pasted in your query in SQL window.  When I run it I get "Canot have 
aggregate function in WHERE clause...", and it puts the entier Where clause 
in the error message.

"Allen Browne" wrote:

> Firstly, open the table in design view, and make a unique index on the 
> combination of DR_ID + Event_Date, so there cannot be duplicates. (Use the 
> Indexes box on the toolbar.)
> 
> Now create this query:
>     SELECT [Scope Event Table].DR_ID,
>     Max([Scope Event Table].[Event_Date]) AS MostRecent,
>     (SELECT Reason from [Scope Event Table] AS Dupe
>     WHERE Dupe.DR_ID = [Scope Event Table].DR_ID
>     AND Dupe.Event_Date = Max([Scope Event Table].[Event_Date]))
>     AS LastReason
>     FROM [Scope Event Table]
>     GROUP BY DR_ID;
> 
> If subqueries are new, here's an introduction:
>     http://allenbrowne.com/subquery-01.html
> 
> Further reading:
>     http://www.mvps.org/access/queries/qry0020.htm
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Wiley" <Wiley@discussions.microsoft.com> wrote in message
> news:09808991-FA2C-4D62-9D24-E4B0FFC85043@microsoft.com...
> >I hae searched the forum tono avail, so I'll ask for help.
> >
> > I have a table [Scope Event Table] which records events with status 
> > changes.
> > The table uses an auto numbered primary key (not shown in the example
> > below).  It has data like this:
> >
> > DR_ID Event_Date Reason
> > 12556 01/03/2008 Added
> > 12556 01/24/2008 Closed
> > 12874 01/05/2008 Added
> > 14128 02/09/2008 Added
> >
> > I am a novice at SQL.  I am trying to build a query that would pull only 
> > the
> > DR_ID’s where the most recent Reason = “Added”.  So I would want results 
> > to
> > look like this:
> >
> > DR_ID Event_Date Reason
> > 12874 01/05/2008 Added
> > 14128 02/09/2008 Added
> >
> > Thanks in advance for your assistance. 
> 
> 
0
Utf
3/13/2008 5:39:01 PM
John,

Thanks so much for your response.  I never knew about the 'Dupe" feature.  
It works great!  Woudl it be good insurance to add the ORDER BY clause belwo 
to force a record order?   

ORDER BY [Scope Event Table].DR_ID, [Scope Event Table].Event_Date;

"John Spencer" wrote:

> I think the following might work for you.
> 
> SELECT S.DR_ID, S.Event_Date, S.Reason
> FROM [Scope Event Table] as S
> WHERE S.Event_Date =
>     (SELECT Max(Event_Date)
>      FROM [Scope Event Table] as Dupe
>      WHERE Dupe.DR_ID = S.DR_ID)
> AND S.Reason = "Added"
> 
> In Design view
> -- Add your table
> -- add your field
> -- Under Reason set the criteria to
>     = "Added"
> -- Under Event_Date set the criteria to
>    = (SELECT Max(Event_Date)  FROM [Scope Event Table] as Dupe WHERE 
> Dupe.DR_ID =[Scope Event Table].DR_ID)
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Wiley" <Wiley@discussions.microsoft.com> wrote in message 
> news:09808991-FA2C-4D62-9D24-E4B0FFC85043@microsoft.com...
> >I hae searched the forum tono avail, so I'll ask for help.
> >
> > I have a table [Scope Event Table] which records events with status 
> > changes.
> > The table uses an auto numbered primary key (not shown in the example
> > below).  It has data like this:
> >
> > DR_ID Event_Date Reason
> > 12556 01/03/2008 Added
> > 12556 01/24/2008 Closed
> > 12874 01/05/2008 Added
> > 14128 02/09/2008 Added
> >
> > I am a novice at SQL.  I am trying to build a query that would pull only 
> > the
> > DR_ID's where the most recent Reason = "Added".  So I would want results 
> > to
> > look like this:
> >
> > DR_ID Event_Date Reason
> > 12874 01/05/2008 Added
> > 14128 02/09/2008 Added
> >
> > Thanks in advance for your assistance.
> > 
> 
> 
> 
0
Utf
3/13/2008 6:06:01 PM
You can add the order by if you want to display the records in that order in 
a query or on a form.

If you want to use the records returned in a report, don't bother to add an 
order by clause here.  Use the reports Sorting and Grouping dialog (View: 
Sorting and Grouping) to set the sort order.

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

"Wiley" <Wiley@discussions.microsoft.com> wrote in message 
news:4C3051FE-D7E6-455A-AE00-F750226AEF67@microsoft.com...
> John,
>
> Thanks so much for your response.  I never knew about the 'Dupe" feature.
> It works great!  Woudl it be good insurance to add the ORDER BY clause 
> belwo
> to force a record order?
>
> ORDER BY [Scope Event Table].DR_ID, [Scope Event Table].Event_Date;
>
> "John Spencer" wrote:
>
>> I think the following might work for you.
>>
>> SELECT S.DR_ID, S.Event_Date, S.Reason
>> FROM [Scope Event Table] as S
>> WHERE S.Event_Date =
>>     (SELECT Max(Event_Date)
>>      FROM [Scope Event Table] as Dupe
>>      WHERE Dupe.DR_ID = S.DR_ID)
>> AND S.Reason = "Added"
>>
>> In Design view
>> -- Add your table
>> -- add your field
>> -- Under Reason set the criteria to
>>     = "Added"
>> -- Under Event_Date set the criteria to
>>    = (SELECT Max(Event_Date)  FROM [Scope Event Table] as Dupe WHERE
>> Dupe.DR_ID =[Scope Event Table].DR_ID)
>> -- 
>> John Spencer
>> Access MVP 2002-2005, 2007-2008
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "Wiley" <Wiley@discussions.microsoft.com> wrote in message
>> news:09808991-FA2C-4D62-9D24-E4B0FFC85043@microsoft.com...
>> >I hae searched the forum tono avail, so I'll ask for help.
>> >
>> > I have a table [Scope Event Table] which records events with status
>> > changes.
>> > The table uses an auto numbered primary key (not shown in the example
>> > below).  It has data like this:
>> >
>> > DR_ID Event_Date Reason
>> > 12556 01/03/2008 Added
>> > 12556 01/24/2008 Closed
>> > 12874 01/05/2008 Added
>> > 14128 02/09/2008 Added
>> >
>> > I am a novice at SQL.  I am trying to build a query that would pull 
>> > only
>> > the
>> > DR_ID's where the most recent Reason = "Added".  So I would want 
>> > results
>> > to
>> > look like this:
>> >
>> > DR_ID Event_Date Reason
>> > 12874 01/05/2008 Added
>> > 14128 02/09/2008 Added
>> >
>> > Thanks in advance for your assistance.
>> >
>>
>>
>> 


0
John
3/13/2008 6:22:37 PM
OK.  Thanks for the advice.

"John Spencer" wrote:

> You can add the order by if you want to display the records in that order in 
> a query or on a form.
> 
> If you want to use the records returned in a report, don't bother to add an 
> order by clause here.  Use the reports Sorting and Grouping dialog (View: 
> Sorting and Grouping) to set the sort order.
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Wiley" <Wiley@discussions.microsoft.com> wrote in message 
> news:4C3051FE-D7E6-455A-AE00-F750226AEF67@microsoft.com...
> > John,
> >
> > Thanks so much for your response.  I never knew about the 'Dupe" feature.
> > It works great!  Woudl it be good insurance to add the ORDER BY clause 
> > belwo
> > to force a record order?
> >
> > ORDER BY [Scope Event Table].DR_ID, [Scope Event Table].Event_Date;
> >
> > "John Spencer" wrote:
> >
> >> I think the following might work for you.
> >>
> >> SELECT S.DR_ID, S.Event_Date, S.Reason
> >> FROM [Scope Event Table] as S
> >> WHERE S.Event_Date =
> >>     (SELECT Max(Event_Date)
> >>      FROM [Scope Event Table] as Dupe
> >>      WHERE Dupe.DR_ID = S.DR_ID)
> >> AND S.Reason = "Added"
> >>
> >> In Design view
> >> -- Add your table
> >> -- add your field
> >> -- Under Reason set the criteria to
> >>     = "Added"
> >> -- Under Event_Date set the criteria to
> >>    = (SELECT Max(Event_Date)  FROM [Scope Event Table] as Dupe WHERE
> >> Dupe.DR_ID =[Scope Event Table].DR_ID)
> >> -- 
> >> John Spencer
> >> Access MVP 2002-2005, 2007-2008
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> ..
> >>
> >> "Wiley" <Wiley@discussions.microsoft.com> wrote in message
> >> news:09808991-FA2C-4D62-9D24-E4B0FFC85043@microsoft.com...
> >> >I hae searched the forum tono avail, so I'll ask for help.
> >> >
> >> > I have a table [Scope Event Table] which records events with status
> >> > changes.
> >> > The table uses an auto numbered primary key (not shown in the example
> >> > below).  It has data like this:
> >> >
> >> > DR_ID Event_Date Reason
> >> > 12556 01/03/2008 Added
> >> > 12556 01/24/2008 Closed
> >> > 12874 01/05/2008 Added
> >> > 14128 02/09/2008 Added
> >> >
> >> > I am a novice at SQL.  I am trying to build a query that would pull 
> >> > only
> >> > the
> >> > DR_ID's where the most recent Reason = "Added".  So I would want 
> >> > results
> >> > to
> >> > look like this:
> >> >
> >> > DR_ID Event_Date Reason
> >> > 12874 01/05/2008 Added
> >> > 14128 02/09/2008 Added
> >> >
> >> > Thanks in advance for your assistance.
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Utf
3/13/2008 6:53:03 PM
Reply:

Similar Artilces:

Write scatterplot point ID to a cell
Hello all I'm trying to click a point in a scatterplot and have its ID (label, values, whatever) written to a cell. Any ideas? Thanks jcamoes Hi, Have a look at Jon Peltier's article "Chart Events in Microsoft Excel" http://www.computorcompanion.com/LPMArticle.asp?ID=221 Cheers Andy jcamoes wrote: > Hello all > > I'm trying to click a point in a scatterplot and have its ID (label, > values, whatever) written to a cell. Any ideas? > > Thanks > > jcamoes > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks! That&#...

Change node attribute value
How would I change the value of an attribute in an XML node using C#? I have tried "findnode.Attributes["ID"].InnerText = newValue;" and "Attribute attri = (XmlAttribute)findnode.Attributes["ID"].Value;" but Im stuck! When I try casting, it say that it cannot convert a string to an XMLAttribute or when I try setting it to a new value it says "Object reference not set to an instance of an object." -------XML-------- <attr-inst id="20" state="known"> <val>100</val> </attr-inst> Thanks for any he...

adding/subtracting a value to cell permanently
hey guys.. i need your help.. How do i add/subtract a value from a cel permanently? I need to know how to do that because I am doing a schoo project.. it's about a grocery store.. for example.. if Sheet2!D2 contains the stocks in the grocery.. and yo enter in Sheet1!B2 the quantity desired by the user to be bought.. afte the transaction, how would i subtract permanently the quantity bought b the user from the stocks left in the grocery? coz everytym i change th quantity, the stocks also changes.. i want to make changes permanen everytym i change the quantity desired.. how do i do that? ...

Graphing With Large Variation In Values
I'm trying to make a simple bar graph to display the results of some data that has 10 data sets. However, there is one data set that has a much higher value that the other nine. The result is that the data sets with lower values all appear to be the same value and her hardly distinguishable because the scale is set by the highest data set. Is there a way to "cut out" the middle section of a bar graph that will show the smaller data sets with better resolution? Thanks in advance Hi see: http://www.tushar-mehta.com/excel/newsgroups/broken_y_axis/index.html http://peltiert...

verify all subforms have records
Access 2003 I have a form called f001ProjectReview (PK ProjectID) it has several subforms linked by ProjectID. Sub1, Sub2, Sub3. There is a button that activates a pop up form called fClosureApprovalPopUp linked by ProjectID. On fClosureApprovalPopUp I have an approval button. When user clicks the approval button I need to verify that all subforms on f001ProjectReview have records. If there is a subform that does not have a record then display message that required data is missing and exit without saving. Your help is greatly appreciated!! -- deb You could u...

Calculation query, how to avoid extreme values?
Good morning, I have 1 table with a few fields (A, B, C) that I'm using for various calculations. For various reasons are some of the input "weird" and I want to leave that out of the calculation (average). For example, Field A requires > 0 AND < 100, B < 400 Now, if one record doesn't fullfil A requirement I don't want to use it for A's average calculation BUT I want to use it for B's average calculation (if it satisfy B's requirement). This means that if I use the query criteria A: >0 AND <100 it will filter out all other records an...

How to add a space after each text value in all the cells in a row
I want to add a space (or any character(s)) after each text value in all the cells in a row or in a column For example, cell A2 contains "ABC", cell B2 contains "DEF", and so on. I want to make all the cells in row 2 to have a space so cell A2 contains "ABC ", cell B2 contains "DEF ",... try going to an empty part of the sheet (say row a4) & type =a2&" " or "[any character]" then copy that across the row for your range. after calc you can copy the range & paste special (values) back into range a2 ...X2 &am...

Have data with multiple records associated to same ID
My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan How many subsets are you likely to have (maximum)? Are the values DLL, CND etc in column C, or are they tagged on to the end of the I...

Any way to add column for Message-ID to Inbox/Sent Items folders?
Hello, Is there any way, please, to add a column showing the Message-ID to the Inbox or Sent Items folders? My boss often receives return receipts from external recipients which show the sender's address and the Message ID, but not the subject. Outlook doesn't correlate them so he has to try and manually do it. Opening each message and looking in its Options takes too long. We're using Exchange 2000 and Outlook 2000 and 2003. Maybe Exchange 2003 does a better job of understanding notifications. Thanks, - Alan. "Alan" <bruguy@gmail.com> wrote in message news:...

minimal payroll records
Hello: I'm pretty sure that I'm correct on this. But, I want to check with you all first. I know that as a means of disaster recovery (server gets blown away) that a client can restore backups of their DYNAMICS and comapany databases onto a new server. I have done that before. But, let's say the client just wants to restore a minimal set of data just to gbe able to cut paychecks out for employees. Can they restore just their DYNAMICS databases and the UPR master tables ? Thanks! childofthe1980s no, that would not work. It may be possible to achieve what you want; You s...

Too Many Headings, Values for Chart Feature To Handle???
I'm having problems setting up a chart with the following headings (see below). The Chart Wizard will only let me select so many headings (& values). If I CTRL - click one heading too many, the list field window clears and shows the last cell selected. Any ideas what's happening here? Column Headings: Aerosol, Art, Batch, Bulk, Dykem, Mark-Tex ,Outsource, Quality, R&D, Receiving, Scrubs, Shipping, WHC Colimn Subheadings: DEV, NCP Row Headings: BATCHING CODING FILLING HANDLING HAZCOM IMPROVEMENT INVENTORY LABEL DEFECT PACKAGE DEFECT RAW MATERIAL RECEIVING ...

Image in Knowledge base
Hi, Is it possible to add an image in an article of the knowledge base? Thanks for your help I don't think this is possible. But, it is pretty easy to drop in a hyperlink to a picture on another server, or a local file, etc. When you publish the article, the hyperlink becomes active and will open another window with the picture. Not that great, but it does work. Good luck! Steve C. "B. Guillout" wrote: > Hi, > Is it possible to add an image in an article of the knowledge base? > Thanks for your help ...

Can't Erase Tapes: DPM Fails with Error ID:3308
I attempt to erase a tape and every time the DPM console crashes (sometimes two hours into the tape erase) and there is this error: * (000002L4) could not be erased for the following reason: (ID 3308) * DPM service terminated unexpectedly during completion of the job. The termination may have been caused by a system reboot. (ID 910) I can verify that the server has not rebooted. I'm using DPM 2007, with June Rollup, on Windows Server (x64) 2008. The server is a Dell PowerEdge R710. The tape drive is an LTO4 in a Dell PowerVault 124T Autoloader. This error seems to ...

force UPN suffix based on OU
is there a way to force a UPN suffix to new accounts created in a particular OU? Fairly certain there isn't - you would have to change it manually when creating the account or insert it when creating the account if it's a scripted process. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "davo12345" <muppet_66@hotmail.com> wrote in message news:1165871526.653985.10070@j44g2000cwa.googlegroups.com... > is there a way to force a UPN suffix to new accounts created in a ...

How to stop acces from saving the record
Hi I am using main form and subform and when I focus on the sub form Access saves the record and I need to know how to stop acces from saving the record when the subform is on focus? I understand that I need to unbound the main form and then bound it on before update event. I tried this code but I get some errors. On Error GoTo Error_Handler Dim db As DAO.Database Dim rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset("tblWhatever", dbOpenDynaset) With rst .AddNew !WhateverID = Me.txtWhateverID !FirstName = Me.tx...

Recording a command button name
I have an application where the user is prompted to save when they leave the the data entry form either by previewing a report of the data or by closing the form completely. There are two buttons a user can use to do this cmdPreview or cmdClose. I use a module to promt the user for svae. What I would like to do is differentiate to the module which button was clicked. Is there a way to do this? thanks You could take a look at Screen.ActiveControl.Name -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "E" <...

Your digital ID name cannot be found by the underlying security system
Hello All, I apologize in advance for the cross posting, but it occurs to me that perhaps this is the NG where originally I should have posted my question. Instead, I posted it on microsoft.public.security. My Class 1 Verisign Digital ID expired and I purchased (or renewed?) a new digital certificate. I disabled my Norton 2008 Firewall during the installation of the VeriSign certificate and received no error messages during the installation. However, unlike in my previous installation I receive the following error message: "Your digital ID name cannot be found by the underlying secu...

VLOOKUP Value in Another Formula
Help, I've almost given up on this. I'm using VLOOKUP in a certain cell. This formula returns a value from another worksheet. I have this part down pat. What I want to do is use the value returned from the cell having the VLOOKUP formula in another cell so I can label the other cell a certain way. My VLOOKUP formula is: =IF(ISNA(VLOOKUP($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE))," ",VLOOKUP ($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE)) Let's say that this VLOOKUP formula is in cell G21. I want to put "PRIMARY NUMBER" in cell G16 if t...

Interpolating value
When a chart generated is it possible in exel to return a value from the chart or chart data by reading chart or interpollating value from knowns. (parabolic/logaritmic charts etc) W The chart can't, but the data sheet (from which the chart was generated) can. Try the FORECAST function. -- David Biddulph "willemeulen" <willemeulen@discussions.microsoft.com> wrote in message news:B885B130-15A9-43C6-B898-E6C157981790@microsoft.com... > When a chart generated is it possible in exel to return a value from the > chart or chart data by reading chart or ...

count related records
2 tables - 1) tickets and 2) messages. Tickets contains the main data and messages contains all the comments made relating to that ticket. How do I count the number of records (or messages) in the related table? So, I have 1 ticket in the system and 4 comments have been made in that ticket. I want to be able to take that number and append it to another ticket table showing "4" in that field. Appending the count to another ticket table is not the thing to do! Say you append 4 and then add another message. The count is now 5 but you only have 4 in the other table....

Unable to view all CRM records
Hello there, We had migrated a CRM v1.2 test implementation into a Production environment some time ago. Although not all the users (especially new users created) are able to view all records from the previous test implementation. We migrated records and recreated the user list and merged all existing UserID for the owners and all users from the old SID's to the new SID's. This has not been a problem for most old users, although we are now receiving problems with some users (some old, but especially new users) not being able to view the old records, only new records created since t...

Select records based upon calculated totals
I've created a report that shows the contract price of a project and using a subreport of invoices, a text box to calculate percentage of $ invoiced, which works great. I'd like to now create a report that shows only the projects that have not been invoiced 100%. What about the idea of doing that calculation in the underlying query, then using the query to feed the report? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein do...

Next Record Event
Hi,I am wanting to add the current date to each new form, as I advancethrough the recordset, using the buttons built in to the form at thebottom. I have currently the following code, but I am wanting toenable the date stamp on successive record entries in the samesession. Thanks, Louis---------Private Sub Form_Load() 'Set date stamp on current record Text50.Value = DateEnd Sub ll, First, you should name your objects something more meaningful than Text50... something CreateDate, or InvoiceDate, MyDate etc... Delete the code you have now in the OnLoad event. Next, is Text50 a D...

IF statement with FALSE value being text AND cell reference? #2
Ok, I found the answer to my own questions, but now it presents a ne question. The formula I want works by adding "&" so it reads: =IF(D9>1,(D9*D8),"<"&(D9*D10)) Now my problem is, the resulting number of D9*10 has like 8 decima places. The cell is ignoring my command to round off to just 2 decima places, I guess since it is part of an IF command, and not just general number in the cell. Or maybe because it has text placed i front of it? Not sure why, but how do I make this FALSE statemen return <#.## and not <#.######## ? -- jcob -------------...

Error ID 7010
Hi, I got a lot of error messages on my Exchange 2003. The source is MSExchange Transport. Event ID is 7010. The description id below: This is an SMTP protocol log for virtual server ID 2, connection #11649. The client at "192.168.100.10" sent a "rcpt" command, and the SMTP server responded with "550 5.7.1 Unable to relay for sdfmmtthxnj@1ownerhomes.com ". The full command sent was "rcpt To:<sdfmmtthxnj@1ownerhomes.com>". This will probably cause the connection to fail. My Exchange does not allow open relay. How can I get rid of those emai...