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
|
|||
|
|||
Using the date on one form to limit the records from another table
How do I limit the records in a query to a certain month? Example: I want to
know how many records (count) there are in a table for the month of September 2009 from one table - but use the date on a form from another table. Table 1: Complaints Table 2: CostOfQuality Table 1: Date Field: "ComplaintDate" Table 2: Date Field: "Month" (always entered as the first day of the month like 9/1/09.) Form: frm852COQ The report uses Table2 as the record source (the current record on frm852COQ) I need to total the number of complaints from table 1 for the current month and display the count on the report. Any help is appreciated. Thanks. |
#2
|
|||
|
|||
Using the date on one form to limit the records from another table
Try this --
SELECT [Forms]![frm852COQ]![Month], Count([ComplaintDate]) AS Complaints FROM Complaints WHERE DateSerial(Year([ComplaintDate]), Month([ComplaintDate]), 1) = [Forms]![frm852COQ]![Month] GROUP BY [Forms]![frm852COQ]![Month]; -- Build a little, test a little. "Bob Waggoner" wrote: How do I limit the records in a query to a certain month? Example: I want to know how many records (count) there are in a table for the month of September 2009 from one table - but use the date on a form from another table. Table 1: Complaints Table 2: CostOfQuality Table 1: Date Field: "ComplaintDate" Table 2: Date Field: "Month" (always entered as the first day of the month like 9/1/09.) Form: frm852COQ The report uses Table2 as the record source (the current record on frm852COQ) I need to total the number of complaints from table 1 for the current month and display the count on the report. Any help is appreciated. Thanks. |
#3
|
|||
|
|||
Using the date on one form to limit the records from another table
Bob
That will depend ... (doesn't it always!G) If your table holds ONLY a single year's records (NOTE: not recommended!), then finding "September"'s records is easy to handle several different ways, including the approach Karl offers. If your table holds many years' records, do you REALLY want to see (all) Septembers' records, or only for a particular year? "How" depends on "what"... Good luck! Regards Jeff Boyce Microsoft Access MVP "Bob Waggoner" wrote in message ... How do I limit the records in a query to a certain month? Example: I want to know how many records (count) there are in a table for the month of September 2009 from one table - but use the date on a form from another table. Table 1: Complaints Table 2: CostOfQuality Table 1: Date Field: "ComplaintDate" Table 2: Date Field: "Month" (always entered as the first day of the month like 9/1/09.) Form: frm852COQ The report uses Table2 as the record source (the current record on frm852COQ) I need to total the number of complaints from table 1 for the current month and display the count on the report. Any help is appreciated. Thanks. |
#4
|
|||
|
|||
Using the date on one form to limit the records from another t
I haven't been able to get Karl's approach to work. My database holds records
from all years. In the Cost of Quality (CoQ) table, one record = one month. We date the month 9/1/09, 10/1/09, and so on. I just want to call up the number of complaints (total number) from the Complaints table that match the COQ Table's current record (current month). With Karl's approach, I can remove the "[Forms]![frm852COQ]![Month] and get all the complaints in the table (total). With that criteria I get zero. I need to find the right date field/format that matches the complaint table's september (09) results for example. [The current record could be 8/1/09 in the CoQ table - which means I want to find the total number of complaints in the complaints table for August 09.] Hope this explains it better and thanks. "Jeff Boyce" wrote: Bob That will depend ... (doesn't it always!G) If your table holds ONLY a single year's records (NOTE: not recommended!), then finding "September"'s records is easy to handle several different ways, including the approach Karl offers. If your table holds many years' records, do you REALLY want to see (all) Septembers' records, or only for a particular year? "How" depends on "what"... Good luck! Regards Jeff Boyce Microsoft Access MVP "Bob Waggoner" wrote in message ... How do I limit the records in a query to a certain month? Example: I want to know how many records (count) there are in a table for the month of September 2009 from one table - but use the date on a form from another table. Table 1: Complaints Table 2: CostOfQuality Table 1: Date Field: "ComplaintDate" Table 2: Date Field: "Month" (always entered as the first day of the month like 9/1/09.) Form: frm852COQ The report uses Table2 as the record source (the current record on frm852COQ) I need to total the number of complaints from table 1 for the current month and display the count on the report. Any help is appreciated. Thanks. |
#5
|
|||
|
|||
Using the date on one form to limit the records from another t
Bob
It sounds like you are saying that you are storing "dates" as 'first day of month' (e.g. "9/1/09", "10/1/09", ...). Does that mean you are storing a text string (in a text-typed field), or are storing actual dates in a date/time field, and chosing to display (i.e., "format") the dates as "9/1/09", ...? How will depend on what!! And since you want to find all records in August '09 (or some other month/year combination), you'll want to use a selection criterion in a query that limits returned records to those within that month/year, right? One way to do this, if you are actually using MS Access' built-in date/time data type (rather than a text representation) would be to use the "Between .... And ..." selection criterion/statement. To do that, your in-query selection criterion for your date field might look like (untested): Between DateSerial([Which Year?], [Which Month (#)?],1) And DateSerial([WhichYear?], [Which Month (#)?] + 1, 0) That second DateSerial() expression has the effect of adding one month, then taking the "zeroth" day (the day before the first of next month = the last day of this month). Good luck! -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Bob Waggoner" wrote in message ... I haven't been able to get Karl's approach to work. My database holds records from all years. In the Cost of Quality (CoQ) table, one record = one month. We date the month 9/1/09, 10/1/09, and so on. I just want to call up the number of complaints (total number) from the Complaints table that match the COQ Table's current record (current month). With Karl's approach, I can remove the "[Forms]![frm852COQ]![Month] and get all the complaints in the table (total). With that criteria I get zero. I need to find the right date field/format that matches the complaint table's september (09) results for example. [The current record could be 8/1/09 in the CoQ table - which means I want to find the total number of complaints in the complaints table for August 09.] Hope this explains it better and thanks. "Jeff Boyce" wrote: Bob That will depend ... (doesn't it always!G) If your table holds ONLY a single year's records (NOTE: not recommended!), then finding "September"'s records is easy to handle several different ways, including the approach Karl offers. If your table holds many years' records, do you REALLY want to see (all) Septembers' records, or only for a particular year? "How" depends on "what"... Good luck! Regards Jeff Boyce Microsoft Access MVP "Bob Waggoner" wrote in message ... How do I limit the records in a query to a certain month? Example: I want to know how many records (count) there are in a table for the month of September 2009 from one table - but use the date on a form from another table. Table 1: Complaints Table 2: CostOfQuality Table 1: Date Field: "ComplaintDate" Table 2: Date Field: "Month" (always entered as the first day of the month like 9/1/09.) Form: frm852COQ The report uses Table2 as the record source (the current record on frm852COQ) I need to total the number of complaints from table 1 for the current month and display the count on the report. Any help is appreciated. Thanks. |
#6
|
|||
|
|||
Using the date on one form to limit the records from another t
Jeff,
you are correct. We simply use the first day of the month to indicate what month it is. 9/1/09 = September 09. We only make one entry each month. It is a date field. Thanks Bob "Jeff Boyce" wrote: Bob It sounds like you are saying that you are storing "dates" as 'first day of month' (e.g. "9/1/09", "10/1/09", ...). Does that mean you are storing a text string (in a text-typed field), or are storing actual dates in a date/time field, and chosing to display (i.e., "format") the dates as "9/1/09", ...? How will depend on what!! And since you want to find all records in August '09 (or some other month/year combination), you'll want to use a selection criterion in a query that limits returned records to those within that month/year, right? One way to do this, if you are actually using MS Access' built-in date/time data type (rather than a text representation) would be to use the "Between .... And ..." selection criterion/statement. To do that, your in-query selection criterion for your date field might look like (untested): Between DateSerial([Which Year?], [Which Month (#)?],1) And DateSerial([WhichYear?], [Which Month (#)?] + 1, 0) That second DateSerial() expression has the effect of adding one month, then taking the "zeroth" day (the day before the first of next month = the last day of this month). Good luck! -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Bob Waggoner" wrote in message ... I haven't been able to get Karl's approach to work. My database holds records from all years. In the Cost of Quality (CoQ) table, one record = one month. We date the month 9/1/09, 10/1/09, and so on. I just want to call up the number of complaints (total number) from the Complaints table that match the COQ Table's current record (current month). With Karl's approach, I can remove the "[Forms]![frm852COQ]![Month] and get all the complaints in the table (total). With that criteria I get zero. I need to find the right date field/format that matches the complaint table's september (09) results for example. [The current record could be 8/1/09 in the CoQ table - which means I want to find the total number of complaints in the complaints table for August 09.] Hope this explains it better and thanks. "Jeff Boyce" wrote: Bob That will depend ... (doesn't it always!G) If your table holds ONLY a single year's records (NOTE: not recommended!), then finding "September"'s records is easy to handle several different ways, including the approach Karl offers. If your table holds many years' records, do you REALLY want to see (all) Septembers' records, or only for a particular year? "How" depends on "what"... Good luck! Regards Jeff Boyce Microsoft Access MVP "Bob Waggoner" wrote in message ... How do I limit the records in a query to a certain month? Example: I want to know how many records (count) there are in a table for the month of September 2009 from one table - but use the date on a form from another table. Table 1: Complaints Table 2: CostOfQuality Table 1: Date Field: "ComplaintDate" Table 2: Date Field: "Month" (always entered as the first day of the month like 9/1/09.) Form: frm852COQ The report uses Table2 as the record source (the current record on frm852COQ) I need to total the number of complaints from table 1 for the current month and display the count on the report. Any help is appreciated. Thanks. |
Thread Tools | |
Display Modes | |
|
|