Autonumber Type Field In A Query 2

[Craig0201]

Hi,

I've got a query where I need to put a sequential number in. It's just a simple select query based upon a taking a value from a form as the criteria. The are sorted sequentially based upon the primary key (an autonumber in the table). From the primary key, I need to number the records so that the first record has a number 1 attached to it, the second has a 2, etc.....

Probably dead easy but i've no idea! Help anyone?

Craig

 

[kathryn]

What are you going to do with the query? Is it the basis of a form or a report? You can do the numbering in the form or report pretty easily. Kathryn

 

[Craig0201]

Unfortunately not. The numbering will be used in the criteria of a subquery.

The alternative would be to find a way of pulling the last six records in the original query. My manager wants to know some volumes but wants them to be only over the last six times the customer has dealt with us.

Craig

 

[kathryn]

Pulling the last (or TOP) records is not difficult. Create the query with the sorting set so that your six records are the first six you see. Then in Query Design view look at the toolbar and find the drop down box that says All. Change that to 6 and you've got it. Kathryn

 

[Craig0201]

Thanks!

Can I run aggregate functions from the query directly or will I then need to sub query?

Craig

 

[kathryn]

Sorry, I'm not sure what you mean. Could you explain when you want to aggregate; before or after the TOP query? Kathryn

 

[Craig0201]

It's an aggregate on the top (which works a dream, may I say!). I was going to write a second query to calculate the aggregates which does work but just wondered if I could do it neater in the one query.

Craig

 

[kathryn]

No, I don't think you can. I just did a quick test and my aggregate still returned six records, and it should have returned only four. So the query must do the aggregate first and then return the top 6. Kathryn

 

[Cagliostro]

At the first create this function in a module:
Function zzz() As Integer
Static xxx As Integer
xxx = xxx + 1
zzz = xxx
End Function

After it just do:
select xxx(), a,b,c from sometable John Fill

ivfmd@mail.md

 

[SyedAqif]

I think u want to attach a serial number type field. Just write a query like this:

Table: Survival
Fields: ID,Time,Status

SELECT (Select Count(*)+1 from Survival where Time<E.time) AS SNo, E.Time, E.Status FROM Survival AS E;

SNo Time Status
1 2.5 1
2 4.1 0
3 4.6 1
4 6.4 1


Good Luck

Cheers!
Aqif