Is this achievable with an autonumber?

[FrunkaBlunka]

Hi guys,

Table 1

JobID (Number) 4 digit
Project(text)
Contract Value(Currrency)

Table 2

JobID (linked)
Claim(Autonumber)
Description of work(Memo)
Amount (Currency)

There are some jobs where I need to make several claims or partial
payments if the job extends over a long period of time. I identify a
job by its JobID which is its invoice number.
When I have a job that has several claims I like them to be listed on
the invoice in sequential order hence the Autonumber but when I skip to
the next job I want to see my AutoNumber reset for the new batch of
claims for that job. For example

Job 3521

1 Installed Track $1200.00 21/09/2006
2 Installed Panels $4000.00 22/10/2006

Job 3522

3 Installed Track $2100.00 26/09/2006

I would like that number 3 to reset back to 0 for each Job

Job 3521

1 Installed Track $1200.00 21/09/2006
2 Installed Panels $4000.00 22/10/2006

Job 3522

1 Installed Track $2100.00 26/09/2006



Like THAT! is that possible if so how do I do it or is there a better
solution to my misery.




Thanks!

Blunka

 

[Larry Daugherty]

I'll stick with just one point; Autonumber.

The Autonumber datatype exists for *one purpose*: as a surrogate
primary key provide uniqueness. It should never be used for any other
thing whatever. No human should ever see it and try to make sense of
it.

It is not guaranteed to be sequential. It is not guaranteed to be
gapless. It is difficult to manage if you ignore all warnings and
miss-use it.

You can create sequence numbers yourself and take such steps as you
need in order to make behave in any way you define.

Visit www.mvps.org/access and see what Dev has on file.

HTH