Access 2007 Duplicate Record query problem (1 Viewer)

radair1960 · Feb 3, 2015

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 Records, but need to be able to write a query that returns just the Codes 1 & 2 in sequence by ascending date but i have to be darn sure there is "not" a code "3" in between the Codes 1 & 2 by ascending date. My entire raw data is tracking of parts (serial number) by an action code or either a 1 or 2 or 3. The benefit of returning all records with the same Serial Number that have a 1 then followed by a 2 in the next record is extremely beneficial to me. i have been working with this silly query since right before Christmas! Seems like just a one table query should suffice! hahaha! I just cant see the woods for the trees i think.

CJ_London · Feb 3, 2015

I think I understand what you want but suggest you post some sample data of your starting dataset and then an example of what you want to see as a result.

The example data should include situations where for example you only have a 1 and 3, 2 and 3 etc - i.e. all the possible permutations

radair1960 · Feb 3, 2015

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 surely this is a simple query. Thanks for all who think with me!

Minty · Feb 3, 2015

If they all have a code you simply need to set the criteria for Code to <>3
Then sort by serial number and then code , then date, all in one query.

Can you post up the SQL of your query you currently have ?

radair1960 · Feb 3, 2015

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 these matches of "1 + 2". haha!

radair1960 · Feb 3, 2015

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!

radair1960 · Feb 3, 2015

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
206A299 4/13/2010 1
206A299 10/13/2010 2
26A2995 2/23/2012 3
2007A13335 9/29/2010 3
2007A13335 11/1/2010 3
2007A13335 8/21/2011 2
2007A13335 8/21/2011 3
2007A13335 8/2/2012 1
2007A13335 6/24/2013 2
2007A13335 6/28/2013 3

that's the raw data basically.
all i'm looking for is a return on a query in Access that will only bring back where the Serial Number is the same and the Code is 1 on one line the next record will have the same Serial Number with a Code of 2. These will be just pairs basically in my result. Geeze, i'm wearing myself out trying to figure this one out! Thanks for thinking with me!

radair1960 · Feb 3, 2015

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 what that in the return either. Just the simple pair of a 1 to a 2 in the Code field. Anyway.. I thank all

Minty · Feb 3, 2015

Can you edit your post with all the data in it, and put [ code ] tags around it as it is playing havoc with the forum spacing on this thread.

Then we can look at your query a bit more in depth... Now you have added some extra criteria query.

CJ_London · Feb 3, 2015

Please edit you post #3 to remove the data line - it is making it extremely difficult to follow.

So to summarise

You want to return all records with an action code of 2 and the preceding one for the same serial number by date is 1.

You don't care if there is a later one with a action code of 3 so for this data, you want the ones in red

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
206A299 4/13/2010 1
206A299 10/13/2010 2
26A2995 2/23/2012 3
2007A13335 9/29/2010 3
2007A13335 11/1/2010 3
2007A13335 8/21/2011 2
2007A13335 8/21/2011 3
2007A13335 8/2/2012 1
2007A13335 6/24/2013 2
2007A13335 6/28/2013 3

CJ_London · Feb 3, 2015

sorry, got that wrong

You are saying

I just need the Pairs where the 1 follows the 2

So actually, not records will be returned

radair1960 · Feb 3, 2015

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 just a sequence of the initial record being a value of 1 then the very next record with identical Serial Numbers where that value is now a 2. these are in order by date making sense that the action of 2 was on a later date than the action of a 1. i'm still thankful someone is looking at this! thanks for bearing with me!

CJ_London · Feb 4, 2015

OK, your query will be along the following lines, you will need to change names to suit your table/fields - don't forget the square brackets if there are spaces in names and that date (per you sample data) is a reserved word

Code:

 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

radair1960 · Feb 4, 2015

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

radair1960 · Feb 4, 2015

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 just rows with the CODE 2 only. What did I goof up on here? I actually built a table called myTable and loaded it with the test data I posted here. I know i'm just missing a silly thing. hahha! Thanks for looking at this.

radair1960 · Feb 4, 2015

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 record to show the "pairs". Showing the record advancing from a Code 1 to a Code 2. many thanks for even trying!

radair1960 · Feb 6, 2015

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

CJ_London · Feb 6, 2015

So figure it out

this is the criteria to find the 2's

Code:

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

so if you want the 1's as well it would be the above OR - I've highlighted the bits that need changing in red

Code:

ActionCode=[COLOR=red]2 [/COLOR]and nz ((SELECT TOP 1 ActionCode FROM myTable as T WHERE SerialNumber=myTable.SerialNumber 
AND ActionDate[COLOR=red]<[/COLOR]myTable.ActionDate ORDER BY
ActionDate [COLOR=red]DESC[/COLOR])) =[COLOR=red]1[/COLOR]

Access 2007 Duplicate Record query problem (1 Viewer)

radair1960

Registered User.

CJ_London

Super Moderator

radair1960

Registered User.

Minty

AWF VIP

radair1960

Registered User.

radair1960

Registered User.

radair1960

Registered User.

radair1960

Registered User.

Minty

AWF VIP

CJ_London

Super Moderator

CJ_London

Super Moderator

radair1960

Registered User.

CJ_London

Super Moderator

radair1960

Registered User.

radair1960

Registered User.

radair1960

Registered User.

radair1960

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread