Lost trying to set up a sub query

[thacravedawg]

I have a small app im working on that allows users to input data into an access database. This data represents incoming and outgoing items. Currently, the user can input all incoming and outgoing transactions, but I haven't been able to set them up to cancel each other.

What I would like to do is run a query that looks something like
Select top "variable" from tablename where type ="variable2"

then I would like to run another query on this query which looks like
select name, name SUM(name) As sumofname from "previous query" Group by name

I can write and get either of these queries to run, but I can't figure out how to use the dataset the first one provided to base the second one.

Any help would be appreciated

[LizR]

Why run it as 2 queries, why not just run it as one?

[nelis]

Uhh... You want to put a sub query into a dataset after the dataset has ran a query? Why not just put the query1 with query2?

Something like:

select name, name SUM(name) As sumofname
from "previous query"
IN (select top "variable" from tablename where type ="variable2")
Group by name

[thacravedawg]

Why run it as 2 queries, why not just run it as one?

The main reason I plan to use two queries is because when I use the SELECT TOP statement & SUM these results, I get the sum of all results fitting the query as opposed to the sum of only the number of results specified in the TOP statement.

In reality, what I would like to do is a bit more complex then my original post described. I would actually like to run a query like:

SELECT TOP "variable" field1, field2, field3, field4
FROM table1
WHERE field3="variable2" AND field4="P";

Then, I would like to edit these results and change field4 to S with something like this, but I don't know how to specify that the results I want to be edited are derived by the first query:

INSERT INTO ??table1 or last query name??(field4) VALUES('S');

Then, I need to sum one of the fields and make this value available as a variable to be used for other functions in my app.

I would like to have all of this take place in a button click event. It looks like from the post above that using the IN statement I can specify additional criteria to my query, but I still can't seem to get the SUM to work correctly (should it?), nor can I figure out how to then edit only the results returned by my query. Again any assistance is greatly appreciated.

[thacravedawg]

I'm really banging my head against the wall on this one. The big leap for me is trying to figure out how to put the first query results into a form where they can be used by later queries.

I have tried creating a new dataset with the original query results, but can't figure out how to access this dataset with further queries.

Someone please help me understand how I can make the results of a query available to be used for further query action.

[thacravedawg]

Alright so I got all this figured out I think. Here is what I did. Hopefully it will save someone some time if they need similar functionality. Also, if you see anything that I could improve on in my design please let me know.

Connected via OleDB method

private void buttonSubmit_Click(object sender, EventArgs e)
        {
            try
            {

//Use OleDbCommand to query database with user input, to specify the exact records you want to edit

                OleDbCommand command = new OleDbCommand("SELECT TOP " + Units + " ID, Date, Type, Cost, Status FROM UnitInput WHERE Type='" + Type + "' AND Status IS NULL", database);
                adapter.SelectCommand = command;
                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

//Make that query result available as an in cache dataset that we can perform calculations on and edit off line
                ds = new DataSet("Current");
                adapter.Fill(ds, "Current");
                DataTable dt = ds.Tables[0];
                dataGridViewCurrent.DataSource = dt;

//Perform whatever calculations available with the .compute and .select commands you would like on your dataset
                string COGS = dt.Compute("Sum(Cost)", "Cost > 0").ToString();
                string rowCount = dt.Compute("Count(ID)", "Cost > 0").ToString();
                textBox1.Text = COGS;
                Count = Convert.ToInt32(rowCount);

//Edit the records as you wish.  This will edit all entries in the column status to S
                for (int i = 0; i < Count; i++)
                {
                    DataRow[] soldRows = ds.Tables[0].Select("Cost > 0");
                    soldRows[i]["Status"] = "S";
                }

//Update our database with current data.
                adapter.Update(ds, "CurrentSale");
            }
            catch(System.Exception err)
            {
                MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }