Sequence numbers that reset on specific criteria

[ErinL]

Hello everyone - I have looked at so many of the posts in the history but cannot find what I am looking for. I have been able to create a sequence number field but this isn't exactly what I need.

What I need is for the series to start renumbering every time the word "Door" appears in the StopLocation field. Here is an example of how I would like it to look:

EmployeeName Date StopLocation Sequence
Jane Doe 5-18 Door 0
Jane Doe 5-18 Slot 1
Jane Doe 5-18 Slot 2
Jane Doe 5-18 Door 0
Jane Doe 5-18 Door 0
Jane Doe 5-18 Slot 1
Jane Doe 5-18 Door 0
Jane Doe 5-18 Slot 1
Jane Doe 5-18 Slot 2
Jane Doe 5-18 Slot 3

I would appreciate any suggestions as I have been trying to get this on my own for quite some time.

Thank you in advance.

 

[Beetle]

A few questions;

What method are you currently using to generate the sequernce number? Maybe it just needs a slight modification.

Do you need to do this only for new records, or do you need to go back and update existing data with the correct sequence numbers?

Your example data does not contain a field to uniquely identify each record. Can you provide a little more info on your table structure?

 

[ErinL]

Thank you so much for the quick response! There are more fields than in my example. I was trying to conserve space in the post but I see that maybe I left too much out. Below is the query I created first and was able to correctly get the sequence number working properly:

SELECT (SELECT Count(*) FROM
qryDockRuns AS T WHERE T.EmployeeID = qryDockRuns.EmployeeID AND T.RecordNumber < qryDockRuns.RecordNumber) AS Sequence, qryDockRuns.EmployeeID, qryDockRuns.Employee, qryDockRuns.Date, qryDockRuns.Time, qryDockRuns.Type, qryDockRuns.Container, qryDockRuns.StopLocation, qryDockRuns.From, qryDockRuns.To
FROM qryDockRuns
ORDER BY qryDockRuns.EmployeeID, qryDockRuns.Date, qryDockRuns.Time;

The Sequence field is correct in that it numbers the records for each associate 0 through "n", restarting at 0 with each associate.

The RecordNumber field is an autoNumber field in tblDockRuns which feeds qryDockRuns and would be the unique field you mentioned in your reply post.

This is a new project so there aren't any previous records that would need to be updated. However, it will be ongoing and will need to update with new information being added daily (every day I will import the data from the previous day's moves to tblDockRuns).

Thanks again for your time.

 

[Beetle]

Something like this might work for you;

SELECT IIf([StopLocation]="Door", 0, (SELECT Count(*) FROM
qryDockRuns AS T WHERE T.EmployeeID = qryDockRuns.EmployeeID AND T.RecordNumber < qryDockRuns.RecordNumber And T.StopLocation <> "Door")+1) AS Sequence, qryDockRuns.EmployeeID, qryDockRuns.Employee, qryDockRuns.Date, qryDockRuns.Time, qryDockRuns.Type, qryDockRuns.Container, qryDockRuns.StopLocation, qryDockRuns.From, qryDockRuns.To
FROM qryDockRuns
ORDER BY qryDockRuns.EmployeeID, qryDockRuns.Date, qryDockRuns.Time;

 

[Galaxiom]

Note that Date, Time, Type, From, To, Sequence and Container are all reserved words and should be avoided for names.

http://allenbrowne.com/AppIssueBadWord.html

 

[ErinL]

I tried the sql you posted and it is close. It inputs a "0" for stop locations of "Door" but it counts all the stop locations of slot. I won't include all the fields but here is an example:

Sequence EmployeeName Date Time ........ StopLocation
0 Jane Doe 5/18/11 7:00 Door
1 Jane Doe 5/18/11 7:03 Slot
2 Jane Doe 5/18/11 7:04 Slot
0 Jane Doe 5/18/11 7:10 Door
3 Jane Doe 5/18/11 7:12 Slot

Where it counted the #3 slot I wanted it to be a #1 again to show a new set of trips from the door.

Thank you again. Any other suggestions?

 

[Beetle]

Now you're getting tricky

Try this;

SELECT IIf([StopLocation]="Door", 0, (SELECT Count(*) FROM
qryDockRuns AS T WHERE T.EmployeeID = qryDockRuns.EmployeeID AND T.RecordNumber < qryDockRuns.RecordNumber And T.StopLocation <> "Door" And T.Time > DMax("Time", "qryDockRuns", "EmployeeID=" & T.EmployeeID & " And StopLocation='Door' And Date=#" & qryDockRuns.Date & "# And Time<#" & qryDockRuns.Time & "#"))+1) AS Sequence, qryDockRuns.EmployeeID, qryDockRuns.Employee, qryDockRuns.Date, qryDockRuns.Time, qryDockRuns.Type, qryDockRuns.Container, qryDockRuns.StopLocation, qryDockRuns.From, qryDockRuns.To
FROM qryDockRuns
ORDER BY qryDockRuns.EmployeeID, qryDockRuns.Date, qryDockRuns.Time;

Note: Since this method relies in part on records with an earlier Date and Time having a lower record number, it's possible it could fail at some point if you are using an Autonumber field for the RecordNumber. That's because, although Autonumbers are genererally sequentially increasing, there is no guarantee that they always will be. It's possible that it could even generate a negative number, as the only guarantee is that each number will be unique. Just FYI.

BTW - Galaxiom brought up a very good point about your field names that I really hadn't paid attention to the first time around. You really should change those or it is very likely to cause you problems in the future.

 

[ErinL]

Sorry to be difficult () but this one numbers each of the "slot" stop locations as 1 like this:

Sequence EmployeeName Date Time ........ StopLocation
0 Jane Doe 5/18/11 7:00 Door
1 Jane Doe 5/18/11 7:03 Slot
1 Jane Doe 5/18/11 7:04 Slot
0 Jane Doe 5/18/11 7:10 Door
1 Jane Doe 5/18/11 7:12 Slot

I have been fighting with this one for some time. I did read Galaxiom response as well and I will take both your advice and change the field names. You don't think that has anything to do with the sql not working right, do you?

 

[Beetle]

Repost your SQL as it is right now

 

[ErinL]

Here you go:

SELECT IIf([StopLocation]="Door",0,(SELECT Count(*) FROM qryDockRuns AS T WHERE T.EmployeeID = qryDockRuns.EmployeeID AND T.RecordNumber < qryDockRuns.RecordNumber And T.StopLocation <> "Door" And T.Time > DMax("Time", "qryDockRuns", "EmployeeID=" & T.EmployeeID & " And StopLocation='Door' And Date=#" & qryDockRuns.Date & "# And Time<#" & qryDockRuns.Time & "#"))+1) AS Sequence, qryDockRuns.EmployeeID, qryDockRuns.Employee, qryDockRuns.Date, qryDockRuns.Time, qryDockRuns.Type, qryDockRuns.Container, qryDockRuns.StopLocation, qryDockRuns.From, qryDockRuns.To
FROM qryDockRuns
ORDER BY qryDockRuns.EmployeeID, qryDockRuns.Date, qryDockRuns.Time;

 

[Beetle]

Hmmmm.......

I put together a table with some test data and a query of that table named qryDockRuns that returns the following;


I then applied the Totals query, exactly as you posted it, and get the following results;





This may have something to do with your recode numbers. Are they sequentially increasing?

Edit: Images did not come through right. I have to go to a meeting so I'll post back later.

 

[ErinL]

Yes, the record numbers are all in sequential order. The only difference I see between my table and the table you created is that the time in my field does not always increase with every record. It increases with the employee number but when a new employee number starts the time shows their moves starting with the first time for them. Here is an example (I shortened the number of fields to only the ones that pertain to the last post since you know what all the fields are already and am using your sample data):

RecordNumber EmployeeID EmployeeName Date Time
1 1 me 5/23/11 7:00AM
2 1 me 5/23/11 7:02AM
3 1 me 5/23/11 7:05AM
4 2 you 5/23/11 6:00AM
5 2 you 5/23/11 6:15AM
6 1 me 5/24/11 7:02AM
7 1 me 5/24/11 7:10AM
8 2 you 5/24/11 6:05AM
9 2 you 5/24/11 6:23AM

I must be doing something wrong if yours works. I am happy to see that it is possible to do since I was starting to wonder. Thanks again for all your time.

Do you think the Time field is causing the problem since it isn't always increasing in concurrence with the record number?

 

[Beetle]

OK, I made another slight change (added a date comparison in the sub-query). See if this works any better.

SELECT qryDockRuns.RecordNumber, IIf([StopLocation]="Door",0,(SELECT Count(*) FROM qryDockRuns AS T WHERE T.EmployeeID = qryDockRuns.EmployeeID AND T.Date = qryDockRuns.Date And T.RecordNumber < qryDockRuns.RecordNumber And T.StopLocation <> "Door" And T.Time > DMax("Time", "qryDockRuns", "EmployeeID=" & T.EmployeeID & " And StopLocation='Door' And Date=#" & qryDockRuns.Date & "# And Time<#" & qryDockRuns.Time & "#"))+1) AS Sequence, qryDockRuns.EmployeeID, qryDockRuns.Employee, qryDockRuns.Date, qryDockRuns.Time, qryDockRuns.StopLocation, qryDockRuns.From, qryDockRuns.To
FROM qryDockRuns
ORDER BY qryDockRuns.EmployeeID, qryDockRuns.Date, qryDockRuns.Time;

 

[ErinL]

Unfortunately I get the same results (i.e. all "Slot" stop locations being assigned a sequence of 1.

 

[Beetle]

Can you attach a copy of your db with any sensitive data removed? Just the table(s) and queries related to the problem is all that would be necessary. If you can please attach it in .mdb (A2003 or earlier) format.

 

[ErinL]

I'm not sure if I did the attachment right as I have never done that before. Please let me know. Thank you.

 

[Beetle]

OK, I took a look at your db.

Since the Time field in tblDockRuns is not a Date/Time field, but rather a Text field with values like 06.22.34, etc., the query could not really determine which value was lesser/greater. You can resolve this by using the TimeValue function on that field in qryDockRuns. Give it a field alias of RunTime for example, so the field will look like;

RunTime: TimeValue([YourTimeField])

This will convert it to an actual time value that can be evaluated. Since the field in qryDockRuns is now called RunTime, the other query needs to be modified to reflect that. Here is modified SQL;

SELECT IIf([StopLocation]="Door",0,(SELECT Count(*) FROM qryDockRuns AS T WHERE T.EmployeeID = qryDockRuns.EmployeeID AND T.RecordNumber < qryDockRuns.RecordNumber And T.StopLocation <> "Door" And T.RunTime > DMax("RunTime", "qryDockRuns", "EmployeeID=" & T.EmployeeID & " And StopLocation='Door' And Date=#" & qryDockRuns.Date & "# And RunTime<#" & qryDockRuns.RunTime & "#"))+1) AS Sequence, qryDockRuns.EmployeeID, qryDockRuns.Employee, qryDockRuns.Date, qryDockRuns.RunTime, qryDockRuns.Type, qryDockRuns.Container, qryDockRuns.StopLocation, qryDockRuns.From, qryDockRuns.To
FROM qryDockRuns
ORDER BY qryDockRuns.EmployeeID, qryDockRuns.Date, qryDockRuns.RunTime;

I tested it and I believe it returns the results you want but it is slow (and this is on a table with only 194 records. If your actual data encompasses thousands of records then performance may be unacceptable). Using a method like this is, unfortunately, going to run slow. It's possible it could be a little faster by using a function to calculate the sequence. I'll play around with that and if I come up with a faster method I'll post back.

 

[ErinL]

Oh WOW!!!! You did it!! It works! Thank you so much for all your help. I worked on that myself for quite awhile before posting for help and I appreciate you taking the time to work with me and get me the results I needed. Thanks again!