automatic number from a calculation---HELP

[juststo]

This is possibly a silly question, but I couldn't find it anywhere so

here it goes.

I have a DB that has many different jobs. All of them have

identifying numbers (ie job# 42, job #43, etc).

In a seperate table, I want to create records and have each record

have a unique serial number for that job. For example I want the 1st

record for Job#42 to be 42-001. The next would be 42-002. However if

I then create a record for job# 43 it should be 43-001.

I want this number to calculate automatically when I create a new

record.

Any help is appreciated.

[Wim Decorte]

Something like this will work:

- you need a self-join relationship in the child table (job # to job #)

- the script that creates a new child record passes the job # from the parent to a subscript by parameter. The sub script creates a new record in the child table, sets the child's job # field to the parameter passed (the parent job #) which triggers the self-join relationship. The script then does a count of the self-join records and sets a child field to job# & max(sj::whateverField) + 1

[juststo]

Wow, I am very new to this and have almost no idea what you just said . Sorry.

I have attached a paired down version of what I'm working with. Maybe seeing it will help with the specifics. There are two jobs. What I am trying to do is when I add a new RFI and the job number for which it is for, I would like the RFI number field to updated with the next number. I hope this helps.

Thanks

test.zip

[mr_vodka]

This file is locked.

[juststo]

Sorry.

Here is an unlocked version. Guest has full access.

test.zip

[mr_vodka]

Juststo,

I have made a mock up file to show you what Wilm was describing. I think you may need to rework your database though. You might want to check out some books for some ideas on Database design as well as David Kachel's White Paper.

RFI_test.zip

[juststo]

Hey, thanks a lot. I'll have to study exactly what you did, but it looks like it works great.

On my DB design. I am very new to this with very little concept of how things should be laid out or why. I will check out some of your suggestions.

Just for my clarificartion, does the design make my DB slower, less effective, or just ugly?