[Avinator]

Hi, im creating a Project in VB Where I have a Table named Coretable and within it 6 Columns, These columns are named; "Appliance", "Quantity", "Watts", "Time(Begin)", "Time(End)", and "Essential".

Basically I want to use SQL to Times the Watts and the quantity together and multiply it by the time and finally find the sum of all those results together.

For example
I have 5 x 50 watt heaters on between 0100 and 0500 Hours
and 6 x12 watt lightbulbs on between 1200 and 1300 Hours


5x50x(0100-0500) + 6x12x(1200-1300)
200 + 720
=920 watts per hour total
I know that i have to use SQL Mathematical functions, i just don't know how to incourperate it into my program through Code.

Also.........
Can you keep in mind that i have VB 2008 and not 2005 and i don't have visual studio, Most other forums ive visited resolve 2005 methods.


Heres a preview of my application

[June7]

Want to use aggregate functions. Something like:

"SELECT Sum(Watts * Quantity * Time) As Total FROM Coretable"

Getting the elapsed time for the calc may be tricky. Saw another posting today about calculating date/time differences. I offered my suggestion there, may have been the VB6 forum. Just search DIC by topic or my username.

Here is URL link with more info.
http://databases.abo...aaggregate1.htm

This post has been edited by June7: 28 April 2009 - 06:41 PM

[Avinator]

June7, on 28 Apr, 2009 - 05:37 PM, said:

Want to use aggregate functions. Something like:

"SELECT Sum(Watts * Quantity * Time) As Total FROM Coretable"

Getting the elapsed time for the calc may be tricky. Saw another posting today about calculating date/time differences. I offered my suggestion there, may have been the VB6 forum. Just search DIC by topic or my username.

Here is URL link with more info.
http://databases.abo...aaggregate1.htm

thx for that but i already know how to do the SQL part, its just the code that goes before it so i can use it in the program...


im looking for something like:

My.database.sql.query(SELECT....FROM...WHERE)
display results in textbox 1

obviously this won't work but you get the picture!! sorry boat that

[T3hC13h]

What database are you using?

[June7]

Need to establish connection to database, such as Access or SQL.

Then can execute SQL statements on the data tables in the DB. For instance in VBA for Access where I live:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
'get data from table
rs.Open "SELECT * FROM FurnaceCalibration WHERE MiscInfo='" & strMixNumber & "' AND ovenID='" & i & "';", cn, adOpenStatic, adLockPessimistic
'save data to table
cn.Execute "UPDATE FurnaceCalibrationReportData SET " & j & "Data" & q & "=" & rs.Fields("Data" & q) & " WHERE Furnace='" & i & "'"

This post has been edited by June7: 29 April 2009 - 10:40 AM