Link to home
Start Free TrialLog in
Avatar of dstjohnjr
dstjohnjrFlag for United States of America

asked on

Access 2003 / SQL 2008 Report Query Help

I am currently tasked with fixing a specific issue inside of a MS Access report that has never worked the way it should for a client.  I inherited this project when the former developer was no longer able to fulfill his duties.  The front end of this is MS Access 2003 and the database behind is MS SQL Server 2008.  I would greatly appreciate any help at all and will share as much info as I can to get to the bottom of it.

The only aspect / column not working properly inside this report is the value coming out of a column called TotCost as the below series of queries will divulge.  You can see it is derived by three other columns called ItemCost, MassCost & VialCost.  Let me know what else I might need to post here to help assess and fix this issue.  Your involvement in this issue is immensely appreciated!
Current Inventory Cost Report

Main Query behind report:

InventoryCostItemRptQry
SELECT Donor.DonorNo, Stage.StageOrder, Stage.Stage, InventoryCrosstabQry.[1/4gm], InventoryCrosstabQry.[1/2gm], InventoryCrosstabQry.[1gm], InventoryCrosstabQry.[2gm], InventoryCrosstabQry.[3gm], IIf(Stage.StageOrder>=10,InventoryCrosstabQry.Total,Donor.EstValue) AS TotValue, Donor.Status, InventoryCrosstabQry.Total, Donor.EstValue, [ItemCost]+[MassCost]+[VialCost] AS TotCost, GetInvCostDonorInvCostQry.ItemCost, GetInvCostDonorInvCostQry.MassCost, GetInvCostDonorInvCostQry.VialCost, InventoryCrosstabQry.BB10x10x10, InventoryCrosstabQry.BB10x10x20, InventoryCrosstabQry.BB10x10x30, InventoryCrosstabQry.BB5x10x10, InventoryCrosstabQry.BB5x10x20, InventoryCrosstabQry.BB5x10x30, InventoryCrosstabQry.[C1/2g], InventoryCrosstabQry.C1g
FROM ((Donor INNER JOIN Stage ON Donor.StageID=Stage.StageID) LEFT JOIN InventoryCrosstabQry ON Donor.DonorID=InventoryCrosstabQry.DonorID) LEFT JOIN GetInvCostDonorInvCostQry ON Donor.DonorID=GetInvCostDonorInvCostQry.DonorID
WHERE (((Donor.Status)=True) AND ((Stage.ShowInv)=True));

Sub Queries for InventoryCostItemRptQry:
InventoryCrosstabQry
GetInvCostDonorInvCostQry

InventoryCrosstabQry
TRANSFORM Count(GetAvailVialQry.VialNo) AS CountOfVialNo
SELECT GetAvailVialQry.DonorID, Sum(GetAvailVialQry.Value) AS Total
FROM VialSize INNER JOIN GetAvailVialQry ON (VialSize.VialSizeID = GetAvailVialQry.VialSizeID) AND (VialSize.VialSizeID = GetAvailVialQry.VialSizeID)
GROUP BY GetAvailVialQry.DonorID
PIVOT VialSize.VialSize;

GetInvCostDonorInvCostQry
SELECT GetInvCostDonorTotalsQry.DonorID, Sum(IIf([TotalAmt]=0,1,([AvailAmt]/([TotalAmt])))*IIf(IsNull([0]),0,[0])) AS ItemCost, Sum(IIf([TotalAmt]=0,0,([AvailAmt]/([TotalAmt])))*IIf(IsNull([1]),0,[1])) AS MassCost, IIf(([TotalVials]=0),0,Sum(([AvailVials]/([TotalVials]))*IIf(IsNull([2]),0,[2]))) AS VialCost, GetInvCostDonorTotalsQry.TotalAmt, GetInvCostDonorTotalsQry.TotalVials
FROM (GetInvCostDonorTotalsQry INNER JOIN GetInvCostDonorCostQry ON GetInvCostDonorTotalsQry.DonorID=GetInvCostDonorCostQry.DonorID) LEFT JOIN GetInvCostDonorAvailVialTotalsQry ON GetInvCostDonorTotalsQry.DonorID=GetInvCostDonorAvailVialTotalsQry.DonorID
GROUP BY GetInvCostDonorTotalsQry.DonorID, GetInvCostDonorTotalsQry.TotalAmt, GetInvCostDonorTotalsQry.TotalVials;

Sub Queries for InventoryCrosstabQry
GetAvailVialQry

GetAvailVialQry
SELECT DonorVial.*
FROM DonorVial
WHERE (((DonorVial.ClientID) Is Null));

Sub Queries for GetInvCostDonorInvCostQry
GetInvCostDonorAvailVialTotalsQry
GetInvCostDonorTotalsQry

GetInvCostDonorAvailVialTotalsQry
SELECT DonorVial.DonorID, Sum(VialSize.Vialgm) AS AvailAmt, Count(DonorVial.VialNo) AS AvailVials, VialSize.Vialgm
FROM VialSize INNER JOIN DonorVial ON VialSize.VialSizeID = DonorVial.VialSizeID
WHERE (((DonorVial.ClientID) Is Null))
GROUP BY DonorVial.DonorID, VialSize.Vialgm;

GetInvCostDonorTotalsQry
SELECT Donor.DonorID, Sum(IIf(IsNull([Vialgm]),0,[Vialgm])) AS TotalAmt, Count(DonorVial.VialNo) AS TotalVials
FROM VialSize RIGHT JOIN (Donor LEFT JOIN DonorVial ON Donor.DonorID=DonorVial.DonorID) ON VialSize.VialSizeID=DonorVial.VialSizeID
GROUP BY Donor.DonorID;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of lof
lof
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dstjohnjr

ASKER

Ok, by "column not working properly", I mean that the value being output is less than what it should be... it is just not correct...  at least that's the basic premise and a start... I believe that the sql query code pertaining to the cost generation needs to be entirely rewritten.  I think it really needs to be simpler.  I am going to take a look at the database and see if I can start piecing this together in order to get this query re-written.
Ok, I might be onto at least a hint of something... I am starting to run these queries from the bottom up and when I run the following query in SQL Serrver Mgmt Studio, I get an error:

SELECT Donor.DonorID, Sum(IIf(IsNull([Vialgm]),0,[Vialgm])) AS TotalAmt, Count(DonorVial.VialNo) AS TotalVials
FROM VialSize RIGHT JOIN (Donor LEFT JOIN DonorVial ON Donor.DonorID=DonorVial.DonorID) ON VialSize.VialSizeID=DonorVial.VialSizeID
GROUP BY Donor.DonorID;


It says that Iif is not a recognized built-in function name.  Is there a more appropriate NULL detection scheme / sql code I can use instead?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok, welll, I just realized since this is actually being run in Access for the report, I'll have to go ahead and stick with the Access specific query... I am just used to being in SQL Mgmt Studio to test with...

Here is the gist of what I need to come up with to produce an effective query to calculate the value needed for my cost column.

$2,153.16 (total costs) / 222 (number of current sellable vials) = $9.70 per vial cost

222 (see above) * $9.70 (see above) = $2,153.40 (this is the actual cost)

I really think the former developer was making this a lot more difficult than it needs to be.



SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the assistance on this!