Access 2007 Duplicate Record query problem

R

radair1960

Registered User.
Local time
Yesterday, 20:17
Joined
Feb 3, 2015
Messages
11
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.
 
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
 
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!
 
Last edited:
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 ?
 
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!
 
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!
 
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!
 
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
 
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.
 
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
 
sorry, got that wrong

You are saying

I just need the Pairs where the 1 follows the 2
Click to expand...

So actually, not records will be returned
 
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!
 
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
 
Oh CJ thanks so much! I can't wait to try this... this is so great!
 
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.
 
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!
 
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
 
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]
 
You must log in or register to reply here.

Similar threads

essaytee
Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table
Replies
11
Views
643
essaytee
essaytee
H
Query combining records
Replies
13
Views
732
theDBguy
theDBguy
C
Solved TempVars as Query criteria
2
Replies
22
Views
889
ClaraBarton
C
S
Link records between two tables based on range?
Replies
3
Views
430
Mark_
M
T
Solved row number in subform
Replies
4
Views
346
TipsyWolf
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom