A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using the date on one form to limit the records from another table



 
 
Thread Tools Display Modes
  #1  
Old October 9th, 2009, 03:41 PM posted to microsoft.public.access.tablesdbdesign
Bob Waggoner[_2_]
external usenet poster
 
Posts: 80
Default 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  
Old October 9th, 2009, 04:13 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old October 9th, 2009, 05:36 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old October 10th, 2009, 03:55 AM posted to microsoft.public.access.tablesdbdesign
Bob Waggoner[_2_]
external usenet poster
 
Posts: 80
Default 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  
Old October 10th, 2009, 02:32 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default 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  
Old October 22nd, 2009, 05:01 AM posted to microsoft.public.access.tablesdbdesign
Bob Waggoner[_2_]
external usenet poster
 
Posts: 80
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.