Hi Ladies & Gentleman,
I have several create table (atblStockLiveShortExpiry1) queries that where stock is required (& available) returns a result that is the shortest dated stock to be used later in my code.
I'm now looking to change the code so that the manufacturers in each of the created tables must be the same, i.e. all identical to the manufacturer in first created table (atblStockLiveShortExpiry1).
When I run the query with a table that has a previous result in everything is fine.
When one of the tables in the query has no results (either atblStockLiveShortExpiry1 or atblStockLiveShortExpiry2) in it then the subsequent query returns no results.
If I create a blank row with no data in either atblStockLiveShortExpiry1 or atblStockLiveShortExpiry2 then the query runs fine.
Is there a way to get a blank row or single result I can ignore into a query to make sure it is never a completly empty table?
My alternative solution is to test if the table is blank and if so run a second piece of SQL to create a single result in the (atblStockLiveShortExpiry1).
I would be intersted to know if option one is possible as I feel this would mean less code and so better performance.
Many Thanks
I have several create table (atblStockLiveShortExpiry1) queries that where stock is required (& available) returns a result that is the shortest dated stock to be used later in my code.
I'm now looking to change the code so that the manufacturers in each of the created tables must be the same, i.e. all identical to the manufacturer in first created table (atblStockLiveShortExpiry1).
When I run the query with a table that has a previous result in everything is fine.
When one of the tables in the query has no results (either atblStockLiveShortExpiry1 or atblStockLiveShortExpiry2) in it then the subsequent query returns no results.
If I create a blank row with no data in either atblStockLiveShortExpiry1 or atblStockLiveShortExpiry2 then the query runs fine.
Code:
SELECT TOP 1 [Forms]![frmChemoRecord].[BatchNumber] AS BatchNumber3, qryStockLiveUse.DrugNameVial AS DrugNameVial3, qryStockLiveUse.Product AS Product3, qryStockLiveUse.ProductSize AS ProductSize3, qryStockLiveUse.Manufacturer AS Manufacturer3, qryStockLiveUse.StockBatchNumber AS StockBatchNumber3, qryStockLiveUse.StockExpiryDate AS StockExpiryDate3, qryStockLiveUse.Stock AS Stock3, qryChemoWSAll1.StockRequired3
FROM qryChemoWSAll1, atblStockLiveShortExpiry2, atblStockLiveShortExpiry1, qryStockChemoWSAll INNER JOIN qryStockLiveUse ON qryStockChemoWSAll.Drug3 = qryStockLiveUse.DrugNameVial
WHERE (((qryStockLiveUse.StockExpiryDate)>Date()) AND ((qryStockLiveUse.Stock)>=[StockRequired3]))
ORDER BY qryStockLiveUse.StockExpiryDate, qryStockLiveUse.StockBatchNumber DESC;
My alternative solution is to test if the table is blank and if so run a second piece of SQL to create a single result in the (atblStockLiveShortExpiry1).
I would be intersted to know if option one is possible as I feel this would mean less code and so better performance.
Many Thanks