Finding a total from multiple lines

[Guest]

I need to pull records from a table that contains multiple locations and
their quantities in order to fill a certain quantity demand. For example, I
need 25 of an item and I have 5 locations that have 10 each. How do I get the
results to show 10 from location A, 10 from location B, 5 from location C,
and dont show the rest at all? Any help would be greatly appreciated

 

[Guest]

Hi

You may need to make changes to suit your table structures but here is one
way...

Assuming a table called tblLocations with columns Location and Quantity

Create and save the following query (qryRunningTotals)...

SELECT a.Location, a.Quantity, Sum(b.Quantity) AS RunningTotal
FROM tblLocations AS a, tblLocations AS b
WHERE a.loc >= b.loc
GROUP BY a.Quantity, a.Quantity;

Then create this query (qryResults)...

SELECT qryRunningTotals.Location, qryRunningTotals.Quantity
FROM qryRunningTotals
WHERE qryRunningTotals.RunningTotal <= [Target]
UNION
SELECT qryRunningTotals.Location, qryRunningTotals.RunningTotal - [Target]
FROM qryRunningTotals
WHERE qryRunningTotals.RunningTotal > [Target]
AND (qryRunningTotals.RunningTotal - [Target]) < qryRunningTotals.Quantity;


[Target] can be left as a query parameter or chnaged to get the value from a
form.
Also, tblLocations is assumed to contain 1 row per Location.

hth

Andy Hull

 

[Guest]

Slotted2 = source, On Hand = quantity, Locator = Location

Using my table names, I wrote this for the first section:

SELECT a. Locator, a. [On Hand], Sum(b.[On Hand]) AS RunningTotal
FROM Slotted2 AS a, Slotted2 AS b
WHERE a.Locator >= b.Locator
GROUP BY a. [On Hand], a. [On Hand]
but I got an error message:
Invalid use of '.', '!', or '()'. in query expression 'a.Locator'.

What am I doing wrong? Im not seeing any differences between mine and yours

Hi

You may need to make changes to suit your table structures but here is one
way...

Assuming a table called tblLocations with columns Location and Quantity

Create and save the following query (qryRunningTotals)...

SELECT a.Location, a.Quantity, Sum(b.Quantity) AS RunningTotal
FROM tblLocations AS a, tblLocations AS b
WHERE a.loc >= b.loc
GROUP BY a.Quantity, a.Quantity;

Then create this query (qryResults)...

SELECT qryRunningTotals.Location, qryRunningTotals.Quantity
FROM qryRunningTotals
WHERE qryRunningTotals.RunningTotal <= [Target]
UNION
SELECT qryRunningTotals.Location, qryRunningTotals.RunningTotal - [Target]
FROM qryRunningTotals
WHERE qryRunningTotals.RunningTotal > [Target]
AND (qryRunningTotals.RunningTotal - [Target]) < qryRunningTotals.Quantity;


[Target] can be left as a query parameter or chnaged to get the value from a
form.
Also, tblLocations is assumed to contain 1 row per Location.

hth

Andy Hull

I need to pull records from a table that contains multiple locations and
their quantities in order to fill a certain quantity demand. For example, I
need 25 of an item and I have 5 locations that have 10 each. How do I get the
results to show 10 from location A, 10 from location B, 5 from location C,
and dont show the rest at all? Any help would be greatly appreciated

 

[Pieter Wijnen]

space between a. & [On Hand]

HtH

Pieter

Slotted2 = source, On Hand = quantity, Locator = Location

Using my table names, I wrote this for the first section:

SELECT a. Locator, a. [On Hand], Sum(b.[On Hand]) AS RunningTotal
FROM Slotted2 AS a, Slotted2 AS b
WHERE a.Locator >= b.Locator
GROUP BY a. [On Hand], a. [On Hand]
but I got an error message:
Invalid use of '.', '!', or '()'. in query expression 'a.Locator'.

What am I doing wrong? Im not seeing any differences between mine and
yours

Hi

You may need to make changes to suit your table structures but here is
one
way...

Assuming a table called tblLocations with columns Location and Quantity

Create and save the following query (qryRunningTotals)...

SELECT a.Location, a.Quantity, Sum(b.Quantity) AS RunningTotal
FROM tblLocations AS a, tblLocations AS b
WHERE a.loc >= b.loc
GROUP BY a.Quantity, a.Quantity;

Then create this query (qryResults)...

SELECT qryRunningTotals.Location, qryRunningTotals.Quantity
FROM qryRunningTotals
WHERE qryRunningTotals.RunningTotal <= [Target]
UNION
SELECT qryRunningTotals.Location, qryRunningTotals.RunningTotal -
[Target]
FROM qryRunningTotals
WHERE qryRunningTotals.RunningTotal > [Target]
AND (qryRunningTotals.RunningTotal - [Target]) <
qryRunningTotals.Quantity;


[Target] can be left as a query parameter or chnaged to get the value
from a
form.
Also, tblLocations is assumed to contain 1 row per Location.

hth

Andy Hull

I need to pull records from a table that contains multiple locations
and
their quantities in order to fill a certain quantity demand. For
example, I
need 25 of an item and I have 5 locations that have 10 each. How do I
get the
results to show 10 from location A, 10 from location B, 5 from location
C,
and dont show the rest at all? Any help would be greatly appreciated

 

[Guest]

Hi

As Pieter has pointed out, you have a space after a. (in several places).
Remove the spaces so the query is as below.

I have also noticed an error on my part - the group by should group Locator
then [On Hand]. The corrected query is...

SELECT a.Locator, a.[On Hand], Sum(b.[On Hand]) AS RunningTotal
FROM Slotted2 AS a, Slotted2 AS b
WHERE a.Locator >= b.Locator
GROUP BY a.Locator, a.[On Hand]


Regards

Andy Hull

Slotted2 = source, On Hand = quantity, Locator = Location

Using my table names, I wrote this for the first section:

SELECT a. Locator, a. [On Hand], Sum(b.[On Hand]) AS RunningTotal
FROM Slotted2 AS a, Slotted2 AS b
WHERE a.Locator >= b.Locator
GROUP BY a. [On Hand], a. [On Hand]
but I got an error message:
Invalid use of '.', '!', or '()'. in query expression 'a.Locator'.

What am I doing wrong? Im not seeing any differences between mine and yours

Hi

You may need to make changes to suit your table structures but here is one
way...

Assuming a table called tblLocations with columns Location and Quantity

Create and save the following query (qryRunningTotals)...

SELECT a.Location, a.Quantity, Sum(b.Quantity) AS RunningTotal
FROM tblLocations AS a, tblLocations AS b
WHERE a.loc >= b.loc
GROUP BY a.Quantity, a.Quantity;

Then create this query (qryResults)...

SELECT qryRunningTotals.Location, qryRunningTotals.Quantity
FROM qryRunningTotals
WHERE qryRunningTotals.RunningTotal <= [Target]
UNION
SELECT qryRunningTotals.Location, qryRunningTotals.RunningTotal - [Target]
FROM qryRunningTotals
WHERE qryRunningTotals.RunningTotal > [Target]
AND (qryRunningTotals.RunningTotal - [Target]) < qryRunningTotals.Quantity;


[Target] can be left as a query parameter or chnaged to get the value from a
form.
Also, tblLocations is assumed to contain 1 row per Location.

hth

Andy Hull

I need to pull records from a table that contains multiple locations and
their quantities in order to fill a certain quantity demand. For example, I
need 25 of an item and I have 5 locations that have 10 each. How do I get the
results to show 10 from location A, 10 from location B, 5 from location C,
and dont show the rest at all? Any help would be greatly appreciated