Problem with Sequence Field

[ErinL]

Hi Everyone -

I have a problem when I try to use a sequence field in another query.

I have a query called qryIncidentDropOffDates. In this query I have created a field called "Sequence" with the following:

(SELECT Count(*) FROM qryIncidents AS T WHERE T.SVNumber = qryIncidents.SVNumber AND T.IncidentDate <= qryIncidents.IncidentDate)

This works perfect and numbers all the records properly 1 through 6.

However, I am trying to create another query based on qryIncidentDropOffDate with certain criteria that pulls only one of the 6 records from that query. When I put the "Sequence" field in the grid, the field value is 0 and not the value it shows in qryIncidentDropOffDate.

For example, the record numbered 3 in qryIncidentDropOffDate shows a 0 in the new query and it does this no matter what record number I isolate in the new query (the sequence field is always 0).

Does anyone know why this is happening and what I can do to stop it?

Thank you in advance.

 

[vbaInet]

Can we see your db Erin.

 

[ErinL]

I have never attached anything to one of these posts so I hope I am doing this right. Let me know if you don't see it.

 

[vbaInet]

So what are the names of the queries I should look at?

 

[ErinL]

qryIncidentDropOffDates is the name of the query that has the original "Sequence" field and qryReasonForDiscipline2 is the query that I created based on qryIncidentDropOffDates but the "Sequence" field does not show the right number.

 

[vbaInet]

It looks like it doesn't handle very well when you're referring to a union query within.

If you're eventually going to present this in a report, then you can use the Running Sum property of a control to get the sequential numbering.