MaxOfDate Not Returning the Expected Results (1 Viewer)

[Pat Hartman]

so I need to keep the multi-value field.

No, you never need to keep a multi-value field. Just create the standard 1-m relationship and use a subform to maintain it rather than the "cool" control you get with the multi-value field. You'll thank me later.

Mushing attributes leads to lots of issues. You could use two separate fields for your Rev so you have type and number. you can even make the number sequence over all or within type for each drawing. Or, you can leave it alone. Once you add the autonumber, you can sort on autonumber to get the rev sequence. This only fails if you are playing catch up and enter revs out of order.

 

[Tophan]

Ok. So instead of the multi-value fields I would then just create fields in my table named Client1, Client2, etc.? Same format for any other multi-value fields I currently have?

So the C2 refers to Construction drawing Revision 2. The field DrawingType also refers to whether it is a construction drawing, tender, as-built, etc. So I should change and put the prefix "C" as drawing type and the number 2 as revision and for the report I can then do a string so it would read as per the drawing i.e. C2.

What do you think?

 

[The_Doc_Man]

So instead of the multi-value fields I would then just create fields in my table named Client1, Client2,

Never do that. You would be exchanging one bad design for another.

When you have a record for which there are at least two possible related entities (in your case, Client), you usually create a child table with the prime key of the independent table (I think in your case, the Drawing ID) and the ID of that multi-valued entity - a Client ID. Then you can use a JOIN to examine the multiple clients. For queries that don't care about clients, you just query the independent table. For queries that care about clients, you JOIN with the table I mentioned. To look up this approach you can search for "Junction Tables" and see how it works.

 

[Tophan]

Never do that. You would be exchanging one bad design for another.

When you have a record for which there are at least two possible related entities (in your case, Client), you usually create a child table with the prime key of the independent table (I think in your case, the Drawing ID) and the ID of that multi-valued entity - a Client ID. Then you can use a JOIN to examine the multiple clients. For queries that don't care about clients, you just query the independent table. For queries that care about clients, you JOIN with the table I mentioned. To look up this approach you can search for "Junction Tables" and see how it works.

I understand now. Thank you.