Best way to uniquely number and store multiple subforms

[Bagels]

Here is the database
Attachment 49627

[Minty]

I used to issue RMA's and the best route is to issue one per product. (It normally means something along the lines of a Return to Manufacturer Authority)
Or use the RMA like an Order Header and then simply assign a RepairRefNo as an internal job number for the individual item. That job number tracks the individual product.

[Bagels]

We issue 1 per part#....so if a customer emails and says they want to return 5 different part#, each one would get an RMA .
But if they have more than 1 of the same part#, it's not really practical to issue each of those it's own RMA

[Micron]

if a customer emails and says they want to return 5 different part#, each one would get an RMA

That is not what I thought you meant. My suggestions were based on the opposite, which I didn't question but thought odd that different parts would have the same RMA. So I don't see a need for a junction table now (tblRmaItems).

[Minty]

We issue 1 per part#....so if a customer emails and says they want to return 5 different part#, each one would get an RMA .
But if they have more than 1 of the same part#, it's not really practical to issue each of those it's own RMA

It makes no sense not to issue one per item. I don't see the difference, and you are going to then separate them into individual items afterwards.
The fact that you collect them or they send them in all at once makes no difference, each one is a separate transaction in your system from a repair/replace perspective.
I was in the repair business for over 20 years, and trying to deal with "sub jobs" is a nightmare from a data perspective.

If you want to issue a return paperwork for 5 items and call them RMA1234 that's fine but they will end up as Job no's 6,7,8,9,and 10.
Why not miss out the middleman and just issue 5 RMA's and simply use the RMA as the Job number through the system.

[Micron]

As a former maintenance contracting coordinator I agree - 1 RMA per part/item as long as they're identifiable. Avoid the logistics involved where 1 part is scrapped, 1 is repaired, 1 passes inspection ... If they're not serialized (uniquely identifiable) then that would negate using separate RMA's?
EDIT - or it's back to a junction table where each serialized part is linked to 1 RMA header.

[Bagels]

As a former maintenance contracting coordinator I agree - 1 RMA per part/item as long as they're identifiable. Avoid the logistics involved where 1 part is scrapped, 1 is repaired, 1 passes inspection ... If they're not serialized (uniquely identifiable) then that would negate using separate RMA's?
EDIT - or it's back to a junction table where each serialized part is linked to 1 RMA header.

Ultimately the RMA is just a reference for the customer. We aren't dealing with end users here, so there is typically an "event" - such as customer finishes a production run and has 5 failed parts accumulated - that triggers an RMA request. It does happen where we have have an RMA request for just 1 part, but its not often. Its generally groups. I can see in theory that having 1:1 RMA to part is probably preferable, but in this particular case it's not really practical. Internally, I'm collecting all the data and information I need for reporting purposes, so to issue 5 separate RMA's to the customer seems cumbersome. I worked in a fuel shop for several years and had several instances of vendors trying to return entire sets of injectors...I didn't issue 8 RMA's for each injector, and when I filed warranty claims with the manufacturer I didn't file a separate claim for each individual injector; if 4 of the 8 failed I filed one single claim for 4.

[xps35]

Is it an idea to take a moment to look at where we are now?
What is the current database model and what is the actual problem?
As said before, the ideal would be if we were to get the current database (with fake data if necessary).

[Micron]

As said before, the ideal would be if we were to get the current database (with fake data if necessary).

Might have bee attempted in post 16 but seemingly not done correctly. Or maybe that was a pasted image.

[Bagels]

here it is again
RMA Log to send.zip

[xps35]

I think your current "Unit" is not right. You should not repeat dat that is already in RMA.
Better use a meaningless (autonumber) key.

The question is: what field identifies the returned instances (individual parts)? I do not see any use to give a sequence number to the individual parts as you seem to do with "Unit".

I do not think this (part of) your database can exist on its own. Returned goods are shipped before. That data should be integrated in the same database in my opinion. Information about the shipped parts is likely to be stored before. Normally you should not store that info again when parts are returned. So what is the whole picture?

[Bagels]

I think your current "Unit" is not right. You should not repeat dat that is already in RMA.
Better use a meaningless (autonumber) key.

The question is: what field identifies the returned instances (individual parts)? I do not see any use to give a sequence number to the individual parts as you seem to do with "Unit".

I do not think this (part of) your database can exist on its own. Returned goods are shipped before. That data should be integrated in the same database in my opinion. Information about the shipped parts is likely to be stored before. Normally you should not store that info again when parts are returned. So what is the whole picture?

The unit is the field that identifies the individual parts returned...Again, the RMA may have multiple parts returned under the same RMA .
I gather some don't agree with that, but that's how the current log is and I don't see a need to change it.

I don't really understand your last paragraph. There's nothing in the subform that's being duplicated.

[xps35]

I don't really understand your last paragraph. There's nothing in the subform that's being duplicated.

In the database there are. Like Customer, CustomerPartNumber and Description in RMAdata.

But that was not my point. What I am trying to say is that handling returned good is not an isolated proces. The returned parts have already been shipped before. All kinds of data are also stored during that process. These are partly the same data as those discussed here. That data is stored in another database (bad idea) or in the same database we are talking about (would make the most sense).In the first case you unnecessarily record data in multiple places, in the other case you must ensure that no data is recorded twice in the same database.

[Bagels]

In the database there are. Like Customer, CustomerPartNumber and Description in RMAdata.

But that was not my point. What I am trying to say is that handling returned good is not an isolated proces. The returned parts have already been shipped before. All kinds of data are also stored during that process. These are partly the same data as those discussed here. That data is stored in another database (bad idea) or in the same database we are talking about (would make the most sense).In the first case you unnecessarily record data in multiple places, in the other case you must ensure that no data is recorded twice in the same database.

Yes those 3 fields are repeated, but they are only shown as a reference to the user since the company p/n alone is not descriptive enough, user wants to be able to see the customer, customer p/n and description as well. I am not storing those values.

And yes, you are correct that it is not an isolated process, but I cannot link everything in this company to one Access database.