Solved

Using the Min/Max Function in a Query

Posted on 2008-06-11
6
4,116 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now