Re: "Identical" query SQL Throws Error

First, Allen, thanks for your interest and persistence in trying to help me
through this.

In the course of yesterday I made a change that resulted in the second query
(strSQL2) running correctly, but I'm completely sure why, so if you have
time, maybe you can explain it to me and others who might encounter something
similar.

As you saw, in the second query I was trying to set up a filter on the MYear
value (e.g., 2006) to be sure I was only getting one year's worth of data.
The second query of course got that value from the first query, and the first
query got it from tblSPIData. My "fix" involved adding MasterCal back into
the second query as a left join to tblSPIData, and setting up the filter to
use the MYear value from MasterCal rather than the one in tblSPIData.
Presto, Jet was happy and the query ran as intended. Go figure. I can't
understand how that added anything significant to the earlier SQL statement,
but obviously Jet did.

If you can shed any light, it'd be welcome. "It works now!" is good, "It
works now because...!" is better.














"Allen Browne" wrote:

Okay, Larry, I can't see any good reason why this won't work, but that's to
be expected given than one query worked anyway. We are therefore trying to
identify where JET gets stuck, and help it out.

JET queries run differently if a field is indexed. Check the Indexed
property of Factory and Team, and see if it helps if they are both set the
same as the one that works.

JET finds INNER JOINs easier than outer. The criteria on the Factory field
in the lower level query will have the effect of excluding nulls, so you
could try an inner join, i.e.:
FROM (tblEndItem INNER JOIN tblSPIData ON ...

Since the upper-level query has criteria on MYear, I think it would have the
effect on being an inner join as well.

Are both the data type and the field size identical on the joined fields?
For example, is YYYYWW a Number field of size Long Integer, and also
MWeekSort is a Number field of size Long Integer?

Do you have relationships (with enforced referential integrity) between
these tables? (It helps.)

If none of that helps, the next level would be to see how the query
statement is being used. Presumably you open an ADO or DAO recordset, or
perhaps you assign it to the RecordSource of a form or Report, or to the SQL
of a QueryDef, or perhaps even the Recorset of a form. If the query itself
works, but the assignment fails, there could be something else going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LarryP" <LarryP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:94F96032-336C-4BF1-8507-17A535D3B523@xxxxxxxxxxxxxxxx
Here ya go. This is the "variant" that corresponds to strSQL1; for
strSQL2
the only difference is that everywhere where this one says "Factory", that
one says "Team". FYI, the values being pulled from table MasterCal are
the
MWeek, e.g., 34, the MYear, e.g., 2006, and MWeekSort, e.g., 200634.

SELECT DISTINCT tblEndItem.Factory, MasterCal.MWeek AS WW,
tblSPIData.YYYYWW, tblSPIData.SchedSales, tblSPIData.ActualSales,
tblSPIData.[P/D], MasterCal.MYear
FROM (tblEndItem LEFT JOIN tblSPIData ON (tblEndItem.Factory =
tblSPIData.Factory) AND (tblEndItem.Team = tblSPIData.Team)) LEFT JOIN
MasterCal ON tblSPIData.YYYYWW = MasterCal.MWeekSort
WHERE (((tblEndItem.Factory)='CRYOGENICS'))
ORDER BY tblEndItem.Factory, MasterCal.MWeek;


"Allen Browne" wrote:

Post the lower-level query, Larry.

Needs another compact after turning off those Name AutoCorrupt options.

(It might be 24 hr before I can get back to this, or perhaps someone else
will comment.)

"LarryP" <LarryP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5E428401-5597-4188-89B6-E18218616B79@xxxxxxxxxxxxxxxx
The lower-level query is identical as well, other than the Team vs.
Factory
thing. Both the Team and the Factory are 50-character text fields, and
none
of the values even approach 50 characters.

I have indeed compacted and repaired; wasn't familiar with the Name
AutoCorrect option, but just tried that and it made no apparent
difference.

"Allen Browne" wrote:

Larry, it looks like the queries feed from another query, so the
grouping
problem could reflect what's going on at the lower level.

What data type and size is the Team field?

Presumably you have already unchecked the boxes under:
Tools | Options | General | Name AutoCorrect
and compacted the database:
Tools | Database Utilities | Compact/Repair

"LarryP" <LarryP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:182E8127-B28C-4CA1-BB15-1CD999A734B7@xxxxxxxxxxxxxxxx
Quoted below are two SQL strings from querydefs that are supposed to
be
identical except that in one the grouping is on Factory, in the
other
it's
on
Team. The first query runs fine. The second one, however, throws
one
of
those mysterious "sorry, we have to shut down, should we tell
Microsoft
about
it?" errors.

Team vs. Factory is the only obvious difference, but the only way
I've
found
to prevent the error from occurring is to take out the WHERE clause
in
the
second SQL string, after which it runs fine except that of course I
am
no
longer selecting for a particular year. (In both strings the WHERE
is
using
a value stored on a form which holds a numeric value signifying a
particular
year, e.g., 2006.)

This is MADDENING! Anybody see what I'm missing?

THE SQL:

strSQL1 = "SELECT qryGetGraphDataPrequery.Factory,
qryGetGraphDataPrequery.WW,
Sum(qryGetGraphDataPrequery.SchedSales) AS Goal,
Sum(qryGetGraphDataPrequery.ActualSales) AS Actual,
Sum(qryGetGraphDataPrequery.[P/D]) AS [Past Due]
FROM qryGetGraphDataPrequery
WHERE MYear = " & Me!txtThisYear & "
GROUP BY qryGetGraphDataPrequery.Factory,
qryGetGraphDataPrequery.WW
ORDER BY qryGetGraphDataPrequery.Factory,
qryGetGraphDataPrequery.WW;"

strSQL2 = "SELECT qryGetGraphDataPrequery.Team,
qryGetGraphDataPrequery.WW,
Sum(qryGetGraphDataPrequery.SchedSales) AS Goal,
Sum(qryGetGraphDataPrequery.ActualSales) AS Actual,
Sum(qryGetGraphDataPrequery.[P/D]) AS [Past Due]
FROM qryGetGraphDataPrequery
WHERE MYear = " & Me!txtThisYear & "
GROUP BY qryGetGraphDataPrequery.Team,
qryGetGraphDataPrequery.WW
ORDER BY qryGetGraphDataPrequery.Team,
qryGetGraphDataPrequery.WW;"



.

Relevant Pages

  • Re: "Identical" query SQL Throws Error
    ... be expected given than one query worked anyway. ... The criteria on the Factory field ... FROM (tblEndItem INNER JOIN tblSPIData ON ... ... Needs another compact after turning off those Name AutoCorrupt options. ...
    (microsoft.public.access.modulesdaovba)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)
Loading