how to create a sum query between multiple tables in Microsoft access
-
i have three tables: Table_provider Name_Provider Table_purchase ID_Purchase Date_Purchase Name_Provider Amount Table_payment ID_payment Date_payment Name_Provider Amount I want to get this resutl: Name_Provider Sum(Amount purchase) sum(Amount payement) thanks in advance:
-
i have three tables: Table_provider Name_Provider Table_purchase ID_Purchase Date_Purchase Name_Provider Amount Table_payment ID_payment Date_payment Name_Provider Amount I want to get this resutl: Name_Provider Sum(Amount purchase) sum(Amount payement) thanks in advance:
-
Im developping vb.net application with access database thats why i want this query
-
Im developping vb.net application with access database thats why i want this query
-
i have three tables: Table_provider Name_Provider Table_purchase ID_Purchase Date_Purchase Name_Provider Amount Table_payment ID_payment Date_payment Name_Provider Amount I want to get this resutl: Name_Provider Sum(Amount purchase) sum(Amount payement) thanks in advance:
Identify the primary/foreign keys, make the joins and create the query. If name_provider is your FK then you are screwed as that is an editable field. Get a book on SQL and that will explain the errors in that structure.
Never underestimate the power of human stupidity RAH
-
i have three tables: Table_provider Name_Provider Table_purchase ID_Purchase Date_Purchase Name_Provider Amount Table_payment ID_payment Date_payment Name_Provider Amount I want to get this resutl: Name_Provider Sum(Amount purchase) sum(Amount payement) thanks in advance:
As others have pointed out, that is a bad table schema. If you insist on having separate tables for purchase and payment then it should look like this:
Table_provider
ID_Provider
Name_ProviderTable_purchase
ID_Purchase
Date_Purchase
ID_Provider
AmountTable_payment
ID_payment
Date_payment
ID_Provider
AmountPersonally I would have single transaction table with a transaction type column but each to their own. Whether you follow my advice or not, you will need to JOIN the tables based on the column that is common to all of them
ID_ProviderorName_Providerif you leave things as they are. Here is an article that tells you how to do that Joining Tables in SQL[^] Note we are referring to "SQL" in the sense of T-SQL - the "language" and not SQL Server the database. As you are connecting from VB.NET to Access I presume you are using ADO or OLEDB - both of which will require SQL statements. Once you have worked out how to join your tables you already have the SELECT clause essentially written...SELECT Provider.Name_Provider. Sum(purchase.Amount), sum(Payment.Amount)
Give it a go, but if you still get stuck reply to this message with the code that isn't working and we will try to help.
-
As others have pointed out, that is a bad table schema. If you insist on having separate tables for purchase and payment then it should look like this:
Table_provider
ID_Provider
Name_ProviderTable_purchase
ID_Purchase
Date_Purchase
ID_Provider
AmountTable_payment
ID_payment
Date_payment
ID_Provider
AmountPersonally I would have single transaction table with a transaction type column but each to their own. Whether you follow my advice or not, you will need to JOIN the tables based on the column that is common to all of them
ID_ProviderorName_Providerif you leave things as they are. Here is an article that tells you how to do that Joining Tables in SQL[^] Note we are referring to "SQL" in the sense of T-SQL - the "language" and not SQL Server the database. As you are connecting from VB.NET to Access I presume you are using ADO or OLEDB - both of which will require SQL statements. Once you have worked out how to join your tables you already have the SELECT clause essentially written...SELECT Provider.Name_Provider. Sum(purchase.Amount), sum(Payment.Amount)
Give it a go, but if you still get stuck reply to this message with the code that isn't working and we will try to help.
CHill60 wrote:
Personally I would have single transaction table with a transaction type column but each to their own
This only works for a retail POS system where there is no credit supplied. As an invoicing exercise the link between purchase and payment is never designed as 1-1.
Never underestimate the power of human stupidity RAH
-
CHill60 wrote:
Personally I would have single transaction table with a transaction type column but each to their own
This only works for a retail POS system where there is no credit supplied. As an invoicing exercise the link between purchase and payment is never designed as 1-1.
Never underestimate the power of human stupidity RAH
Quote:
As an invoicing exercise the link between purchase and payment is never designed as 1-1.
Good point, although I wasn't trying to suggest that sort of link between an invoice and a payment, just that in its simplest terms a transaction is money either positive or negative. Similar to Accounting Systems Model[^] Of course the OP doesn't have a column to indicate a credit transaction on the purchase table anyway. Or an Order table, or Invoice, or product .... :laugh:
-
Quote:
As an invoicing exercise the link between purchase and payment is never designed as 1-1.
Good point, although I wasn't trying to suggest that sort of link between an invoice and a payment, just that in its simplest terms a transaction is money either positive or negative. Similar to Accounting Systems Model[^] Of course the OP doesn't have a column to indicate a credit transaction on the purchase table anyway. Or an Order table, or Invoice, or product .... :laugh:
I suspect the OP is either doing a training excercise or, Ghu forbid, writing something for a friend in need, one does not know retail and the other does not know software, fun times.
Never underestimate the power of human stupidity RAH
