Sequence Numbers!!!

[WhizzkidWallace]

Hi,

I have a table with two fields...'Project Number' and 'House Type'. I have another table with three fields...'Project Number', 'House Type' and 'Bill Number'.

Can I use an Append Query to create records in the second table, with the additional field created in the query as a sequential number....eg

Query from Table 1....

Project Number House Type Bill Number
2536 Type A 1
2536 Type B 2
2536 Type C 3
2537 Type A 1
2537 Type B 2

...appending this query to Table 2.

mmmm....tricky? It is for me. Any ideas?

 

[RichO]

You show "2536 Type A 1" as one record example. Is the bill number "A 1" or is it just 1?

Is the bill number related to the type? In you example all Type A's are 1, Type B's are 2, etc.


Please explain a bit further.

 

[WhizzkidWallace]

Sequence No's

It looked better when I typed it...I have found another way around the problem, but still would like to know how to do it the easy way.

Here it is better spaced....

Project No__House Type____Bill Number
1000___________a_____________1
1000___________b_____________2
1000___________g_____________3
1001___________a_____________1
1001___________q_____________2

etc

so you see, what I want is the query to create the Bill Numbers, or even better, a table that has an autonumber field that is reset when when the Project Number changes.

Hope that explains it better.....

 

[RichO]

The only way I know of to update the new field with an autonumber of sorts, as you are describing, is to use VBA and cycle through the recordset updating the field as you go. It's not too difficult, however...

Is there going to be new data entered into this table as well, needing the same kind of number formatting?

 

[m kappler]

Number sequence

WhizzKid Wallace

You said you found a solution to the nuber sequencing. Would you kindly let me know what you did ?

 

[JinaneKarhani]

Like Mr Kappler
You said you found a solution to the nuber sequencing. Would you kindly let me know what you did ?

 

[m kappler]

Number sequences

Hi WhizzKidWallace

Are you still around ?. Would like to know how you got around the problem of number sequencing

M Kappler

 

[Ukraine82]

If you have the answer please share it. I'm sure some one will ask the same question again.

Michael

 

[WayneRyan]

Here's a couple of approaches. Each query generates successive numbers.
One resets to "1" when some Key Value changes. The other just continuously
counts for the duration of the query.

Wayne

 

[WhizzkidWallace]

Sorry guys...when I said I found a solution, it was an alternative way of storing the data, not really a solution to the problem itself.

 

[rthrun]

Thanks Wayne!!

Finally, I found what I have been looking for. You have solved my issue!

Thanks,

Ralph