Does not recognise?

[Guest]

Hi

I have a query that looks for a certain worktype fields, builderswork, main
contractor issue, electrical and mechanical. However, if I run the query and
none of the above have a record associated with it I get the error - Jet
Database does not recognise eg ' builderswork'.

Is there anyway round this.

Any help would be great.

Query is below.

Cheers,

Phil

SELECT [Killbys Grand Total].Quarter, [qry: Projects Per Quarter (Killby &
Gayford)].[CountOfProject Code] AS Projects, [Killbys Grand Total].[Grand
Total] AS [No Items], [Killbys Grand Total.Grand Total]/[qry: Projects Per
Quarter (Killby & Gayford).CountOfProject Code] AS [Average Per Project],
[Average Priority]+[Average Variance to Target Timescale] AS [Average Time
Per Item], [qry: Variance to Timescale Killbys (New)2].[Average Variance to
Target Timescale], [qry: Average Times Killbys (New)2].[AvgOfPriority (In
Days)] AS [Average Priority]
FROM [Killbys Grand Total] INNER JOIN (([qry: Average Times Killbys (New)2]
INNER JOIN [qry: Variance to Timescale Killbys (New)2] ON [qry: Average Times
Killbys (New)2].[Quarter]=[qry: Variance to Timescale Killbys
(New)2].[Quarter]) INNER JOIN [qry: Projects Per Quarter (Killby & Gayford)]
ON [qry: Variance to Timescale Killbys (New)2].[Quarter]=[qry: Projects Per
Quarter (Killby & Gayford)].[Quarter]) ON [Killbys Grand Total].Quarter=[qry:
Average Times Killbys (New)2].[Quarter]
GROUP BY [Killbys Grand Total].Quarter, [qry: Projects Per Quarter (Killby &
Gayford)].[CountOfProject Code], [Killbys Grand Total].[Grand Total],
[Killbys Grand Total.Grand Total]/[qry: Projects Per Quarter (Killby &
Gayford).CountOfProject Code], [qry: Variance to Timescale Killbys
(New)2].[Average Variance to Target Timescale], [qry: Average Times Killbys
(New)2].[AvgOfPriority (In Days)];

 

[Jeff Boyce]

?Where's the "worktype field" in the query?

When you say "worktype fields", does this mean you have more than one field
in your table to define worktype? What happens if "workstype" is more than
one (i.e., both builderswork and electrical)?

 

[Guest]

Hi,
The wortypetype field is calculated in a Grand Total query. It sums a count
of the worktype grouped by quarter.
There is one worktype field and the record can only be either builderswork
or electrical etc.

In the Grand Total query I have used the Nz function to account for no
instances of a specific worktype.

Cheers,

Phil

?Where's the "worktype field" in the query?

When you say "worktype fields", does this mean you have more than one field
in your table to define worktype? What happens if "workstype" is more than
one (i.e., both builderswork and electrical)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi

I have a query that looks for a certain worktype fields, builderswork, main
contractor issue, electrical and mechanical. However, if I run the query and
none of the above have a record associated with it I get the error - Jet
Database does not recognise eg ' builderswork'.

Is there anyway round this.

Any help would be great.

Query is below.

Cheers,

Phil

SELECT [Killbys Grand Total].Quarter, [qry: Projects Per Quarter (Killby &
Gayford)].[CountOfProject Code] AS Projects, [Killbys Grand Total].[Grand
Total] AS [No Items], [Killbys Grand Total.Grand Total]/[qry: Projects Per
Quarter (Killby & Gayford).CountOfProject Code] AS [Average Per Project],
[Average Priority]+[Average Variance to Target Timescale] AS [Average Time
Per Item], [qry: Variance to Timescale Killbys (New)2].[Average Variance to
Target Timescale], [qry: Average Times Killbys (New)2].[AvgOfPriority (In
Days)] AS [Average Priority]
FROM [Killbys Grand Total] INNER JOIN (([qry: Average Times Killbys (New)2]
INNER JOIN [qry: Variance to Timescale Killbys (New)2] ON [qry: Average Times
Killbys (New)2].[Quarter]=[qry: Variance to Timescale Killbys
(New)2].[Quarter]) INNER JOIN [qry: Projects Per Quarter (Killby & Gayford)]
ON [qry: Variance to Timescale Killbys (New)2].[Quarter]=[qry: Projects Per
Quarter (Killby & Gayford)].[Quarter]) ON [Killbys Grand Total].Quarter=[qry:
Average Times Killbys (New)2].[Quarter]
GROUP BY [Killbys Grand Total].Quarter, [qry: Projects Per Quarter (Killby &
Gayford)].[CountOfProject Code], [Killbys Grand Total].[Grand Total],
[Killbys Grand Total.Grand Total]/[qry: Projects Per Quarter (Killby &
Gayford).CountOfProject Code], [qry: Variance to Timescale Killbys
(New)2].[Average Variance to Target Timescale], [qry: Average Times Killbys
(New)2].[AvgOfPriority (In Days)];

 

[Jeff Boyce]

Phil

I'm still in the dark here. I don't see how your SQL statement relates to
what you are trying to do.

Have you stepped back from the more complex to try simpler queries first?

Where is the worktype field in your query?

Jeff Boyce
<Access MVP>

Hi,
The wortypetype field is calculated in a Grand Total query. It sums a count
of the worktype grouped by quarter.
There is one worktype field and the record can only be either builderswork
or electrical etc.

In the Grand Total query I have used the Nz function to account for no
instances of a specific worktype.

Cheers,

Phil

?Where's the "worktype field" in the query?

When you say "worktype fields", does this mean you have more than one field
in your table to define worktype? What happens if "workstype" is more than
one (i.e., both builderswork and electrical)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi

I have a query that looks for a certain worktype fields, builderswork, main
contractor issue, electrical and mechanical. However, if I run the

query
and

none of the above have a record associated with it I get the error - Jet
Database does not recognise eg ' builderswork'.

Is there anyway round this.

Any help would be great.

Query is below.

Cheers,

Phil

SELECT [Killbys Grand Total].Quarter, [qry: Projects Per Quarter (Killby &
Gayford)].[CountOfProject Code] AS Projects, [Killbys Grand Total].[Grand
Total] AS [No Items], [Killbys Grand Total.Grand Total]/[qry: Projects Per
Quarter (Killby & Gayford).CountOfProject Code] AS [Average Per Project],
[Average Priority]+[Average Variance to Target Timescale] AS [Average Time
Per Item], [qry: Variance to Timescale Killbys (New)2].[Average

Variance
to

Target Timescale], [qry: Average Times Killbys (New)2].[AvgOfPriority (In
Days)] AS [Average Priority]
FROM [Killbys Grand Total] INNER JOIN (([qry: Average Times Killbys (New)2]
INNER JOIN [qry: Variance to Timescale Killbys (New)2] ON [qry:

Average
Times

Killbys (New)2].[Quarter]=[qry: Variance to Timescale Killbys
(New)2].[Quarter]) INNER JOIN [qry: Projects Per Quarter (Killby & Gayford)]
ON [qry: Variance to Timescale Killbys (New)2].[Quarter]=[qry:

Projects
Per

Quarter (Killby & Gayford)].[Quarter]) ON [Killbys Grand Total].Quarter=[qry:
Average Times Killbys (New)2].[Quarter]
GROUP BY [Killbys Grand Total].Quarter, [qry: Projects Per Quarter

(Killby
&

Gayford)].[CountOfProject Code], [Killbys Grand Total].[Grand Total],
[Killbys Grand Total.Grand Total]/[qry: Projects Per Quarter (Killby &
Gayford).CountOfProject Code], [qry: Variance to Timescale Killbys
(New)2].[Average Variance to Target Timescale], [qry: Average Times Killbys
(New)2].[AvgOfPriority (In Days)];