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!