AnsweredAssumed Answered

One to many relationship problem

Question asked by mz5005 on Jul 14, 2015
Latest reply on Jul 19, 2015 by mz5005

Hi,

 

a (probably) newbie question.

 

Have worked with MS Access before, know theoretical concepts of normalizing data etc, but obviously not a pro.

 

I have read the Help guide to the end, looked through the Portal related subjects on this forum but can't find a solution.

 

Q: i have a 1 to many relationship which does not work how it should. Can anyone point me in the right direction?

 

The simplified problem with table structure:

the system needs to keep track of what customer has received what document. One customer can receive more than one document; the same document can be send to more than one customer (so it’s many-to-many but business wise we are only interested in the What Customer Received What Doc side)

everything works well… until a customer receives more than 1 document … then the first document sent event is overwritten and replaced by the newer one. (I am not only talking about the presentation in the portal, the data is overwritten in the relevant table).

table: Customers   field: CustomerID  / table: Mailings   fields: CustomerID, DocumentID, DateSent

Relationship: Customers>CustomerID —> Mailings>CustomerID  / Allow to create records in Mailings

 

CustomerID is obviously the link to all customer info like address etc

DocumentID uniquely identities the document

The combination CustomerID + DocumentID + DateSent uniquely identities one document sending event.

 

My key problem is that I, after reading back and forth the Help guide, still not seem to understand well, is how to handle a 1-to-many relationship like described above:

it is not in my view a multiple field relationship since the Customers table has only one for this case relevant field CustomerID

if the above is true, then what do I do wrong with the Tables definition and/or relationships?

 

 

Any help greatly appreciated, am really stuck!

Outcomes