I have an access db (see attached) with a tabled titled TCampProj that contains sample data collected at various locations (LOCCODE) on various dates (MYDATE) and presents data for various elements (SumOfFe, SumOfMn, SumOfZn, etc), along with their corresponding element-lab method reporting limit (SumOfFe-PQL, SumOfMn-PQL, SumOfZn-PQL, etc).
I need a query/update command that will
1) re-name my element and element-lab method reporting limit columns by deleting the first 5 letters of each of those fields (SumOfFe becomes Fe, etc.....SumOfFe-PQL becomes Fe-PQL, etc);
2) create a new table (same updated field names as TCampProject table) that contains only those rows corresponding to unique LOCCODEs, with the values for each of the elements averaged for all MYDATES associated with that LOCCODE. For example, if there are 5 sample dates (MYDATES) for LOCCODE = A470000, then Fe = average of all 5 Fe's, Mn = average of all Mn's, etc for all elements.
3) Repeat two except provide the median instead of the average.
Prior related question: http:Q_28485399.html