Search results

I still am looking for a way to write a query to just get the matching lines where the Serial Number matches and the Action Code has a 1 in the first line and an Action Code of 2 in the second. I think I know why I can't figure this out. hahaha! its HARD

i looked at the 2s that returned and actually they were the 2s i'm looking for i just want to display that row preceding the 2 that shows the Code 1 as well. Anyway what ever you wrote here actually returned the right 2s. woo hoo! that was brilliant. i just need the Code 1 in the preceding...

CJ_London! i'm getting close. Here's what I used SELECT * FROM myTable WHERE ActionCode=2 and nz ((SELECT TOP 1 ActionCode FROM myTable as T WHERE SerialNumber=myTable.SerialNumber AND ActionDate<myTable.ActionDate ORDER BY ActionDate DESC)) =1 The result when I plugged it all in was...

Oh CJ thanks so much! I can't wait to try this... this is so great!

oh dear! you are so right! I mean where the 2 follows the 1 in the next record!.. i'm sorry.. geeze.. now I can't see the woods or the trees! haha!.. simply i'm looking for Matching Serial Numbers where the Action Code goes from a 1 to a 2. For sure I do not want any 3's or any other 1's...

Well, I left out that sometimes a Serial Number will just have a Code 1 with no other record and so I don't want that record returned. I just need the Pairs where the 1 follows the 2. In a similar way, but I didn't show one in the Raw Data the single record could just be a 2 and so I don't...

Serial Number Date Action Code ___________ ____ ___________ 2006A10623 7/26/2012 3 2006A 11/13/2009 1 2006A 11/1/2010 2 2006A28 7/22/2011 3 2006A28 11/15/2012 1...

Maybe I could save that query with the <>3 then use is as Raw Data for another query to bleed off the stray Action Code "1"s i'm getting . The cool part is the pairs are showing up with the <>3 rountine! I just need to eliminate the excess! woo hoo!

thanks! yes my basic query is just in Design View and I have Serial Number set to Ascending and Date set to Ascending. I put the <> 3 for Criteria and that eliminated the 3s but can I eliminate the stray other records? I still get a lot of Action Code "1"s by themselves and i'm trying to get...

that's it! pretty much the raw data! I need a return that gives me just the highlighted yellow! woo hoo! dern trees are in the way and I can't see the FOREST! HELP! i'm using ACCESS 2007 and love it and I can't seem to WRITE a QUERY to pull this data. This is ONE single table so I thought...

i've got a basic Serial Number tracking db i'm trying to use in Access. All works fine except trying to write a query for a particular set of records. Each record simply contain a Serial Number, Date, then Code. The Code(s) are pretty simple they are either 1 or 2 or 3. I have piles of...