Finding a total from multiple lines

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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


Andy Hull said:
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


Hendricks97 said:
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
Click to expand...
Click to expand...
 
space between a. & [On Hand]

HtH

Pieter

Hendricks97 said:
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


Andy Hull said:
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


Hendricks97 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Hendricks97 said:
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


Andy Hull said:
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


Hendricks97 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top