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

dstjohnjrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lofCommented:
what do you mean by 'column not working properly'

what result you expect and what are you getting?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dstjohnjrAuthor Commented:
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.
0
dstjohnjrAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lofCommented:
the reason being that this query is MS Access specific. look at

Sum(IIf(IsNull([Vialgm]),0,[Vialgm]))

IIF() function is VB way of doing 'in-line' conditions. IsNull() here is VB again.

in SQL you would have something like:

sum(case when Vialgm is null then 0 else Vialgm end)

which in Transact-SQL you may simplify to

sum(Vialgm)
0
dstjohnjrAuthor Commented:
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.



0
lofCommented:
you are right it looks rather (over)complicated. if you like using SQL Management Studio you and the data is stored in MS SQL server anyway why won't you create a view on the server that will produce the report data and then link to it from MS Access.

This way access does the presentation part and data manipulation is on the database server.
0
dstjohnjrAuthor Commented:
Thanks for the assistance on this!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.