Solved

Using the Min/Max Function in a Query

Posted on 2008-06-11
6
4,131 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
[X]
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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

739 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