Best way to uniquely number and store multiple subforms

[Bagels]

Back to my return log...the key on the main form is the RMA#. It is an autonumber and I have it formatted like "23-001", 23-002", etc.



However a customer might return 5 parts under 1 RMA# (of the same part number)

I have a subform that contains the evaluation information for each individual part within the overall RMA#

Right now on the subform I just have a text box linked to the RMA# from the main form for info purposes then the key is just a sequential autonumber. I would prefer to have the key in the subform be related- like 23-001-1, 23-001-2, 23-002-3, etc.
Is there some way to link it ? Could I have the user enter it, but restrict it so it's 23-001-X (then they'd fill if it is part# 1, 2, 3, etc.) Since then those numbers would be unique to say 24-001-001, 24-001-002, etc.

[xps35]

First of all I don't think RMA# is an "autonumber" (number automatically generated by Access) but a number generated by some code in your database.

To be able to answer your question we need more info about your database (structure). Please provide at least a picture of your relations diagram. A copy of your database would be perfect.

[Bagels]

Here are screenshots of the main form/subform and the relationships
In the entry part, the RMA# is autogenerated . The clerk would enter the customer is returning 5 parts
Each record in the subform would represent 1 of the 5 parts.
But instead of Unit# 3 or Unit# 46, I'd like it to say something like 23-007-3, since that is more meaningful and also unique




Attachment 49619Attachment 49620

[Micron]

Methinks this should be much like a PO and PO Line Items relationship. 1 RMA number with fields pertaining just to the RMA (e.g. CreateDate, CreatedBy, Status, etc.) and a related table for items returned under the RMA - one record per item. The RMA number should not be meaningful data. Generation of the number is a separate problem.

BTW, you cannot paste images in this forum so your attachment links don't work. See "how to attach files" at the top of the page.

[Bagels]

Apologies regarding the attachments, I believe I did it correctly now.

I think how you're describing it is at least the way I have it laid out now, but I'm obviously have something wrong.

[Micron]

After a quick look I'd say your tables are not correct, but it would take some time to make more than cursory suggestions. Some of those would be
- no special characters (e.g. #) or spaces in object names. Underscore is ok but I seldom use it.
- no pk to pk joins (at least not in this case)
- relationships on each side of a join should be obvious, not just today, but 6 months from now, and especially for anyone who has to assume maintenance of db. What looks like an example of that is RMA# being joined to Unit#
- customer does not belong in a parts table
I don't see the need for having the same fields on a main form and its subform(s)

Consider a review of normalization before continuing.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[Bagels]

Thanks, will review.

[xps35]

One more thing is that the link between RMA data and RMA subform (strange name for a table ) is not ok. Tables should be linked on matching fields (like RMA# on both sides). If a RMA data record kan have multiple RMA subform records, they should be linked by RMA#. And you should NOT copy data like customer partnumers. You should store that one ONLY in the partnumbers table.

[Bagels]

Thanks, appreciate all the help i'm checking out the blog Micron suggested.

I renamed everything and removed special characters and underscores etc.
I removed the duplicate part number fields in the subform i only wanted that for reference/display purposes but it looks better without it anyways.
I corrected the link from form to subform to be RMA to RMA, that was the only pk to pk join I had

One thing i guess im not clear on, "customer does not belong in a parts table "
Our company part number corresponds to a customer, customer part number and a short description of that part
I guess as I type this now i'm thinking through it, but if our company part number changes, the customer part number and description changes. But the customer may not change since we may supply one customer with 10 different parts. So should "customers" be a part table of it's own and then linked by customer p/n ?

[Micron]

My opinion is that you would have a customers table with all the relevant fields. Customer is who requests the RMA, yes? So tblRMA (RMA header info) has CustIDfk as foreign key (related to CustIDpk in tblCustomers); returned parts are in tblRmaItems linked via tblRMA.RmaIDpk to tblRmaItems.RmaIDfk.
pk = primary key; fk = foreign key.
HTH

EDIT - your part numbers in parts table (if you have one) would be in tblRmaItems as a foreign key.

[xps35]

On second thought I don't really understand what the subform table is for. This is partly due to the meaningless name. Normally you can see from a table name what kind of data it contains. That's not the case here. I understand that RMA has to do with the return of parts (as a Dutchman I was not familiar with the abbreviation). The RMA table already lists the part number. Why then can several "subform" records have to be linked to an RMA? That would only make sense if you want to record details per returned instance.

About partnumbers and customers: I assume that a part can be bought and returned by several customers. In that case you need a parts table (with your partnumber), a customers table and a junction table where you store the partnumber the customer uses for a part.

[mike60smart]

Can you upload a copy of the database?

[Bagels]

On second thought I don't really understand what the subform table is for. This is partly due to the meaningless name. Normally you can see from a table name what kind of data it contains. That's not the case here. I understand that RMA has to do with the return of parts (as a Dutchman I was not familiar with the abbreviation). The RMA table already lists the part number. Why then can several "subform" records have to be linked to an RMA? That would only make sense if you want to record details per returned instance.

About partnumbers and customers: I assume that a part can be bought and returned by several customers. In that case you need a parts table (with your partnumber), a customers table and a junction table where you store the partnumber the customer uses for a part.

I'll rename the subform to something more meaningful I agree.
The subform is needed because a customer may return more than 1 part (of the same part number), under the same RMA#
So for example, they return 5 parts under RMA 1234
Each of the 5 parts has to be evaluated and has it's own unique outcome- repair, credit, return, etc.
So there would be 5 records in the subform for that 1 RMA

The main form "RMA Entry" is when the process beings, customer contacts us with initial information like part number, quantity, etc.
Then once the parts are received by us, the subforms represent the actual analysis portion of it, such as what failed, etc.

And no, with rare exceptions, parts cannot be bought and returned by several customers. We don't sell common off-the-shelf product, they more or less are completely unique to that customer.

[Bagels]

I think i more or less have it working correctly now, I corrected the relationships based on what I picked up from the blog
But I will upload it shortly , i'm sure i probably overlooked things or could improve others.

[Micron]

@xps35; in case you didn't google it, it means Return Materials Authorization, aka RGA Return Goods Authorization. Many companies will not accept goods or materials without prior authorization.

@Bagels; suggest that you research PO and PO Line items relationships for what might provide helpful guidance.