2 columns with one Autonumber?

[WBonar]

Is this (subject) a possiblity in access? I need to link the 'id'
field in two tables in my database together so the auto number provides
unique identifiers ACROSS the two columns. IE if the first 3 entrys in
table a are id 1, 2, and 3 the next entry in table b will be id 4

[Rick Wannall]

You'd have to use normal number fields and write some VB to do this.

Granted that you COULD do it, it's such an odd request (IMO) that I have to
ask whether you might be better off to put all the data in one table
(assuming that the columns are the same) and add one column that indicates
what kind of row it is. In essence, the new column would indicate which
table you would have put it in IF you were using two tables. Then, you can
have queries that pull the rows out based on that column and, for all
practical purposes, be using two tables.

If the columns are not the same, then I have trouble making up a reason for
having unique ID coordinated across the tables.

Can you say more about what your data looks like and what you need to
accomplish?

[WBonar]

I believe my method is correct in designing this. It exist like this:

My two tables hold instances of features for the website i am
designing. The reason for the seperate tables is we want to be able to
expand and add more instances later. (in that matter the vb may be the
best answer cause it will onday need to spread across more tables)
because they are all unique instances of features we want them to have
unique ids. However because they are very different features they need
different tables. I hope this all makes sense. I was kinda hoping
there was just a quick ability to like the autonumbers to make lookup
late easy.

[Ron2006]

Then the next question is will the occurance of the next record in the
first table with a 4 as the number cause a problem?

[WBonar]

Yes it would need to be unique ACROSS the to tables only one id of 4
could exist in each table

Ron2006 wrote:

Then the next question is will the occurance of the next record in the
first table with a 4 as the number cause a problem?

[tinnews]

WB****@gmail.com wrote:

I believe my method is correct in designing this. It exist like this:

My two tables hold instances of features for the website i am
designing. The reason for the seperate tables is we want to be able to
expand and add more instances later. (in that matter the vb may be the
best answer cause it will onday need to spread across more tables)
because they are all unique instances of features we want them to have
unique ids. However because they are very different features they need
different tables. I hope this all makes sense. I was kinda hoping
there was just a quick ability to like the autonumbers to make lookup
late easy.

Surely the answer is to have a 'master' table which just has two (or
possibly three) columns which are:-

Unique record number (which is what you require)
Pointer to table/record where the data is stored

The pointer could be made up of two columns (name of table and record
number) or a single variable from which you can work out where the data
is.

--
Chris Green

[Rick Wannall]

Still not sure I agree with the approach, but here is a way to do it:

Make yourself a table with an autonumber field. Put any other identifying
fields you come to feel will help after thinking this over.

Before adding data to any one of the (unlimited number of) features tables,
add a row to this Master Features table. Get the ID of the new row.

In your features tables, don't use AutoNumber. Use Long. Use the newly
retrieved ID as the primary key in whichever table you append the data to.
In fact, that suggests that one of the fields in the Master Features table
should be some indication of the table where you used the new ID value.
Otherwise, you'll have a hard time coming back later to do anything with
this master table. It's a standard master/detail relation, with the small
wrinkle of parsing out details across multiple tables. If you're not
fixated on using cross-table unique AutoNumber, which doesn't exist, this
approach should do the job.

[Dean]

I would look at two tables one to many relationship. You want to add
another instance for the same website? Doesn't seem to make since to
have two identical tables do this.