duplicate in 2 locations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have part numbers we supply at 2 of our locations. My query contains usage
per quarter and a field that has estimated annual usages. My table will
contain duplicates because the part is at 2 locations. The outcome I want is
to have no duplicate parts, but I want the larger of the 2 estimated annual
usages. I dont know how to do that?
 
If you already use a GROUP BY which does not group over the locations, use
MAX(quantity), or SUM(quantity), to get the max, over all the locations (ok
if there is just one, or more than one), or the SUM for all the locations.


Hoping it may help,
Vanderghast, Access MVP
 
It doesnt allow me to take the max of the estimated annual usage because of
the othere fields that have quarter 1 average quarter 2 average etc.

example: if I have Plant A part number ABC Estimated Annual usage of 400
Plant B part number ABC(same) Estimated annual
usage of 20

I want to just see Plant A because it its Estimated annual usage is higher.
 
If the data is like:

Plant PartNumber quarter1 quarter2 quarter3 ' fields name



Cannot you use:

SELECT PartNumber, MAX(quarter1), MAX(quarter2), MAX(quarter3)
FROM yourTable
GROUP BY PartNumber



?


Vanderghast, Access MVP
 
Lets say

Plant Partnumber qt1 qt2 qt3 qt4 Estimated Annual Usage
22 abc 1 15 0 0 48
27 abc 14 0 1 0 45

So in my table I want to see Plant 22 info(on my outcome) because Estimated
Annual usage is 48. If I max each qt will it work because plant 27 has 14 in
qt1 and Plant 22 has 1?
 
If you *just* want the maximum estimated usage, try:

SELECT PartNumber, MAX([estimated annual usage)
FROM yourTable
GROUP BY partNumber


If you want other fields, what will we get, as result, if the whole data is
like you just gave?



Vanderghast, Access MVP
 
Was missing a ], should be:

SELECT PartNumber, MAX([estimated annual usage])
FROM yourTable
GROUP BY partNumber


You don't need the [ ] if the field name is properly formed (such as having
NO space in it).


Vanderghast, Access MVP
 
Thanks for you help. The information was very useful. It worked, but I
realized that if both locations have a zero value it will keep the duplicates
in.
 
You can then make a

SELECT DISTINCT ...


to remove the two identical records in the result. (Note that this would
also occur if the two sites get the same estimated value. The Max is then
observable at the two sites! and the query has no way to select one over the
other. But if the records, in the result, have no mention of the site, the
SELECT DISTINCT will do the job, nicely)


Vanderghast, Access MVP
 
Back
Top