Looking up a previous record based on criteria

Hi everyone,

I have a textbox in my form that needs to display the value of another 
textbox on the form, but from the previous record, given two criteria:

1. that the value of "ModNumber" (on the current record) is > 1 and
2. that the value of "TaskName" on the current record matches the "TaskName" 
of the previous record


More Details:

My database contains task orders, and each task order can have many Mods.  
This question was about the ModForm, which updates the ModTable.  TaskName is 
a foreign key from the TaskTable (no two TaskNames will be identical), and 
ModAutoNumber is the primary key of the ModTable.  ModNumber is a 
user-defined field that will have integer values > 0.  

My rationale for the two criteria: (1)if ModNumber = 1, then there is no 
previous Mod, and therefore no field needs to be referenced; (2)If TaskName 
doesn't match
the previous record's TaskName, then nothing should be looked up because the 
Mods are for different tasks.

Thank you so much!

Cindy
0
Utf
12/10/2009 4:43:01 PM
access 16762 articles. 2 followers. Follow

2 Replies
380 Views

Similar Articles

[PageSpeed] 20

Try this --
SELECT YourTable.*, IIF(Nz(SELECT T.[SomeTextBox] FROM YourTable AS T WHERE 
(T.ModNumber)+1 = YourTable.ModNumber AND T.TaskName = 
YourTable.TaskName),"First Record") AS Last_Record
FROM YourTable; 

-- 
Build a little, test a little.


"CindyR" wrote:

> Hi everyone,
> 
> I have a textbox in my form that needs to display the value of another 
> textbox on the form, but from the previous record, given two criteria:
> 
> 1. that the value of "ModNumber" (on the current record) is > 1 and
> 2. that the value of "TaskName" on the current record matches the "TaskName" 
> of the previous record
> 
> 
> More Details:
> 
> My database contains task orders, and each task order can have many Mods.  
> This question was about the ModForm, which updates the ModTable.  TaskName is 
> a foreign key from the TaskTable (no two TaskNames will be identical), and 
> ModAutoNumber is the primary key of the ModTable.  ModNumber is a 
> user-defined field that will have integer values > 0.  
> 
> My rationale for the two criteria: (1)if ModNumber = 1, then there is no 
> previous Mod, and therefore no field needs to be referenced; (2)If TaskName 
> doesn't match
> the previous record's TaskName, then nothing should be looked up because the 
> Mods are for different tasks.
> 
> Thank you so much!
> 
> Cindy
0
Utf
12/10/2009 7:24:02 PM
Karl,

Thank you for your reply!

Unfortunately, I got a "subquery syntax is incorrect" error when trying to 
use your suggestion.  I put it in the following location: OutputTextbox > 
right-click > build event (in expression builder).  I also substituted 
ModTable for "YourTable",  and T.[ModLaborFinal] (the texbox to be looked up) 
for "T.[SomeTextbox]".  Is there something I'm missing?

Also, the purpose of this is to make my ModForm cumulative--if the total of 
a Task has been updated by a previous Mod for that task, then I would like 
the current Mod to take the previous Mod's changes into account.  So by 
displaying the previous Mod's final total, I can then use this total with the 
new Mod's "delta" amount to create a new final total.  I would like the 
TaskTable to still contain the original (before any Mods) Task total, however.

Is there a better way of doing this in a form? 

Thanks,

Cindy

"KARL DEWEY" wrote:

> Try this --
> SELECT YourTable.*, IIF(Nz(SELECT T.[SomeTextBox] FROM YourTable AS T WHERE 
> (T.ModNumber)+1 = YourTable.ModNumber AND T.TaskName = 
> YourTable.TaskName),"First Record") AS Last_Record
> FROM YourTable; 
> 
> -- 
> Build a little, test a little.
> 
> 
> "CindyR" wrote:
> 
> > Hi everyone,
> > 
> > I have a textbox in my form that needs to display the value of another 
> > textbox on the form, but from the previous record, given two criteria:
> > 
> > 1. that the value of "ModNumber" (on the current record) is > 1 and
> > 2. that the value of "TaskName" on the current record matches the "TaskName" 
> > of the previous record
> > 
> > 
> > More Details:
> > 
> > My database contains task orders, and each task order can have many Mods.  
> > This question was about the ModForm, which updates the ModTable.  TaskName is 
> > a foreign key from the TaskTable (no two TaskNames will be identical), and 
> > ModAutoNumber is the primary key of the ModTable.  ModNumber is a 
> > user-defined field that will have integer values > 0.  
> > 
> > My rationale for the two criteria: (1)if ModNumber = 1, then there is no 
> > previous Mod, and therefore no field needs to be referenced; (2)If TaskName 
> > doesn't match
> > the previous record's TaskName, then nothing should be looked up because the 
> > Mods are for different tasks.
> > 
> > Thank you so much!
> > 
> > Cindy
0
Utf
12/12/2009 7:10:01 PM
Reply:

Similar Artilces:

Moving OLE Based Control
I have a control that is bound to an OLE object in a table. When i try to reposition the control with the command Me.ObjectName.Left = 4000, it does not compile. The error message "Invalid Qualifier" appears. In addition, when I normally enter the period after the name of the object such as Me.ObjectName. a list of optional commands comes up. For this control, it does not. Could anyone help with with the problem of not being able to access the properties of this control. I use Access 2000. Thanks -- Frank Wagner fwagner111@aol.com What's the control? Are you sur...

VBA Open Record Set multiple tables
Dear All: I am trying to open two tables and read the data in VBA. One is a master table that contains key info, needed to grab data from the second. I am having trouble with the OpenRecordSet Commands. Set rst = db.OpenRecordset("Eweek", dbOpenTable) I want to Loop each record from "Eweek" - [ENO] and compare it to another table that has a same field [ENO]. (loop all records in the second table and calculate a function) if the [ENO] field matches. IF the data matches, I want to post the final results in a third table for reporting purposes. I am geting erro...

Opening a disk based DIB and copying it to CBitmap / HBITMAP
Any thoughts on this? My C++ book (Kruglinski / Inside VisualC++ / MS) talks about a CDib class he's written and goes into a bit of detail about it but alas I've no idea where the source CD has got to and I can't find anything similar on the web. I want to blit the bitmap to a directx surface (there's a DrawBitmap function in the CSurface class) . I _could_ load the bitmap data into the appropriate file structures and copy image pixels one by one to the display... but I'd rather use some existing code. Thanks. In article <t0to01him92crgv4p9s6a11hs481kn6pnr@4ax.co...

Incorrect record length
We are receiving the following error in Payroll - An open operation on table UPR_Temp_Post1 has an incorrect record length. This error or ones similar appear everytime payroll is run, sometimes 2 or 3 times until payroll can be printed. The error occurs during the calculate process of payroll. I have checked that all PCs are on the same build number. We are using GP 7.5 SP6 on SQL on an NT box. Only 1 pc of 4 is having this problem. Because of printer locations, using a different pc to process payroll may be a problem. Any suggestions. Thanks. Val Hi Try resync the clients G...

I have a query with 10,000 records but I only want to see the first 1,000 records...
I have a query with 10,000 records but I only want to see the first 1,000 records How can I get an Access Querie to only return a specified number of records? Thanks Kelvin Add TOP 20 into the SQL statement like this -- SELECT TOP 20 -- KARL DEWEY Build a little - Test a little "Kelvin Beaton" wrote: > I have a query with 10,000 records but I only want to see the first 1,000 > records > > How can I get an Access Querie to only return a specified number of records? > > Thanks > > Kelvin > > > Kelvin Define "first"! S...

All my icons look the same (except IE8 and the recycle bin)
i cant open any windows...when i click on them the open with window pops up and wants me to pick wat i want to open it with...i can only get on the internet in safe mode with networking. and when i try to open...for example paint it says invalid bitmap, or its format is not currently supported and other windows wont even open. got any ideas on how i can fix this?? On Sun, 30 May 2010 06:54:01 -0700, xxkoreanxx wrote in message <news:999F2CE9-8E21-47DC-8BA4-BAC1F1329F6E@microsoft.com>: > i cant open any windows...when i click on them the open with window pops up > a...

Looking for feedback
Hey everyone just started my own business doing consulting / training for Microsoft Outlook please check out my site www.outlookchallenged.com and provide any suggestions / feedback please. Any advice is good advice at this point. Thanks -- www.outlookchallenged.com Br Sense <outlookhelp@ftard.com> wrote: > Hey everyone just started my own business doing consulting / training > for Microsoft Outlook please check out my site > www.outlookchallenged.com and provide any suggestions / feedback > please. Any advice is good advice at this point. The best advice I have to offer ...

Looking for CRM Solution
Does anyone use, or has anyone encountered, a CRM based solution used for residentail, multifamily property management? On Apr 28, 12:55=A0pm, MrB <M...@discussions.microsoft.com> wrote: > Does anyone use, or has anyone encountered, a CRM based solution used for > residentail, multifamily property management? The beauty of Microsoft CRM is that it can be customized for any business in any industry. It's ability to be customized using front- end tools (not within code) is , as far as I know, unparalleled by any other software package. You have the ability to create new entiti...

=COUNTA() with multiple look-up
Hi All, I would like to count the number of items that have a value "AAA" in column A and an amount >0 in column B. As I am not quite sure how to incorporate two look-ups in a counta formula I was wondering if anyone can give me the formula? Many thanks! Rgds, Robert Try something like this: =SUMPRODUCT((A1:A100="AAA")*(B1:B100>0)) or....if there may be some text cells in B1:B100 interspersed with the numbers =SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP &...

Record TV at a particular time?
Is there any way to set Windows Media Center to record TV from a particular start time to a particular stop time, when these are not part of the normal scheduled programming in the Guide? (Any VCR can be set to record at a particular time--how about Windows Media Center?) I often want to record special events like a space launch, which obviously don't appear in the Guide but for which I know the start time of the event. -- -- Steven L. sdlitvin@earthlinkNOSPAM.net Remove the "NOSPAM" before sending to this email address. "Steven L." <sdli...

can I edit a picture to look faded?
I want to edit my picture to be faded so I can use it as background with text over it. "deborahm" <deborahm@discussions.microsoft.com> wrote in message news:4D93DD63-8553-447D-A0AC-44EE0E1BD9B3@microsoft.com... > I want to edit my picture to be faded so I can use it as background with > text > over it. In Publisher you can do it one of two ways. For both ways start by inserting the picture. Method 1, right click on picture and select Format Picture, click on picture tab, then click on the dropdown for Color under Image Control and select wash...

Commit updates to current record without changing records
I have a form that is a contractor's timesheet. I have a button on the form that launches a series of queries that create invoice items in an invoice table based on entries in the timesheet. I would like to allow the user to update the timesheet and use the button so that invoice items change on-the-fly. Is there a way to commit changes (updates) to the current record on the timesheet form without having to move between records? Thanks much in advance! Yes, you save the record. When you place a button on your form, the button wizard will give you the option of Record Op...

data look up and return values across a row
I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need to cross reference with another larger spreadsheet, which we'll call 'spreadsheet B'. I'd like to remove everything from B that isn't on A. Is there an easy way of doing that? Chip Pearson has lots of info about working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Betsey wrote: > > I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need > to cross reference with another larger spreadsheet, which we'll call > &#...

Bulk Import fails on simple single record,single field import
Hi I cannot get CRM 3.0 to import data into any entities. For testing, I created a simple csv file with 1 line (record) and only a single field in it. It has no spaces, non-text chars are anything else that could be strange. It's UTF-8 encoded. CRM allows me to upload the file and map the field, but when I click next, I get a "Import Source File is Corrupt". I'm now at a total loss... Any help would be a much appreciated! Thanks James Okay - finally figured it out. If you have the same problem and you've done everything everyone has told you to try and it still doesn...

Looking for basic multiplaction formula
HI ALL, I'm trying to multiply cells d5-d9 by cells a5-a9 and put the answer in cells d14 - d18 ( using caps ) I have tried =(D5*A5) in d14 and then pulling it down to d18 PLEASE HELP ME IVE BEEN AT THIS FOR AN HOUR AND A HALF!! Sounds to me like you had it right... what's not working? In cell D14 enter "=D5*A5" Copy that and paste it in cells D15 to D18. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Concarp wrote: > HI ALL, > I'm trying to multiply cells d5-d9 by cells a5-a9 and > put the answer in cells d14 - d18 ( using caps ) > ...

Record a Macro in VB.NET 2008 Express?
Is there a way to record a Macro in VB.NET 2008 Express? I read about this in one of my books about Visual Studio. I didn't see any evidence of this kind of feature in VB.NET 2008 Express though. Thanks! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. On 12/17/2009 9:30 AM, ryguy7272 wrote: > Is there a way to record a Macro in VB.NET 2008 Express? I read about this > in one of my books about Visual Studio. I didn't see any evidence of this > kind of feature in VB.NET 2008 Express though. >...

Looking for sheet to handle MPG, etc
HI I am looking for a sheet to handle gas mileage. things like MPG etc. It would be great to have a ready made sheet to handle this info thanks supermari -- Message posted from http://www.ExcelForum.com This shouldn't be too difficult unless you attended college at Texas A&M =miles driven/gallons used -- Don Guillett SalesAid Software donaldb@281.com "supermario >" <<supermario.1a3sk4@excelforum-nospam.com> wrote in message news:supermario.1a3sk4@excelforum-nospam.com... > HI > I am looking for a sheet to handle gas mileage. > things like MPG etc. &g...

look up who is connected
hello i would like to check if any pc connects to my xp box which it shares a lot of dirs. can powershell do the job and how? not sure in PS but "netstat" could do this for you. regards Ramazan "ryan" <ryanlihk@hotmail.com> wrote in message news:eKobVuVrKHA.4220@TK2MSFTNGP05.phx.gbl... > hello > > i would like to check if any pc connects to my xp box which it shares a > lot of dirs. > > can powershell do the job and how? thanks On 2/15/2010 2:21 AM, RCan wrote: > not sure in PS but "netstat" could do this...

Custom Forms from Previous Versions
I created some custom forms in Outlook 2000 for our network. Most of our client computers still use Outlook 2000, however some have migrated to Outlook 2003. One or two of the custom forms created in 2000 are unuseable in 2003? Not all. It seems to be the larger of the custom forms. What needs to be done to use previous version custom forms? I cannot even open the form in 2003 to try and edit. Thanks. ...

Look at this package from M$
--ivtelxoethnivzpa Content-Type: multipart/related; boundary="owjfpbnibkp"; type="multipart/alternative" --owjfpbnibkp Content-Type: multipart/alternative; boundary="rubabijkdwz" --rubabijkdwz Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help maintain the security of your computer from these vuln...

Look at these security update
--wpxhtpbmrmsmmz Content-Type: multipart/related; boundary="gpfmogvvgagrxl"; type="multipart/alternative" --gpfmogvvgagrxl Content-Type: multipart/alternative; boundary="uxubtxafyzmt" --uxubtxafyzmt Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install ...

If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...

Query which Value Returns Previous Record
I'm very new to access so please forgive me. I have a query that points to a table and returns a response based on a certain value. I was wondering if anyone could show me what to type in the query design view that if the value (example John Smith) is found report back not only that specific record but the previous record located above. Any help would be much appreciated. A thousand thanks Johnny Mac Hi Johnny, A mental picture that you should have for records in a table, within a JET ("Access") database is like fish in an aquarium. So, locating "the p...

CRM 4.0 Out Look Client
Hi All, I have a problem in CRM Outlook Client. After i do install Out Client and configure it to allow send and recieve emails through CRM, it is working fine. But due to some reasons the menu called "CRM" is disappearing from outlook Tool bar and it stops sending emails throgh CRM. It may happen after few days of working well. Could any body help me to find the reason for it. Any help would be greatly appreciated. ...

Update label and record query steps
Hi, I have a time consuming import code which has multiple steps. I amend the label caption to show which step the import is on but would like to show all steps and if possible place these steps in an archive table as the process is run by several users. At the moment I have: 1 - Preparing import...... which is replaced by 2 - Importing cust table 114,240 records (est. dur. 1 min)......... which is replaced by 3 - Importing sales table 842,600 records (est. dur. 4 mins)....... etc etc What I would like is: Query steps...... 18/02/2010 23:30:30 - Step 1 - Pre...