Solved

Counting with sql in delphi and ms access database

Posted on 2004-10-08
38
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 14
  • 6
38 Comments
 
LVL 12

Expert Comment

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

Author Comment

by:Kloot74
ID: 12257214
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
ID: 12257292
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 12257312
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
ID: 12257320
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
ID: 12257334
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
ID: 12257367
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
ID: 12257379
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
ID: 12257443
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
ID: 12257602
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
ID: 12257616
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
ID: 12257621
I could provide you an example code ....
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 12257677
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
ID: 12257769
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
ID: 12257778
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
ID: 12257784
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
ID: 12257819
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
ID: 12257846
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
ID: 12257869
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12257893
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
ID: 12257907
No, only one and unique number of train per day. Perhaps, that helps.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12257913
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
ID: 12257931
The field WAGONNUMBER must to be removed from this table and to be in another table ....
0
 

Author Comment

by:Kloot74
ID: 12257938
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
ID: 12257960
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
ID: 12257961
I could make an example for you if you can wait 30 minutes ?
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 12257968
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
ID: 12258056
To wimmeyvaert : with this solution I get an error " syntax error in from clause ". Is it access97 ?
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12258177
 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
ID: 12258419
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
ID: 12258467
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
ID: 12258492
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
ID: 12258718
Does not work, I'm sorry.
0
 
LVL 12

Expert Comment

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

Author Comment

by:Kloot74
ID: 12258872
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
ID: 12259037
please wait .... I will ....
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12259478
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
ID: 12263904
download an example from:
page:        http://www.geocities.com/esoftbg/
  link:        Q_21160739.zip
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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