correlated query to find cheapest manufacturer for a product

[rafnews]

Hi,

I have a SQL Query that returns me:
- the "product code" i must purchase,
- the "quantity" to purchase for this product
- and if it's in "promotion or not".

Based on this result, i must to look at "manufacturer" table and find the cheapest "price" for each product to purchase and the quantity available at the manufacturer.
If "quantity to order" (e.g. 20) is higher than "quantity available" (e.g. 12) at "cheapest manufacturer", i must order the missing quantity (8) to the next cheapest manufacturer.
How can i do that ?

thx

 

[Joe4]

Cross-posted: Correlated query to find cheapest manufactuer
and here: http://www.access-programmers.co.uk/forums/showthread.php?t=276135

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

 

[rafnews]

Cross-posted: Correlated query to find cheapest manufactuer

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

Sorry but i only posted this message once on this forum...so where is the cross-posted ?

 

[Joe4]

Sorry but i only posted this message once on this forum...so where is the cross-posted ?

Cross-post does not mean it is posted more than once on a single forum (that is a "duplicate post").
Cross-post means that you have posted the same question across multiple forums (which you did, I provided a link to the other post you made on Access forums).

I strongly suggest you read the link provided on Cross-Posting to understand the concerns: Excelguru Help Site - A message to forum cross posters

Like I said, we don't have a problem with you doing it, provided that you mention it and provide links to where you have posted the question on other forums. That way people do not waste time going down a path which might have already been explored on one of the other forums.

 

[rafnews]

ok but something is crazy here... if there are several different forums about a topic (in my case MS Access), how can i be sure to get help if i an not post my message on several forums ? if i post my message on a single forum, will i have more chance to get an answer than if i post it on 3 different forums ? i do not think so. Moreover, how can i know that this forum is partner/link to another forum about the same topic ? That's crazy as the purpose of forums are to help people finding answers to a problem, so if such situation can not be, in this case WHY are there so much forums talking about the same topic ? I make no sense to tell i cross-posted on different forums. I could understand if it was on several sub-topics/categories of the SAME forum... but this is not the case.
By the way, people in this forum are not necessary the same in other forums...

I can understand you informed people that the same topic is somewhere else on internet.

 

[Joe4]

It really boils down to one thing, common courtesy. I will explain exactly why we have this policy with a real example that happened to me (as has happened to many other people).

I once spent hours working on an elaborate solution for a person. I posted my reply, and they replied back, "oh, I already got an answer to that on another forum". Imagine how that made me feel! I spent hours of timing answering a question for a person free of charge, only to find out that I wasted my time. This has happened to many people. This can lead to some hard feelings, and is a good way to get a reputation and start getting ignored on forums. The IT community is a small world, and a lot of people help out on multiple forums.

Like I said more than once, we are not saying that you cannot post your question on multiple forums -- we are just saying that you need to mention that you are doing so and provide links to the other posts, so others can see what has already been done (if anything) before they start working on something for you.

It really isn't much to ask, just a little common courtesy for people who are freely giving of their time to help you.

 

[xenou]

You can read here for more information on cross-posting (basically, what Joe said):
Excelguru Help Site - A message to forum cross posters

One thing to realize is that if you get a reputation for cross-posting, many "regulars" will then ignore your questions and the end result will be exactly what you want to avoid - no answers to your questions.

 

[xenou]

You can try this. Does it help? For future reference its best to give precise field names and table names so we don't have to guess at them.

This isn't really a correlated query in the strict sense - it's just a regular old query.

SELECT  
	Q.[ProductCode], M.[ManufacturerName], MIN(M.[Price]) As MinPrice, 
	M.[QtyAvailable] 
FROM 
	Query1 Q
	INNER JOIN
	Manufacturer M
	ON Q.[ProductCode] = M.[ProductCode]
WHERE
	M.[QtyAvailable] >= Q.[QtyToPurchase]
GROUP BY
	Q.[ProductCode], M.[ManufacturerName],
	M.[QtyAvailable]

I added the criteria about quantity so as to avoid purchasing from a supplier that can't provide the quantity. With a little more work, you could order from lowest and second lowest in those cases, if you prefer.