Hi Tom,
Not sure if you are mixing up different tables here. In most of the stock systems I have seen you have: Stock header file. [stock id] [quantity] (in stock, allocated, on PO etc) This total could be say 100 in 4 locations, so 4 records in location file.
then you have a stock location file
[Stock id] [location] [quantity] (of that stock id in that location)
So the stock header file contains an instock total and the location file is a detail file which will have a record for each location for each stock item.
HTH
Peter
On 20/04/2017 13:38, Tom Dawson wrote:
Hi All
I have a stock table which records the locations and quantities of various items. The layout of the table is like so:
|stock id| |location| |quantity|
I want to be able to add a total field into the table which is a total of each unique stock id.
To do this I tried to use a sum(quantity) followed by a group by. The problem is grouping by id ignores the distinct location info. For example
stock id
location
quantity
1
A
10
1
B
20
2
C
50
2
D
50
Becomes:
stock id
location
quantity
Total
1
A
10
30
2
C
20
100
Although the totals are right the group by has cause me to lose my distinct location info, what I really want is
stock id
location
quantity
Total
1
A
10
30
1
B
20
30
2
C
50
100
2
D
50
100
I'm sure it's relatively but I'm obvious missing something.
Thanks in advance Tom
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]