many to many relationships

[Anthony Heydon]

I don't know whether I am coming or going, I have got
myself that I can't see the wood for the trees.

I have an existing database with data already entered. I
have been asked to refine the database so that we are able
to obtain more localised reports.

The database is used to hold details of Clinical Audits,
who, what, why etc AuditID is an autonumber and is the
primary key. We want to add a couple of extra fields
relating to the audits Standards and References. I can see
how these tables need a third table having the two fields
StandardID and ReferenceID as joined primary key but I am
getting no joy for results. Do I only need, say the
Standards table relating to the Audit table with say the
AuditID as a number field in the Standards table?

Should I emply the Audit table of data and start entering
from scratch so that the two new tables are being filled
from a fresh start.

I hope you can understand.
regards

Anthony Heydon

 

[Tim Ferguson]

The database is used to hold details of Clinical Audits,
who, what, why etc AuditID is an autonumber and is the
primary key. We want to add a couple of extra fields
relating to the audits Standards and References.

I'm really jealous if your medics actually set standards to audit
against... and References? Jeesh.

Still, this sounds like a pair of one-to-many relationships rather than
many-to-many. Although some citations may be used more than once, unless
you really need to know which ones and how often, I'd accept a bit of
repetition. And realisticallty, how often are audit standards re-used?

So I'd go with (* is a PK, + is a FK):-

References(*RefNumber, AuditID+, JournalSource, Title, Authors)

Standards(*StdNumber, AuditID+, Quality, Exceptions, Target, Actual)

But, depending on what you actually want to achieve with the data, it may
be that Access is not the best solution. Far and away, the best list
manager on the PC is still Excel, so if you just need to see how many audit
the surgeons have done in the last six months that's your best solution. If
you want good text searching ("who did that thing last year on intraocular
foreign object?..") then I'd probably go for Word. To be sure, if you need
a query like

how many times in the last 12 months have the surgical team had more
than 130 in the audience in Lecture Theatre 4..?

.... then Access is your answer. But I remain sceptical.

Best of luck


Tim F