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]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
