Re: Change of field name causes application to crash

Hi, Allen.

Well, this all kind of got me searching through all the underlying queries
in detail, and I seem to have found a way to resolve this. Perhaps you can
just explain to me why this happens. Maybe I'm way out on a limb here, but I
have a six-way union query separated from the query in question by an
intervening query. Here is the six-way union query (see my notes below it):

SELECT LookupInventoryArriveIn.*
FROM LookupInventoryArriveIn;

UNION SELECT LookupInventoryArriveOut.*
FROM LookupInventoryArriveOut;

UNION SELECT LookupInventoryAdjustIn.*
FROM LookupInventoryAdjustIn;

UNION SELECT LookupInventoryAdjustOut.*
FROM LookupInventoryAdjustOut;

UNION SELECT LookupInventoryShipIn.*
FROM LookupInventoryShipIn;

UNION SELECT LookupInventoryShipOut.*
FROM LookupInventoryShipOut;

Based on some reading-up I did on database efficiency that seemed to
indicate that UNION ALL was preferable to just UNION, I originally had all
the UNION statements as UNION ALL. Removing the ALL seems to have resolved
the immediate problem, but:

1. Why?
2. What am I potentially doing to my application by removing the ALL?


"Allen Browne" wrote:

> Hi Brian
>
> You've obviously done some good digging to understand what JET is choking on
> here. I'm assuming that LookupInventoryCombinedNet is the UNION query you
> referred to earlier, which is based on other queries. What we are seeing
> therefore is the tip of the iceberg, with lots of other stuff going on in
> the lower level queries.
>
> It's fairly easy to crash or confuse JET. It often crashes if I use a
> subquery in a lower level query, and then try to use that field in a higher
> level query (e.g. in a JOIN.) Null in the yes/no field also causes crashes.
> The query-to-complex message is often caused by type mismatches, which can
> occur with UNION queries, undeclared parameters, incorrect implicit
> typecasts, and so on.
>
> The fact that the query works when you ArriveNet field is included suggests
> that summing this field also causes JET to take a different execution path,
> which ends up handling ShipNet correctly as well. The default name
> (SumOfShipNet) might hint at the same issue, or at a name confusion. I'm not
> aware of the specifics of what david@epsomdotcomdotau was referring to, but
> his comments are always very good value, so there may be an issue there.
>
> Importing the data into a new mdb (with Name AutoCorrupt turned off
> presumably) was a good move.
>
> I'm guessing that the source of the issue is the way the data is generated
> by the lower level queries. Typecasting there, and avoiding nulls in the
> yes/no results could make a difference when you try to use those results in
> your higher level query.
>
> --
> 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.
>
> "Brian" <Brian@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:1653AC8F-9046-4ABA-9A76-7FCCD20700F2@xxxxxxxxxxxxxxxx
> > Thanks, Allen. This thing is driving me crazy today.
> >
> > #1, 2, & 4: Already the way you suggest
> > #3 I have duplicated the problem with Active left out
> > #5 Haven't specifically tried this, but simply changing the name of the
> > field resolves the problem (a solution that I won't use, at least not yet,
> > because I really don't understand why); furthermore, parallel fields (e.g.
> > ArriveNet, coming through the same sequence of queries to generate the
> > total)
> > in the same report do not cause the crash.
> >
> > After creating a new DB & importing everything (to isolate potential
> > corruption issues), I created a simple Sum query against the underlying
> > query. Note that this works consistently:
> >
> > SELECT LookupInventoryCombinedNet.CommodityID,
> > LookupInventoryCombinedNet.UnitID,
> > Sum(LookupInventoryCombinedNet.ArriveNet)
> > AS ArriveNet, Sum(LookupInventoryCombinedNet.ShipNet) AS ABC
> > FROM LookupInventoryCombinedNet
> > GROUP BY LookupInventoryCombinedNet.CommodityID,
> > LookupInventoryCombinedNet.UnitID;
> >
> > but this generates a "Query is too complex" error (it is actually simpler
> > because I just removed the ArriveNet field from the query):
> >
> > SELECT LookupInventoryCombinedNet.CommodityID,
> > LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
> > AS
> > ABC
> > FROM LookupInventoryCombinedNet
> > GROUP BY LookupInventoryCombinedNet.CommodityID,
> > LookupInventoryCombinedNet.UnitID;
> >
> > and this crashes (allowed AS to default to SumOfShipNet instead of
> > specifying ABC)
> >
> > SELECT LookupInventoryCombinedNet.CommodityID,
> > LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
> > AS
> > SumOfShipNet
> > FROM LookupInventoryCombinedNet
> > GROUP BY LookupInventoryCombinedNet.CommodityID,
> > LookupInventoryCombinedNet.UnitID;
> >
> > This results in "Query too complex" when run from within the query builder
> > but crashes if the query is saved and then double-clicked from the
> > database
> > window:
> >
> > SELECT LookupInventoryCombinedNet.CommodityID,
> > LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
> > AS
> > ABC
> > FROM LookupInventoryCombinedNet
> > GROUP BY LookupInventoryCombinedNet.CommodityID,
> > LookupInventoryCombinedNet.UnitID;
> >
> > "Allen Browne" wrote:
> >
> >> Brian, there's a lot of stuff going on here, but hopefully some of these
> >> suggestions will help.
> >>
> >> 1. Uncheck the boxes under:
> >> Tools | Options | General | Name AutoCorrect
> >> Explanation of why:
> >> http://allenbrowne.com/bug-03.html
> >> Then compact the database again to get rid of this junk:
> >> Tools | Database Utilities | Compact
> >>
> >> 2. Presumably Active is a Yes/No field? Just to be sure Access
> >> understands
> >> this, bracket it. Won't hurt to bracket the other 2 phrases in the
> >> Criteria
> >> argument of DSum() as well, i.e.:
> >> ("[CommodityID] = " & [CommodityID] & ") And ([UnitID] = " & [UnitID] &
> >> ")
> >> And ([Active])"
> >>
> >> 3. As you know, a yes/no field is 2-state only in JET, i.e. it cannot
> >> store
> >> a Null. But if the yes/no field comes from a table that is on the outer
> >> side
> >> of a join, a yes/no field in a query can be Null. I've actually seen this
> >> crash Access (shut down by Windows). Any chance the table that Active is
> >> drawn from is on the outer side of a join? If so, you could probably
> >> solve
> >> the problem with:
> >> SELECT Nz([Table1].[Active], False) AS IsActive, ...
> >>
> >> 4. Presumably CommodityID and UnitID are both Number fields, and there is
> >> no
> >> chance of them being Null (e.g. result of outer join).
> >>
> >> 5. Might need some explicit typecasting in the UNION or other stacked
> >> queries, though the expressions you have suggest you probably understand
> >> how
> >> to do that.
>
>
>
.

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... Using my query names, can you say exactly what my SQL should be ... > my Union Queries short and simple and do the rest of the work elsewhere. ... >>> Subject and Professional Mentors that are allocated to placements. ...
    (microsoft.public.access.queries)
  • Re: Question about Queries with Subqueries
    ... performance reasons to use UNION ALL instead of UNION. ... i have a query i am working on that draws data from 3 different data ... i have a question about queries like this. ... to try "flattening" my queries by using subqueries. ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: Change of field name causes application to crash
    ... UNION ALL returns all records. ... Again, the 6 lower level queries are involved here, so there is a good ... > have a six-way union query separated from the query in question by an ... I'm assuming that LookupInventoryCombinedNet is the UNION query you ...
    (microsoft.public.access.modulesdaovba)
Loading