Unanswered: Need assistance with some Relationship/subform logic
Hello my friends,
I am relatively new to Access. I was asked by my boss to re-design the company's Return Material Authorization (RMA from now on) database. I have designed, for the most part, an operating version of the database. However, I have one problem. I will state the problem after I give a small bit of background information, to help you understand the scenario.
The RMA process has three KEY aspects: RMA number, Serial number, and Failures.
The relationship of these aspects are as follows:
-Each RMA may have many Serial Numbers.
-Each Serial Number may have many Failures.
-RMA numbers are unique.
-Serial Numbers may reappear in a different RMA.
-Failures for those reappearing Serial Numbers need to differentiate for the different RMA numbers
I have the form setup to detail RMA information, and then contain a subform (for Serial Number Info) containing another subform (for Failure Information). The goal of the setup was to provide our sales department with quick and accurate data entry, without having to browse through many different forms.
With my form setup I cannot figure out how to simultaneously view Failures by Serial Number yet be able to separate the Failures that have identical Serial Numbers from different RMA's (so that once you enter a new RMA and Serial Number, the Failure Information from the previously used Serial Number does not appear/is not overwritten).
I will attach a few pictures of my current form setup and relationships. The attachments of the form illustrate the problem well. As you can see, I have two RMA numbers (R000001 and R000002) each are using the same SN (10610) but as you can see, when I enter "R000001" in the failure customer description field in RMA Number R000001, it ALSO appears when I move to RMA Number R000002.
Does anyone have any ideas? I have been struggling with this one for a little bit. Thank you in advance
Last edited by eGR; 06-02-10 at 11:53.
Reason: Changed attachment types to JPEG.
I'm no expert on Access but I do have a database for product returns (RGA in our case). It does not go to the detail that yours does but it looks to me as though you have no direct link to the actual RMA number from the failures table. It's only link is through the SNID so all of the failures associated with that particular SNID will appear every time it comes up regardless of the actual RMA#.
I think you need to combine the SNID and RMA in the failures table to make it a unique link to that particular return?????