Access crashes on a subquery

[Carl Rapson]

I've got a table with the following structure (I realize it's not properly
normalized, but I plan to do that after I get this problem solved):

tblRatingData
-------------
[Vendor ID], Long
[Rating Date], Date/Time
[Full], Yes/No
[Quality Rating], Double
[Delivery Rating], Double

The field [Full] is set to Yes (True) if the [Rating Date] corresponds to a
full rating recalculation (individual vendors can have their ratings
recalculated at any time; a full recalculation does all vendors at the same
time). My basic goal is to pull from the table the ratings for all vendors
from the last full rating recalculation date. I have this working, with the
following set of queries:

Query1
------
SELECT MAX(tblRatingData.[Rating Date]) AS [Last Full Rating Date]
FROM tblRatingData WHERE tblRatingData.Full=True;

Query2
------
SELECT tblRatingData.[Vendor ID],tblRatingData.[Rating
Date],tblRatingData.[Full],tblRatingData.[Quality
Rating],tblRatingData.[Delivery Rating]
FROM tblRatingData INNER JOIN Query1 ON tblRatingData.[Rating
Date]=Query1.[Last Full Rating Date];

So far, so good. What I would like to do now is add to my data set another
field containing the number of records in tblRatingData for each vendor with
a [Rating Date] greater than the last full rating date I'm working with, if
that's clear. That will tell me if the vendor has had its ratings
recalculated since the last Full rating calculation. I've tried to do this
with an embedded subquery, as follows:

Query3
------
SELECT A.[Vendor ID],A.[Rating Date],A.[Full],A.[Quality Rating],A.[Delivery
Rating],(SELECT COUNT(*) FROM tblRatingData WHERE [Vendor ID]=A.[Vendor ID]
AND [Rating Date]>A.[Rating Date]) AS [More Recent]
FROM Query2 A;

Unfortunately, Access crashes with the dreaded 'Microsoft Access has
encountered a problem and needs to close' message when I try to run the
query. Can someone give me a clue as to the syntax for doing something like
this (an embedded subquery as a field)? Is it even possible? I can try to
provide more details if needed.

Thanks for any assistance,

Carl Rapson

 

[taurus via AccessMonster.com]

(I write without spaces) You could combine your first and second query like
qryVendorRatingLastFull:

SELECT tblRatingData.vendorId, tblRatingData.ratingDate, tblRatingData.full,
tblRatingData.qualityRating, tblRatingData.deliveryRating
FROM tblRatingData
WHERE (((tblRatingData.ratingDate) In (SELECT MAX(tblRatingData.[RatingDate])
AS [Last Full Rating Date]
FROM tblRatingData WHERE tblRatingData.Full=True)));

and for the data of vendors updated since last full rating a query like
qryVendorRatingAfterLastFull:

SELECT a.vendorId, a.qualityRating, a.deliveryRating, a.ratingDate
FROM tblRatingData AS a INNER JOIN tblRatingData AS b ON a.vendorId = b.
vendorId
WHERE (((a.full)<>-1))
GROUP BY a.vendorId, a.qualityRating, a.deliveryRating, a.ratingDate
HAVING (((a.ratingDate)>(select max(tblRatingData.ratingDate) from
tblRatingData where tblRatingData.full = -1)));

should work.

 

[Peter Yang [MSFT]]

Hello Carl,

You may want to try taurus's suggestion to see if it works. However, it is
not normal that the 3rd query crashes Access in the first place.

I was not able to reproduce the issue by duplicate the structure of the
table/qurires as you mentioned. I can run the query3 properly on my side
and get the result as you expect if I understand it correctly.

You may want to create a new table and new quires with the same schema to
see if you could reproduce the problem. If the issue still occurs, you may
try a new database and test on a different machine.

Also, please make sure you installed latest Office Service pack on the
machine. What's OS and service pack level of the system?

You may want to follow the steps below to collect a Dr. Watson dump file
for further analysis:

1. Run "drwtsn32" (without the quotation marks) to open the 'Dr. Watson for
Windows' window:
Click start, click Run, type "drwtsn32" in open box.

2. Select the check box next to the Create Crash Dump File option.

3. Search the dump file under the folder indicated by the 'Crash Dump' edit
box. The default directory is "\Documents and Settings\All
Users\Application Data\Microsoft\Dr Watson" (user.dmp).

4. Search the log file under the folder indicated by the 'Log File Path'
edit box. The default directory is "\Documents and Settings\All
Users\Application Data\Microsoft\Dr Watson" (drwtsn32.log).

5. Select the check boxes next to the following options:

Dump All Thread Contexts.
Append to Existing Log File.
Create Crash Dump File.

6. Click OK.

7. Run "drwtsn32 -i" (without the quotation marks) to enable Dr. Watson as
the default debugger.

8. Try to reproduce the problem, when the error message re-appears, please
send the drwtsn32.log to me at (e-mail address removed)

To find out the root cause of this issue we may need to analyze memory
dumps, this work has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If the issue is urgent, I recommend that you open a
Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

I look forward to your reply.
Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Carl Rapson]

taurus,

Thanks for the suggestions. Your first suggestion worked great, with the
added benefit of making the query updatable (although it's not really
necessary in this case). The problem is (I guess it wasn't clear), I need
both queries combined - that is, I need to know in my first query that there
are additional ratings for each vendor. The reason is, this is being used to
populate a continuous form, so all fields have to be part of the query (I
can't have an unbound field with a DCount as its Control Source, for
example). So I need to combine the two queries into a single record set.

However, your code has given me some ideas that I will investigate.
Additionally, Peter (below) gave some suggestions that I will try also.

Thanks again,

Carl Rapson

 

[Carl Rapson]

Peter,

Thanks for the suggestions. I deleted and recreated the queries and also the
table, but Access still crashes when I run the query. I created a new
database and created the table and queries from scratch, but Access still
crashes.

I'm running Access 2002 (10.6771.6817 SP3) on Windows XP (5.1.2600 SP2 Build
2600). I followed the steps you gave to install Dr. Watson as the default
debugger, but when Access crashed the dump and log files weren't created at
the location specified. I was, however, able to get the queries to work as I
need by starting with taurus' suggestion and moving from there, so it's not
a critical situation. If the problem arises again, I'll pursue the Dr.
Watson and product support avenues further.

Thanks again for the help,

Carl Rapson

 

[taurus via AccessMonster.com]

You're welcome. Here is a single query that should work
qryVendorRatingLastFullWithUpdateAfter:

SELECT a.vendorId, a.ratingDate AS lastFullRun, b.ratingDate AS LatestRun, a.
full, a.qualityRating, a.deliveryRating
FROM tblRatingData AS a INNER JOIN tblRatingData AS b ON (a.ratingDate = b.
ratingDate) AND (a.vendorId = b.vendorId)
WHERE (((b.ratingDate)>(SELECT MAX(tblRatingData.[RatingDate])from
tblRatingData where tblRatingData.full=-1))) OR (((a.ratingDate) In (SELECT
MAX(tblRatingData.[RatingDate]) AS [Last Full Rating Date]
FROM tblRatingData WHERE tblRatingData.Full=True)));

 

[Peter Yang [MSFT]]

Hello Carl,

I understand. If you'd like to further troubleshoot the crash issue,
please feel free to post back or call PSS directly.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Carl Rapson]

taurus,

Thanks again. Your suggestions have helped a lot in understanding how to
cross-reference a table with itself to pull out information such as this.

Carl Rapson