----- Forwarded message -----
From: Adeboyejo Oyenuga <aoye_99@yahoo.co.uk>
To: MSAccessProfessionals <msaccessprofessionals+owner@groups.io>
Sent: Wednesday, 27 November 2019, 12:08:47 GMT
Subject: 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
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: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar