 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / June 20062 columns with one Autonumber?
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 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? 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. > I believe my method is correct in designing this. It exist like this: > [quoted text clipped - 7 lines] > 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.  SignatureChris Green 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? Yes it would need to be unique ACROSS the to tables only one id of 4 could exist in each table > 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? 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. 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.