Hi,

I need to create a table with 2 sets of different information both referring to job types. the problem is that i need the autonumber which created the job number to scale together. so both tables have a primary key of "job number" and i want the autonumber to only ever use 1 number in both tables.

e.g.


job type 1 - autonumbers - 1,2,3,4,6,7,9,10
job type 2 - autonumbers - 5,8,11,12

is there anyway of achieving this?

Any help greatly appreciated

These problems don't occure when you have properly normalised your database (http://en.wikipedia.org/wiki/Database_normalization).

You can't force an autonumber field to be the same as another. In this case you must use one leading autonumber field and another number field as the number field for the other table instead of an autonumber field.

Hi,

Thanks for your reply but i think you have misunderstood.

i dont want the autonumber to be the same as the other one, i want to make sure that the two separate autonumbers do NOT have the same number as they both represent a job, I wish to look up information based on this autonumber.

many thanks

What about using the Autonumber as your ID for table1 and using increment

But in Table2 use the Autonumber in increment to set the value of your ID field with Auto*-1

I suspect you need to normailise your structure as Guus suggests. If you have two similar tables it is likely that what you really need is one table with an extra field to identify what type the record is.

Alternatively, you could use a third table that holds the autonumber and link the two tables to this by their own autonumber. A bit cumbersome but possible.

Thats a good idea thank you for this!!!

The reason for the 2 forms is that the jobs have completely different information and relate to reports that i produce containing data for the job, they are both sub forms of the customer forms which means that i pull information from the customer form and the job creation to get the relevant information.

If i create a central (third) file as you say though that could cure a lot of the problems.

Thanks a lot