dstjohnjr
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!
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Do norID) ON VialSize.VialSizeID=DonorV ial.VialSi zeID
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?
SELECT Donor.DonorID, Sum(IIf(IsNull([Vialgm]),0
FROM VialSize RIGHT JOIN (Donor LEFT JOIN DonorVial ON Donor.DonorID=DonorVial.Do
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the assistance on this!
ASKER