If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access crashes on a subquery
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 |
#2
|
|||
|
|||
Access crashes on a subquery
(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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200612/1 |
#3
|
|||
|
|||
Access crashes on a subquery
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 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/subscripti...ult.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. |
#4
|
|||
|
|||
Access crashes on a subquery
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 "taurus via AccessMonster.com" u30124@uwe wrote in message news:6aa0df6dc7d04@uwe... (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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200612/1 |
#5
|
|||
|
|||
Access crashes on a subquery
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 "Peter Yang [MSFT]" wrote in message ... 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 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/subscripti...ult.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. |
#6
|
|||
|
|||
Access crashes on a subquery
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))); -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Access crashes on a subquery
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. ================================================== ==== |
#8
|
|||
|
|||
Access crashes on a subquery
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 "taurus via AccessMonster.com" u30124@uwe wrote in message news:6aa925dbb1edf@uwe... 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))); -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|