Create table for top/min record only (1 Viewer)

[Thicko]

Hi Guys,

I have a problem with a make take sql query. I have the make table query below

Dim sql1 As String
sql1 = " SELECT TOP 1 [Forms]![frmChemoRecord].[BatchNumber] AS BatchNumber1, qryStockLiveUse.DrugNameVial AS DrugNameVial1, qryStockLiveUse.Product AS Product1, qryStockLiveUse.ProductSize AS ProductSize1, qryStockLiveUse.Manufacturer AS Manufacturer1, qryStockLiveUse.StockBatchNumber AS StockBatchNumber1, qryStockLiveUse.StockExpiryDate AS StockExpiryDate1, qryStockLiveUse.Stock AS Stock1, qryChemoWSAll1.StockRequired1 "
sql1 = sql1 + "INTO atblStockLiveShortExpiry1 FROM qryChemoWSAll1, qryStockChemoWSAll INNER JOIN qryStockLiveUse ON qryStockChemoWSAll.Drug1 = qryStockLiveUse.DrugNameVial WHERE (((qryStockLiveUse.StockExpiryDate)>Date()) "
sql1 = sql1 + "AND ((qryStockLiveUse.Stock)>=[StockRequired1])) ORDER BY qryStockLiveUse.StockExpiryDate ;"
DoCmd.RunSQL sql1

The problem comes when I have two products with the same name, manufactuer and expiry date but a different batch number. This creates a table with two records which when I come to use the data later in a (total stock) - (used stock) query. Because two records where created in the table twice the stock is booked out.

Beyond some clever person giving a way to create a single line table from the above code, my thoughts are either later in the code do (total stock) - (used stock / number of records) but not sure how I would get the number of records using vba into another sql statement.

Alternative build another select query to get a single record of stock used, however this would require passing the result into another sql command and I'm not sure how to do this.

All solutions and ideas on the best way to proceed much appreciated.

Regards
Thicko