Totals Query (1 Viewer)

[4366]

Hi
I need help with this query
I have a table which contains a bunch of fields
item , description, cost, date when the sale occurred etc etc
has a few thousand rows with each transaction on a different row
unique item is about 150
cost can be different for same item
i need to find the lowest cost on a particular item
so i made a totals query
grouped by item and used Min for cost
so far so good works fine

but i need to show when the last date that that item low cost occurred
Very important for me

also would love to show the description if possible
please help
David

 

[CJ_London]

by last, I presume you mean latest?

1. create another query based on this one and the original table
2. join these on item and price
3. show fields from your original query plus the date and description from the table
4. group this query again and use max for the date

from your post it would appear your data is not normalised so you may have problems

 

[4366]

Thanks a Billion
I spot checked a few Items
Works like a Charm
Thanks Again
David

 

[4366]

Hi
Perhaps i can ask another question related
I would like to tag each item where the cos is between 10% and 30 % more than the Min cost .and_
tag each that's between 30 % and 50 % than the Min cost , and tag each item that's between 50% & 75% more than Min. etc. etc
and then create a total by item for each percent increase
Thanks
David

 

[oleronesoftwares]

@4366 the min function is a domain aggregate function, so it can not be used as criteria.

You might have to do the following.

1. Create a copy of the same table(this is to protect the integrity of data in the original table)
2. Add a field called minimum cost
3. Create an insert query

INSERT INTO [Copy Of costs] ( item, cost, mincost )
SELECT costs.item, costs.cost, DMin("cost","costs") AS mincost
FROM costs
GROUP BY costs.item, costs.cost;

Now you have a copy of the data, with a field that has the minimum cost as value in all the records
You can then proceed to use the value in your criteria expression in a select query

 

[bastanu]

@4366 the min function is a domain aggregate function, so it can not be used as criteria.

Actually dMin is the domain aggregate function you are thinking of and it can be used as criteria, but not needed in this context. The OP simply needs to build a query based on the original table and the totals query that returns the min cost, join them by Item and have a calculated field to get the percentage difference PercentDiff: (ItemCost-MinCost)\100 and another with an IIF or better Switch function for the tagging Tag:Switch (PercentDiff <0.1, "Less than 10 percent", PercentDiff >=0.1 AND PercentDiff <0.3, "Between 10 And 30 Percent", ....)
Cheers,

 

[Pat Hartman]

the min function is a domain aggregate function, so it can not be used as criteria.

When you have criteria that applies to aggregated data, you would use the HAVING predicate rather than WHERE.

WHERE selects/omits data based on original values PRIOR to aggregation.
HAVING selects/omits data based on aggregated values so it is applied AFTER aggregation.

You would use HAVING for example if you were looking for all orders over $1,000 or all customers who placed more than 10 orders in a year.