AccessXP Query Question (AccessXp)

Topic

New Reply

Hi Rich…

All you have to do is create a second query…. Keep the totals query as is (with just the Min and Max of Cost)…
Then create a second query that joins the Table to the totals query on the Cost field… The inner join will return only the records where those amounts match (ie the Min and Max Cost records)… In this query, select the Description field from the table and you should get the results you are looking for…

HTH

Reply | Quote

Replies

We’re trying to design a Query that returns the Highest Costing Item, and the Lowest Costing Item. Our table includes a field name of Description, and another field name of Cost.
We ran the Query successfully using totals to find at the same time the Max and the Min for the “Cost” field. But we want to know the Description of the Highest and the Lowest! When we include the field “Description”, the Query returns all the records, not simpply max and min.

Does anybody know how we could show the “Description” and the Max and Min?

Thanks,
Rich
see http://www.mrwteaches.net/d221%5B/url%5D

(URL corrected and made clickable by HansV – see Help 19)

Reply | Quote

We’re trying to design a Query that returns the Highest Costing Item, and the Lowest Costing Item. Our table includes a field name of Description, and another field name of Cost.
We ran the Query successfully using totals to find at the same time the Max and the Min for the “Cost” field. But we want to know the Description of the Highest and the Lowest! When we include the field “Description”, the Query returns all the records, not simpply max and min.

Does anybody know how we could show the “Description” and the Max and Min?

Thanks,
Rich
see http://www.mrwteaches.net/d221%5B/url%5D

(URL corrected and made clickable by HansV – see Help 19)

Reply | Quote

Hi Trudi,

Thank you so much for your quick reply!

I tried your suggestion, but I must not be doing something correct. Maybe I’m not as familiar with inner joins. I still can get it to work the way we envisioned! I get no results. Do I run the new query to see all the info we’re after?

Any suggestions?

Thanks a bunch,
G’Day,
Rich

Reply | Quote

Let’s say that you have a table tblCosts with fields ID (AutoNymber), Description (Text) and Cost (Currency).

1. Create a query qryMinMax that returns the lowest and highest costs:

SELECT Min([Cost]) AS MinCost, Max([Cost]) AS MaxCost FROM tblCosts

2. Create a query based in qryMinMax and on two instances of tblCosts. Join one instance to qryMinMax on Cost vs MinCost, and the other instance on Cost vs MaxCost. Return the Description field from each:

SELECT tblCosts.Description AS [Least Expensive], qryMinMax.MinCost, tblCosts_1.Description AS [Most expensive], qryMinMax.MaxCost
FROM (qryMinMax INNER JOIN tblCosts ON qryMinMax.MinCost = tblCosts.Cost) INNER JOIN tblCosts AS tblCosts_1 ON qryMinMax.MaxCost = tblCosts_1.Cost
GROUP BY tblCosts.Description, qryMinMax.MinCost, tblCosts_1.Description, qryMinMax.MaxCost;

See screenshot.

Reply | Quote

Let’s say that you have a table tblCosts with fields ID (AutoNymber), Description (Text) and Cost (Currency).

1. Create a query qryMinMax that returns the lowest and highest costs:

SELECT Min([Cost]) AS MinCost, Max([Cost]) AS MaxCost FROM tblCosts

2. Create a query based in qryMinMax and on two instances of tblCosts. Join one instance to qryMinMax on Cost vs MinCost, and the other instance on Cost vs MaxCost. Return the Description field from each:

SELECT tblCosts.Description AS [Least Expensive], qryMinMax.MinCost, tblCosts_1.Description AS [Most expensive], qryMinMax.MaxCost
FROM (qryMinMax INNER JOIN tblCosts ON qryMinMax.MinCost = tblCosts.Cost) INNER JOIN tblCosts AS tblCosts_1 ON qryMinMax.MaxCost = tblCosts_1.Cost
GROUP BY tblCosts.Description, qryMinMax.MinCost, tblCosts_1.Description, qryMinMax.MaxCost;

See screenshot.

Reply | Quote