Numbering in Access 4

[jocasio]

Hey gang:

I have a question concerning numbering in Access. What I'm trying to do is run a make-table that will retrieve a bunch of records from an existing table and add it to a new table. What I would like to do is have a column that numbers the records sequentially based on a particular field.
It would look like this:

1 john
2 john
3 john
1 james
2 james
3 james
4 james
1 janet
2 janet

The names are coming from the table I'm querying and the numbers are being assigned dynamically. I know I can't use auto number becuase it would go from 1 to n. Please let me know if there is a way to have Access start back to 1 based on a new name coming up.

Thanks

jocasio

 

[dhookom]

There needs to be some logic regarding which John is 1 and which James is 4. Do you have a field that might suggest this?

Also, please provide a table name and some field names.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jocasio]

dhookom:

These are just records in the first table. I want to have the occurences of each name numbered sequentially start with number 1. They are not sequential in the table being queried, but I am ordering them in the make-table. What I would like is for column 1 to have the sequential numbers, but when a new name appears, the numbering starts over.

Thanks

jocasio

So the make-table columns would be

occurance name
1 john
2 john
3 john
1 james
2 james
...

 

[dhookom]

Are you suggesting there is just one field in your existing table? If so, there is no way to sequence them without some field in some table that identifies which record should get the 1, 2, or 3.

If you truly have no other fields, then add an autonumber field named "ID". Then create a query like:
SELECT
(SELECT Count(*)
FROM tblA A
WHERE A.[Name]=tblA.[Name]
and A.ID <=tblA.ID) as Occurance,
[Name]
FROM tblA;

Change the above into a make table query or use it as the source for a make table query.

BTW: Name is not a good name for any object in Access since every object has a name property.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jocasio]

dhookom :

Thanks again for the reply. What I want to do is create a new column in my make-table and number it as a new row is inserted (as is the case with autonumber). The difference is I want the numbers to start back at one when a new name appears.

If this can be done, please let me kow.

Thanks

Juan

 

[dhookom]

Did you try the sql that I suggested? It should create the new column.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jocasio]

Duane:

Thanks a bunch that worked! I created my original make-table (actually I imported data as it would look from my make-table query) and used your statement. The one thing I was wonder was is there a way to add the column without having to modify the table that was made. So for example my make-table with it's 'name' column was the only field. I would like to have another field automatically be created. Is it possible?

Thanks again for your help!

jocasio

 

[dhookom]

You can do this by creating a table of numbers [tblNums] with one field [Num] and values 1 - whatever. Then create a totals query [qgrpNameCount] based on your existing table:
[tt]
SELECT [Name], Count([Name]) AS NumOf
FROM tblA
GROUP BY [Name];
[/tt]
Then create a maketable query based on tblNums and qgrpNameCount:
[tt]
SELECT qgrpNameCount.Name, tblNums.Num AS Occurance INTO tblNewTable
FROM tblNums, qgrpNameCount
WHERE (((tblNums.Num)<=[NumOf]))
ORDER BY qgrpNameCount.Name, tblNums.Num;
[/tt]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jocasio]

dhookom:

Thanks again for your help. I got everything running. I did encounter one problem and it's more of a question about it as opposed to getting a solution for it:

If I run a select statement using your suggestion, it does not return the correct number of rows. In fact it will return an arbitrary number of rows each time and not always the same rows. If I run a make-table on the query, it works every time.

Can you think of a reason why this wouold happen? Again, I now have the solution, thanks to you, but I was wondering if there was an explanation for the anomolies.

Many thanks for your help

jocasio

 

[dhookom]

What are your SQL views of the queries that "return an arbitrary number of rows"?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Hosacans]

i did a search and came to this thread. I have a similar problem but instead of names i am working with ID#. each record with the same ID is uniquely identified by a time field. and i want to sequence the same ID records from earliest to latest time.

i.e.

original table

ID | Time
1111 | 1:00
1111 | 6:00
1111 | 3:00
2222 | 4:00
2222 | 8:00
2222 | 2:00
2222 | 11:00

the query would output:

Occurance | ID |Time
1 |1111 |1:00
2 |1111 |3:00
3 |1111 |6:00
1 |2222 |2:00
2 |2222 |4:00
3 |2222 |8:00
4 |2222 |11:00

i think this would be a loop to compare time within a loop to compare IDs. but i have no idea how to do it in Access (XP)

thanks
H

 

[dhookom]

This can be done with a subquery

SELECT (Select Count(*)
    FROM tblOriginal O
    WHERE O.ID=tblOriginal.ID AND O.Time<=tblOriginal.Time) AS Occurance,
    tblOriginal.ID, tblOriginal.Time
FROM tblOriginal
ORDER BY tblOriginal.ID, tblOriginal.Time;

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Hosacans]

thanks dhookom, that did the trick. now i wont get fired

H

 

[dhookom]

You owe me big time ;-)

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]