Calculation help

[brickelldb]

It doesnt prompt me for an ItemID. If I attempt to run the query just as you provided, it does not return data, it returns the message "only 1 record can be returned at a time with this subquery". So, in order to return data, I manually hardcoded into the query a criteria for a particular itemID. Because it would not return any data otherwise.

Re: Your question, I would imagine that possible it could occur that 2 vendors contain the same lowest price for a particular ItemID. There are over 20,000 uniqe items. And in the Tbl_PriceCheck, there are 2 to 5 vendors selling that same ItemID with different prices. I would be foolish to say it would never happen...............(could this be the reason for the message "only 1 record can be returned...."?)

Here is an example of what is in the Tbl_PriceCheck (this table contains the various prices from each vendor, for each item):

ID, ItemID, VendorID, Price
--------------------------
1, ab123, 1, 200
2, ab123, 2, 300
3, ab123, 3, 400
4, tv987, 1, 50
5, tv987, 2, 20
6, tv987, 3, 75
7, jk456, 1, 5
8, jk456, 2, 15
9, jk456, 3, 7

So, what I would want the query to return to me is as follows (notice how it returned only the lowest price and it's respective vendor for each ItemID available in the PriceCheck table):

ItemID, VendorID, Price
-----------------------
ab123, 1, 200
tv987, 2, 20
jk456, 1, 5

So that I can turn around and with the above query, update the table called Tbl_PurchaseOrder which already contains the following:

ID, POID, ItemID, VendorID, Price
-------------------------------------
1, 1, abc123, 0, 0
1, 1, tv987, 0, 0
1, 1, jk456, 0, 0

In summary, the Tbl_PriceCheck contains all available Items and their prices from all available vendors who sell those items. The Tbl_PurchaseOrder contains the items we need to order. The user enters the itemID's he wants.......and then I need for the query to update it with the lowest price and the vendorID from which that lowest price is coming from...........ahhhhhhhhhhh I hope I'm explaining myself properly.

 

[jjturner]

brickelldb,

I just tested the query with the sample data you posted. It works fine as long as your data meets the following condition:

---> You can't have more than 1 VendorID with the same minimum price (or even the same VendorID repeated with the same minimum price)

So I changed the query to show you multiple records for the same ItemID when more than 1 VendorID has the same minimum Price:

SELECT DISTINCT tPCMain.ItemID, tPCMain.VendorID,
  (SELECT DISTINCT TOP 1 tPC.Cost
   FROM Tbl_PriceCheck tPC
   WHERE tPC.ItemID = tPCMain.ItemID
   ORDER BY tPC.Cost) AS Price
FROM Tbl_PriceCheck AS tPCMain
WHERE tPCMain.VendorID In (SELECT TOP 1 tPCV.VendorID
   FROM Tbl_PriceCheck tPCV
   WHERE tPCV.ItemID = tPCMain.ItemID
   ORDER BY tPCV.Cost);

So that's why I wanted to see your data - you have this situation occurring where more than 1 Vendor has the same minimum price. That is what the message is telling you (and what I was trying to explain). Hopefully this makes some sense.

HTH,
John

 

[brickelldb]

It does and I really do appreciate you taking the time to help me through this.

Thanks again John.

 

[jjturner]

NP - time for me to 'hit the hay'

 

[killyridols]

JJTurner, You seem to be an access master. Could I ask you for some assistance as well??

So I was able to use the advice you gave to Casey to do a count of the number of species per sample, resulting in a table with the columns sample_id, and maxi.

SELECT sample_id, Count([sample_id]) AS maxi
FROM REVDAT
GROUP BY sample_id;

However, is there a way to give the table that appears when I run the query a name or some sort of ID? I would like this because I want to further extend my query so that I can extract data from this new temporary(?) table and others in my database to be compiled and unloaded as dat files.

I hope my question is clear.... Your help would be greatly appreciated!
Thank you.

 

[brickelldb]

I more or less had to do what you are asking.......

I converted the query to a make table query.....the resulting table I used to query against and update other tables, etc....

Convert your query to a MAKE TABLE query.....then you can further query against that data (table it makes).................

Hope this helps....