cssamerican
03-29-2006, 09:41 AM
I am using Excel as a small database program. I am using the DOA approach to access a worksheet with the data located on it. The data is a collection of text, quantity and cost fields similar to this.
Last_Name...First_Name.....Budget_Code....Amount...Quantity1...Quantity2... Quantity3
Doe.........John...........1110...........$210.00..50..........60.......... 65
Doe.........Jan............1110...........$105.00..25..........35.......... 10
Doe.........John...........1115...........$300.00..40..........30.......... 51
Doe.........John...........1110...........$100.00..20..........40.......... 97
If I choose to pull the data for Budget_Code 1110 I want the output to look like this.
Last_Name...First_Name.....Budget_Code....Amount....Quantity1...Quantity... Quantity3...(Q1+Q2+Q3)*.05
Doe.........John...........1110...........$300.00...70..........100........ 162.........$16.60
Doe.........Jan............1110...........$105.00...25..........35......... 10..........$3.50
Is there a simple SQL statement that will give me this, or will I have to create virtual tables and run multiple Sql statements on those tables? If the later is the case could someone here please point in the right direction on how to go about doing that with VB in Excel.
Last_Name...First_Name.....Budget_Code....Amount...Quantity1...Quantity2... Quantity3
Doe.........John...........1110...........$210.00..50..........60.......... 65
Doe.........Jan............1110...........$105.00..25..........35.......... 10
Doe.........John...........1115...........$300.00..40..........30.......... 51
Doe.........John...........1110...........$100.00..20..........40.......... 97
If I choose to pull the data for Budget_Code 1110 I want the output to look like this.
Last_Name...First_Name.....Budget_Code....Amount....Quantity1...Quantity... Quantity3...(Q1+Q2+Q3)*.05
Doe.........John...........1110...........$300.00...70..........100........ 162.........$16.60
Doe.........Jan............1110...........$105.00...25..........35......... 10..........$3.50
Is there a simple SQL statement that will give me this, or will I have to create virtual tables and run multiple Sql statements on those tables? If the later is the case could someone here please point in the right direction on how to go about doing that with VB in Excel.