Counting query

[blake7]

Hi I have the folowing code in a query, but for some reason it returns the
value of 911 ?? any ideas where I have gone wrong. Thanks All

SELECT [Main Audit Data].[Audit Date], DCount("*","[main Audit
Data]","[fault code]" Like '*[Jacket Panel Damage]*') AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]));

 

[blake7]

Sorry guys not been very clear in what i have put, the query returns a list
of 92 entries between the two dates the second column shows 911 all the way
down, It should return a value of 4 because thats how many instances of
"jacket panel damage" have been recorded. hope this helps.

 

[John Spencer]

Your DCount is incorrectly constructed.

SELECT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"



That could return many duplicate lines. You might want to consider using
DISTINCT in the above. As in

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If you need the zeroes for the dates in the date range then try stacking the
queries. Save the following as q1 (or whatever name you wish to use.

SELECT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE [Fault Code] Like "*[Jacket Panel Damage]*"
AND [Main Audit Data].[Audit Date] Between [forms]![graphcriteria]![startdate]
And [forms]![graphcriteria]![enddate]
GROUP BY [Audit Date]

SELECT DISTINCT M.[Audit Date], Nz(Q.[SELECT],0) as TheCount
FROM [Main Audit Data] as M LEFT JOIN q1 as Q
On M.[Audit Date] = Q.[Audit Date]
WHERE M.[Audit Date] Between [forms]![graphcriteria]![startdate] And
[forms]![graphcriteria]![enddate]


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

 

[blake7]

Hi John, Thanks for your help, I tried the 'DISTINCT' version below but it
returns the following error - "The syntax of the subquery in this expression
is incorrect - check the subquery's syntax and enclose and enclose the
subquery in parenttheses"
Thanks again John

Your DCount is incorrectly constructed.

SELECT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"



That could return many duplicate lines. You might want to consider using
DISTINCT in the above. As in

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If you need the zeroes for the dates in the date range then try stacking the
queries. Save the following as q1 (or whatever name you wish to use.

SELECT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE [Fault Code] Like "*[Jacket Panel Damage]*"
AND [Main Audit Data].[Audit Date] Between [forms]![graphcriteria]![startdate]
And [forms]![graphcriteria]![enddate]
GROUP BY [Audit Date]

SELECT DISTINCT M.[Audit Date], Nz(Q.[SELECT],0) as TheCount
FROM [Main Audit Data] as M LEFT JOIN q1 as Q
On M.[Audit Date] = Q.[Audit Date]
WHERE M.[Audit Date] Between [forms]![graphcriteria]![startdate] And
[forms]![graphcriteria]![enddate]


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

Hi I have the folowing code in a query, but for some reason it returns the
value of 911 ?? any ideas where I have gone wrong. Thanks All

SELECT [Main Audit Data].[Audit Date], DCount("*","[main Audit
Data]","[fault code]" Like '*[Jacket Panel Damage]*') AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]));

 

[John Spencer]

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

 

[blake7]

Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

 

[John Spencer]

Since SELECT is a reserved word and usually starts a query or subquery, lets
try taking that out of the query.

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If that doesn't fix the problem, then I would check that you have a field
named Fault Code and that it is a text field.

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

Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

 

[blake7]

Hi John, just tried it by removing the word SELECT and just left the word
DISTINCT it now has the following error
the expression contains Invalid syntax, you may have entered an operand
without an operator
Thanks John
Tony

Since SELECT is a reserved word and usually starts a query or subquery, lets
try taking that out of the query.

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If that doesn't fix the problem, then I would check that you have a field
named Fault Code and that it is a text field.

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

Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

blake7 wrote:
Hi John, Thanks for your help, I tried the 'DISTINCT' version below but it
returns the following error - "The syntax of the subquery in this expression
is incorrect - check the subquery's syntax and enclose and enclose the
subquery in parenttheses"
Thanks again John

 

[John Spencer]

I think you may have taken out the FIRST SELECT in the query. You need
to take out the [SELECT] that is being used as the name for the value
being returned by the DCount function.

Watch out for the line wrap in the following. The entire DCount
statement should be on one line.

SELECT DISTINCT [Main Audit Data].[Audit Date]
, DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel
Damage]*'") AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

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

Hi John, just tried it by removing the word SELECT and just left the word
DISTINCT it now has the following error
the expression contains Invalid syntax, you may have entered an operand
without an operator
Thanks John
Tony

Since SELECT is a reserved word and usually starts a query or subquery, lets
try taking that out of the query.

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If that doesn't fix the problem, then I would check that you have a field
named Fault Code and that it is a text field.

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

Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

:

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

blake7 wrote:
Hi John, Thanks for your help, I tried the 'DISTINCT' version below but it
returns the following error - "The syntax of the subquery in this expression
is incorrect - check the subquery's syntax and enclose and enclose the
subquery in parenttheses"
Thanks again John

 

[blake7]

Hi John, Thanks once again for your help, sorry to be a pain but I not really
sure what you mean?, is the code you posted in your last reply the one I
should now use or does it need altering to what you have said in your reply?
if so where do I remove the [select]
I tried cut and pasting it but it still asks for the sub query to be put in
parentheses ??

Thanks Tony.

I think you may have taken out the FIRST SELECT in the query. You need
to take out the [SELECT] that is being used as the name for the value
being returned by the DCount function.

Watch out for the line wrap in the following. The entire DCount
statement should be on one line.

SELECT DISTINCT [Main Audit Data].[Audit Date]
, DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel
Damage]*'") AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

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

Hi John, just tried it by removing the word SELECT and just left the word
DISTINCT it now has the following error
the expression contains Invalid syntax, you may have entered an operand
without an operator
Thanks John
Tony

Since SELECT is a reserved word and usually starts a query or subquery, lets
try taking that out of the query.

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If that doesn't fix the problem, then I would check that you have a field
named Fault Code and that it is a text field.

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

blake7 wrote:
Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

:

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

blake7 wrote:
Hi John, Thanks for your help, I tried the 'DISTINCT' version below but it
returns the following error - "The syntax of the subquery in this expression
is incorrect - check the subquery's syntax and enclose and enclose the
subquery in parenttheses"
Thanks again John

 

[John Spencer]

That is the ENTIRE QUERY as it should be. You should not need to make any
changes to the query.

Are you cut and pasting that into the SQL view of a query or are you
attempting to paste that into a "block" in an existing query?

The error you are getting suggests that you are attempting to paste the SQL
statement into a field or criteria block of an existing query.

Open a new query
DO NOT add any tables
Switch to the SQL view (View: SQL on the menu)
Paste the SQL statement into the text window.

If that doesn't apply, then I am stuck. I am obviously NOT understanding what
you are doing. Perhaps you should start a new thread and hopefully someone
else will respond with fresh eyes.

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

Hi John, Thanks once again for your help, sorry to be a pain but I not really
sure what you mean?, is the code you posted in your last reply the one I
should now use or does it need altering to what you have said in your reply?
if so where do I remove the [select]
I tried cut and pasting it but it still asks for the sub query to be put in
parentheses ??

Thanks Tony.

I think you may have taken out the FIRST SELECT in the query. You need
to take out the [SELECT] that is being used as the name for the value
being returned by the DCount function.

Watch out for the line wrap in the following. The entire DCount
statement should be on one line.

SELECT DISTINCT [Main Audit Data].[Audit Date]
, DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel
Damage]*'") AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

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

Hi John, just tried it by removing the word SELECT and just left the word
DISTINCT it now has the following error
the expression contains Invalid syntax, you may have entered an operand
without an operator
Thanks John
Tony


:

Since SELECT is a reserved word and usually starts a query or subquery, lets
try taking that out of the query.

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If that doesn't fix the problem, then I would check that you have a field
named Fault Code and that it is a text field.

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

blake7 wrote:
Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

:

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

blake7 wrote:
Hi John, Thanks for your help, I tried the 'DISTINCT' version below but it
returns the following error - "The syntax of the subquery in this expression
is incorrect - check the subquery's syntax and enclose and enclose the
subquery in parenttheses"
Thanks again John

 

[blake7]

Hi John, I did what you said and the query run ok with no error message,
although it still returns a table with 11 records in it, this is the number
of "jacket panel damage" it should find, there is a date column and another
column with "911" in it ? the "911" is the number of records in the whole
table.

Anyway I have found the following code which does count the number of unique
entries in the whole table and works ok, but I need to specify the BETWEEN
DATES part so I can enter the start and end dates as per the other query you
did for me, but dont know how to add it to the SQL code, could you help one
last time ?? Thanks John

SELECT Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel
damage], Sum(IIf([faultcode]='OK',1,0)) AS OK, Sum(IIf([faultcode]='Part
Missing',1,0)) AS [Part Missing], Sum(IIf([faultcode]='Loose Component',1,0))
AS [Loose Component], Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring
Fault], Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing],
Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing],
Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing],
Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue],
Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack],
Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data];

That is the ENTIRE QUERY as it should be. You should not need to make any
changes to the query.

Are you cut and pasting that into the SQL view of a query or are you
attempting to paste that into a "block" in an existing query?

The error you are getting suggests that you are attempting to paste the SQL
statement into a field or criteria block of an existing query.

Open a new query
DO NOT add any tables
Switch to the SQL view (View: SQL on the menu)
Paste the SQL statement into the text window.

If that doesn't apply, then I am stuck. I am obviously NOT understanding what
you are doing. Perhaps you should start a new thread and hopefully someone
else will respond with fresh eyes.

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

Hi John, Thanks once again for your help, sorry to be a pain but I not really
sure what you mean?, is the code you posted in your last reply the one I
should now use or does it need altering to what you have said in your reply?
if so where do I remove the [select]
I tried cut and pasting it but it still asks for the sub query to be put in
parentheses ??

Thanks Tony.

I think you may have taken out the FIRST SELECT in the query. You need
to take out the [SELECT] that is being used as the name for the value
being returned by the DCount function.

Watch out for the line wrap in the following. The entire DCount
statement should be on one line.

SELECT DISTINCT [Main Audit Data].[Audit Date]
, DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel
Damage]*'") AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

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


blake7 wrote:
Hi John, just tried it by removing the word SELECT and just left the word
DISTINCT it now has the following error
the expression contains Invalid syntax, you may have entered an operand
without an operator
Thanks John
Tony


:

Since SELECT is a reserved word and usually starts a query or subquery, lets
try taking that out of the query.

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If that doesn't fix the problem, then I would check that you have a field
named Fault Code and that it is a text field.

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

blake7 wrote:
Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

:

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

blake7 wrote:
Hi John, Thanks for your help, I tried the 'DISTINCT' version below but it
returns the following error - "The syntax of the subquery in this expression
is incorrect - check the subquery's syntax and enclose and enclose the
subquery in parenttheses"
Thanks again John

 

[John Spencer]

Parameters [Start Date] Datetime, [End Date] Datetime;
SELECT
Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel damage]
, Sum(IIf([faultcode]='OK',1,0)) AS OK
, Sum(IIf([faultcode]='Part Missing',1,0)) AS [Part Missing]
, Sum(IIf([faultcode]='Loose Component',1,0)) AS [Loose Component]
, Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring Fault]
, Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing]
, Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing]
, Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing]
, Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue]
, Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack]
, Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data]
WHERE [Some Date Field] Between [Start Date] and [End Date]


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

Hi John, I did what you said and the query run ok with no error message,
although it still returns a table with 11 records in it, this is the number
of "jacket panel damage" it should find, there is a date column and another
column with "911" in it ? the "911" is the number of records in the whole
table.

Anyway I have found the following code which does count the number of unique
entries in the whole table and works ok, but I need to specify the BETWEEN
DATES part so I can enter the start and end dates as per the other query you
did for me, but dont know how to add it to the SQL code, could you help one
last time ?? Thanks John

SELECT Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel
damage], Sum(IIf([faultcode]='OK',1,0)) AS OK, Sum(IIf([faultcode]='Part
Missing',1,0)) AS [Part Missing], Sum(IIf([faultcode]='Loose Component',1,0))
AS [Loose Component], Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring
Fault], Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing],
Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing],
Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing],
Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue],
Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack],
Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data];

That is the ENTIRE QUERY as it should be. You should not need to make any
changes to the query.

Are you cut and pasting that into the SQL view of a query or are you
attempting to paste that into a "block" in an existing query?

The error you are getting suggests that you are attempting to paste the SQL
statement into a field or criteria block of an existing query.

Open a new query
DO NOT add any tables
Switch to the SQL view (View: SQL on the menu)
Paste the SQL statement into the text window.

If that doesn't apply, then I am stuck. I am obviously NOT understanding what
you are doing. Perhaps you should start a new thread and hopefully someone
else will respond with fresh eyes.

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

Hi John, Thanks once again for your help, sorry to be a pain but I not really
sure what you mean?, is the code you posted in your last reply the one I
should now use or does it need altering to what you have said in your reply?
if so where do I remove the [select]
I tried cut and pasting it but it still asks for the sub query to be put in
parentheses ??

Thanks Tony.

:

I think you may have taken out the FIRST SELECT in the query. You need
to take out the [SELECT] that is being used as the name for the value
being returned by the DCount function.

Watch out for the line wrap in the following. The entire DCount
statement should be on one line.

SELECT DISTINCT [Main Audit Data].[Audit Date]
, DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel
Damage]*'") AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

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


blake7 wrote:
Hi John, just tried it by removing the word SELECT and just left the word
DISTINCT it now has the following error
the expression contains Invalid syntax, you may have entered an operand
without an operator
Thanks John
Tony


:

Since SELECT is a reserved word and usually starts a query or subquery, lets
try taking that out of the query.

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If that doesn't fix the problem, then I would check that you have a field
named Fault Code and that it is a text field.

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

blake7 wrote:
Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

:

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

blake7 wrote:
Hi John, Thanks for your help, I tried the 'DISTINCT' version below but it
returns the following error - "The syntax of the subquery in this expression
is incorrect - check the subquery's syntax and enclose and enclose the
subquery in parenttheses"
Thanks again John

 

[blake7]

Thanks John, we are nearly there, I have done the following below but it is
still counting records from the WHOLE table and not between the two date
fields as i want, I have created a form with the two text boxes and a button
to run the query, Have I again not got my SQL correct?. Thanks John.

PARAMETERS [forms]![graphcriteria]![StartDate] DateTime,
[forms]![graphcriteria]![enddate] DateTime;
SELECT Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel
damage], Sum(IIf([faultcode]='OK',1,0)) AS OK, Sum(IIf([faultcode]='Part
Missing',1,0)) AS [Part Missing], Sum(IIf([faultcode]='Loose Component',1,0))
AS [Loose Component], Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring
Fault], Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing],
Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing],
Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing],
Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue],
Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack],
Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data];

Parameters [Start Date] Datetime, [End Date] Datetime;
SELECT
Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel damage]
, Sum(IIf([faultcode]='OK',1,0)) AS OK
, Sum(IIf([faultcode]='Part Missing',1,0)) AS [Part Missing]
, Sum(IIf([faultcode]='Loose Component',1,0)) AS [Loose Component]
, Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring Fault]
, Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing]
, Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing]
, Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing]
, Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue]
, Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack]
, Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data]
WHERE [Some Date Field] Between [Start Date] and [End Date]


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

Hi John, I did what you said and the query run ok with no error message,
although it still returns a table with 11 records in it, this is the number
of "jacket panel damage" it should find, there is a date column and another
column with "911" in it ? the "911" is the number of records in the whole
table.

Anyway I have found the following code which does count the number of unique
entries in the whole table and works ok, but I need to specify the BETWEEN
DATES part so I can enter the start and end dates as per the other query you
did for me, but dont know how to add it to the SQL code, could you help one
last time ?? Thanks John

SELECT Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel
damage], Sum(IIf([faultcode]='OK',1,0)) AS OK, Sum(IIf([faultcode]='Part
Missing',1,0)) AS [Part Missing], Sum(IIf([faultcode]='Loose Component',1,0))
AS [Loose Component], Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring
Fault], Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing],
Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing],
Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing],
Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue],
Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack],
Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data];

That is the ENTIRE QUERY as it should be. You should not need to make any
changes to the query.

Are you cut and pasting that into the SQL view of a query or are you
attempting to paste that into a "block" in an existing query?

The error you are getting suggests that you are attempting to paste the SQL
statement into a field or criteria block of an existing query.

Open a new query
DO NOT add any tables
Switch to the SQL view (View: SQL on the menu)
Paste the SQL statement into the text window.

If that doesn't apply, then I am stuck. I am obviously NOT understanding what
you are doing. Perhaps you should start a new thread and hopefully someone
else will respond with fresh eyes.

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

blake7 wrote:
Hi John, Thanks once again for your help, sorry to be a pain but I not really
sure what you mean?, is the code you posted in your last reply the one I
should now use or does it need altering to what you have said in your reply?
if so where do I remove the [select]
I tried cut and pasting it but it still asks for the sub query to be put in
parentheses ??

Thanks Tony.

:

I think you may have taken out the FIRST SELECT in the query. You need
to take out the [SELECT] that is being used as the name for the value
being returned by the DCount function.

Watch out for the line wrap in the following. The entire DCount
statement should be on one line.

SELECT DISTINCT [Main Audit Data].[Audit Date]
, DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel
Damage]*'") AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

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


blake7 wrote:
Hi John, just tried it by removing the word SELECT and just left the word
DISTINCT it now has the following error
the expression contains Invalid syntax, you may have entered an operand
without an operator
Thanks John
Tony


:

Since SELECT is a reserved word and usually starts a query or subquery, lets
try taking that out of the query.

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS CountFaults
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

If that doesn't fix the problem, then I would check that you have a field
named Fault Code and that it is a text field.

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

blake7 wrote:
Hi john, I cut and pasted exactly as you wrote eg

SELECT DISTINCT [Main Audit Data].[Audit Date],
DCount("*","[main Audit Data]","[fault code] Like '*[Jacket Panel Damage]*'")
AS [SELECT]
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Audit Date]) Between
[forms]![graphcriteria]![startdate] And [forms]![graphcriteria]![enddate]))
AND [Fault Code] Like "*[Jacket Panel Damage]*"
GROUP BY [Audit Date]

i even tried the none DISTINCT version you posted and it came back with the
same error message
Thanks Tony

:

Ok, I've confused myself.

Can you post the SQL of the query you attempted to run? If you can switch
from Design View to SQL view and post the SQL statement.

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

blake7 wrote:
Hi John, Thanks for your help, I tried the 'DISTINCT' version below but it
returns the following error - "The syntax of the subquery in this expression
is incorrect - check the subquery's syntax and enclose and enclose the
subquery in parenttheses"
Thanks again John

 

[John Spencer]

I don't see a where clause on what you posted. At the very end, you
should have

WHERE [Some Date Field] Between [Start Date] and [End Date]

By the way, you might be able to do this whole thing in a much simpler
manner

SELECT FaultCode, Count(FaultCode) as [number of Faults]
FROM [Main Audit Data]
WHERE [Some Date Field] Between [Start Date] and [End Date]
GROUP BY FaultCode

If you need to limit it to specific codes then add that into the where
clause


SELECT FaultCode, Count(FaultCode) as [number of Faults]
FROM [Main Audit Data]
WHERE [Some Date Field] Between [Start Date] and [End Date]
AND FaultCode in ('Wiring Fault','Ok', 'jacket panel damage',
'Literature Missing', 'Loose component')
GROUP BY FaultCode

Hopefully, you can figure out how to fill in the other codes if you use
the simpler structure above.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks John, we are nearly there, I have done the following below but it is
still counting records from the WHOLE table and not between the two date
fields as i want, I have created a form with the two text boxes and a button
to run the query, Have I again not got my SQL correct?. Thanks John.

PARAMETERS [forms]![graphcriteria]![StartDate] DateTime,
[forms]![graphcriteria]![enddate] DateTime;
SELECT Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel
damage], Sum(IIf([faultcode]='OK',1,0)) AS OK, Sum(IIf([faultcode]='Part
Missing',1,0)) AS [Part Missing], Sum(IIf([faultcode]='Loose Component',1,0))
AS [Loose Component], Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring
Fault], Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing],
Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing],
Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing],
Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue],
Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack],
Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data];

Parameters [Start Date] Datetime, [End Date] Datetime;
SELECT
Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel damage]
, Sum(IIf([faultcode]='OK',1,0)) AS OK
, Sum(IIf([faultcode]='Part Missing',1,0)) AS [Part Missing]
, Sum(IIf([faultcode]='Loose Component',1,0)) AS [Loose Component]
, Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring Fault]
, Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing]
, Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing]
, Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing]
, Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue]
, Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack]
, Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data]
WHERE [Some Date Field] Between [Start Date] and [End Date]

 

[blake7]

PERFECT !! That works great John, thanks once again for putting up with me, I
have just started the rocky road to learning SQL via my first book - wish me
luck
I'm sure I will be coming back for more advice soon. Bye for now.
regards Tony.

I don't see a where clause on what you posted. At the very end, you
should have

WHERE [Some Date Field] Between [Start Date] and [End Date]

By the way, you might be able to do this whole thing in a much simpler
manner

SELECT FaultCode, Count(FaultCode) as [number of Faults]
FROM [Main Audit Data]
WHERE [Some Date Field] Between [Start Date] and [End Date]
GROUP BY FaultCode

If you need to limit it to specific codes then add that into the where
clause


SELECT FaultCode, Count(FaultCode) as [number of Faults]
FROM [Main Audit Data]
WHERE [Some Date Field] Between [Start Date] and [End Date]
AND FaultCode in ('Wiring Fault','Ok', 'jacket panel damage',
'Literature Missing', 'Loose component')
GROUP BY FaultCode

Hopefully, you can figure out how to fill in the other codes if you use
the simpler structure above.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks John, we are nearly there, I have done the following below but it is
still counting records from the WHOLE table and not between the two date
fields as i want, I have created a form with the two text boxes and a button
to run the query, Have I again not got my SQL correct?. Thanks John.

PARAMETERS [forms]![graphcriteria]![StartDate] DateTime,
[forms]![graphcriteria]![enddate] DateTime;
SELECT Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel
damage], Sum(IIf([faultcode]='OK',1,0)) AS OK, Sum(IIf([faultcode]='Part
Missing',1,0)) AS [Part Missing], Sum(IIf([faultcode]='Loose Component',1,0))
AS [Loose Component], Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring
Fault], Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing],
Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing],
Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing],
Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue],
Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack],
Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data];

Parameters [Start Date] Datetime, [End Date] Datetime;
SELECT
Sum(IIf([faultcode]='jacket panel damage',1,0)) AS [Jacket panel damage]
, Sum(IIf([faultcode]='OK',1,0)) AS OK
, Sum(IIf([faultcode]='Part Missing',1,0)) AS [Part Missing]
, Sum(IIf([faultcode]='Loose Component',1,0)) AS [Loose Component]
, Sum(IIf([faultcode]='Wiring fault',1,0)) AS [Wiring Fault]
, Sum(IIf([faultcode]='Faulty Fixing',1,0)) AS [Faulty Fixing]
, Sum(IIf([faultcode]='Rivet Missing',1,0)) AS [Rivet Missing]
, Sum(IIf([faultcode]='Literature Missing',1,0)) AS [Literature missing]
, Sum(IIf([faultcode]='Data Plate Issue',1,0)) AS [Data Plate Issue]
, Sum(IIf([faultcode]='Hardware Pack',1,0)) AS [Hardware Pack]
, Sum(IIf([faultcode]='Packaging',1,0)) AS Packaging
FROM [Main Audit Data]
WHERE [Some Date Field] Between [Start Date] and [End Date]