?
Solved

Access 2003 / SQL 2008 Report Query Help

Posted on 2010-01-12
7
Medium Priority
?
320 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:dstjohnjr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 10

Accepted Solution

by:
lof earned 2000 total points
ID: 26298156
what do you mean by 'column not working properly'

what result you expect and what are you getting?
0
 

Author Comment

by:dstjohnjr
ID: 26306527
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
 

Author Comment

by:dstjohnjr
ID: 26306932
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 10

Assisted Solution

by:lof
lof earned 2000 total points
ID: 26307033
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
 

Author Comment

by:dstjohnjr
ID: 26307230
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
 
LVL 10

Assisted Solution

by:lof
lof earned 2000 total points
ID: 26307467
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
 

Author Closing Comment

by:dstjohnjr
ID: 31676344
Thanks for the assistance on this!
0

Featured Post

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question