Min Max

[rob.lyles]

Hi,
I cannot find a thread relating exactly to my topic as I need it, and I need help with min max functions. I am using a select query to pull from a couple of warehouse inventory style tables to give me part number, description, cost, and qty. The cost and qty fields are listed by location and are grouped. So on the record after description, you would see cost for location 002, qty for 002, and so on by location. I need to locate the min and max of cost/qty by record. Moreover, as I mentioned, the cost and quantity are grouped by location. So the function would, in my mind, be operating from defined fields (a,c,e..) and not a range of fields (a:e).

So for a records I would need to see, part number, description, all of my cost/qty fields, then min of cost, then if possible in an adjacent field the name of field or location that this data was retrieved, then max cost, then again with another field depicting field name/location in which data was retrieved. This process adjacent to that would need to repeat for qty.

Hopefully I simplified my wants and needs. If I could achieve this, then I would really be making some progress. Any response is appreciated.

Thanks,
Rob

 

[jdraw]

Do you have a database with tables and relationships?
Could you show us a jpg of the relationships window?

 

[rob.lyles]

Here is a copy of the SQL along with a snapshot of the query. I checked the relationships, and I suppose I did not have anything built. I have to admit also, for simpler purposes, I have not normalized this very far either at this point. Is this sufficient for now? If not, let me know, and I can go back and put together a smaller test db. Or let me know what else I can provide. Currently, I have 40k+ records along with 25 fields or so, and it contains data for the company I work for which is proprietary.

SELECT Available.[Part Number], CIM1.ITEM_CL, CIM1.GRP, CIM1.GRP_CL, Available.DESC80, Available.UM, Available.[002], Cost.[002], Available.[012], Cost.[012], Available.[017], Cost.[017], Available.[018], Cost.[018], Available.[029], Cost.[029], Available.[032], Cost.[032], Available.[041], Cost.[041], Available.[042], Cost.[042], Available.[057], Cost.[057], Available.[065], Cost.[065], Available.[066], Cost.[066], Available.[067], Cost.[067], Available.[107], Cost.[107], Available.[7MX], Cost.[7MX], Available.[865], Cost.[865]
FROM (Available LEFT JOIN Cost ON Available.[Part Number] = Cost.[Part Number]) LEFT JOIN CIM1 ON Available.[Part Number] = CIM1.MCJ_ID;

Thanks,
Rob