CODEWITHSUNDEEP
ms-accessms-access-2007
user597987
user597987

Reputation:

write an Access query have more than 1 SELECT statement

I have an Access database with more than 1 table.

I need to select more than value from more than one table and insert them in another table.

Example:

Table: CashFlow with Fields(ID, Date, BooksBuyPrice, AuthorIncomes, 
          EmployeesIncomes, OpeningAmount, ClosingAmount, DailyResult)

Table: Books with Fields(ID, Name, AuthorID, BuyPrice, SellPrice, IsSold, SellDate)

Table: Author with Fields(ID, Name, DailyIncome)

Table: Employee with Fields(ID, Name, DailyIncome)

Note: User Must Insert OpeningAmount And ClosingAmount

I need to:

1- Select Sum(DailyIncome) For All Authors and set its value to AuthorIncomes in CashFlow Table

2- Select Sum(DailyIncome) For All Employees and set its value to EmployeesIncomes in CashFlow Table

3- Select Sum(BuyPrice) for All Book that sold at selected date and set its value to BooksBuyPrice in CashFlow Table

4- Set Value of User OpeningAmount to OpeningAmount in CashFlow Table

5- Set Value of User ClosingAmount to ClosingAmount in CashFlow Table

6- DailyResult Field must be equal to ((ClosingAmount - OpeningAmount - AuthorIncomes - EmployeesIncomes - BooksBuyPrice)

Then I must insert a new record in the CashFlow table with the above values.

How can I do that?

Note: My application is a C# .NET application under Visual Studio 2010 connected to an Access 2007 database.

Upvotes: 1

Views: 420

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

Okay, let's cover a couple of the basic tasks and then you can flesh out the code for your specific requirements.

We'll start by opening a connection to the database:

var con = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=C:\__tmp\accounting.accdb;");
con.Open();

Now, we'll calculate the SUM(DailyIncome) for all Authors, and store that in a variable

var cmd = new System.Data.OleDb.OleDbCommand("SELECT SUM(DailyIncome) FROM Author", con);
decimal SumOfDailyIncomeAuthors = (decimal)cmd.ExecuteScalar();

You can repeat that for Sum(DailyIncome) for all Employees.

To calculate the SUM(BuyPrice) for Books is essentially the same, but you have to specify the SellDate:

cmd = new System.Data.OleDb.OleDbCommand("SELECT SUM(BuyPrice) FROM Books WHERE SellDate = ?", con);
cmd.Parameters.AddWithValue("?", new DateTime(2013, 4, 18));
decimal SumOfBuyPrice = (decimal)cmd.ExecuteScalar();

You've already got your OpeningAmount and ClosingAmount in text boxes on your form, so you can do the rest of your calculations and INSERT a new row in the [CashFlow] table, something like this:

cmd = new System.Data.OleDb.OleDbCommand("INSERT INTO CashFlow (AuthorIncomes, BooksBuyPrice, OpeningAmount, ClosingAmount) VALUES (?, ?, ?, ?)", con);
cmd.Parameters.AddWithValue("?", SumOfDailyIncomeAuthors);
cmd.Parameters.AddWithValue("?", SumOfBuyPrice);
cmd.Parameters.AddWithValue("?", txtOpeningAmount.Text);
cmd.Parameters.AddWithValue("?", txtClosingAmount.Text);
cmd.ExecuteNonQuery();

Note that for ACE.OLEDB when adding OleDbCommand.Parameters you must specify them in the order that they appear in the CommandText. (Parameter names are ignored, which it why I didn't use them here, although for queries with a large number of parameters the names can still sometimes help keep things straight for us humans.)

Upvotes: 1

Related Questions