Linked List in Access?

[ModelM]

As part of my internship, I have been asked to create a database for some acronyms that my company frequently uses. I am going to create the database in Access, but I find that some of the acronyms have more than one definition. Basically, I would like to create a linked list. Right now I can only think to make each table with 3 tuples (acronyms, definition, next_table). If the next_table field is not null, then I would know that there is more than one definition, and next_table would contain a pointer to the next table. Is this the best way to do this? If it is, how do I implement a pointer field in access?

Thanks!

 

[Descartes]

There's no such thing as a pointer in a relational database. At first I was going to suggest using a Collection (before reading your post), but it looks to me you have a simple issue of data normalization. You simply need to derive the appropriate entities and define the necessary relationships. This falls withing the realm of data modeling. Here is how I would probably break it down:

Table: Acronyms
AcronymID int, autonumber, primary key
Acronym text

Table: AcronymDefinitions
AcronymDefinitionID int, autonumber
AcronymID int, foreign key for AcronymID in table Acronyms
Definition text

That's it. The above applies to any RDBMS, including Access. There's a 1:n (one-to-many) relationship between Acronyms and AcronymDefitions: a single acronym can have multiple definitions.

 

[tkdkid]

Uh...just add another record for the multiple definition

1: doa dead on arrival
2: doa dead or alive

when you query (select * from table where acronym = 'doa'), you get back both records.

The linked list thing isn't standard database design, it will just get confusing later. Also, you can end up with a really messed up table doing it like that.

 

[ModelM]

Thanks guys!

Yah, I don't know what I was thinking. I guess I forgot that entries don't need to be distinct.

 

[b4u]

Picking up where Descartes left, I would add some examples (simples ones, you probably know them):

Table [Acronym]
AcronymID* &nbsp&nbsp&nbsp&nbsp&nbsp Acronym
--------------- &nbsp&nbsp&nbsp&nbsp&nbsp --------------
1 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspacron1
2 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspacron2
3 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspacron3


Table [AcronymDefinitions]
AcronymDefinitionID*   &nbsp&nbsp&nbsp&nbsp&nbsp AcronymID &nbsp&nbsp&nbsp&nbsp&nbsp Definition
----------------------------- &nbsp&nbsp&nbsp&nbsp&nbsp ------------------- &nbsp&nbsp&nbsp&nbsp&nbsp --------------------------
1 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp1 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspacron1_def1
2 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp1 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspacron1_def2
3 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp1 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspacron1_def3
4 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp2 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp acron2_def1
5 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp2 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspacron2_def2

* Those are autonumbers, so they are self-generating, you don't insert data.


[1] To get a list of all acronyms:

select Acronym
from Acronyms
order by Acronym ASC

Result List:

Acronym
---------------
acron1
acron2
acron3


[2] To get all definitions of a given acronym (in this case acronym with Acronym=2):

select Acronym, Definition
from Acronyms, AcronymDefinitions
where Acronyms.AcronymID = AcronymDefinitions.AcronymID
and Acronym = 'acron2'
order by Definition ASC

Result List:

Acronym &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Definition
------------------ &nbsp&nbsp&nbsp&nbsp&nbsp ---------------------
acron2 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspacron2_def1
acron2 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp acron2_def2


Again, this is a simple example, for anyone who needs it 🙂 (I didn't test it, hope I didn't messed anything)


Edit: Ops ... posted just after you read this ModelM 🙂