Best way / work around around to handle a zero results query (1 Viewer)

[Thicko]

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.

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;

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

 

[jdraw]

What exactly does this mean

create table (atblStockLiveShortExpiry1) queries

?? Do you mean MakeTable query?

You can use multiple queries/subqueries instead of making a new table to resolve info.

Might be helpful if you gave a concrete example of what you are doing.

 

[Thicko]

Re: Best way / work around to handle a zero results query

Yes I should have said MakeTable query, and not create table.

I use the prefix atbl instead of tbl so I can identify those tables that are automaticlly created.

I also showed code for a select table query rather than a make table query simply because I was working with it as select query before changing to a make table query. Should have read:

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 "
sql1 = sql1 + "FROM qryChemoWSAll1, qryStockChemoWSAll INNER JOIN qryStockLiveUse ON qryStockChemoWSAll.Drug1 = qryStockLiveUse.DrugNameVial "
sql1 = sql1 + "WHERE (((qryStockLiveUse.StockExpiryDate)>Date()) AND ((qryStockLiveUse.Stock)>=[StockRequired1])) "
sql1 = sql1 + "ORDER BY qryStockLiveUse.StockExpiryDate , qryStockLiveUse.StockBatchNumber DESC ;"
DoCmd.RunSQL sql1

I have 5 of these make table queries using different drug size vials to create a single dose. Not all doses will require each size of drug vial to create the dose. Hence why some tables come back with no records.

 

[jdraw]

I am not sure whether your database is in the design and development stage or whether you are in production. We don't know what qryChemoWSAll1 or qryStockChemoWSAll
or qryStockLiveUse represent. I recommend you tell us in 5-6 lines of plain simple English, what your database/business is about. No jargon, no Access just simple English. Get your database designed to match your needs before you get into forms etc.

 

[Thicko]

Thanks for the interest, don't sweet on it, it's not a show stopper just a learning point for me. To explain some of what is going on:

qryStockChemoWSAll identifies the chemotherapy drug vial sizes required (for example a carboplatin dose of 800mg would require a Carboplatin 600mg vial, a Carboplatin 150mg vial and a Carboplatin 50mg vial, but not a 450mg vial). These are labelled Drug 1 to 5 in sequential order of Drug Vial size, so in the above example Drug 2 will be null as no 450mg vials are required. This links to qryStockLiveUse which is a stock list and will hold the name and quantities of each drug in stock.

qryChemoWSAll1 identifies the quantity of each of the drug vials required labelled as StockRequired 1 to 5 again in the same sequential order of drug vial size.

The make table query combines this data to pull a specific batch number for each of the drug vial sizes into atblStockLiveShortExpiry1 to 5 sequentially. This is later inserted into another table and the record from these tables are deleted.

We are not allowed to combine drug vials from different companies but due to procurement issues we often have stock from different manufactures on the shelf.

I need a mechanism by which the same manufacterer is selected throughout. The only way I see to do this is to check the batch number the in atblStockLiveShortExpiry1and make sure the result in atblStockLiveShortExpiry2 is the same manufacturer but if one vial size isn't required the atblStockLiveShortExpiry has no records so I can't put a criteria for even a null record because there isn't even a null record in the table.

My current plan is to test if the table is null and if so append a result to an unused field but I wondered if there's a one step way to force a null row to a query.