"LIKE" statement

B

blake7

Hi all, can someone help me, I want to add the LIKE statement into the code
below rather than the IN statement, because the search criteria eg Isar is
followed by a series of letters eg Isar HE24 or Isar HE30 and so on, i just
want to search for the word "Isar" and return the number found. Hope this
makes sense. rgards Tony.

SELECT [Main Audit Data].[Boiler Type], Count([Main Audit Data].[Boiler
Type]) AS [Boiler Count]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1) And (([Main
Audit Data].[Boiler Type]) In ('Isar','Icos','Esprit','Mexico','Elise')))
GROUP BY [Main Audit Data].[Boiler Type];
 
J

John Spencer

You can only do that by using multiple Or clauses

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
B

blake7

Hi John, that works great, how do I get the results into expressions so I can
drag the results to a form for viewing, is that easy to do?
Thanks John
John Spencer said:
You can only do that by using multiple Or clauses

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi all, can someone help me, I want to add the LIKE statement into the code
below rather than the IN statement, because the search criteria eg Isar is
followed by a series of letters eg Isar HE24 or Isar HE30 and so on, i just
want to search for the word "Isar" and return the number found. Hope this
makes sense. rgards Tony.

SELECT [Main Audit Data].[Boiler Type], Count([Main Audit Data].[Boiler
Type]) AS [Boiler Count]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1) And (([Main
Audit Data].[Boiler Type]) In ('Isar','Icos','Esprit','Mexico','Elise')))
GROUP BY [Main Audit Data].[Boiler Type];
Click to expand...
Click to expand...
 
J

John Spencer

Open a new form in design view
Use the query as the record source for the form.
Use the fields

Or Save the query with a name you will remember
Switch to the form tab
Select NEW Form and use the wizard to build the form.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, that works great, how do I get the results into expressions so I can
drag the results to a form for viewing, is that easy to do?
Thanks John
John Spencer said:
You can only do that by using multiple Or clauses

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi all, can someone help me, I want to add the LIKE statement into the code
below rather than the IN statement, because the search criteria eg Isar is
followed by a series of letters eg Isar HE24 or Isar HE30 and so on, i just
want to search for the word "Isar" and return the number found. Hope this
makes sense. rgards Tony.

SELECT [Main Audit Data].[Boiler Type], Count([Main Audit Data].[Boiler
Type]) AS [Boiler Count]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1) And (([Main
Audit Data].[Boiler Type]) In ('Isar','Icos','Esprit','Mexico','Elise')))
GROUP BY [Main Audit Data].[Boiler Type];
Click to expand...
Click to expand...
Click to expand...
 
B

blake7

Hi John, I already tried that but the query only shows me two fields (boiler
type and boiler count), what i was wanting to do was have a form that when
opened it would display the 5 text boxes showing the results of the query
(number of instances of each boiler variant) based simply on the audit date
field between say january 1st to january 31st. Hope this makes sense. Thanks

John Spencer said:
Open a new form in design view
Use the query as the record source for the form.
Use the fields

Or Save the query with a name you will remember
Switch to the form tab
Select NEW Form and use the wizard to build the form.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, that works great, how do I get the results into expressions so I can
drag the results to a form for viewing, is that easy to do?
Thanks John
John Spencer said:
You can only do that by using multiple Or clauses

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi all, can someone help me, I want to add the LIKE statement into the code
below rather than the IN statement, because the search criteria eg Isar is
followed by a series of letters eg Isar HE24 or Isar HE30 and so on, i just
want to search for the word "Isar" and return the number found. Hope this
makes sense. rgards Tony.

SELECT [Main Audit Data].[Boiler Type], Count([Main Audit Data].[Boiler
Type]) AS [Boiler Count]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1) And (([Main
Audit Data].[Boiler Type]) In ('Isar','Icos','Esprit','Mexico','Elise')))
GROUP BY [Main Audit Data].[Boiler Type];
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

It is not really making sense to me.

The query should be returning multiple rows of data. One for each type of
boiler and a count for each type of boiler.

So if you have five rows, that would mean 5 types and five counts. (2 per row)

If you display that in a continuous form, you will have 5 sections. It is
possible to use a list box to display all the information. The listbox on the
form would use the query as its source and it would display 5 rows (or however
many were returned by the query).

A form set to Single Form view will only display one row of data. It sounds
to me as if you either need a continuous form (Form Properties: Format Tab:
Default View) or a form in single view with a listbox to show the information.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
B

blake7

Hi John, thanks for your help, that seemed to work ok with a continuous form,
but it is showing all the variants, eg if it finds one instance of Isar HE24
it displays it ok but then if I add an Isar HE30 it shows that too but in a
separate field etc, and i really wanted to just count instances of Isar or
Icos etc thats why I used the LIKE statement, the other SQL statement I have
(shown below) works fine and displays what i want, but how do I limit it to
look between dates, I have tried all sorts of combinations but it still
returns all the data from the whole table and not between two dates as I
want. Thanks again John.

SELECT DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS
Icos, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar,
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS [Icos
System], DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'")
AS Mexico, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'")
AS Esprit, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic
HE*'") AS Classic, DCount("*","[main Audit Data]","[Boiler Type] LIKE
'*Elise*'") AS Elise
FROM [Main Audit Data]
GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");
 
B

blake7

Hi John, I posted the wrong code the one below works ok but i want to limit
it between two dates, sorry for confusion

SELECT DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS
Icos, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar,
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS [Icos
System], DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'")
AS Mexico, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'")
AS Esprit, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic
HE*'") AS Classic, DCount("*","[main Audit Data]","[Boiler Type] LIKE
'*Elise*'") AS Elise
FROM [Main Audit Data]
GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");



blake7 said:
Hi John, thanks for your help, that seemed to work ok with a continuous form,
but it is showing all the variants, eg if it finds one instance of Isar HE24
it displays it ok but then if I add an Isar HE30 it shows that too but in a
separate field etc, and i really wanted to just count instances of Isar or
Icos etc thats why I used the LIKE statement, the other SQL statement I have
(shown below) works fine and displays what i want, but how do I limit it to
look between dates, I have tried all sorts of combinations but it still
returns all the data from the whole table and not between two dates as I
want. Thanks again John.

SELECT DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS
Icos, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar,
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS [Icos
System], DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'")
AS Mexico, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'")
AS Esprit, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic
HE*'") AS Classic, DCount("*","[main Audit Data]","[Boiler Type] LIKE
'*Elise*'") AS Elise
FROM [Main Audit Data]
GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");


John Spencer said:
It is not really making sense to me.

The query should be returning multiple rows of data. One for each type of
boiler and a count for each type of boiler.

So if you have five rows, that would mean 5 types and five counts. (2 per row)

If you display that in a continuous form, you will have 5 sections. It is
possible to use a list box to display all the information. The listbox on the
form would use the query as its source and it would display 5 rows (or however
many were returned by the query).

A form set to Single Form view will only display one row of data. It sounds
to me as if you either need a continuous form (Form Properties: Format Tab:
Default View) or a form in single view with a listbox to show the information.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
J

John Spencer

Then add a where clause in to do so. Note that the where clause goes BEFORE
the GROUP BY. It filters the records before the aggregation takes place so it
should be faster.

SELECT
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS Icos
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS
[Icos System]
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'") AS Mexico
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'") AS Esprit
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'") AS Classic
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'") AS Elise
FROM [Main Audit Data]

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");

There is one problem in that the ICOS count is going to include the items
counted in the ICOS system count. You can try changing that to

DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*' AND [Boiler Type]
NOT LIKE '*ICOS System*' ") AS Icos



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi John, I posted the wrong code the one below works ok but i want to limit
it between two dates, sorry for confusion

SELECT DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS
Icos, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar,
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS [Icos
System], DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'")
AS Mexico, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'")
AS Esprit, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic
HE*'") AS Classic, DCount("*","[main Audit Data]","[Boiler Type] LIKE
'*Elise*'") AS Elise
FROM [Main Audit Data]
GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");
Click to expand...
 
B

blake7

Hi John, thanks for your help, i cut and pasted your SQL into a new query and
ran it, as expected i was prompted to enter the two date parameters, but it
still returns the data from the whole table, I tried entering dates for
February because I know I only have one entry for February, but it still
returns the count for the whole table, I really cannot see why ?can you?
Regards Tony

John Spencer said:
Then add a where clause in to do so. Note that the where clause goes BEFORE
the GROUP BY. It filters the records before the aggregation takes place so it
should be faster.

SELECT
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS Icos
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS
[Icos System]
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'") AS Mexico
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'") AS Esprit
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'") AS Classic
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'") AS Elise
FROM [Main Audit Data]

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");

There is one problem in that the ICOS count is going to include the items
counted in the ICOS system count. You can try changing that to

DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*' AND [Boiler Type]
NOT LIKE '*ICOS System*' ") AS Icos



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi John, I posted the wrong code the one below works ok but i want to limit
it between two dates, sorry for confusion

SELECT DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS
Icos, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar,
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS [Icos
System], DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'")
AS Mexico, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'")
AS Esprit, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic
HE*'") AS Classic, DCount("*","[main Audit Data]","[Boiler Type] LIKE
'*Elise*'") AS Elise
FROM [Main Audit Data]
GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");
Click to expand...
Click to expand...
 
J

John Spencer

DARN, I SHOULD have noticed that you are using DCOUNT and not Count.

All those DCount should look like:

, Count(IIF([Boiler Type] LIKE '*icos*',1,Null)) as ICOS
, Count (IIF([Boiler Type] LIKE '*isar*',1,Null)) as ISAR

etcetera

There is a really good chance this will be faster.

If you used DCOUNT you would have to include the date range in the third
argument.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, thanks for your help, i cut and pasted your SQL into a new query and
ran it, as expected i was prompted to enter the two date parameters, but it
still returns the data from the whole table, I tried entering dates for
February because I know I only have one entry for February, but it still
returns the count for the whole table, I really cannot see why ?can you?
Regards Tony

John Spencer said:
Then add a where clause in to do so. Note that the where clause goes BEFORE
the GROUP BY. It filters the records before the aggregation takes place so it
should be faster.

SELECT
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS Icos
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS
[Icos System]
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'") AS Mexico
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'") AS Esprit
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'") AS Classic
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'") AS Elise
FROM [Main Audit Data]

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");

There is one problem in that the ICOS count is going to include the items
counted in the ICOS system count. You can try changing that to

DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*' AND [Boiler Type]
NOT LIKE '*ICOS System*' ") AS Icos



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi John, I posted the wrong code the one below works ok but i want to limit
it between two dates, sorry for confusion

SELECT DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS
Icos, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar,
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS [Icos
System], DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'")
AS Mexico, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'")
AS Esprit, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic
HE*'") AS Classic, DCount("*","[main Audit Data]","[Boiler Type] LIKE
'*Elise*'") AS Elise
FROM [Main Audit Data]
GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");
Click to expand...
Click to expand...
Click to expand...
 
B

blake7

BRILLIANT - Thanks John your a Genius, just on chapter one of my firts learn
SQL book, its very confusing, but i will get there. By the way whats the
difference between a "Dcount" and Count ??

John Spencer said:
DARN, I SHOULD have noticed that you are using DCOUNT and not Count.

All those DCount should look like:

, Count(IIF([Boiler Type] LIKE '*icos*',1,Null)) as ICOS
, Count (IIF([Boiler Type] LIKE '*isar*',1,Null)) as ISAR

etcetera

There is a really good chance this will be faster.

If you used DCOUNT you would have to include the date range in the third
argument.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, thanks for your help, i cut and pasted your SQL into a new query and
ran it, as expected i was prompted to enter the two date parameters, but it
still returns the data from the whole table, I tried entering dates for
February because I know I only have one entry for February, but it still
returns the count for the whole table, I really cannot see why ?can you?
Regards Tony

John Spencer said:
Then add a where clause in to do so. Note that the where clause goes BEFORE
the GROUP BY. It filters the records before the aggregation takes place so it
should be faster.

SELECT
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS Icos
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS
[Icos System]
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'") AS Mexico
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'") AS Esprit
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'") AS Classic
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'") AS Elise
FROM [Main Audit Data]

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");

There is one problem in that the ICOS count is going to include the items
counted in the ICOS system count. You can try changing that to

DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*' AND [Boiler Type]
NOT LIKE '*ICOS System*' ") AS Icos



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi John, I posted the wrong code the one below works ok but i want to limit
it between two dates, sorry for confusion

SELECT DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS
Icos, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar,
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS [Icos
System], DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'")
AS Mexico, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'")
AS Esprit, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic
HE*'") AS Classic, DCount("*","[main Audit Data]","[Boiler Type] LIKE
'*Elise*'") AS Elise
FROM [Main Audit Data]
GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

DCount is a VBA function.
Count is an SQL operator that counts things.

Behind the covers, I BELIEVE (no proof) that DCOUNT constructs a query and
runs it. If you switch to a VBA module, you will be able to look up the help
on DCount, DAvg, DSum, DMin, DMax.

Usually it is better to use the SQL operators to get the information. In SOME
cases in Access you are forced to use the VBA aggregate functions (notably in
update queries).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
BRILLIANT - Thanks John your a Genius, just on chapter one of my firts learn
SQL book, its very confusing, but i will get there. By the way whats the
difference between a "Dcount" and Count ??

John Spencer said:
DARN, I SHOULD have noticed that you are using DCOUNT and not Count.

All those DCount should look like:

, Count(IIF([Boiler Type] LIKE '*icos*',1,Null)) as ICOS
, Count (IIF([Boiler Type] LIKE '*isar*',1,Null)) as ISAR

etcetera

There is a really good chance this will be faster.

If you used DCOUNT you would have to include the date range in the third
argument.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, thanks for your help, i cut and pasted your SQL into a new query and
ran it, as expected i was prompted to enter the two date parameters, but it
still returns the data from the whole table, I tried entering dates for
February because I know I only have one entry for February, but it still
returns the count for the whole table, I really cannot see why ?can you?
Regards Tony

:

Then add a where clause in to do so. Note that the where clause goes BEFORE
the GROUP BY. It filters the records before the aggregation takes place so it
should be faster.

SELECT
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS Icos
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS
[Icos System]
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'") AS Mexico
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'") AS Esprit
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'") AS Classic
, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'") AS Elise
FROM [Main Audit Data]

WHERE [Main Audit Data].[Audit Date] Between
forms!graphcriteria!startdate1 And forms!graphcriteria!enddate1
And ( [Main Audit Data].[Boiler Type] Like "ISAR*"
OR [Main Audit Data].[Boiler Type] Like "Icos*"
OR [Main Audit Data].[Boiler Type] Like "Esprit*"
OR [Main Audit Data].[Boiler Type] Like "Mexico*"
OR [Main Audit Data].[Boiler Type] Like "Elise*" )

GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");

There is one problem in that the ICOS count is going to include the items
counted in the ICOS system count. You can try changing that to

DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*' AND [Boiler Type]
NOT LIKE '*ICOS System*' ") AS Icos



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi John, I posted the wrong code the one below works ok but i want to limit
it between two dates, sorry for confusion

SELECT DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'") AS
Icos, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'") AS Isar,
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'") AS [Icos
System], DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'")
AS Mexico, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'")
AS Esprit, DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic
HE*'") AS Classic, DCount("*","[main Audit Data]","[Boiler Type] LIKE
'*Elise*'") AS Elise
FROM [Main Audit Data]
GROUP BY DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*isar*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos system*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*mexico HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Esprit HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*classic HE*'"),
DCount("*","[main Audit Data]","[Boiler Type] LIKE '*Elise*'");
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding a date parameter to an SQL statement 4
Search criteria for querys 3
Combo boxes in subforms 3
Abs(sum()) or DCount() 5
Urgent: Changing RecordSource Causes A Reload of Sub Form Destroying References 4
Status Calculating Forever 0
Changing RecordSource Causes A Reload of Sub Form Destroying References 12
Using like in an iif statement 1

Top