Query to Add Counters

[DanAuber]

I have data that looks like this in a table:

Table_1
F1 F2 F3 F4 F5
A B C D 56667
A B C D 56667
A B C D 56667
Y H D F 54463
Y H D F 54463
D F H G 77821
D F H G 77821
D F H G 77821
D F H G 77821

I want to add an extra row that acts as a counter each time so that the data above would look like this:

F1 F2 F3 F4 F5 F6
A B C D 56667 1
A B C D 56667 2
A B C D 56667 3
Y H D F 54463 1
Y H D F 54463 2
D F H G 77821 1
D F H G 77821 2
D F H G 77821 3
D F H G 77821 4

Can anyone help me write a query/function that will do this ?

Thanks for any help

 

[dhookom]

This is near impossible without you describing a field or fields that determine the order within each grouping. Are the records all nearly identical as your sample or are there some details you aren't providing?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DanAuber]

Thanks for looking at this Duane - I have worked out a way to do it !

 

[SkipVought]

Dan,

It is almost never a good idea to store aggregations in a table.

First of all, it ASSUMES order in the table. The very definition of a table assumes NO ORDER.

"The basic idea behind the relational model is that a database consists of a series of unordered tables (or relations) that can be manipulated using non-procedural operations that return tables."

You might be better off thinking in terms of a query (report) that performs what you want on demand.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[whitespaces]

Please, please can you tell me the solution I have exactly the same problem my data is attached for info. Basically the records are sorted by YEar and Term (SP = Spring, SU = Summer, AU = Autumn) but I need the counter to reset after each pupil (When the UPN changes) as shown in the highlighted column. Also, I've included the sort table in another sheet.

Many thanks

 

[whitespaces]

Sorry the new link to the file is

http://www.box.net/shared/r7o7id0y8p

 

[dhookom]

Try:

SELECT MainTable.*, (SELECT Count(*) FROM MainTable N INNER JOIN OrderTable O ON (N.Term = O.Term) AND (N.Year = O.Year) WHERE N.UPN = MainTable.UPN and O.Order<=OrderTable.Order) AS [What I want]
FROM MainTable INNER JOIN OrderTable ON (MainTable.Year = OrderTable.Year) AND (MainTable.Term = OrderTable.Term)
ORDER BY MainTable.UPN, OrderTable.Order;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[whitespaces]

dhhokom you're a genious.

Many many thanks mate, I can't thank you enough. I've never used nested SELECT statements within SELECT statements like this. I'll have to learn how to do them.

Would you say this was a fairly complex query then? Because I thought I knew a lot about queries etc. but never knew how to do this.

Cheers

 

[whitespaces]

'genius' I meant of course!!

 

[dhookom]

The second "Select" is a subquery. It is somewhat complex for an Access query. You could also perform the same task with a DCount() but it would be very slow.

I expect PHV could create another version without the subquery but PH is smarter than I am ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]