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
|
|||
|
|||
"Query is too complex" Problem
Hello All-
I have the following query: SELECT qryReceivingDateRange.ItemCode, Min(qryReceivingDateRange.LeadTimeEndDate) AS FirstLeadTimeEndDate FROM qryReceivingDateRange GROUP BY qryReceivingDateRange.ItemCode ORDER BY qryReceivingDateRange.ItemCode, Min(qryReceivingDateRange.LeadTimeEndDate); The query will work at some times but not at others. For example, if I choose a date range of one month, th query runs fine and returns the minimum date in the LeadTimeEndDate field (LeadTimeEndDate is a calculated field). However, if I go out to a date range of six weeks, I receive the error message that my query is too complex. However...again, if I use the same six week period and enter one item code as criteria, it will work pulling up only that item code and returning the minimum date in the LeadTimeEndDate field. Can anyone shed some light on to this error message for me and maybe why it works sometimes but not others? Thank you so much. |
#2
|
|||
|
|||
"Query is too complex" Problem
The query that is too complex is probably qryReceivingDateRange and you have
not posted that query. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County AccessIM wrote: Hello All- I have the following query: SELECT qryReceivingDateRange.ItemCode, Min(qryReceivingDateRange.LeadTimeEndDate) AS FirstLeadTimeEndDate FROM qryReceivingDateRange GROUP BY qryReceivingDateRange.ItemCode ORDER BY qryReceivingDateRange.ItemCode, Min(qryReceivingDateRange.LeadTimeEndDate); The query will work at some times but not at others. For example, if I choose a date range of one month, th query runs fine and returns the minimum date in the LeadTimeEndDate field (LeadTimeEndDate is a calculated field). However, if I go out to a date range of six weeks, I receive the error message that my query is too complex. However...again, if I use the same six week period and enter one item code as criteria, it will work pulling up only that item code and returning the minimum date in the LeadTimeEndDate field. Can anyone shed some light on to this error message for me and maybe why it works sometimes but not others? Thank you so much. |
#3
|
|||
|
|||
"Query is too complex" Problem
In addition, why ORDER BY the Min(...) column since there should only be one
record per ItemCode? -- Duane Hookom Microsoft Access MVP "John Spencer" wrote: The query that is too complex is probably qryReceivingDateRange and you have not posted that query. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County AccessIM wrote: Hello All- I have the following query: SELECT qryReceivingDateRange.ItemCode, Min(qryReceivingDateRange.LeadTimeEndDate) AS FirstLeadTimeEndDate FROM qryReceivingDateRange GROUP BY qryReceivingDateRange.ItemCode ORDER BY qryReceivingDateRange.ItemCode, Min(qryReceivingDateRange.LeadTimeEndDate); The query will work at some times but not at others. For example, if I choose a date range of one month, th query runs fine and returns the minimum date in the LeadTimeEndDate field (LeadTimeEndDate is a calculated field). However, if I go out to a date range of six weeks, I receive the error message that my query is too complex. However...again, if I use the same six week period and enter one item code as criteria, it will work pulling up only that item code and returning the minimum date in the LeadTimeEndDate field. Can anyone shed some light on to this error message for me and maybe why it works sometimes but not others? Thank you so much. |
#4
|
|||
|
|||
"Query is too complex" Problem
Hi John-
I'm sorry for the delay but I was having a lot of problems replying to your response and then I was pulled away to work on a different project. All other queries work fine regardless of the date range I choose. Here is the SQL of qryReceivingDateRange: ELECT qryMA302.RcvdDate, qryMA302.[PO#], qryMA302.[Seq#], qryMA302.[Vendor#], qryMA302.VendorName, qryMA302.Vendor_Lead_Time, qryMA302.ItemCode, qryMA302.Whse_Zone, qryMA302.OrderQty, qryMA302.RcvdQty, qryMA302.LeadTimeEndDate FROM qryMA302 WHERE (((qryMA302.RcvdDate) Between forms!frmMainMenu!BeginningDate And forms!frmMainMenu!EndingDate)); And here is the SQL for qryMA302 which the query above uses: SELECT tblMA302.RcvdDate, dbo_vw_ItemFileMRDC.Vendor_Lead_Time, DateAdd("d",[Vendor_Lead_Time],[RcvdDate]) AS LeadTimeEndDate, tblMA302.[PO#], tblMA302.[Seq#], tblMA302.[Vendor#], tblMA302.VendorName, dbo_vw_ItemFileMRDC.Buyer, tblMA302.ItemCode, dbo_vw_ItemFileMRDC.Whse_Zone, dbo_vw_ItemFileMRDC.Brand, dbo_vw_ItemFileMRDC.Description, dbo_vw_ItemFileMRDC.Item_Pack, dbo_vw_ItemFileMRDC.Vendor_Pack, tblMA302.OrderQty, tblMA302.RcvdQty, tblMY508.WhseTie, tblMY508.WhseTier FROM tblMY508 RIGHT JOIN (tblMA302 LEFT JOIN dbo_vw_ItemFileMRDC ON tblMA302.ItemCode = dbo_vw_ItemFileMRDC.Item_Code) ON tblMY508.ItemCode = tblMA302.ItemCode ORDER BY tblMA302.RcvdDate, tblMA302.[PO#], tblMA302.[Seq#], tblMA302.ItemCode; "John Spencer" wrote: The query that is too complex is probably qryReceivingDateRange and you have not posted that query. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County AccessIM wrote: Hello All- I have the following query: SELECT qryReceivingDateRange.ItemCode, Min(qryReceivingDateRange.LeadTimeEndDate) AS FirstLeadTimeEndDate FROM qryReceivingDateRange GROUP BY qryReceivingDateRange.ItemCode ORDER BY qryReceivingDateRange.ItemCode, Min(qryReceivingDateRange.LeadTimeEndDate); The query will work at some times but not at others. For example, if I choose a date range of one month, th query runs fine and returns the minimum date in the LeadTimeEndDate field (LeadTimeEndDate is a calculated field). However, if I go out to a date range of six weeks, I receive the error message that my query is too complex. However...again, if I use the same six week period and enter one item code as criteria, it will work pulling up only that item code and returning the minimum date in the LeadTimeEndDate field. Can anyone shed some light on to this error message for me and maybe why it works sometimes but not others? Thank you so much. |
Thread Tools | |
Display Modes | |
|
|