Prev: Linked SubForm Issues - cannot edit in subform or move from main r
Next: Transferring Control From One Database To Another Using Access 2007 Runtime
From: Clarkyboy420 on 28 Oct 2009 12:58
Hi Tom,

Well, I've been doing some looking into this problem and I think you are
right. The main problem is that I have the many side of the 1:m in the parent
table... Figured out this wont work at all... You are correct, I need a many
to many relationship, but I am unsure how to do this in Access, I'm aware
that a 1:m or m:1 relationship is formed by the position of the Primary and
Foreign Keys (ie the PK denoting the one side), but how would I do a
many:many? Would I need to have FK and PK in both tables, both linked or
something?

Apologies if my understanding is poor, I have done some theoretical study
into relational databases and normalisation, but this is the first time I
have been able to apply it to a real db. I'm finding it's not as easy as I
thought to get access to get my existin data into the model designed through
ERDs.

I appreciate any help/advice you can offer :)

"Tom van Stiphout" wrote:

> On Wed, 28 Oct 2009 07:38:01 -0700, Clarkyboy420
> <Clarkyboy420(a)discussions.microsoft.com> wrote:
>
> You may have an incorrect db design. It sounds like you are creating a
> new Fix record for each Incident. However, some fixes apply to several
> incidents I would think, and some incidents require several fixes.
> Therefore a M:M relationship would be needed.
>
> -Tom.
> Microsoft Access MVP
>
>
> >Hi,
> >
> >I'm currently developing a normalised Access Database to record software
> >problems on my companies products.
> >
> >I have a normalised structure of related tables, linked together into a
> >table called Incident via indexes. There is an associated table called Fix,
> >where related information is stored. These exist as data entry forms also
> >called Incident and Fix.
> >
> >The Fix form is a subform of Incident and on the existing information in the
> >datasbase, works perfectly, relating the correct Fix record to the correct
> >Incident record. They are linked through the FixID field which exists as
> >Primary in Fix and Foreign in Incident. There is no occurence of IncidentID
> >in Fix, but there is FixID in Incident as FK.
> >
> >THE PROBLEM:
> >
> >When I create a new incident record, this works fine. The problem is that
> >when I wish to enter information in the associated Fix subform, it will not
> >allow me to enter anything. The error message appears saying that I cannot
> >add or change a record because a related record is required in table 'Fix'.
> >If I try to move out of the record, the same error appears. It seems the
> >record requires a fix record to be assigned against it to confirm the
> >incident.
> >
> >I'll be the first to admit I'm a little out of my depth here. Obviously, I
> >need Incidents to be able to be exist without a fix, as the fix is applied
> >afterwards. This brings the following questions;
> >
> >- Firstly, is my subform strategy the flaw, or is it that I am just not
> >implementing it properly due to lack of knowledge?
> >- Secondly, If my subform strategy is workable, what do I need to do to
> >allow a fix record to be attached later? FixID and IncidentID are
> >AutoNumbers, so I assumed the number would be applied automatically, but
> >obviously the Fix record needs to append its key ref to the Incident table -
> >can this be done through an event procedure on a 'new fix' button perhaps?
> >
> >I'm really baffled as to what the issue is and if I'm honest, I don't even
> >know what topics I should be researching to identify a remedy.
> >
> >I am more than happy to discuss the details further with any helpful soul
> >out there with a good samaritan's heart!!!!
> .
>
 | 
Pages: 1
Prev: Linked SubForm Issues - cannot edit in subform or move from main r
Next: Transferring Control From One Database To Another Using Access 2007 Runtime