MaxOfDate Not Returning the Expected Results

[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.