Board index » delphi » Query against Access Db using ADO via ODBC DSN

Query against Access Db using ADO via ODBC DSN


2005-07-11 06:01:41 AM
delphi179
Hi there,
I'm wondering if someone could assist me with trying to get data from
an Access Db.
I need to firstly, get all data which has the latest or, highest date,
from a table. Then I need to sum the data from an amount field, where
the values in a description field are the same.
I have managed to extract the data based on the latest date, but I
cannot work out how to get the sums of the amount field.
Possible values for the description field are:
Directors Wages, Basic Wage, OverTime,OverTime Double
from the result set based on the date I need to get a total for all
amounts where description = 'Directors Wages' then a total for all
amounts where description = 'Basic Wage' etc
I have tried using CASE statements but that throws a missing operator
error.
Is what I am trying to do possible in a single SQL statement, including
a statement for selecting by latest date. If so I will be grateful for,
if possible, a simple example of how to go about this.
Using Delphi 7 Enterprise.
Thanks for any assistance.
--
Graham A Welsh
Targeted Software Solutions Ltd
To reply change 'limited' to 'ltd' in email address
 
 

Re:Query against Access Db using ADO via ODBC DSN

Latest date:
SELECT TOP 1 * FROM MyTable ORDER BY DateColumn DESC
Sum for one description:
SELECT SUM(Amount) FROM MyTable WHERE Description = 'XXX'
Sum for all descriptions:
SELECT Description, SUM(Amount) FROM MyTable
GROUP BY Description
//------------------------------------------
Regards,
Vassiliev V. V.
www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"Graham A Welsh" <XXXX@XXXXX.COM>
???????/???????? ? ???????? ?????????:
Quote
Hi there,

I'm wondering if someone could assist me with trying to get data from
an Access Db.

I need to firstly, get all data which has the latest or, highest date,
from a table. Then I need to sum the data from an amount field, where
the values in a description field are the same.

I have managed to extract the data based on the latest date, but I
cannot work out how to get the sums of the amount field.

Possible values for the description field are:

Directors Wages, Basic Wage, OverTime,OverTime Double

from the result set based on the date I need to get a total for all
amounts where description = 'Directors Wages' then a total for all
amounts where description = 'Basic Wage' etc

I have tried using CASE statements but that throws a missing operator
error.

Is what I am trying to do possible in a single SQL statement, including
a statement for selecting by latest date. If so I will be grateful for,
if possible, a simple example of how to go about this.

Using Delphi 7 Enterprise.

Thanks for any assistance.

--
Graham A Welsh
Targeted Software Solutions Ltd
To reply change 'limited' to 'ltd' in email address
 

Re:Query against Access Db using ADO via ODBC DSN

Viatcheslav V. Vassiliev writes:
Quote
Sum for all descriptions:

SELECT Description, SUM(Amount) FROM MyTable
GROUP BY Description
Yes indeed, of course it is. I must have been having a bad day.
Thanks for reminding me.
--
Graham A Welsh
Targeted Software Solutions Ltd
To reply change 'limited' to 'ltd' in email address