inventory problem (sort of) 1

[simon551]

Hi All,
I've run into a snag in designing my database (or at least my query). I'm trying to track stocks.
I have tables:
tblSecurities
SecID,Tckr, Description
tblInventory
LotID, SecID, PurchDate, NumShares
tblSales
SaleID, LotID, SaleDate, NumSold

Then I query for BalShares by NumShares-NumSold. I run into a problem if I sell the same lot twice. For example I may buy 1000 and Sell 500 twice.
When I run the query for BalShares I then have 1000 (1000-500 x2). Because the LotID is duplicated in the sale. I'm sure someone has run into this before, but I tried searching for keyword inventory and I'm not seeing an answer.

 

[fredericofonseca]

Show us your SQL.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Join on an aggregate query instead of directly on tblSales:
SELECT LotID, Sum(NumSold) AS TotalSold FROM tblSales GROUP BY LotID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

I get the message:You tried to execute a query that does not include the specified expression "transID" as part of an aggegate function.

SELECT tblSales.TransID, tblSales.LotID, tblSales.SaleDate, Sum(tblSales.NumSold) AS TotalSold, tblSales.SellPrice, tblSales.ST, tblSales.Notes
FROM tblSales
GROUP BY tblSales.LotID;

 

[simon551]

PHV,
I'm trying it w/out the date.price, and transID. I'll let you know after I try it in the other query.

Thanks,
Simon

 

[simon551]

Worked beautifully, PHV. Thanks a million. If you live in L.A. I'd wash your car.
Can I ask you something else? What if I needed to only include in my later query transaction between certain dates? I wasn't able to do it anyway, but is there a way to get back the dates? Here's a sql that I would want to abridge to select only sales before date and purchases before date:

SELECT tblInventory.LotID, tblInventory.SecID, tblInventory.MgrID, tblManagers.Manager, tblInventory.Description, tblInventory.Tckr, tblInventory.Pdate, tblPrices.Date, [Date]-[Pdate] AS PeriodHeld, IIf((([Date]-[Pdate])<=365),"yes","no") AS Shortterm, [NumShares]-Nz([TotalSold],0) AS BalShares, tblPrices.Price, Nz([BalShares],0)*[Price] AS FMVRD, tblInventory.NumShares, tblInventory.Price1, Nz([BalShares],0)*[Price1] AS Cost1, tblInventory.Price2, Nz([BalShares],0)*[Price2] AS Cost2, [FMVRD]-[Cost1] AS Unrealized1, [FMVRD]-[Cost2] AS Unrealized2
FROM ((tblInventory INNER JOIN tblPrices ON tblInventory.SecID = tblPrices.SecID) INNER JOIN tblManagers ON tblInventory.MgrID = tblManagers.MgrID) LEFT JOIN qrySalesForInventorySolution ON tblInventory.LotID = qrySalesForInventorySolution.LotID
WHERE (((tblInventory.MgrID)=[Forms]![frmFMVReviewMgr].[txtMgrID]) AND ((tblInventory.Pdate)<(([Forms]![frmFMVReviewMgr].[txtDate]))) AND ((tblPrices.Date)=[Forms]![frmFMVReviewMgr].[txtDate]) AND (([NumShares]-Nz([TotalSold],0))>0))
ORDER BY tblInventory.Description;

 

[PHV]

Something like this ?
SELECT I.LotID, I.SecID, I.MgrID, M.Manager, I.Description, I.Tckr,
I.Pdate, P.Date, P.Date-I.Pdate AS PeriodHeld, IIf(P.Date-I.Pdate<=365,"yes","no") AS Shortterm,
I.NumShares-Nz(S.TotalSold,0) AS BalShares, P.Price, Nz([BalShares],0)*P.Price AS FMVRD, I.NumShares,
I.Price1, Nz([BalShares],0)*P.Price1 AS Cost1, I.Price2, Nz([BalShares],0)*P.Price2 AS Cost2,
[FMVRD]-[Cost1] AS Unrealized1, [FMVRD]-[Cost2] AS Unrealized2
FROM ((tblInventory AS I
INNER JOIN tblPrices AS P ON I.SecID = P.SecID)
INNER JOIN tblManagers AS M ON I.MgrID = M.MgrID)
LEFT JOIN (SELECT LotID, Sum(NumSold) AS TotalSold FROM tblSales
WHERE SaleDate < [Forms]![frmFMVReviewMgr]![txtDate] GROUP BY LotID) AS S ON I.LotID = S.LotID
WHERE I.MgrID = [Forms]![frmFMVReviewMgr]![txtMgrID]
AND I.Pdate < [Forms]![frmFMVReviewMgr]![txtDate]
AND P.Date = [Forms]![frmFMVReviewMgr]![txtDate]
AND I.NumShares > Nz(S.TotalSold,0)
ORDER BY I.Description;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

I'm getting a syntax error message:
SELECT tblInventory.LotID, tblInventory.SecID, tblInventory.MgrID, tblManagers.Manager, tblInventory.Description, tblInventory.Tckr, tblInventory.Pdate, tblPrices.Date, [Date]-[Pdate] AS PeriodHeld, IIf((([Date]-[Pdate])<=365),"yes","no") AS Shortterm, [NumShares]-Nz([TotalSold],0) AS BalShares, tblPrices.Price, Nz([BalShares],0)*[Price] AS FMVRD, tblInventory.NumShares, tblInventory.Price1, Nz([BalShares],0)*[Price1] AS Cost1, tblInventory.Price2, Nz([BalShares],0)*[Price2] AS Cost2, [FMVRD]-[Cost1] AS Unrealized1, [FMVRD]-[Cost2] AS Unrealized2
FROM ((tblInventory INNER JOIN tblPrices ON tblInventory.SecID = tblPrices.SecID) INNER JOIN tblManagers ON tblInventory.MgrID = tblManagers.MgrID) LEFT JOIN (SELECT LotID, Sum(NumSold) AS TotalSold FROM tblSales WHERE SaleDate<([Forms]![frmFMVReviewMgr].[txtDate]) GROUP BY LotID) AS S ON tblInventory.LotID = S.LotID AND
(((tblInventory.MgrID)=[Forms]![frmFMVReviewMgr].[txtMgrID]) AND ((tblInventory.Pdate)<(([Forms]![frmFMVReviewMgr].[txtDate]))) AND ((tblPrices.Date)=[Forms]![frmFMVReviewMgr].[txtDate]) AND (([NumShares]-Nz([TotalSold],0))>0))
ORDER BY tblInventory.Description;

 

[PHV]

At least replace this:
tblInventory.LotID = S.LotID AND
By this:
tblInventory.LotID = S.LotID WHERE

Or simply paste the previous suggested code into your SQL pane

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

Syntax Error message again. If I paste your code (which I have no Idea why it almost works) I am asked for the parameters on Price1 and Price2

-Simon

 

[simon551]

Make that P.Price1 and P.Price2

 

[simon551]

I changed it to I.Price1 and I.Price2 and it works! I guess that is something to do with the capitalized letters in my table names? I hadn't seen that before. Does this mean I can delete that "qrySalesForInventorySolution?"

Thanks dude.

 

[PHV]

I can delete that "qrySalesForInventorySolution"
Yes, it is replaced by the inline view (S) with the date criteria.
By the way I, M and P are alias for tblInventory, tblManagers and tblPrices respectively.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

Your the best.

 

[simon551]

Do you have any idea why if I try to save the query as a different name so that I might modify it a bit for a different form, I receive a syntax error message?