Need AutoNumber to generate individual ID# across 2 tables

[shteev]

I'm still writing a database in MS Access 2000.

I have 2 tables, one which holds data from Type A Questionnaires, and one which holds data from Type B Questionnaires. There are large differences between
the 2 types of Questionnaires.

However, I need each questionnaire, regardless of type, to have a unique identifying number. These numbers are not printed on the questionnaires and so must be generated freshly when the data is input. If I use AutoNumber, it will generate unique ID# for each individual table; but is there a way of making sure that a Type A is not assigned an ID# that is already used by a Type B, and vice versa?

 

[KenReay]

Hi

Not sure about this, but you could perhaps use a type called a GUID ((G)lobal (U)nique (ID)entifier), these are used in replication are are unique across tables, but beware they are very long!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scriverb]

I would create a Primary Index for both tables that incorporated the AutoNumber field plus another field that indicates the table. Each table would create its own AutoNumber but that would not be enough to make the records uniquely identified between tables. Create a new field after the AutoNumber field called Table_Indicator(text 1 character) and give it a default of "A" for one table and B for the other table. In the Indexes window create your primary index as a combination of both the AutoNumber and this new field called Table_Indicator.

Now you have uniquely identified primary indexes in both tables but are using the autonumber type field to generate each.

Post back of you have any more questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[shteev]

@KenReay:
I must confess I don't fully understand GUIDs, but I've already tested them and they don't produce unique identifiers across tables.

@scriverb:
Good idea; although it does create a practically redundant field in the table. Instead of storing the type of Questionnaire in the table itself, is there a way of constructing a query which would be able to generate the Questionnaire type automatically?

 

[KenReay]

Hi

As I said, I was not sure, but "and they don't produce unique identifiers across tables" surprises me, since they are used in replication, perhaps the number generated uses something on the PC as a seed, so doing it all on one PC defeats the object? , but I am puzzled by this, must try it when I have a spare half hour

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scriverb]

Sure, you could create a query that creates a new field called Primary_Key--

Select A![RecNumber] & "A" as Primary_Key, A.* 
FROM yourtablename as A 
ORDER BY A![RecNumber] & "A";

This query would construct a unique identifier just like the table idea but you don't need to store anything in the table. The individual records would now have a field that is unique when to compared to another query for table B where you modify it slightly to cancatenate a "B" to the AutoNumber field.

Is this what you were looking for?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[shteev]

@KenReay: Ah, I see what the problem was; I converted an existing column of integers into GUIDs. If I create fresh data, everything gets an individual ID. Cheers. I don't think I'll use this system, tho, since the Replication IDs are too complex for humans to refer to.


@scriverb: Thanks for your code. I did implement your original idea this morning, and found that in my db it threw up another small problem:

Each questionnaire can contain an open ended list of comments, which I have categorized. I've created a cross-referenced table which shows which comments (held in a 'comments' table) are linked to which questionnaires. However, the primary key of the Questionnaire table now contains 2 fields. Is there a way I can input a single value, (e.g. from a drop box) which will complete both primary key fields?


thx

 

[scriverb]

What are the values for this primary key? Are the fields available in the Questionnaire table?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[StellaIndigo]

OK if you want a single unique number across both tables, I'd do this...

create both tables with a number field and set as the primary key.

In your data entry forms for both tables (assuming you have a different form because the data is different) add update event code that Max's both tables primary keys to get the largest, then add one, set the record key to that new value.

You're manually coding an AutoNumber field but across 2 (or more) tables.

Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[StellaIndigo]

Or even better...

Assuming you're not gettnig more than 1 billion returned forms.

Set table 1 to AutoNumber from 1

Set Table 2 to AutoNumber from 1 Billion.

You can do this creating a temp table with 1 long int field set to the start value -1, remove the primary key from table 2 and then do an append query from temp to table 2, put the primary key back on and delete the temporary table.

Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[shteev]

Stella, thx for the info. Currently, I'd prefer to implement a system where each Questinnaire has a meaningful Primary Index; so that it's type can be told from the index alone. But if I run into too many problems I'll implement your system. Ta!

 

[scriverb]

You don't need a cross-reference table to implement the linkeage between the Questionnaire tables to the Comments table. You just need a Foreign Key field in the Comments table which would be a combination between the Questionnaire's Primary Key and the Comments table. The comments table can have it's own AutoNumber field. But, add another field called LinkToQuestionnaire(Text; 10 characters). Now the field can be filled manually with code or automatically between the Questionnaire screen and a subform control where you set the Master and Child link fields appropriately. In this last technique everytime you create a new Comment record ACCESS would automatically fill the AutoNumber & "A" combination into the Foreign Key field in the Comments table. Thus the linkeage you are looking for.

Let me know if this is making sense to you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[shteev]

scriverb: I'm sure I do need a cross-reference table. I'm storing the 'comments' data from each questionnaire in 2 ways. Firstly, the actual text written on each questionnaire is recorded. This, as you would expect, is stored in the main 'questionnaire table'.

But comments are also generalized and categorized and stored in their own table. e.g.

Questionnaire 1 comment as written: My brain hurts
Questionnaire 2 comment as written: I hurt my brain
Generalised comment stored in comment table: My brain hurts

So 2 Questionnaires may point to the same comment in the Comments table; additionally, 1 Questionnaire may potentially point to 2 comments ('My brain hurts and my shoes are too tight'). Action will be taken based on these comments, and duplication in the table may cause confusion.

 

[StellaIndigo]

I'd go for my option 2 (above) with the AutoNumber on both tables split by 1 billion.

For the tblComments a compound key of QuestionaireID + intCommentNumber.

When adding a new comment to a questionaire you want to Max the CommentNumber for the selected QuestionaireID and add 1.

This will give you QuestionaireID + intNewCommentNumber for the new comment record.

Stella

There are 10 types of people in the world. Those that understand binary and those that don't.