Subtracting one query from another?

[msaunders26]

Hi everyone,

I am familiar with databases and basic SQL, but never used Access, so your help is greatly appreciated!

What I *think* I need to do here is take the results from one query, subtract another query from that, then create a table of the results, so that I can make a chart in a report. I say that I *think* I need do that because I’m not confident that this approach is the best but that’s a whole other story...

Please look at the following tables below for an example. The Item table has ItemCode as a primary key. The Bids Table has multiple rows for each ItemCode showing bids for the item:

Item Table
ItemCode Date Address
JH231 2008-Feb-20 123 Test St.
KH831 2008-Feb-21 512 Test St.


Bids Table
ItemCode Amount
JH231 100.00
JH231 60.00
JH421 310.00
KH831 20.00
KH831 40.00
KH831 10.00
KH831 90.00

To make a pretty graph, I would like a table (so a query, or macro, or VBA or something?) that returns the minimum Amount for an ItemCode subtracted from the maximum Amount for an ItemCode based on the example above. What I would end up is something like this:

ItemCode Difference Between Maximum and Minimum
JH321 250
KH831 80.00

I hope this is clear. Any help on how to do this from you Access pros would be great, OR if I’m taking the wrong approach, suggestions on what I should try next. Thanks!!!

Marc

 

[Brianwarnock]

You can get the data from 1 query.

SELECT Bidstable.itemcode, Min(bidstable.Amount) AS minf, Max(bidstable.Amount) AS maxf, [maxf]-[minf] AS diff
FROM Bidstable
GROUP BY Bidstable.itemcode;

Brian

 

[msaunders26]

Thanks, Brian!!
Works great.

Does anyone know how I can also take an average of the diff column? I tried modifying the query by adding "AVG(diff) as average" but I get an error of "Subqueries cannot be used in the expression (AVG([maxf]-[minf]))"

 

[Cremate]

Thanks, Brian!!
Works great.

Does anyone know how I can also take an average of the diff column? I tried modifying the query by adding "AVG(diff) as average" but I get an error of "Subqueries cannot be used in the expression (AVG([maxf]-[minf]))"

Why would you want the avg of the difference? Or you do mean the avg of all the values in the diff column? If so, sum(diff)/count(diff) as avg should do the trick.

 

[msaunders26]

Sorry, I meant the average of all the values in the diff column. However, I tried your suggestion and still get the same error

Any ideas?

 

[Cremate]

Sorry, I meant the average of all the values in the diff column. However, I tried your suggestion and still get the same error

Any ideas?

Weird. Youre not really running a subquery, its only another column with an expression.

But come to think of it - what youre seeking doesnt make sense. That value will be returned in every row which would be a lot of duplicated data. If you just want that value, for whatever reason, put it in a separate query.

 

[msaunders26]

You're absolutely right... sorry I'm new to Access.
I've made a new query "SELECT AVG(diff) from [above query]" that works wonderfully. Thanks guys