Fw: [MS_AccessPros] MAX AND STANDARD DEVIATION

 

Hi Duane and all,

kindly help with the modification I need to make to the query below if I only want to return the records having the highest ( or lowest) value in field 6 when field 7 for all such records are the same.

I simplified the case to ease the solution.

Thanks.

Ade

----- Forwarded message -----

From: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <ms_access_professionals@yahoogroups.com>

To: "ms_access_professionals@yahoogroups.com" <ms_access_professionals@yahoogroups.com>

Sent: Monday, 9 March 2015, 13:20:57 GMT

Subject: RE: [MS_AccessPros] MAX AND STANDARD DEVIATION

 

Ade,
 
Any time you wish to calculate across fields, it suggests an un-normalized table structure. For your current table (if you can't change your structure), I would suggest first adding a primary key field if you don't have one and then creating a normalizing union query.
 
SELECT PKField, 1 as Fld, Field1 as TheValue
FROM tblTransactions
UNION ALL
SELECT PKField, 2, Field2
FROM tblTransactions
UNION ALL
SELECT PKField, 3, Field3
FROM tblTransactions
UNION ALL
SELECT PKField, 4, Field4
FROM tblTransactions
UNION ALL
SELECT PKField, 5, Field5
FROM tblTransactions
UNION ALL
SELECT PKField, 6, Field6
FROM tblTransactions
UNION ALL
SELECT PKField, 7, Field7
FROM tblTransactions;
 
You can then use this query in a totals query and group by the PKField and use SQL aggregate functions for your calculations.
 
SELECT PKField, Max(Thevalue) as MaxValue, Stdev(TheValue) as StandDev
FROM quniTransactions
GROUP BY PKField;
 
Duane Hookom MVP
MS Access

 

---

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 9 Mar 2015 10:59:52 +0000
Subject: [MS_AccessPros] MAX AND STANDARD DEVIATION

Hello all,

just to see if you could help out with applying the Max and StDev function across a record.

For ease of reference, hypothetical but relevant transactions are shown below.

	Field1	Field2	Field3	Field4	Field5	Field6	Field7
Record1	12	15	25	19	37	20	14
Record2	1	8	50	21	17	45	38
Record3	2	15	33	47	29	30	31

Thanks.

Ade

__._,_.___

---

Posted by: Adeboyejo Oyenuga <aoye_99@yahoo.co.uk>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___