Getting Query to show calculation for all records

[Iowa Joe]

I have a query which is used to subtract small quantities ('Releases') from a
large initial quantity ('Hold'). The query works fine as it gives me a
current value still in hold for each HoldID. However, it only shows the
current value for holds that have had at least on release quantity. I would
like my query to show all current values because I am using a DLookup in one
of my forms so that the user can see how many items are currently in hold.

So, my problem is that when a user selects a HoldID (using a combo box) that
hasn't had any releases yet, the current quantity (using the DLookup
Function) is Null. I would like it to show the current quantity as the
initial quantity if no releases have been made yet.

Any help would be great. Thanks

 

[Dale Fye]

Joe,

Where are you from in Iowa?

Can you post the SQL of your query so we can see what tables you are using.
My guess is that it will be as simple as changing a join from an inner join
to an outer join but without your SQL, it is hard to describe that to you.

Dale

 

[Iowa Joe]

I'm from the Fort Dodge area.

Here is the SQL for the query. Thanks for the help

SELECT tblHolds.HoldID, ([InitialQuantity]-Sum([ReleaseQuantity])) AS
CurrentHoldQty
FROM tblHolds INNER JOIN tblReleases ON tblHolds.HoldID = tblReleases.HoldID
GROUP BY tblHolds.HoldID, tblHolds.InitialQuantity;

 

[John Spencer]

Try the following modification. Chaange the Join to show all records in
tblHolds and any matching records in tblReleases (Change INNER to LEFT).
Since that will return Null values for ReleaseQuantity when there are no
related records, use the NZ function to force a Zero in the calculation.

SELECT tblHolds.HoldID
, ([InitialQuantity]-NZ(Sum([ReleaseQuantity]),0)) AS CurrentHoldQty
FROM tblHolds LEFT JOIN tblReleases ON tblHolds.HoldID = tblReleases.HoldID
GROUP BY tblHolds.HoldID, tblHolds.InitialQuantity;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm from the Fort Dodge area.

Here is the SQL for the query. Thanks for the help

SELECT tblHolds.HoldID, ([InitialQuantity]-Sum([ReleaseQuantity])) AS
CurrentHoldQty
FROM tblHolds INNER JOIN tblReleases ON tblHolds.HoldID =
tblReleases.HoldID
GROUP BY tblHolds.HoldID, tblHolds.InitialQuantity;

Joe,

Where are you from in Iowa?

Can you post the SQL of your query so we can see what tables you are
using.
My guess is that it will be as simple as changing a join from an inner
join
to an outer join but without your SQL, it is hard to describe that to
you.

Dale

 

[Iowa Joe]

Hey, thanks a lot guys. It's working great. I'm still a little uneasy about
it because I am using a DLookup in order to get the query values to show up
in the form, and this can be inefficient (or so I've heard). If anyone has
any suggestions on how to get the values into my form without a DLookup, that
would be great. Otherwise, I will see how well the DLookup can perform as my
database gets larger.

Thanks again.

Try the following modification. Chaange the Join to show all records in
tblHolds and any matching records in tblReleases (Change INNER to LEFT).
Since that will return Null values for ReleaseQuantity when there are no
related records, use the NZ function to force a Zero in the calculation.

SELECT tblHolds.HoldID
, ([InitialQuantity]-NZ(Sum([ReleaseQuantity]),0)) AS CurrentHoldQty
FROM tblHolds LEFT JOIN tblReleases ON tblHolds.HoldID = tblReleases.HoldID
GROUP BY tblHolds.HoldID, tblHolds.InitialQuantity;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm from the Fort Dodge area.

Here is the SQL for the query. Thanks for the help

SELECT tblHolds.HoldID, ([InitialQuantity]-Sum([ReleaseQuantity])) AS
CurrentHoldQty
FROM tblHolds INNER JOIN tblReleases ON tblHolds.HoldID =
tblReleases.HoldID
GROUP BY tblHolds.HoldID, tblHolds.InitialQuantity;

Joe,

Where are you from in Iowa?

Can you post the SQL of your query so we can see what tables you are
using.
My guess is that it will be as simple as changing a join from an inner
join
to an outer join but without your SQL, it is hard to describe that to
you.

Dale


--
Email address is not valid.
Please reply to newsgroup only.


:

I have a query which is used to subtract small quantities ('Releases')
from a
large initial quantity ('Hold'). The query works fine as it gives me a
current value still in hold for each HoldID. However, it only shows the
current value for holds that have had at least on release quantity. I
would
like my query to show all current values because I am using a DLookup
in one
of my forms so that the user can see how many items are currently in
hold.

So, my problem is that when a user selects a HoldID (using a combo box)
that
hasn't had any releases yet, the current quantity (using the DLookup
Function) is Null. I would like it to show the current quantity as the
initial quantity if no releases have been made yet.

Any help would be great. Thanks