2 Identical queries, 1 works......

  • Thread starter Thread starter Steven Phillips via AccessMonster.com
  • Start date Start date
S

Steven Phillips via AccessMonster.com

Hi,
Can you please advise why one of these will work and the other won't. The
first does at it should.

1)
SELECT [Advanced Matls OEE SUB QUERY].[ADV MAT MACHINE], Sum([Advanced
Matls OEE SUB QUERY].[SumOfRun Hrs]) AS [SumOfSumOfRun Hrs], Avg(
[PERFORMANCE]/[SUMOFRUN HRS]) AS [OEE PERFORMANCE]
FROM ([Machie List ADV MAT] INNER JOIN [Advanced Matls OEE Quality Query]
ON [Machie List ADV MAT].Machine = [Advanced Matls OEE Quality Query].[ADV
MAT MACHINE]) INNER JOIN [Advanced Matls OEE SUB QUERY] ON [Machie List ADV
MAT].Machine = [Advanced Matls OEE SUB QUERY].[ADV MAT MACHINE]
GROUP BY [Advanced Matls OEE SUB QUERY].[ADV MAT MACHINE], [Advanced Matls
OEE Quality Query].PERFORMANCE;

2)
SELECT [KYON OEE SUB QUERY].[KYON Machine], Sum([KYON OEE SUB QUERY].
[SumOfRun Hrs]) AS [SumOfSumOfRun Hrs], [KYON OEE Quality Query].[OEE
Quality], Avg([performance]/[SumOfRun Hrs]) AS [OEE PERFORMANCE]
FROM [KYON OEE SUB QUERY], [KYON OEE Quality Query]
GROUP BY [KYON OEE SUB QUERY].[KYON Machine], [KYON OEE Quality Query].[OEE
Quality], [KYON OEE Quality Query].performance;
 
Hi Steven,

Well, those hardly seem like identical queries, but I'm guessing that you
meant they are trying to do similar things.

You didn't mention what type of error message or erroneous result you are
getting, but I would guess that it has to do with the fact that the second
query does not define any relationship between the tables (which will provide
a cartesian product, every combination of records from both tables).

Take a look at the first query's FROM clause, note that it defines the type
of join between the tables, and is followed by the ON clause to define which
fields are related between the two tables. The second query just lists the
two tables, no join type or ON clause.

HTH, Ted Allen
 
Hi Ted,
Forgive my oversight, I don't how I managed it but I did. Here is the
correct SQL for the seond querie that doesn't work, it should appear more
"identical" except for the machine references.

SELECT [KYON OEE SUB QUERY].[KYON MACHINE], Sum([KYON OEE SUB QUERY].
[SumOfRun Hrs]) AS [SumOfSumOfRun Hrs], Avg([PERFORMANCE]/[SUMOFRUN HRS])
AS [OEE PERFORMANCE]
FROM ([Machine List KYON] INNER JOIN [KYON OEE Quality Query] ON [Machine
List KYON].[Machine]=[KYON OEE Quality Query].[KYON MACHINE]) INNER JOIN
[KYON OEE SUB QUERY] ON [Machine List KYON].[Machine]=[KYON OEE SUB QUERY].
[KYON MACHINE]
GROUP BY [KYON OEE SUB QUERY].[KYON MACHINE], [KYON OEE Quality Query].
[PERFORMANCE];

The error states that the expression is typed incorrectly or is to complex
to be evaluated...
 
Nothing jumps out at me offhand, but to be on the safe side I would fully
qualify all field references with the table/query names. Most are already
included, but there are a few such as those used in
Avg([PERFORMANCE]/[SUMOFRUN HRS]) that are not.

It seems a little suspicious that some field names, such as [SUMOFRUN HRS]
are lower case in some references, and all caps in others. That may be a
sign that the field name is not typed correctly, that it exists in more than
one table, or that Access is not recognizing it (otherwise I think Access
would normally automatically change it).

I would add all table references before the field names without them, then
double check the field names to make sure that they are accurate, and see if
that works.

If that doesn't work, I would probably try to recreate the query in the
design grid of a new access query to see if that gets around whatever bug
there is in the code.

HTH, Ted Allen
 
Back
Top