Multi-query problem

[mjdemaris]

I have a form that uses qryPartEdit to display most of the information, and uses another query (qryLastStockTake) to get some more details. Yet if there is no data in the record for qryLastStockTake I do not get any results. I've tried to change the INNER join to LEFT, but that join is not supported.

Here is the sql:

 SELECT [T-Parts].MasterNum, [T-Parts].Item, [T-Parts].CategoryID_FK, [T-SupplierPartNums].PartNumber, [T-SupplierPartNums].Barcode, Sum([Quantity]*[Result]) AS [Current Stock], [T-Parts].Attachment, [T-Warehouses]![WarehouseName] & " " & [T-Bins]![BinName] AS FullBinLoc, [T-Parts].WinterLevel, [T-Parts].SummerLevel, [T-Parts].Discontinued, [Q-LastStockTake].MaxOfTransDate
FROM [T-Warehouses] INNER JOIN ((([T-Categories] INNER JOIN ([T-TransTypes] INNER JOIN (([T-Bins] INNER JOIN ([T-Parts] INNER JOIN [T-PartLocations] ON [T-Parts].MasterNum = [T-PartLocations].[MasterID_FK]) ON [T-Bins].BinID = [T-PartLocations].BinID_FK) INNER JOIN [Q-LastStockTake] ON [T-Parts].MasterNum = [Q-LastStockTake].MasterNum) ON [T-TransTypes].TransTypeID = [Q-LastStockTake].TransTypeID) ON [T-Categories].CategoryID = [T-Parts].CategoryID_FK) INNER JOIN [T-SupplierPartNums] ON [T-Parts].MasterNum = [T-SupplierPartNums].MasterID_FK) INNER JOIN [T-Transactions] ON ([T-Parts].MasterNum = [T-Transactions].MasterNumID) AND ([T-TransTypes].TransTypeID = [T-Transactions].TransTypeID)) ON [T-Warehouses].WarehouseID = [T-Bins].WHID_FK
GROUP BY [T-Parts].MasterNum, [T-Parts].Item, [T-Parts].CategoryID_FK, [T-SupplierPartNums].PartNumber, [T-SupplierPartNums].Barcode, [T-Warehouses]![WarehouseName] & " " & [T-Bins]![BinName], [T-Parts].WinterLevel, [T-Parts].SummerLevel, [T-Parts].Discontinued, [Q-LastStockTake].MaxOfTransDate, [Q-LastStockTake].MasterNum
HAVING ((([T-Parts].MasterNum)=[TempVars]![tmpMaster]));

I want to display the MasterNum, Item, FullBinLoc, Category, WinterLevel, SummerLevel, CurrentStock, and Last Stock Take (MaxOfTransDate).

How can I get a record to display even if a field is null or empty?

Thanks.

 

[plog]

You reference qryPartEdit and qryLastStockTake in your explanation, yet neither of those appear in the query you posted.

Please do a better job of explaining where you are starting and where you hope to end. Actualy, it would be best if you just did that with data. Post 2 sets:

A - starting sample data from your tables. Include table/field names and enough data to cover all cases.

B - expected results based on A. Show what you expect your query to return when you feed it the data from A.

 

[mjdemaris]

Here is my database as it stands. The opening form does nothing at the present, we are discussing whether to use something like this.

There are two main problem areas, one is using the Tech Inv form, which is very incomplete, and the other is under the PCM form, when New Part or Edit Part is clicked.

Some of the queries may have changed since that last post.

 

[plog]

Ok, that covers A.

Now please provide B.

 

[mjdemaris]

This form is the one I use to edit the part information. It should open up filled with data from the T-Parts table based on the Master number, which I think is stored as TempVar tmpMaster.

I had it working somewhat when I posted the original thread, but have been working on it so now it is orphaned from the data.

The problem I was having was getting the LocationID_FK data and the LastCountDate. I tried using a sub query as part of the main query. The main query provided MasterNum, Item, Category, Winter and Summer levels, and the Amount (I think), while the sub (is supposed to) provides the other two.

That answer your question?

Also, if I remember correctly, I could not edit one of the fields...locationID_FK.

 

[plog]

That answer your question?

Nope. See post #2 (http://www.access-programmers.co.uk/forums/showpost.php?p=1459013&postcount=2). 2 sets of data. Not 1 set and an explanation.