Custom Counter Question (Repost)
in [Queries]
|
From: AccessIM on 6 Nov 2009 16:20 Hello Everyone - I haven't received a response back to one of my posts so I am re-posting with the hope that someone will pick up the new post. I have created a field in a query to assign a sequence number to records. The code is below: Sequence_Number: SELECT Count (*) FROM [qryTotalDetail] AS [XX] WHERE [qryTotalDetail].[SSN]=[XX].[SSN] AND [qryTotalDetail].[INCIDENTDATE] &[qryTotalDetail].[TYPE]<=[XX].[INCIDENTDATE] & [XX].[TYPE])-1 I was entering past information as a test for when the database is running live and it ran perfect for 6 weeks and then, for some reason, on the 7th week, the sequence number of one employee was out of order. Below are the results I saw: EmployeeID SSN IncidentDate Seq# Type Points AccumTotal 6 111223333 8/13/09 1 LATE 0.50 1.00 6 111223333 8/5/09 0 LATE 0.50 1.50 6 111223333 7/30/09 2 LATE 0.50 0.50 6 111223333 4/6/98 3 START 0.00 0.00 As you can see, the top two sequence numbers are reversed and the AccumTotal field, though correct, is also in the wrong order. I have all but given up and moved on since I have a deadline nearing for this project but I still believe this is the best way to design the query. Could anyone help with some suggestions for why this may have happened and how I might correct it? Thank you so much.
From: John W. Vinson on 6 Nov 2009 18:43 On Fri, 6 Nov 2009 13:20:07 -0800, AccessIM <AccessIM(a)discussions.microsoft.com> wrote: >Hello Everyone - > >I haven't received a response back to one of my posts so I am re-posting >with the hope that someone will pick up the new post. > >I have created a field in a query to assign a sequence number to records. >The code is below: > > Sequence_Number: SELECT Count (*) FROM [qryTotalDetail] AS [XX] WHERE >[qryTotalDetail].[SSN]=[XX].[SSN] AND [qryTotalDetail].[INCIDENTDATE] >&[qryTotalDetail].[TYPE]<=[XX].[INCIDENTDATE] & [XX].[TYPE])-1 > >I was entering past information as a test for when the database is running >live and it ran perfect for 6 weeks and then, for some reason, on the 7th >week, the sequence number of one employee was out of order. Below are the >results I saw: > >EmployeeID SSN IncidentDate Seq# Type Points >AccumTotal > 6 111223333 8/13/09 1 LATE 0.50 > 1.00 > 6 111223333 8/5/09 0 LATE 0.50 > 1.50 > 6 111223333 7/30/09 2 LATE 0.50 > 0.50 > 6 111223333 4/6/98 3 START 0.00 > 0.00 > >As you can see, the top two sequence numbers are reversed and the AccumTotal >field, though correct, is also in the wrong order. > >I have all but given up and moved on since I have a deadline nearing for >this project but I still believe this is the best way to design the query. > >Could anyone help with some suggestions for why this may have happened and >how I might correct it? Thank you so much. Your expression [qryTotalDetail].[INCIDENTDATE]&[qryTotalDetail].[TYPE] is converting a date and a string to... a string. The text string "8/13/09LATE" in fact does sort before "8/5/09LATE", not chronologically, since the first different character is 1 and 5 respectively. Try specifying the rank order on the two fields separately rather than concatenating them. -- John W. Vinson [MVP]
From: Duane Hookom on 7 Nov 2009 00:00 You are converting a general date field and changing it to a string. You then expect it to sort like a date. 8/1 comes befor 8/5. If you really want to store or compare dates like this, change their format so the order is correct as a string Format([datefld],"yyyymmdd") -- Duane Hookom Microsoft Access MVP "AccessIM" wrote: > Hello Everyone - > > I haven't received a response back to one of my posts so I am re-posting > with the hope that someone will pick up the new post. > > I have created a field in a query to assign a sequence number to records. > The code is below: > > Sequence_Number: SELECT Count (*) FROM [qryTotalDetail] AS [XX] WHERE > [qryTotalDetail].[SSN]=[XX].[SSN] AND [qryTotalDetail].[INCIDENTDATE] > &[qryTotalDetail].[TYPE]<=[XX].[INCIDENTDATE] & [XX].[TYPE])-1 > > I was entering past information as a test for when the database is running > live and it ran perfect for 6 weeks and then, for some reason, on the 7th > week, the sequence number of one employee was out of order. Below are the > results I saw: > > EmployeeID SSN IncidentDate Seq# Type Points > AccumTotal > 6 111223333 8/13/09 1 LATE 0.50 > 1.00 > 6 111223333 8/5/09 0 LATE 0.50 > 1.50 > 6 111223333 7/30/09 2 LATE 0.50 > 0.50 > 6 111223333 4/6/98 3 START 0.00 > 0.00 > > As you can see, the top two sequence numbers are reversed and the AccumTotal > field, though correct, is also in the wrong order. > > I have all but given up and moved on since I have a deadline nearing for > this project but I still believe this is the best way to design the query. > > Could anyone help with some suggestions for why this may have happened and > how I might correct it? Thank you so much.
From: KenSheridan via AccessMonster.com on 7 Nov 2009 14:55 I assume that the Type column is brought into play in determining the Seq# and AcumTotal values to cater for the possibility of two or more incidents for the same employee but of different types on the same day. Correlate the subqueries with the outer query separately on the SSN, Incident date and Type columns, e.g. SELECT TD1.EmployeeID, TD1.SSN, TD1.IncidentDate, (SELECT COUNT(*) FROM qryTotalDetail AS TD2 WHERE TD2.SSN = TD1.SSN AND TD2.IncidentDate >= TD1.IncidentDate AND TD2.Type >= TD1.Type)-1 AS [Seq#], TD1.Type, TD1.Points, (SELECT SUM(Points) FROM qryTotalDetail AS TD2 WHERE TD2.SSN = TD1.SSN AND TD2.IncidentDate <= TD1.IncidentDate AND TD2.Type <= TD1.Type) AS AccumTotal FROM qryTotalDetail AS TD1 ORDER BY TD1.SSN, TD1.IncidentDate DESC, TD1.Type DESC; This should give you a set of Seq# values per SSN in descending date order, starting with zero in each case, and the cumulative total of points per SSN value, incrementing in ascending date order, which, from your sample rows, appears to be the result you want . Ken Sheridan Stafford, England AccessIM wrote: >Hello Everyone - > >I haven't received a response back to one of my posts so I am re-posting >with the hope that someone will pick up the new post. > >I have created a field in a query to assign a sequence number to records. >The code is below: > > Sequence_Number: SELECT Count (*) FROM [qryTotalDetail] AS [XX] WHERE >[qryTotalDetail].[SSN]=[XX].[SSN] AND [qryTotalDetail].[INCIDENTDATE] >&[qryTotalDetail].[TYPE]<=[XX].[INCIDENTDATE] & [XX].[TYPE])-1 > >I was entering past information as a test for when the database is running >live and it ran perfect for 6 weeks and then, for some reason, on the 7th >week, the sequence number of one employee was out of order. Below are the >results I saw: > >EmployeeID SSN IncidentDate Seq# Type Points >AccumTotal > 6 111223333 8/13/09 1 LATE 0.50 > 1.00 > 6 111223333 8/5/09 0 LATE 0.50 > 1.50 > 6 111223333 7/30/09 2 LATE 0.50 > 0.50 > 6 111223333 4/6/98 3 START 0.00 > 0.00 > >As you can see, the top two sequence numbers are reversed and the AccumTotal >field, though correct, is also in the wrong order. > >I have all but given up and moved on since I have a deadline nearing for >this project but I still believe this is the best way to design the query. > >Could anyone help with some suggestions for why this may have happened and >how I might correct it? Thank you so much. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200911/1
From: AccessIM on 9 Nov 2009 12:41 That was it. Thank you Duane. "Duane Hookom" wrote: > You are converting a general date field and changing it to a string. You then > expect it to sort like a date. 8/1 comes befor 8/5. If you really want to > store or compare dates like this, change their format so the order is correct > as a string Format([datefld],"yyyymmdd") > > -- > Duane Hookom > Microsoft Access MVP > > > "AccessIM" wrote: > > > Hello Everyone - > > > > I haven't received a response back to one of my posts so I am re-posting > > with the hope that someone will pick up the new post. > > > > I have created a field in a query to assign a sequence number to records. > > The code is below: > > > > Sequence_Number: SELECT Count (*) FROM [qryTotalDetail] AS [XX] WHERE > > [qryTotalDetail].[SSN]=[XX].[SSN] AND [qryTotalDetail].[INCIDENTDATE] > > &[qryTotalDetail].[TYPE]<=[XX].[INCIDENTDATE] & [XX].[TYPE])-1 > > > > I was entering past information as a test for when the database is running > > live and it ran perfect for 6 weeks and then, for some reason, on the 7th > > week, the sequence number of one employee was out of order. Below are the > > results I saw: > > > > EmployeeID SSN IncidentDate Seq# Type Points > > AccumTotal > > 6 111223333 8/13/09 1 LATE 0.50 > > 1.00 > > 6 111223333 8/5/09 0 LATE 0.50 > > 1.50 > > 6 111223333 7/30/09 2 LATE 0.50 > > 0.50 > > 6 111223333 4/6/98 3 START 0.00 > > 0.00 > > > > As you can see, the top two sequence numbers are reversed and the AccumTotal > > field, though correct, is also in the wrong order. > > > > I have all but given up and moved on since I have a deadline nearing for > > this project but I still believe this is the best way to design the query. > > > > Could anyone help with some suggestions for why this may have happened and > > how I might correct it? Thank you so much.
|
Pages: 1 Prev: XIRR Calculation Next: Query to Find Records with only 1 transaction |