Finding the Min price of materials (Engineer in confusion)

BAW331 · Jul 11, 2012

Hey! So this is my first post on this forums and ive been searching around the forums for an answer, but can't seem a simple solution. I've been given this access database problem by my company, im actually just an engineer by degree, so im not really use to building databases, so I was wondering if anyone could help me.

Basically, we are buying materials from a supplier. The suppliers give dates where prices are valid from. I have a query which chooses the valid prices for each material. Now im trying to figure out how to choose the minimum price between different suppliers for the same material.

Query 1 (finds the valid prices from a list of prices)
Component A
Price $30 Valid from 01/01/2011 to 01/01/2012
Price $40 Valid from 01/01/2002 to 01/01/2013

So now I want to choose the lowest price between suppliers. The above query gives:
Supplier ID ....... Material ID ........ Price
1 ..................... 58712 .............. $40
2 .....................58712 ............... $50
3 ..................... 99998 ............... $23

Im just confused how I program access to choose supplier 1.. Ive tried grouping the material ID, and finding the min price, however that just chooses $40 but not the corresponding supplier ID...Also I was wondering whether it is possible to do this under one query...

Thanks for any help!!!

jdraw · Jul 11, 2012

This may be helpful
http://www.techonthenet.com/sql/min.php

BAW331 · Jul 11, 2012

jdraw said:
This may be helpful

Thanks for the link, will attempt to do it. Ive only done basic programming, so this is all new to me. Only really used "Matlab" as a program to do things for engineering applications..

Furthermore, I was wondering if someone could also help with another request..

Say I have components in other currencies and I want to convert them all to one currency (GBP)
e.g.
Component A Price 50 EUR
Component B Price 30 USD
Component C Price 20 CAD

Is there a way for Access to run down the list and ask the user to enter the exchange rate to change all the currencies into GBP in a new column. So Access effectively stores the exchange rate of EUR to GBP and changes all the components in EUR to GBP...

Im actually just an intern in a rather big company, so trying to figure out some stuff for them...

THANKS for the help if anyone can!

Click to expand...

John Big Booty · Jul 11, 2012

You may be able to adapt the procedure I've used in the attached sample, to deal with your exchange rates.

BAW331 · Jul 12, 2012

Hey thanks for the file!
I was wondering whether anyone could check my code:
I managed to find a way to do my first problem however when I close SQL viewer, it changes the code:
I start with:
SELECT T.[Vendor ID], T.[Material ID], T.[Rate]
FROM VCP AS T INNER JOIN
(SELECT [Material ID], Min(Rate) as LowPrice
FROM VCP
GROUP BY [Material ID]) AS LP
ON (T.Rate=LP.LowPrice) AND (T.[Material ID]=LP.[Material ID]);

Once I save and close SQL viewer, it changes my code to this:

SELECT T.[Vendor ID], T.[Material ID], T.[Rate]
FROM VCP AS T INNER JOIN [SELECT [Material ID], Min(Rate) as LowPrice
FROM VCP
GROUP BY [Material ID]]. AS LP ON (T.[Material ID]=LP.[Material ID]) AND (T.Rate=LP.LowPrice);

Ive marked in red what it changes. Once these changes happen to the type of brackets and extra full stop, the code cannot run givinging a "syntax error in FROM clause"

Is there any way to stop SQL code thing to stop changing the brackets and adding an extra full stop....

Thanks!

MSAccessRookie · Jul 12, 2012

MS Access will always try to be helpful and reformat your Sub-Query for you. The problem is that it gets the intention WRONG a significant percent of the time. The only way that I know to avoid this is not to use In-Line Sub-Queries,and change them to real Queries instead. If you take the code SELECT [Material ID], Min(Rate) as LowPrice FROM VCP GROUP BY [Material ID and store it in a new Query (qryFindLowPrice), you could then change your Primary Query to the following, (which will not be reconfigured by MS Access:

Code:

[FONT=Arial]SELECT T.[Vendor ID], T.[Material ID], T.[Rate]
FROM VCP AS T INNER JOIN [B]qryFindLowPrice[/B][/FONT][FONT=Arial] AS LP[/FONT]
[FONT=Arial]ON (T.Rate=LP.LowPrice) AND (T.[Material ID]=LP.[Material ID]);[/FONT]

BAW331 · Jul 13, 2012

MSAccessRookie said:
MS Access will always try to be helpful and reformat your Sub-Query for you. The problem is that it gets the intention WRONG a significant percent of the time. The only way that I know to avoid this is not to use In-Line Sub-Queries,and change them to real Queries instead. If you take the code SELECT [Material ID], Min(Rate) as LowPrice FROM VCP GROUP BY [Material ID and store it in a new Query (qryFindLowPrice), you could then change your Primary Query to the following, (which will not be reconfigured by MS Access:

Code:

[FONT=Arial]SELECT T.[Vendor ID], T.[Material ID], T.[Rate][/FONT] [FONT=Arial]FROM VCP AS T INNER JOIN [B]qryFindLowPrice[/B][/FONT][FONT=Arial] AS LP[/FONT] [FONT=Arial]ON (T.Rate=LP.LowPrice) AND (T.[Material ID]=LP.[Material ID]);[/FONT]

Works great! Thanks very much!!

Finding the Min price of materials (Engineer in confusion)

BAW331

New member

jdraw

Super Moderator

BAW331

New member

John Big Booty

AWF VIP

Attachments

BAW331

New member

MSAccessRookie

AWF VIP

BAW331

New member

Similar threads

Users who are viewing this thread