Unique ID problem

[Remster]

Hi Folks

I'm trying to create a database that, for the purposes of this thread, contains data on three different types of document and the hyperlinks between them. I have four tables:

• Doc1Table
• Doc2Table
• Doc3Table
• LinksTable

LinksTable is an associate table that consists of two columns, LinkFrom and LinkTo, and is populated with values from the AutoNumber ID column in each DocTable.

Now, here's the problem.



Corresponding to each DocTable is a DocForm. Each DocForm contains two subforms, one of which populates the LinkFrom column in LinksTable and the other of which populates the LinkTo column. The subforms consist of combo boxes that, by means of UNION queries, list all the documents of all three types in the database. The combo boxes display the familiar (non-unique) names of the documents but send the corresponding (unique) IDs to LinksTable.

So suppose I'm in record 6 of Doc1Form and I select 'Skill Document', a type 2 document with ID 8, in its LinkTo subform. LinksTable will look like this:

 
ID..........LinkFrom.......LinkTo
1................6...............8

Now suppose I'm in record 7 of Doc2Form and I select 'Beast Document', a type 3 document also with ID 8, in its LinkTo subform. LinksTable will look like this:

 
ID.........LinkFrom......LinkTo
1...............6...............8
2...............7...............8

So the LinkTo column will contain the same value for two different documents. How can I get Access to recognise that the 8 in the first row denotes a different document from the 8 in the second row? Is there any way of determining which number autonumbering starts with, so that Doc1Table could start with 1000, Doc2Table with 2000 and Doc3Table with 3000? Alternatively, is there any way of prefixing autonumbering with a letter, so that Doc1Table could contain A1 etc., Doc2Table A2 etc. and Doc3Table A3 etc.?

I hope I've made the problem clear enough. Your help would be very welcome.

Best wishes

Remster

[kimmer]

How about put all docs in a single table with a field noting the doc type? If some doc types have fields unique to that type, then make a 1 to 1 relationship to another table.
Anytime union queries are needed, you should ask yourself if you have an unecessary horizontal partition.

[Remster]

Kimmer

Many thanks for your reply.

I definitely need three separate tables, as vastly different information about each document type needs to be recorded. I abstracted the hyperlink feature for purposes of simplification. It's in fact only one of many functions this database is intended to fulfil.

Remster

[Remster]

For the benefit of anyone else who encounters this problem, here's how to implement my Plan B. In Design View, in the Format field for the ID column, enter 'A0' to generate 'A1', 'A2', 'A3' etc. as the autonumbers in your table, or enter '"01-"0000' to generate '01-0001', '01-0002', '01-0003' etc.

[kimmer]

I didn't expect altering the appearance of your autonumbers would solve your issue.

[RuralGuy]

Remster: If you feel you have solved your issue then go ahead and follow the link in my sig and mark this thread as Solved.

[Remster]

Remster: If you feel you have solved your issue then go ahead and follow the link in my sig and mark this thread as Solved.

Don't worry, I haven't forgotten about marking it solved. But I'm still hoping that someone will come up with an answer to my original question. I'll give it a few more days.

[Remster]

I didn't expect altering the appearance of your autonumbers would solve your issue.

Really? Surely the ID '01-0001' (the first record in Doc1Table) will be treated as a different number from the ID '02-0001' (the first record in Doc2Table).

[kimmer]

Try out a union query, but I'm pretty sure that a nine, whether formatted to look like 9 or 01-0009 or 02-0009, is still a nine.

vastly different information about each document type needs to be recorded

Doesn't that make union queries even less appealing?

[Remster]

Try out a union query, but I'm pretty sure that a nine, whether formatted to look like 9 or 01-0009 or 02-0009, is still a nine.

You're right, dammit. Even though Doc1Table displays '01-0009' and Doc2Table displays '02-0009', LinksTable displays '9' in both cases.

I've found another method, recommended by Microsoft, that's supposed to achieve the desired result. I'll give that a go if I can make sense of the instructions. If not, you may hear from me again!

vastly different information about each document type needs to be recorded

Doesn't that make union queries even less appealing?

Why would it? The purpose of the UNION query is to populate a dropdown list within a subform with the names of all the documents on the database. How else could I achieve this?

Having said that, I've been reconsidering your suggestion of combining all three documents tables. If a field applies to type 1 documents but not type 2 documents, it can simply be left blank for type 2 documents and omitted from any forms or reports for that type of document, right? What I'll need, though, is a method of autofilling the 'Document type' field with 'Doc1', 'Doc2' or 'Doc3' depending on whether a record is created by Doc1Form, Doc2Form or Doc3Form. Do you know how I can achieve this? The only possibility that occurs to me is to include within Doc1Form, somewhere off to the side, a field with 'Default Value' set to 'Doc1' and 'Visible' set to 'No', but I don't know if that's the best way.

[Remster]

Okay, I can confirm that the method recommended in this page for manipulating autonumbering works!

I can also confirm that the invisible field idea I mentioned in the final paragraph of my last post works (though I'd be interested to know if there's a better method).