E
EMILYTAN via AccessMonster.com
May I know the way to calculate among different table and finally get the
total amount?
total amount?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Calculate among different table to get the final total
E
EMILYTAN via AccessMonster.com
Mai I know the way to caluclate among different table and finally get the
total amount and group it into different category..
For example, Grand Total ItemA , Item B...
total amount and group it into different category..
For example, Grand Total ItemA , Item B...
E
Evi
One way is to use DLookup/DSum/DMax or one of that family, depending on what
you want to do. Say you wanted to add up a field in a number of tables
eg
=DSum("[The field you want to add/lookup]","[TblNumber1]") + DSum("[Field in
the other table]", "[TblNumber2]")
You can also filter the D--functions (eg only add up the fields where the
Year field says 2007).
Give more details if that does not meet your needs.
Evi
you want to do. Say you wanted to add up a field in a number of tables
eg
=DSum("[The field you want to add/lookup]","[TblNumber1]") + DSum("[Field in
the other table]", "[TblNumber2]")
You can also filter the D--functions (eg only add up the fields where the
Year field says 2007).
Give more details if that does not meet your needs.
Evi
E
EMILYTAN via AccessMonster.com
Hi Evi,
Your solution almost near to what I want.Thank you.
I will further elaborate my example ok
For example:-
Table 1 - Item A, QuantityReceived - 10
ItemB, QuantityReceived - 20
Table 2- Item A, QuantityIn - 10
Item B, QuantityIn - 30
Table 3 - Item A, QuantityOut-10
- Item B, QuantityOut- 10
So, I want it to sum up according to their Item (sum group by the item)
For example for ItemA= QuantityReceived + QuantityIn-QuantityOut
=10
And same goes for Item B..
Is it clearer for you?
Your solution almost near to what I want.Thank you.
I will further elaborate my example ok
For example:-
Table 1 - Item A, QuantityReceived - 10
ItemB, QuantityReceived - 20
Table 2- Item A, QuantityIn - 10
Item B, QuantityIn - 30
Table 3 - Item A, QuantityOut-10
- Item B, QuantityOut- 10
So, I want it to sum up according to their Item (sum group by the item)
For example for ItemA= QuantityReceived + QuantityIn-QuantityOut
=10
And same goes for Item B..
Is it clearer for you?
Evi said:One way is to use DLookup/DSum/DMax or one of that family, depending on what
you want to do. Say you wanted to add up a field in a number of tables
eg
=DSum("[The field you want to add/lookup]","[TblNumber1]") + DSum("[Field in
the other table]", "[TblNumber2]")
You can also filter the D--functions (eg only add up the fields where the
Year field says 2007).
Give more details if that does not meet your needs.
Evi
May I know the way to calculate among different table and finally get the
total amount?
E
Evi
Hi Evi,Evi said:One way is to use DLookup/DSum/DMax or one of that family, depending on what
you want to do. Say you wanted to add up a field in a number of tables
eg
=DSum("[The field you want to add/lookup]","[TblNumber1]") + DSum("[Field in
the other table]", "[TblNumber2]")
You can also filter the D--functions (eg only add up the fields where the
Year field says 2007).
Give more details if that does not meet your needs.
Evi
Your solution almost near to what I want.Thank you.
I will further elaborate my example ok
For example:-
Table 1 - Item A, QuantityReceived - 10
ItemB, QuantityReceived - 20
Table 2- Item A, QuantityIn - 10
Item B, QuantityIn - 30
Table 3 - Item A, QuantityOut-10
- Item B, QuantityOut- 10
So, I want it to sum up according to their Item (sum group by the item)
For example for ItemA= QuantityReceived + QuantityIn-QuantityOut
=10
And same goes for Item B..
Is it clearer for you?
Itt looks as if this might be tackled more efficiently with a query.
I'm hoping that you have a table which contains your Items.
eg
ItemId (or whatever your Primary Key field is called)
Item
ItemDescription
etc
and that your other tables have eg
A Primary Key Field
ItemID
Quantity Received
A Primary Key Field
ItemID
Quantity In
If you don't have this, then you may have to consider restructuring your
database.
But lets say you have...
Create a query for each of your other tables.
which sums Quantity Received, Quantity In, Quantity Out
(please say if you're not sure how to do this)
Create another query. Add the Items table and the other 3 queries summing up
the items, joining the 3 queries to the Items table by ItemID using Join 2
(show all the fields in TblItem) for each join.
Add eg ItemID, Item Name from the Items table then, from the other 3
queries, SumOfQuantity Received, SumOfQuantity In, SumOfQuantity Out
fields to
your Query.
In another column you can now have a formula like
ItemResult: NZ(SumOfQuantity Received) + NZ(SumOfQuantity In) -
NZ(SumOfQuantity Out)
Now, if you want your results to appear in a text box in a form you could
put in a text box into which you could type
=DLookup("[ItemResult]","Your query", "[ItemID]=3")
(if you want to look up the Result for ItemID 3)
Does that make any sense?
Evi
E
EMILYTAN via AccessMonster.com
Hi Evi,
Yup you are right but I want the result to be group. It can sum up already
but as it is not group in different ItemID all the results are same... May I
know the way of groupin into different ItemID?
For eg Total Quantity For ItemA is 7 while ToTAL Quantity for ItemB is 10...
And not just a round number 17...
Is it ok?
Thank for helping me a lot ya...
Yup you are right but I want the result to be group. It can sum up already
but as it is not group in different ItemID all the results are same... May I
know the way of groupin into different ItemID?
For eg Total Quantity For ItemA is 7 while ToTAL Quantity for ItemB is 10...
And not just a round number 17...
Is it ok?
Thank for helping me a lot ya...
May I know the way to calculate among different table and finally get the
total amount?
One way is to use DLookup/DSum/DMax or one of that family, depending on what
you want to do. Say you wanted to add up a field in a number of tables [quoted text clipped - 5 lines]
Give more details if that does not meet your needs.
Evi[quoted text clipped - 12 lines]Hi Evi,
Your solution almost near to what I want.Thank you.And same goes for Item B..
Is it clearer for you?
Itt looks as if this might be tackled more efficiently with a query.
I'm hoping that you have a table which contains your Items.
eg
ItemId (or whatever your Primary Key field is called)
Item
ItemDescription
etc
and that your other tables have eg
A Primary Key Field
ItemID
Quantity Received
A Primary Key Field
ItemID
Quantity In
If you don't have this, then you may have to consider restructuring your
database.
But lets say you have...
Create a query for each of your other tables.
which sums Quantity Received, Quantity In, Quantity Out
(please say if you're not sure how to do this)
Create another query. Add the Items table and the other 3 queries summing up
the items, joining the 3 queries to the Items table by ItemID using Join 2
(show all the fields in TblItem) for each join.
Add eg ItemID, Item Name from the Items table then, from the other 3
queries, SumOfQuantity Received, SumOfQuantity In, SumOfQuantity Out
fields to
your Query.
In another column you can now have a formula like
ItemResult: NZ(SumOfQuantity Received) + NZ(SumOfQuantity In) -
NZ(SumOfQuantity Out)
Now, if you want your results to appear in a text box in a form you could
put in a text box into which you could type
=DLookup("[ItemResult]","Your query", "[ItemID]=3")
(if you want to look up the Result for ItemID 3)
Does that make any sense?
Evi
E
Evi
EMILYTAN via AccessMonster.com said:[quoted text clipped - 5 lines]Evi said:One way is to use DLookup/DSum/DMax or one of that family, depending on what
you want to do. Say you wanted to add up a field in a number of tablesGive more details if that does not meet your needs.
Evi[quoted text clipped - 12 lines]Hi Evi,
Your solution almost near to what I want.Thank you.And same goes for Item B..
Is it clearer for you?
Itt looks as if this might be tackled more efficiently with a query.
I'm hoping that you have a table which contains your Items.
eg
ItemId (or whatever your Primary Key field is called)
Item
ItemDescription
etc
and that your other tables have eg
A Primary Key Field
ItemID
Quantity Received
A Primary Key Field
ItemID
Quantity In
If you don't have this, then you may have to consider restructuring your
database.
But lets say you have...
Create a query for each of your other tables.
which sums Quantity Received, Quantity In, Quantity Out
(please say if you're not sure how to do this)
Create another query. Add the Items table and the other 3 queries summing up
the items, joining the 3 queries to the Items table by ItemID using Join 2
(show all the fields in TblItem) for each join.
Add eg ItemID, Item Name from the Items table then, from the other 3
queries, SumOfQuantity Received, SumOfQuantity In, SumOfQuantity Out
fields to
your Query.
In another column you can now have a formula like
ItemResult: NZ(SumOfQuantity Received) + NZ(SumOfQuantity In) -
NZ(SumOfQuantity Out)
Now, if you want your results to appear in a text box in a form you could
put in a text box into which you could type
=DLookup("[ItemResult]","Your query", "[ItemID]=3")
(if you want to look up the Result for ItemID 3)
Does that make any sense?
Evi
Hi Evi,
Yup you are right but I want the result to be group. It can sum up already
but as it is not group in different ItemID all the results are same... May I
know the way of groupin into different ItemID?
For eg Total Quantity For ItemA is 7 while ToTAL Quantity for ItemB is 10...
And not just a round number 17...
Is it ok?
Thank for helping me a lot ya...
The query I described would group each item giving you the Total Quantity
for itemA and the Total Quantity for itemB on seperate rows.
The query would give a result like this (with the first word as the column
heading )
Item - Apples
SumOfQuantity Received - 10
SumOfQuantity In - 2
SumOfQuantity Out - 4
TotalQuantity - 8
Item - Bananas
SumOfQuantity Received - 8
SumOfQuantity In - 2
SumOfQuantity Out - 6
TotalQuantity - 4
If you wish, I can send you a database so that you can see it working.
Evi
E
EMILYTAN via AccessMonster.com
Yaya...I would like to have a look at your database..
Thank you..
Thank you..
[quoted text clipped - 61 lines]Is it ok?
Thank for helping me a lot ya...
The query I described would group each item giving you the Total Quantity
for itemA and the Total Quantity for itemB on seperate rows.
The query would give a result like this (with the first word as the column
heading )
Item - Apples
SumOfQuantity Received - 10
SumOfQuantity In - 2
SumOfQuantity Out - 4
TotalQuantity - 8
Item - Bananas
SumOfQuantity Received - 8
SumOfQuantity In - 2
SumOfQuantity Out - 6
TotalQuantity - 4
If you wish, I can send you a database so that you can see it working.
Evi
E
EMILYTAN via AccessMonster.com
Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I really need your help in this....
Thank you
I really need your help in this....
Thank you
May I know the way to calculate among different table and finally get the
total amount?
One way is to use DLookup/DSum/DMax or one of that family, depending on what
you want to do. Say you wanted to add up a field in a number of tables [quoted text clipped - 5 lines]
Give more details if that does not meet your needs.
Evi[quoted text clipped - 12 lines]Hi Evi,
Your solution almost near to what I want.Thank you.And same goes for Item B..
Is it clearer for you?
Itt looks as if this might be tackled more efficiently with a query.
I'm hoping that you have a table which contains your Items.
eg
ItemId (or whatever your Primary Key field is called)
Item
ItemDescription
etc
and that your other tables have eg
A Primary Key Field
ItemID
Quantity Received
A Primary Key Field
ItemID
Quantity In
If you don't have this, then you may have to consider restructuring your
database.
But lets say you have...
Create a query for each of your other tables.
which sums Quantity Received, Quantity In, Quantity Out
(please say if you're not sure how to do this)
Create another query. Add the Items table and the other 3 queries summing up
the items, joining the 3 queries to the Items table by ItemID using Join 2
(show all the fields in TblItem) for each join.
Add eg ItemID, Item Name from the Items table then, from the other 3
queries, SumOfQuantity Received, SumOfQuantity In, SumOfQuantity Out
fields to
your Query.
In another column you can now have a formula like
ItemResult: NZ(SumOfQuantity Received) + NZ(SumOfQuantity In) -
NZ(SumOfQuantity Out)
Now, if you want your results to appear in a text box in a form you could
put in a text box into which you could type
=DLookup("[ItemResult]","Your query", "[ItemID]=3")
(if you want to look up the Result for ItemID 3)
Does that make any sense?
Evi
E
EMILYTAN via AccessMonster.com
Thanks can work already after going through your code...
Thank you Evi.
Thank you Evi.
EMILYTAN said:Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I really need your help in this....
Thank you
[quoted text clipped - 55 lines]Does that make any sense?
Evi
E
EMILYTAN via AccessMonster.com
Hi EVI,
I can work, however, when I add in another Loan table and and add one more
Loan ID field in table 2, it totaly can't work...any idea
Loan table
LoanID(PK)
LoanDesc
Table 2
Bil
LoanID
ItemID
Quantity Received
I can work, however, when I add in another Loan table and and add one more
Loan ID field in table 2, it totaly can't work...any idea
Loan table
LoanID(PK)
LoanDesc
Table 2
Bil
LoanID
ItemID
Quantity Received
Thanks can work already after going through your code...
Thank you Evi.[quoted text clipped - 5 lines]Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I really need your help in this....
E
Evi
Perhaps you can you do this by taking your previous query (the one that
contained the 3 tables), click on it in the database window and go to Insert
Query so that you have a new query based on your 3 table query. Can you add
Table 2 and the Loan Table to that new query? ItemID
Remember that the LoadID which you need to add to the query grid is the one
in Table 2, not the one in the Loan Table.
Evi
contained the 3 tables), click on it in the database window and go to Insert
Query so that you have a new query based on your 3 table query. Can you add
Table 2 and the Loan Table to that new query? ItemID
Remember that the LoadID which you need to add to the query grid is the one
in Table 2, not the one in the Loan Table.
Evi
EMILYTAN via AccessMonster.com said:Hi EVI,
I can work, however, when I add in another Loan table and and add one more
Loan ID field in table 2, it totaly can't work...any idea
Loan table
LoanID(PK)
LoanDesc
Table 2
Bil
LoanID
ItemID
Quantity Received
Thanks can work already after going through your code...
Thank you Evi.[quoted text clipped - 5 lines]Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I really need your help in this....Does that make any sense?
Evi
E
Evi
I've just tried it out and I can see the problem.
If you have one of those Join 2's (Left Joins) in a query then you can't
always have a normal Join1 (Inner Join) as well, so any other tables that
need to be joined together need to be joined first in another query.
You problem is that while each item only appears once in each of the other
tables, does it appear more than once in the Table2? ie do you have Apples
with LoanID 1 and Apples with LoanID 2? If you have, then you have to decide
if you want to know the quantity of Apples or do you need to have the Total
Quantity of Apples for LoanID1 and the Total Quantity of Apples for
LoanID2?
If you just want the total number of Apples, Pears and Bananas that have
been Received in the Table2, then just base a query on Table2 making it a
Calucated query summing Quantity Received (you won't be able to add the Loan
Table Loan Description to query field).
You can add this query to the query which you already have, containing all
the results from the other queries and again make sure that it has a Join 2
(Left Join) with the Items table.
Evi
If you have one of those Join 2's (Left Joins) in a query then you can't
always have a normal Join1 (Inner Join) as well, so any other tables that
need to be joined together need to be joined first in another query.
You problem is that while each item only appears once in each of the other
tables, does it appear more than once in the Table2? ie do you have Apples
with LoanID 1 and Apples with LoanID 2? If you have, then you have to decide
if you want to know the quantity of Apples or do you need to have the Total
Quantity of Apples for LoanID1 and the Total Quantity of Apples for
LoanID2?
If you just want the total number of Apples, Pears and Bananas that have
been Received in the Table2, then just base a query on Table2 making it a
Calucated query summing Quantity Received (you won't be able to add the Loan
Table Loan Description to query field).
You can add this query to the query which you already have, containing all
the results from the other queries and again make sure that it has a Join 2
(Left Join) with the Items table.
Evi
EMILYTAN via AccessMonster.com said:Hi EVI,
I can work, however, when I add in another Loan table and and add one more
Loan ID field in table 2, it totaly can't work...any idea
Loan table
LoanID(PK)
LoanDesc
Table 2
Bil
LoanID
ItemID
Quantity Received
Thanks can work already after going through your code...
Thank you Evi.[quoted text clipped - 5 lines]Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I really need your help in this....Does that make any sense?
Evi
E
Evi
Forgot to say - if you do want to have the Loan Description field showing
with Items being entered more than once if they appear more than once in
Table 2, then
have a calculated query with all of Table 2 and Loan Description from the
Loan Table (call it eg QryLoanAndItems).
Add this query to a new query along with the query you produced when joining
all the 3 tables together (we'll call it QryResult). Add all the fields from
QryResult and Sum of Quantity Received and Loan Description from QryLoan
and Items.
Join the 2 queries with a join that shows all the items from QryResult.
Phew!
Evi
with Items being entered more than once if they appear more than once in
Table 2, then
have a calculated query with all of Table 2 and Loan Description from the
Loan Table (call it eg QryLoanAndItems).
Add this query to a new query along with the query you produced when joining
all the 3 tables together (we'll call it QryResult). Add all the fields from
QryResult and Sum of Quantity Received and Loan Description from QryLoan
and Items.
Join the 2 queries with a join that shows all the items from QryResult.
Phew!
Evi
Evi said:I've just tried it out and I can see the problem.
If you have one of those Join 2's (Left Joins) in a query then you can't
always have a normal Join1 (Inner Join) as well, so any other tables that
need to be joined together need to be joined first in another query.
You problem is that while each item only appears once in each of the other
tables, does it appear more than once in the Table2? ie do you have Apples
with LoanID 1 and Apples with LoanID 2? If you have, then you have to decide
if you want to know the quantity of Apples or do you need to have the Total
Quantity of Apples for LoanID1 and the Total Quantity of Apples for
LoanID2?
If you just want the total number of Apples, Pears and Bananas that have
been Received in the Table2, then just base a query on Table2 making it a
Calucated query summing Quantity Received (you won't be able to add the Loan
Table Loan Description to query field).
You can add this query to the query which you already have, containing all
the results from the other queries and again make sure that it has a Join 2
(Left Join) with the Items table.
Evi
EMILYTAN via AccessMonster.com said:Hi EVI,
I can work, however, when I add in another Loan table and and add one more
Loan ID field in table 2, it totaly can't work...any idea
Loan table
LoanID(PK)
LoanDesc
Table 2
Bil
LoanID
ItemID
Quantity Received
Thanks can work already after going through your code...
Thank you Evi.
Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I really need your help in this....
[quoted text clipped - 5 lines]
Does that make any sense?
Evi
E
EMILYTAN via AccessMonster.com
Hey! Great to hear your reply...
Sorry for posting it twice as it show me error when I post...
I am totally lost again....
Can I give your the table structure and you guide me along?
1.LoanTable
LoanID( primary key)
LoanDate
2.LoanDetails
L_Bil (primary key--->just ignore this if you do not use this)
LoanID (foreign key of LOAN TABLE)
ItemID (foreign key of ITEM TABLE)
FinalQty
3.Miscellaneous Table
MiscID (primary key---> just ignore this if you do not use this)
ItemID (foreign key of ITEM TABLE)
M_InQty
M_OutQty
4.Item Table
ItemID (primary key)
Location
Store Quantity
TotalQuantity--------this is the one I hope to calculate
5.ItemTransaction Table
Bil (primary key--->just ignore this if you do not use this)
ItemID(foreign key of ITEM TABLE)
I_InQty
Description of what I want to figure:-
1. To find out the total quantity of each ItemID (eg APPLES 10, ORANGE 20)
2. I previously tried your method and I found that I need to insert all the
Item into each table in order
for them to display in the query. For example if I want add items in loan
details table, I need to add
that item to all the other table too although I am not performing other
transactions or else it wont
show me the results...
3. Formula for total quantity (ITEM TABLE)= StoreQuantity(ITEM TABLE) +
I_InQty (ITEM_TRANSACTION_TABLE)+FinalQty(LOANDETAILS)+M_InQty(MISC TABLE) -
M_OutQty(MISC TABLE)
4. I dont mind how it works as long as I can get the total Quantity to be
displayed
Well, thanks a lot for your work...as I have been figuring it for a long time
and no 1 able to help me..thanks...
Sorry for posting it twice as it show me error when I post...
I am totally lost again....
Can I give your the table structure and you guide me along?
1.LoanTable
LoanID( primary key)
LoanDate
2.LoanDetails
L_Bil (primary key--->just ignore this if you do not use this)
LoanID (foreign key of LOAN TABLE)
ItemID (foreign key of ITEM TABLE)
FinalQty
3.Miscellaneous Table
MiscID (primary key---> just ignore this if you do not use this)
ItemID (foreign key of ITEM TABLE)
M_InQty
M_OutQty
4.Item Table
ItemID (primary key)
Location
Store Quantity
TotalQuantity--------this is the one I hope to calculate
5.ItemTransaction Table
Bil (primary key--->just ignore this if you do not use this)
ItemID(foreign key of ITEM TABLE)
I_InQty
Description of what I want to figure:-
1. To find out the total quantity of each ItemID (eg APPLES 10, ORANGE 20)
2. I previously tried your method and I found that I need to insert all the
Item into each table in order
for them to display in the query. For example if I want add items in loan
details table, I need to add
that item to all the other table too although I am not performing other
transactions or else it wont
show me the results...
3. Formula for total quantity (ITEM TABLE)= StoreQuantity(ITEM TABLE) +
I_InQty (ITEM_TRANSACTION_TABLE)+FinalQty(LOANDETAILS)+M_InQty(MISC TABLE) -
M_OutQty(MISC TABLE)
4. I dont mind how it works as long as I can get the total Quantity to be
displayed
Well, thanks a lot for your work...as I have been figuring it for a long time
and no 1 able to help me..thanks...
Forgot to say - if you do want to have the Loan Description field showing
with Items being entered more than once if they appear more than once in
Table 2, then
have a calculated query with all of Table 2 and Loan Description from the
Loan Table (call it eg QryLoanAndItems).
Add this query to a new query along with the query you produced when joining
all the 3 tables together (we'll call it QryResult). Add all the fields from
QryResult and Sum of Quantity Received and Loan Description from QryLoan
and Items.
Join the 2 queries with a join that shows all the items from QryResult.
Phew!
Evi
[quoted text clipped - 43 lines]I've just tried it out and I can see the problem.
If you have one of those Join 2's (Left Joins) in a query then you can't
E
EMILYTAN via AccessMonster.com
Hey! Thanks for your help..
I can get it done already...
I can get it done already...
Hey! Great to hear your reply...
Sorry for posting it twice as it show me error when I post...
I am totally lost again....
Can I give your the table structure and you guide me along?
1.LoanTable
LoanID( primary key)
LoanDate
2.LoanDetails
L_Bil (primary key--->just ignore this if you do not use this)
LoanID (foreign key of LOAN TABLE)
ItemID (foreign key of ITEM TABLE)
FinalQty
3.Miscellaneous Table
MiscID (primary key---> just ignore this if you do not use this)
ItemID (foreign key of ITEM TABLE)
M_InQty
M_OutQty
4.Item Table
ItemID (primary key)
Location
Store Quantity
TotalQuantity--------this is the one I hope to calculate
5.ItemTransaction Table
Bil (primary key--->just ignore this if you do not use this)
ItemID(foreign key of ITEM TABLE)
I_InQty
Description of what I want to figure:-
1. To find out the total quantity of each ItemID (eg APPLES 10, ORANGE 20)
2. I previously tried your method and I found that I need to insert all the
Item into each table in order
for them to display in the query. For example if I want add items in loan
details table, I need to add
that item to all the other table too although I am not performing other
transactions or else it wont
show me the results...
3. Formula for total quantity (ITEM TABLE)= StoreQuantity(ITEM TABLE) +
I_InQty (ITEM_TRANSACTION_TABLE)+FinalQty(LOANDETAILS)+M_InQty(MISC TABLE) -
M_OutQty(MISC TABLE)
4. I dont mind how it works as long as I can get the total Quantity to be
displayed
Well, thanks a lot for your work...as I have been figuring it for a long time
and no 1 able to help me..thanks...
[quoted text clipped - 14 lines]Forgot to say - if you do want to have the Loan Description field showing
with Items being entered more than once if they appear more than once in