Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using the Min/Max Function in a Query

Posted on 2008-06-11
6
4,127 Views
Last Modified: 2008-09-22
Hi,

I want to thank you in advance for the help.

I currently have multiple tables set up that contain part numbers with prices. I have one "Volume Master" which contains all the part numbers in the database. Every table is linked to this Volume Master through their component number with a join proper of number 2 "Include All records from Volume Master and only those records from "Table" where the joined fields are equal. When I run the query i can easily see all the parts and their numbers by row. By column are different manufacturers with their prices. I can easily compare prices this way. I want to create a column that would take the min and another column that would take the max price of each part number so that i can quickly determine the lowest and highest prices. Right now i currently export the data to excel and use the min and max functions there. I would like to set this up in Access so that whenever i update my tables i automatically get the new min and max for each part number therefore saving me valuable time because exporting the large list can take up to an hour.

I hope i explained myself clearly.

Thank you
SELECT [Volume Master].[Part Type], [Volume Master].[Part Number], [Volume Master].Description, [Volume Master].Quantity, [Mfg Detail].Manufacturer, [Mfg Detail].[Mfg PN], [Mfg Detail].Status, [Mfg Master].Currency, [$ ABS Q1 07].[Cost in Dollars], [$ ABS Q2 08].[Cost in Dollars], [$ MGN Misc Q1 07].[Cost in Dollars], [$ ATL SAP Q1 07].[Cost in Dollars], [$ RCH Agile Q1 07].[Cost in Dollars], [$ ABS ICs 3-31-07].[Cost in Dollars], [$ BMK Q1 07].[Cost in Dollars], [$ BMK Q2 08].[Cost in Dollars], [$ SANM-CN Q1 07].[Cost in Dollars], [$ SANM-US Q1 07].[Cost in Dollars], [$ FLEX-EU Q1 07].[Cost in Dollars], [$ FLEX-US Q1 07].[Cost in Dollars], [$ BMK 4-24-07].[Cost in Dollars], [$ BMK ICs 4-4-07].[Cost in Dollars], [$ Ludke ICs 3-31-07].[Cost in Dollars], [$ SANM CN 2-1-07].[Cost in Dollars], [$ SANM US 2-2-07].[Cost in Dollars], [$ MGN Baan 4-30-07].[Cost in Dollars], [$ MGN Baan 6-4-07].[Cost in Dollars], [$ ATL Agile 6-12-07].[Cost in Dollars], [$ Max and Min].[Min Cost], [Volume Master.Quantity]*[Min Cost] AS Expr1, [$ Max and Min].[Max Cost], [Volume Master.Quantity]*[Max Cost] AS Expr2
FROM (((((((((((((((((((((([Volume Master] LEFT JOIN [Mfg Detail] ON [Volume Master].[Part Number] = [Mfg Detail].[Part Number]) LEFT JOIN [Mfg Master] ON [Mfg Detail].Manufacturer = [Mfg Master].[Manufacturer's Name]) LEFT JOIN [$ ABS Q1 07] ON [Volume Master].[Part Number] = [$ ABS Q1 07].Component) LEFT JOIN [$ MGN Misc Q1 07] ON [Volume Master].[Part Number] = [$ MGN Misc Q1 07].Component) LEFT JOIN [$ ATL SAP Q1 07] ON [Volume Master].[Part Number] = [$ ATL SAP Q1 07].Component) LEFT JOIN [$ RCH Agile Q1 07] ON [Volume Master].[Part Number] = [$ RCH Agile Q1 07].Component) LEFT JOIN [$ BMK Q1 07] ON [Volume Master].[Part Number] = [$ BMK Q1 07].Component) LEFT JOIN [$ SANM-CN Q1 07] ON [Volume Master].[Part Number] = [$ SANM-CN Q1 07].Component) LEFT JOIN [$ SANM-US Q1 07] ON [Volume Master].[Part Number] = [$ SANM-US Q1 07].Component) LEFT JOIN [$ FLEX-US Q1 07] ON [Volume Master].[Part Number] = [$ FLEX-US Q1 07].Component) LEFT JOIN [$ ABS ICs 3-31-07] ON [Volume Master].[Part Number] = [$ ABS ICs 3-31-07].Component) LEFT JOIN [$ BMK 4-24-07] ON [Volume Master].[Part Number] = [$ BMK 4-24-07].Component) LEFT JOIN [$ BMK ICs 4-4-07] ON [Volume Master].[Part Number] = [$ BMK ICs 4-4-07].Component) LEFT JOIN [$ FLEX-EU Q1 07] ON [Volume Master].[Part Number] = [$ FLEX-EU Q1 07].Component) LEFT JOIN [$ Max and Min] ON [Volume Master].[Part Number] = [$ Max and Min].Component) LEFT JOIN [$ Ludke ICs 3-31-07] ON [Volume Master].[Part Number] = [$ Ludke ICs 3-31-07].Component) LEFT JOIN [$ SANM CN 2-1-07] ON [Volume Master].[Part Number] = [$ SANM CN 2-1-07].Component) LEFT JOIN [$ SANM US 2-2-07] ON [Volume Master].[Part Number] = [$ SANM US 2-2-07].Component) LEFT JOIN [$ MGN Baan 4-30-07] ON [Volume Master].[Part Number] = [$ MGN Baan 4-30-07].Component) LEFT JOIN [$ MGN Baan 6-4-07] ON [Volume Master].[Part Number] = [$ MGN Baan 6-4-07].Component) LEFT JOIN [$ ATL Agile 6-12-07] ON [Volume Master].[Part Number] = [$ ATL Agile 6-12-07].Component) LEFT JOIN [$ ABS Q2 08] ON [Volume Master].[Part Number] = [$ ABS Q2 08].Component) LEFT JOIN [$ BMK Q2 08] ON [Volume Master].[Part Number] = [$ BMK Q2 08].Component
WHERE ((([Volume Master].[Part Type])="Part") AND (([Volume Master].Quantity)<>0));

Open in new window

0
Comment
Question by:jtovar3
  • 4
6 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 21765986
as you have a massive sql statement to go thru i'll just post an example of max and min
below returns min and max invoice amount per supplier
select s.supplierID, min(i.InvoiceAmount) as minInvoice, max(i.InvoiceAmount) as maxInvoice
from tblsupplier s join tblInvoice I
on s.supplierid = i.supplierid
group by s.supplierID
0
 
LVL 19

Accepted Solution

by:
frankytee earned 500 total points
ID: 21766192
Do you mean get the max of
[$ ABS Q1 07].[Cost in Dollars], [$ ABS Q2 08].[Cost in Dollars], [$ MGN Misc Q1 07].[Cost in Dollars], [$ ATL SAP Q1 07].[Cost in Dollars], [$ RCH Agile Q1 07].[Cost in Dollars], [$ ABS ICs 3-31-07].[Cost in Dollars], [$ BMK Q1 07].[Cost in Dollars], [$ BMK Q2 08].[Cost in Dollars], [$ SANM-CN Q1 07].[Cost in Dollars], [$ SANM-US Q1 07].[Cost in Dollars], [$ FLEX-EU Q1 07].[Cost in Dollars], [$ FLEX-US Q1 07].[Cost in Dollars], [$ BMK 4-24-07].[Cost in Dollars], [$ BMK ICs 4-4-07].[Cost in Dollars], [$ Ludke ICs 3-31-07].[Cost in Dollars], [$ SANM CN 2-1-07].[Cost in Dollars], [$ SANM US 2-2-07].[Cost in Dollars], [$ MGN Baan 4-30-07].[Cost in Dollars], [$ MGN Baan 6-4-07].[Cost in Dollars], [$ ATL Agile 6-12-07].[Cost in Dollars]
?

Is so then you need to rewrite your query from one big query to a union query of select statements where each select statement joins only to 1 component table, and then get the max of that union query
For below will return the max cost for each part number

Select u.[Part Number], max(u.Cost) as MaxCost
from
(Select vm.[Part Number], "[$ ABS Q1 07]" as table,  [$ ABS Q1 07].[Cost in Dollars] as Cost,
FROM [Volume Master]  vm
JOIN [$ ABS Q1 07] ON vm.[Part Number] = [$ ABS Q1 07].Component
WHERE (((vm.[Part Type])="Part") AND ((vm.Quantity)<>0))
Union
Select vm.[Part Number], "[$ MGN Misc Q1 07]" as table, [$ MGN Misc Q1 07].[Cost in Dollars] as Cost,
FROM [Volume Master]  vm
JOIN [$ MGN Misc Q1 07] ON vm.[Part Number] = [$ MGN Misc Q1 07].Component
WHERE (((vm.[Part Type])="Part") AND ((vm.Quantity)<>0))
Union&.
Etc

Union
Select vm.[Part Number], "[$ BMK Q2 08]" as table, [$ BMK Q2 08].[Cost in Dollars] as Cost,
FROM [Volume Master]  vm
LEFT JOIN [$ BMK Q2 08] ON vm.[Part Number] = [$ BMK Q2 08].Component
WHERE (((vm.[Part Type])="Part") AND ((vm.Quantity)<>0))
) as u

You can then join this query to your other tables [Mfg Detail], [Mfg Master] etc to get the other fields that you need
0
 

Author Comment

by:jtovar3
ID: 21770995
Yeah i kinda understand what your saying but will that take the max from each table or the max for that product across many tables?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 19

Expert Comment

by:frankytee
ID: 21776943
it will get the max for that product across many tables.
0
 

Expert Comment

by:intsup
ID: 22535459
i'm working on a master price file for our company and would love to chat with you about your project.

i've hit more than a few snags dealing with multiple vendors for the same part number and how to put it all in one file.

i actually have an open question looking for an off the shelf database but, if i can't find one, i'll have to go ahead and program one myself.

as far as i know there's no way to send private mail through this system so if you're open to talking about our master price files, please contact me by replying to one of my questions and we can figure out how to get into direct contact.

thanks!
0
 
LVL 19

Expert Comment

by:frankytee
ID: 22546122
my email address is on my ee profile. just ensure subject matter is "ExpExch" and explain your proposal.

cheers
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question