Solved

Counting with sql in delphi and ms access database

Posted on 2004-10-08
38
303 Views
Last Modified: 2010-04-05
Hi all,
I have a problem on counting with an query-component in delphi and an underlying ms access database.
My database is organized as follows:
Field1= date, Field2= Wagonnumber, Field3= Trainnumber, aso...
Now I want to count the different trains within a certain date.
But as I work with ms access 97, I get always some errors.
I tried the following:
select distinct field1, count(field3) from table group by field1, but that counts the wagonnumbers, because every wagonnumber has a trainnumber.
I hope I've made me clear, because I passed several hours with this problem and I can't manage it. Could so please give me a solution , so that I can count my trains.
0
Comment
Question by:Kloot74
  • 18
  • 14
  • 6
38 Comments
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
select distinct field1, count(field3) from table WHERE field1= 08.10.2004 group by field1, field2  
0
 

Author Comment

by:Kloot74
Comment Utility
This is rather good and a very quick answer, but it always counts 1 as number of trains, although there are 10 and more on this specific date. The condition " where " should also be not a fixed date, but all the dates in a certain period.
0
 

Author Comment

by:Kloot74
Comment Utility
I tried it now a little bit further, and I must say that it is the solution when I have only 3 fields. But in fact I have 4 fields, one more with the direction of the train passing. When I introduce this field in my query ( ...group by field1,field4,field2...), I suddenly get wrong results. How is this ?
0
 
LVL 6

Expert Comment

by:wimmeyvaert
Comment Utility
Let's say that the content of the table is :

Field1                                                 Field2      Field3      
------------------------------------------------------ ----------- -----------
2004-10-01 00:00:00                                    1           1
2004-10-01 00:00:00                                    2           1
2004-10-01 00:00:00                                    3           1
2004-10-01 00:00:00                                    4           1
2004-10-01 00:00:00                                    1           2
2004-10-01 00:00:00                                    2           2
2004-10-01 00:00:00                                    3           2
2004-10-01 00:00:00                                    1           3
2004-10-01 00:00:00                                    1           4
2004-10-02 00:00:00                                    1           1


then this is the query to count the amount of trains on '2004-10-01' :
select Field1, Count(distinct Field3)
from table1
where Field1 = '2004-10-01'
group by Field1

Result :
2004-10-01 00:00:00                                    4


Is this what you are looking for ?
0
 
LVL 6

Expert Comment

by:wimmeyvaert
Comment Utility
Btw,

I tried this out on a SQL Server Database, but it should also work on an MS Access database.

Best regards,

The Mayor.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
May be:
select distinct field1, field2, count(field3), field4 from table WHERE field1=08.10.2004 group by field1, field2, field4
0
 
LVL 6

Expert Comment

by:wimmeyvaert
Comment Utility
I don't see why you should include Field2, Field3 and Field4 in your group by clause.
You said you just want to see the total amount of trains for a certain date.
So you only need to make a group, based on the date-field then.

Btw, if you ommit the where-clause in my query, you get an overview per date (found in the table) and the total amount of trains found (=Count).

So un my example, this will be :
Content of my complete Table
Field1                                                 Field2      Field3      
------------------------------------------------------ ----------- -----------
2004-10-01 00:00:00                                    1           1
2004-10-01 00:00:00                                    2           1
2004-10-01 00:00:00                                    3           1
2004-10-01 00:00:00                                    4           1
2004-10-01 00:00:00                                    1           2
2004-10-01 00:00:00                                    2           2
2004-10-01 00:00:00                                    3           2
2004-10-01 00:00:00                                    1           3
2004-10-01 00:00:00                                    1           4
2004-10-02 00:00:00                                    1           1

My first query:
select Field1, Count(distinct Field3)
from table1
where Field1 = '2004-10-01'
group by Field1

Results in :
2004-10-01 00:00:00                                    4


and a second query:
select Field1, Count(distinct Field3)
from table1
group by Field1

Results in :
2004-10-01 00:00:00                                    4
2004-10-02 00:00:00                                    1

I think this is exactly what you were asking for, isn't ir ?

Best regards,

The Mayor.


PS : is it possible to post the content of your table, and the exact query you developed?
Maybe then we get a better view of what's causing your problem here.
0
 

Author Comment

by:Kloot74
Comment Utility
Not just that; I have a table like this:
Field1: Wagonnumber Field2:Date Field3:Trainnumber Field4:Directionoftrain
________________________________________________________________
23455.......................10/08/2004.....34555...................1
45678.......................10/08/2004.....34555...................1
84556.......................10/08/2004.....34555...................1
34444.......................10/08/2004.....78433...................2
55656.......................10/08/2004.....78433...................2
34444.......................10/07/2004.....34555...................1
and so on, and I want to have the total of Trainnumbers( Field3) on a certain date ( field2) for example on the 10/08/2004 in the direction 1 = 1 train ( Number 34555 ), in the direction 2=1 train ( Number:78433 ).
By the way, count(distinct field ) is not working in access97.
0
 

Author Comment

by:Kloot74
Comment Utility
My query is as follows now:
Select distinct [field2],[field4],count([field3]) from table group by [field2],[field4],[field1].
But it gives wrong results...
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
The next code works fine:

procedure TForm1.btnSelectClick(Sender: TObject);
var
  T:      string;
begin
  T :=''
    + ' SELECT DISTINCT(DATES), TRAINNUMBER, DIRECTION, COUNT(TRAINNUMBER) as COUNT_OF_TRAINS'
    + ' FROM TRAINS WHERE DATES=:PDATES'
    + ' GROUP BY DATES, TRAINNUMBER, DIRECTION';
  ADOQueryDate.Active := False;
  ADOQueryDate.SQL.Text := T;
  ADOQueryDate.Parameters.ParamByName('PDATES').Value := TDateTime(StrToDateTime('08.10.2004'));
  ADOQueryDate.Active := True;
end;
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
But when I try to implement the field WAGONNUMBER it does not work as expected:

procedure TForm1.btnSelectClick(Sender: TObject);
var
  T:      string;
begin
  T :=''
    + ' SELECT DISTINCT(DATES), TRAINNUMBER, WAGONNUMBER, DIRECTION, COUNT(TRAINNUMBER) as COUNT_OF_TRAINS'
    + ' FROM TRAINS WHERE DATES=:PDATES'
    + ' GROUP BY DATES, TRAINNUMBER, WAGONNUMBER, DIRECTION';
  ADOQueryDate.Active := False;
  ADOQueryDate.SQL.Text := T;
  ADOQueryDate.Parameters.ParamByName('PDATES').Value := TDateTime(StrToDateTime('08.10.2004'));
  ADOQueryDate.Active := True;
end;
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
I could provide you an example code ....
0
 
LVL 6

Expert Comment

by:wimmeyvaert
Comment Utility
So, if I understand correctly you want to group the data into groups of Date+Direction.
Then your query should be like this:

select Field2, Field4, Count(distinct Field3) as Total
from table1
where Field2 = '2004-10-08'
group by Field2, Field4

With a resultset like :

Field2                                                 Field4      Total      
------------------------------------------------------ -----------
2004-10-08 00:00:00                              1           1
2004-10-08 00:00:00                              2           1


About you distinct-problem : have you tried to use  'distinct count(Field3)' instead ?
I have to test this out. This could take a while.

Best regards,

The Mayor.
0
 
LVL 6

Expert Comment

by:wimmeyvaert
Comment Utility
About you code-snipet : that could be handy.

I tested out the query on a Ms-Access2002 database and indeed, there is a 'syntax error' in the query on the 'distinct'.
I have to look a bit closer to that problem because you really need a distinct count here, otherwise your result will show Totals that are a count of the Wagons, and not of the trains.

so give me a bit of time to take a closer look at the distinct problem on an Access DB.

Best regards,

The Mayor.
0
 

Author Comment

by:Kloot74
Comment Utility
I have my query like the one of wimmeyvaert, and it does not work. It seems as if the "distinct count(field3)" or "count (distinct field3)" does not work in access97. So it must be another solution ?
To esoftbg I must say that your solution does give me the number og wagons in a train and not the number of trains only, sorry. With the wagonnumber in my query it gives me all the wagons and as total each time the 1.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
when field WAGONNUMBER is in the query then the result is not as expected because every course is traveled by different WAGONNUMBER .... and for date 10/08/2004 is shown separated (for every WAGONNUMBER):

DATES              TRAIN       WAGON   DIRECT   COUNT
08.10.2004........34555........23455........1........1
08.10.2004........34555........45678........1........1
08.10.2004........34555........84556........1........1
08.10.2004........78433........34444........2........1
08.10.2004........78433........55656........2........1

but when WAGONNUMBER is not in the query then the result is:

DATES              TRAIN    DIRECT   COUNT
08.10.2004........34555........1........3
08.10.2004........78433........2........2
0
 

Author Comment

by:Kloot74
Comment Utility
Thats right esoftbg, but I don't want the count=3 Wagons and 2=Wagons, but 1 TRAIN in DIRECT 1 and 1 TRAIN in DIRECT 2 on the 8.10.2004, I want to count the 34555 and the 78433. I hope it is clearer now.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
3 is not the count os WAGONS, that is the count of COURSES travelled by the train 34555 at 08.10.2004
0
 

Author Comment

by:Kloot74
Comment Utility
Thats not how it works or how the table is built of: The train with number 34555 travelled only once on the 8.10.2004; in the table we have three wagons that travelled one time on the 8.10.04 with train Number 34555; so the trainnumber occurs three times but in reality travelled only one time. Hmh, it's difficult.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
It is really difficult, because my opinion was that train Number 34555 is travelled 3 times with different Wagons at 8.10.2004
0
 

Author Comment

by:Kloot74
Comment Utility
No, only one and unique number of train per day. Perhaps, that helps.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
You need to describe the WagonNumbers travelled with a train in a separated Table so to chain many WagonNumbers to only one TrainNumber !!!!
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
The field WAGONNUMBER must to be removed from this table and to be in another table ....
0
 

Author Comment

by:Kloot74
Comment Utility
In access I made a temporary query to group the trainnumbers and then a second query to count these groups of trainnumbers. That works fine, but I don't know how to make this with delphi ( perhaps something like a subquery ? )
0
 

Author Comment

by:Kloot74
Comment Utility
I can't rebuilt the structure of the table, it is not my database, and it is an old one.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
I could make an example for you if you can wait 30 minutes ?
0
 
LVL 6

Expert Comment

by:wimmeyvaert
Comment Utility
I think I have some bad news about the 'distinct' in the MS-Access-query.
You cannot use a distinct in an Access-query !!!!
See this question :
http://beta.experts-exchange.com/Databases/MS_Access/Q_20703207.html

The only workaround I have is the following query :
SELECT COUNT(*) as Total
FROM (SELECT DISTINCT Field2, Field3 FROM table1  WHERE Field2 = #10/08/2004# GROUP BY Field2, Field3)

This query only shows the amount of trains found in the table for a certain date.


I'll take a closer look to the code you posted.

Best regards,

The Mayor.
0
 

Author Comment

by:Kloot74
Comment Utility
To wimmeyvaert : with this solution I get an error " syntax error in from clause ". Is it access97 ?
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
 T :=''
    + ' SELECT COUNT(*) AS COUNT_OF_TRAINS FROM ('
    + ' SELECT DISTINCT(TRAINNUMBER), DATES, DIRECTION'
    + ' FROM TRAINS WHERE DATES=:PDATES'
    + ' GROUP BY DATES, TRAINNUMBER, DIRECTION'
    + ')'
    + '';
  ADOQueryDate.Active := False;
  ADOQueryDate.SQL.Text := T;
  ADOQueryDate.Parameters.ParamByName('PDATES').Value := TDateTime(StrToDateTime('08.10.2004'));
  ADOQueryDate.Active := True;
0
 

Author Comment

by:Kloot74
Comment Utility
I'm sorry to say, that I always get an error in the from clause when I make "select (*) from ( select....). Is this access97, or what am I doing wrong?
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
I am testin with dynamic-created-database by Delphi under Windows XP with MSAccess 2002 (but I am not starting it)
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
Try this:

var
  DT:     TDateTime;
  S:      string;
  T:      string;
  R:      string;
begin
  DT := EncodeDate(Calendar.Year, Calendar.Month, Calendar.Day);
  S := FormatDateTime('dd/mm/yyyy', DT);
  R := StringReplace(S, '.', '_', [rfReplaceAll]);
  T :=''
    + ' SELECT COUNT(TRAINNUMBER) AS COUNT_OF_TRAINS_' + R
    + ' FROM (SELECT DISTINCT(TRAINNUMBER), DATES, DIRECTION'
    + ' FROM TRAINS WHERE DATES=:PDATES'
    + ' GROUP BY DATES, TRAINNUMBER, DIRECTION'
    + ')'
    + '';
  ADOQueryDate.Active := False;
  ADOQueryDate.SQL.Text := T;
  ADOQueryDate.Parameters.ParamByName('PDATES').Value := TDateTime(DT);
  ADOQueryDate.Active := True;
end;
0
 

Author Comment

by:Kloot74
Comment Utility
Does not work, I'm sorry.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
Is there another table in this Database which contains only TRAINNUMBER whithout DATES and WAGONNUMBER and DIRECTION ?
0
 

Author Comment

by:Kloot74
Comment Utility
The Database has only one table and I can't change the structure. Perhaps it is possible to create a temporary query or table, but I can't do it in delphi or in programmation. Could you give me an example of creating a temporary query?
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
please wait .... I will ....
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
function  CreateMSAccessTable(ADOCnctn: TADOConnection; ConnStr, TableName, CmndText: string): Boolean;
var
  I:      Integer;
  FN:     string;
  V:      Variant;
  SLTab:  TStringList;
  ADOCmd: TADOCommand;
begin
  Result := True;
  try
    ADOCmd := TADOCommand.Create(nil);
    try
      SLTab := TStringList.Create;
      try
        ADOCmd.Connection := ADOCnctn;
        ADOCnctn.GetTableNames(SLTab);
        if (SLTab.IndexOf(TableName)<0) then
        begin
          ADOCmd.Connection := ADOCnctn;
          ADOCmd.CommandText := CmndText;
          ADOCmd.Execute;
        end;
      finally
        SLTab.Free;
      end;
    finally
      ADOCmd.Free;
    end;
  except
    Result := False;
  end;
end;

procedure TForm1.ButtonCreateTableClick(Sender: TObject);
var
  T:      string;
  TName:  string;
begin
  TName := 'TempTrains';
  T :=''
    + ' create table ' + TName
    + '( ID AUTOINCREMENT PRIMARY KEY,'
    + ' TRAINNUMBER INTEGER'
    + ')'
    + '';

  CreateMSAccessTable(ADOConnection, ConnStr, TName, T);
end;
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 500 total points
Comment Utility
download an example from:
page:        http://www.geocities.com/esoftbg/
  link:        Q_21160739.zip
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now