Form Validation with Multiple Yes/No boxes

[dsc2bjn]

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not selected) in
two separate tables. The "Yes" value records will be stored in my "Closed"
table and the "No" value records will be stored in the "Open" table.

I created a button which will open a pop-up dialog box for the user to enter
a comment. I tried adding the code below to that button, but it only stores
the first No (open) and the first Yes (closed) records. How can I have it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[Jeff Boyce]

It all starts with the data ...

It would be a fairly unusual design for a well-normalized relational
database to keep the "Yes"s in one table and the "No"s in another. That
might be the way you'd have to do it if you were using a spreadsheet, but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying data/table structure,
the folks here in the newsgroup may be able to offer a bit more specific
suggestions. You've described "how" you are trying to do something, but not
"why". What will you be able to do once you know whether a record has a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dsc2bjn]

One of the purposes of the database is to report the status to management of
records as either open or closed.

Closed records must be "accepted" by management before they are counted on
an Executive Summary report.

I have a table with all records (open and closed). The user updates the
records and at some point and will determine the work has been completed and
the record should be closed. Through a data entry form the user changes the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting Period) and
reviews the data and either accepts, requests changes, additional information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary Report.

I need to report how many records as: "Total number", "Open", "Closed" based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however, counting
records that are Open and Closed have proved to be more difficult. The user
determines the "Reporting Period" value in the database, since they may
submit their quarterly update at any time within the quarter. Additionally,
they may need to revise their quarterly report based upon management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR Reporting
Period for past FYs where the record count remaining is greater than zero.
In some cases the records from 1, 2 or 3 years earlier may not have been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record for each
Reporting Period. The Reporting period for each Open record will change from
Quarter to Quarter. My thought was to write the Open and Closed records to
seperate tables at the time managment initiates the Executive Summary Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

It all starts with the data ...

It would be a fairly unusual design for a well-normalized relational
database to keep the "Yes"s in one table and the "No"s in another. That
might be the way you'd have to do it if you were using a spreadsheet, but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying data/table structure,
the folks here in the newsgroup may be able to offer a bit more specific
suggestions. You've described "how" you are trying to do something, but not
"why". What will you be able to do once you know whether a record has a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not selected)
in
two separate tables. The "Yes" value records will be stored in my
"Closed"
table and the "No" value records will be stored in the "Open" table.

I created a button which will open a pop-up dialog box for the user to
enter
a comment. I tried adding the code below to that button, but it only
stores
the first No (open) and the first Yes (closed) records. How can I have it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[Jeff Boyce]

I'm not entirely clear on the data you are keeping/reporting on...

If you are saying you need to know what status a record is in, and when it
achieved that status, it seems like two fields in your "records" table would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to management
of
records as either open or closed.

Closed records must be "accepted" by management before they are counted on
an Executive Summary report.

I have a table with all records (open and closed). The user updates the
records and at some point and will determine the work has been completed
and
the record should be closed. Through a data entry form the user changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting Period) and
reviews the data and either accepts, requests changes, additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary Report.

I need to report how many records as: "Total number", "Open", "Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however, counting
records that are Open and Closed have proved to be more difficult. The
user
determines the "Reporting Period" value in the database, since they may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR Reporting
Period for past FYs where the record count remaining is greater than zero.
In some cases the records from 1, 2 or 3 years earlier may not have been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record for each
Reporting Period. The Reporting period for each Open record will change
from
Quarter to Quarter. My thought was to write the Open and Closed records
to
seperate tables at the time managment initiates the Executive Summary
Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

It all starts with the data ...

It would be a fairly unusual design for a well-normalized relational
database to keep the "Yes"s in one table and the "No"s in another. That
might be the way you'd have to do it if you were using a spreadsheet, but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying data/table
structure,
the folks here in the newsgroup may be able to offer a bit more specific
suggestions. You've described "how" you are trying to do something, but
not
"why". What will you be able to do once you know whether a record has a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not
selected)
in
two separate tables. The "Yes" value records will be stored in my
"Closed"
table and the "No" value records will be stored in the "Open" table.

I created a button which will open a pop-up dialog box for the user to
enter
a comment. I tried adding the code below to that button, but it only
stores
the first No (open) and the first Yes (closed) records. How can I have
it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[dsc2bjn]

I am not sure you are missing anything. I am still trying to wrap my head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the 2nd, 3rd,
4th periods. I loose the ablity to count the previous quarter, since the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end of each
[Reporting Period]. That is why I was thinking about storing the data for
the records at the end of each Reporting Period into the two tables (Open and
Closed).

How did you invision using the [Current Status] and [StatusDate] fields?
Are you suggesting adding them to the table in addition to the [Status] and
[Reporting Period] fields?

I'm not entirely clear on the data you are keeping/reporting on...

If you are saying you need to know what status a record is in, and when it
achieved that status, it seems like two fields in your "records" table would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to management
of
records as either open or closed.

Closed records must be "accepted" by management before they are counted on
an Executive Summary report.

I have a table with all records (open and closed). The user updates the
records and at some point and will determine the work has been completed
and
the record should be closed. Through a data entry form the user changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting Period) and
reviews the data and either accepts, requests changes, additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary Report.

I need to report how many records as: "Total number", "Open", "Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however, counting
records that are Open and Closed have proved to be more difficult. The
user
determines the "Reporting Period" value in the database, since they may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR Reporting
Period for past FYs where the record count remaining is greater than zero.
In some cases the records from 1, 2 or 3 years earlier may not have been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record for each
Reporting Period. The Reporting period for each Open record will change
from
Quarter to Quarter. My thought was to write the Open and Closed records
to
seperate tables at the time managment initiates the Executive Summary
Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

It all starts with the data ...

It would be a fairly unusual design for a well-normalized relational
database to keep the "Yes"s in one table and the "No"s in another. That
might be the way you'd have to do it if you were using a spreadsheet, but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying data/table
structure,
the folks here in the newsgroup may be able to offer a bit more specific
suggestions. You've described "how" you are trying to do something, but
not
"why". What will you be able to do once you know whether a record has a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not
selected)
in
two separate tables. The "Yes" value records will be stored in my
"Closed"
table and the "No" value records will be stored in the "Open" table.

I created a button which will open a pop-up dialog box for the user to
enter
a comment. I tried adding the code below to that button, but it only
stores
the first No (open) and the first Yes (closed) records. How can I have
it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[Jeff Boyce]

Since we're not there, we can't see the underlying data structure you are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using, with example
data.

For instance, I have no idea what you are storing in a field named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or "current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to wrap my head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the 2nd,
3rd,
4th periods. I loose the ablity to count the previous quarter, since the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end of each
[Reporting Period]. That is why I was thinking about storing the data for
the records at the end of each Reporting Period into the two tables (Open
and
Closed).

How did you invision using the [Current Status] and [StatusDate] fields?
Are you suggesting adding them to the table in addition to the [Status]
and
[Reporting Period] fields?

I'm not entirely clear on the data you are keeping/reporting on...

If you are saying you need to know what status a record is in, and when
it
achieved that status, it seems like two fields in your "records" table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they are counted
on
an Executive Summary report.

I have a table with all records (open and closed). The user updates
the
records and at some point and will determine the work has been
completed
and
the record should be closed. Through a data entry form the user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting Period) and
reviews the data and either accepts, requests changes, additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary Report.

I need to report how many records as: "Total number", "Open", "Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however, counting
records that are Open and Closed have proved to be more difficult. The
user
determines the "Reporting Period" value in the database, since they may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR
Reporting
Period for past FYs where the record count remaining is greater than
zero.
In some cases the records from 1, 2 or 3 years earlier may not have
been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record for each
Reporting Period. The Reporting period for each Open record will
change
from
Quarter to Quarter. My thought was to write the Open and Closed
records
to
seperate tables at the time managment initiates the Executive Summary
Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

:

It all starts with the data ...

It would be a fairly unusual design for a well-normalized relational
database to keep the "Yes"s in one table and the "No"s in another.
That
might be the way you'd have to do it if you were using a spreadsheet,
but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying data/table
structure,
the folks here in the newsgroup may be able to offer a bit more
specific
suggestions. You've described "how" you are trying to do something,
but
not
"why". What will you be able to do once you know whether a record has
a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not
selected)
in
two separate tables. The "Yes" value records will be stored in my
"Closed"
table and the "No" value records will be stored in the "Open" table.

I created a button which will open a pop-up dialog box for the user
to
enter
a comment. I tried adding the code below to that button, but it
only
stores
the first No (open) and the first Yes (closed) records. How can I
have
it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[dsc2bjn]

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a questionnaire)
[Weakness] Memo (description of a flaw found during the self assessment)
[Corrective Actions] Memo (description of the actions to be taken to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value expressed like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records which are Open
and Closed for each [Reporting Period]. This means I need to be able to know
what a record's [STATUS] was/is for every [Reporting Period] until all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date for "Open"
records (changes every quarter until the record is closed. For "Closed"
records, I no longer update the [Reporting Period] value. It contains the
quarter date the record was "Closed'.

Since we're not there, we can't see the underlying data structure you are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using, with example
data.

For instance, I have no idea what you are storing in a field named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or "current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to wrap my head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the 2nd,
3rd,
4th periods. I loose the ablity to count the previous quarter, since the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end of each
[Reporting Period]. That is why I was thinking about storing the data for
the records at the end of each Reporting Period into the two tables (Open
and
Closed).

How did you invision using the [Current Status] and [StatusDate] fields?
Are you suggesting adding them to the table in addition to the [Status]
and
[Reporting Period] fields?

I'm not entirely clear on the data you are keeping/reporting on...

If you are saying you need to know what status a record is in, and when
it
achieved that status, it seems like two fields in your "records" table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they are counted
on
an Executive Summary report.

I have a table with all records (open and closed). The user updates
the
records and at some point and will determine the work has been
completed
and
the record should be closed. Through a data entry form the user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting Period) and
reviews the data and either accepts, requests changes, additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary Report.

I need to report how many records as: "Total number", "Open", "Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however, counting
records that are Open and Closed have proved to be more difficult. The
user
determines the "Reporting Period" value in the database, since they may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR
Reporting
Period for past FYs where the record count remaining is greater than
zero.
In some cases the records from 1, 2 or 3 years earlier may not have
been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record for each
Reporting Period. The Reporting period for each Open record will
change
from
Quarter to Quarter. My thought was to write the Open and Closed
records
to
seperate tables at the time managment initiates the Executive Summary
Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

:

It all starts with the data ...

It would be a fairly unusual design for a well-normalized relational
database to keep the "Yes"s in one table and the "No"s in another.
That
might be the way you'd have to do it if you were using a spreadsheet,
but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying data/table
structure,
the folks here in the newsgroup may be able to offer a bit more
specific
suggestions. You've described "how" you are trying to do something,
but
not
"why". What will you be able to do once you know whether a record has
a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not
selected)
in
two separate tables. The "Yes" value records will be stored in my
"Closed"
table and the "No" value records will be stored in the "Open" table.

I created a button which will open a pop-up dialog box for the user
to
enter
a comment. I tried adding the code below to that button, but it
only
stores
the first No (open) and the first Yes (closed) records. How can I
have
it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[Jeff Boyce]

"1st QTR FY07" is not actually a date in the MS Access Date/Time sense. Is
there a chance this is leading to confusion on Access' (or your) part?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a questionnaire)
[Weakness] Memo (description of a flaw found during the self assessment)
[Corrective Actions] Memo (description of the actions to be taken to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value expressed
like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records which are
Open
and Closed for each [Reporting Period]. This means I need to be able to
know
what a record's [STATUS] was/is for every [Reporting Period] until all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date for
"Open"
records (changes every quarter until the record is closed. For "Closed"
records, I no longer update the [Reporting Period] value. It contains the
quarter date the record was "Closed'.

Since we're not there, we can't see the underlying data structure you are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using, with
example
data.

For instance, I have no idea what you are storing in a field named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or
"current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to wrap my
head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at
different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the 2nd,
3rd,
4th periods. I loose the ablity to count the previous quarter, since
the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end of
each
[Reporting Period]. That is why I was thinking about storing the data
for
the records at the end of each Reporting Period into the two tables
(Open
and
Closed).

How did you invision using the [Current Status] and [StatusDate]
fields?
Are you suggesting adding them to the table in addition to the [Status]
and
[Reporting Period] fields?





:

I'm not entirely clear on the data you are keeping/reporting on...

If you are saying you need to know what status a record is in, and
when
it
achieved that status, it seems like two fields in your "records" table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they are
counted
on
an Executive Summary report.

I have a table with all records (open and closed). The user updates
the
records and at some point and will determine the work has been
completed
and
the record should be closed. Through a data entry form the user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting Period)
and
reviews the data and either accepts, requests changes, additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary Report.

I need to report how many records as: "Total number", "Open",
"Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however,
counting
records that are Open and Closed have proved to be more difficult.
The
user
determines the "Reporting Period" value in the database, since they
may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon
management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR
Reporting
Period for past FYs where the record count remaining is greater than
zero.
In some cases the records from 1, 2 or 3 years earlier may not have
been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record for
each
Reporting Period. The Reporting period for each Open record will
change
from
Quarter to Quarter. My thought was to write the Open and Closed
records
to
seperate tables at the time managment initiates the Executive
Summary
Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

:

It all starts with the data ...

It would be a fairly unusual design for a well-normalized
relational
database to keep the "Yes"s in one table and the "No"s in another.
That
might be the way you'd have to do it if you were using a
spreadsheet,
but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying data/table
structure,
the folks here in the newsgroup may be able to offer a bit more
specific
suggestions. You've described "how" you are trying to do
something,
but
not
"why". What will you be able to do once you know whether a record
has
a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not
selected)
in
two separate tables. The "Yes" value records will be stored in
my
"Closed"
table and the "No" value records will be stored in the "Open"
table.

I created a button which will open a pop-up dialog box for the
user
to
enter
a comment. I tried adding the code below to that button, but it
only
stores
the first No (open) and the first Yes (closed) records. How can
I
have
it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[dsc2bjn]

I don't think so; however, anything is possible.

I run aquery for the 1st QTR FY07 and return the values of the records for
that particular [Reporting Period].

The issue I have is the next [Reporting Period] any "OPEN" record's
[Reporting Period] value now says, "2nd QTR FY07". This means I no longer
know what it's [STATUS] value was in the 1st QTR, as there is nothing stored
to show what the [STATUS] was for the record in the "1st QTR FY07".

I think I can do what I want, if I know how to do a loop in the VB code. So
that it will continue appending the records to my "history" tables. Right
now it only places the first "Open" and "Closed" records into the history
tables. I guess I could store all of the "history" in one table, if that
would help.

"1st QTR FY07" is not actually a date in the MS Access Date/Time sense. Is
there a chance this is leading to confusion on Access' (or your) part?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a questionnaire)
[Weakness] Memo (description of a flaw found during the self assessment)
[Corrective Actions] Memo (description of the actions to be taken to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value expressed
like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records which are
Open
and Closed for each [Reporting Period]. This means I need to be able to
know
what a record's [STATUS] was/is for every [Reporting Period] until all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date for
"Open"
records (changes every quarter until the record is closed. For "Closed"
records, I no longer update the [Reporting Period] value. It contains the
quarter date the record was "Closed'.

Since we're not there, we can't see the underlying data structure you are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using, with
example
data.

For instance, I have no idea what you are storing in a field named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or
"current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to wrap my
head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at
different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the 2nd,
3rd,
4th periods. I loose the ablity to count the previous quarter, since
the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end of
each
[Reporting Period]. That is why I was thinking about storing the data
for
the records at the end of each Reporting Period into the two tables
(Open
and
Closed).

How did you invision using the [Current Status] and [StatusDate]
fields?
Are you suggesting adding them to the table in addition to the [Status]
and
[Reporting Period] fields?





:

I'm not entirely clear on the data you are keeping/reporting on...

If you are saying you need to know what status a record is in, and
when
it
achieved that status, it seems like two fields in your "records" table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they are
counted
on
an Executive Summary report.

I have a table with all records (open and closed). The user updates
the
records and at some point and will determine the work has been
completed
and
the record should be closed. Through a data entry form the user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting Period)
and
reviews the data and either accepts, requests changes, additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary Report.

I need to report how many records as: "Total number", "Open",
"Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however,
counting
records that are Open and Closed have proved to be more difficult.
The
user
determines the "Reporting Period" value in the database, since they
may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon
management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR
Reporting
Period for past FYs where the record count remaining is greater than
zero.
In some cases the records from 1, 2 or 3 years earlier may not have
been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record for
each
Reporting Period. The Reporting period for each Open record will
change
from
Quarter to Quarter. My thought was to write the Open and Closed
records
to
seperate tables at the time managment initiates the Executive
Summary
Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

:

It all starts with the data ...

It would be a fairly unusual design for a well-normalized
relational
database to keep the "Yes"s in one table and the "No"s in another.
That
might be the way you'd have to do it if you were using a
spreadsheet,
but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying data/table
structure,
the folks here in the newsgroup may be able to offer a bit more
specific
suggestions. You've described "how" you are trying to do
something,
but
not
"why". What will you be able to do once you know whether a record
has
a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not
selected)
in
two separate tables. The "Yes" value records will be stored in
my
"Closed"
table and the "No" value records will be stored in the "Open"
table.

I created a button which will open a pop-up dialog box for the
user
to
enter
a comment. I tried adding the code below to that button, but it
only
stores
the first No (open) and the first Yes (closed) records. How can
I
have
it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[Jeff Boyce]

I guess I didn't understand that your "history" was in multiple tables. If
you were using a spreadsheet, you'd pretty much have to do it that way.

But Access is a relational database -- you won't get good use of the
features/functions in it if you feed it 'sheet data.

Why not have a single table with all (current & history) records, by adding
whatever additional field(s) you need to identify a record as "historical"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't think so; however, anything is possible.

I run aquery for the 1st QTR FY07 and return the values of the records for
that particular [Reporting Period].

The issue I have is the next [Reporting Period] any "OPEN" record's
[Reporting Period] value now says, "2nd QTR FY07". This means I no
longer
know what it's [STATUS] value was in the 1st QTR, as there is nothing
stored
to show what the [STATUS] was for the record in the "1st QTR FY07".

I think I can do what I want, if I know how to do a loop in the VB code.
So
that it will continue appending the records to my "history" tables. Right
now it only places the first "Open" and "Closed" records into the history
tables. I guess I could store all of the "history" in one table, if that
would help.

"1st QTR FY07" is not actually a date in the MS Access Date/Time sense.
Is
there a chance this is leading to confusion on Access' (or your) part?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a questionnaire)
[Weakness] Memo (description of a flaw found during the self
assessment)
[Corrective Actions] Memo (description of the actions to be taken to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value expressed
like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records which are
Open
and Closed for each [Reporting Period]. This means I need to be able
to
know
what a record's [STATUS] was/is for every [Reporting Period] until all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date for
"Open"
records (changes every quarter until the record is closed. For
"Closed"
records, I no longer update the [Reporting Period] value. It contains
the
quarter date the record was "Closed'.



:

Since we're not there, we can't see the underlying data structure you
are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using, with
example
data.

For instance, I have no idea what you are storing in a field named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or
"current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to wrap my
head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at
different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the
2nd,
3rd,
4th periods. I loose the ablity to count the previous quarter,
since
the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end of
each
[Reporting Period]. That is why I was thinking about storing the
data
for
the records at the end of each Reporting Period into the two tables
(Open
and
Closed).

How did you invision using the [Current Status] and [StatusDate]
fields?
Are you suggesting adding them to the table in addition to the
[Status]
and
[Reporting Period] fields?





:

I'm not entirely clear on the data you are keeping/reporting on...

If you are saying you need to know what status a record is in, and
when
it
achieved that status, it seems like two fields in your "records"
table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they are
counted
on
an Executive Summary report.

I have a table with all records (open and closed). The user
updates
the
records and at some point and will determine the work has been
completed
and
the record should be closed. Through a data entry form the user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting
Period)
and
reviews the data and either accepts, requests changes, additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary
Report.

I need to report how many records as: "Total number", "Open",
"Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however,
counting
records that are Open and Closed have proved to be more
difficult.
The
user
determines the "Reporting Period" value in the database, since
they
may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon
management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR
Reporting
Period for past FYs where the record count remaining is greater
than
zero.
In some cases the records from 1, 2 or 3 years earlier may not
have
been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record
for
each
Reporting Period. The Reporting period for each Open record will
change
from
Quarter to Quarter. My thought was to write the Open and Closed
records
to
seperate tables at the time managment initiates the Executive
Summary
Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

:

It all starts with the data ...

It would be a fairly unusual design for a well-normalized
relational
database to keep the "Yes"s in one table and the "No"s in
another.
That
might be the way you'd have to do it if you were using a
spreadsheet,
but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying
data/table
structure,
the folks here in the newsgroup may be able to offer a bit more
specific
suggestions. You've described "how" you are trying to do
something,
but
not
"why". What will you be able to do once you know whether a
record
has
a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and
not
selected)
in
two separate tables. The "Yes" value records will be stored
in
my
"Closed"
table and the "No" value records will be stored in the "Open"
table.

I created a button which will open a pop-up dialog box for the
user
to
enter
a comment. I tried adding the code below to that button, but
it
only
stores
the first No (open) and the first Yes (closed) records. How
can
I
have
it
loop through all the records on the screen?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

If CAMO_Concurrence = True Then
DoCmd.RunMacro "macSAR Weakness (Closed)"
DoCmd.RunMacro "macSAR Weakness (Open)"
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SA Monitoring Report Comments"

stLinkCriteria = "[Office]=" & "'" & Me![Office] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

 

[dsc2bjn]

I don't need to have multiple history tables. I could append the Historical
Data(whether open or closed) in to a single table. I could then run multiple
queries to do the count for each status, as well as the total number of
records.

I suppose I could put the "history" with the "current", but my thought was
to create [Open History] and [Closed History] tables that stored only enough
information for me to count records as "Open" or "Closed" at the end of each
reporting cycle.

I don't need things like [Action Officer Phone Number] in order to do a
count to see if a record is open or closed. I wouldn't think housing a copy
of the details of every record in the database for every reporting period
(which may span 5 or more years (4 for each year)) is a very efficient way to
work.

My thought was to append only those data elements needed to do my "Executive
Summary Report" (Counting the records for Total, Open, Closed, Remaining for
each Reporting Period) into the [History] table(s).

I wanted to know how to loop through a form containing multiple Yes/No boxes
to append fields from the form to a table. I think I would need to know that
in order to do your solution or mine.

Wouldn't I will need to "add new records" (Historical data) into the single
table solution you suggested?

I guess I didn't understand that your "history" was in multiple tables. If
you were using a spreadsheet, you'd pretty much have to do it that way.

But Access is a relational database -- you won't get good use of the
features/functions in it if you feed it 'sheet data.

Why not have a single table with all (current & history) records, by adding
whatever additional field(s) you need to identify a record as "historical"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't think so; however, anything is possible.

I run aquery for the 1st QTR FY07 and return the values of the records for
that particular [Reporting Period].

The issue I have is the next [Reporting Period] any "OPEN" record's
[Reporting Period] value now says, "2nd QTR FY07". This means I no
longer
know what it's [STATUS] value was in the 1st QTR, as there is nothing
stored
to show what the [STATUS] was for the record in the "1st QTR FY07".

I think I can do what I want, if I know how to do a loop in the VB code.
So
that it will continue appending the records to my "history" tables. Right
now it only places the first "Open" and "Closed" records into the history
tables. I guess I could store all of the "history" in one table, if that
would help.

"1st QTR FY07" is not actually a date in the MS Access Date/Time sense.
Is
there a chance this is leading to confusion on Access' (or your) part?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a questionnaire)
[Weakness] Memo (description of a flaw found during the self
assessment)
[Corrective Actions] Memo (description of the actions to be taken to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value expressed
like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records which are
Open
and Closed for each [Reporting Period]. This means I need to be able
to
know
what a record's [STATUS] was/is for every [Reporting Period] until all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date for
"Open"
records (changes every quarter until the record is closed. For
"Closed"
records, I no longer update the [Reporting Period] value. It contains
the
quarter date the record was "Closed'.



:

Since we're not there, we can't see the underlying data structure you
are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using, with
example
data.

For instance, I have no idea what you are storing in a field named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or
"current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to wrap my
head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at
different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the
2nd,
3rd,
4th periods. I loose the ablity to count the previous quarter,
since
the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end of
each
[Reporting Period]. That is why I was thinking about storing the
data
for
the records at the end of each Reporting Period into the two tables
(Open
and
Closed).

How did you invision using the [Current Status] and [StatusDate]
fields?
Are you suggesting adding them to the table in addition to the
[Status]
and
[Reporting Period] fields?





:

I'm not entirely clear on the data you are keeping/reporting on...

If you are saying you need to know what status a record is in, and
when
it
achieved that status, it seems like two fields in your "records"
table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they are
counted
on
an Executive Summary report.

I have a table with all records (open and closed). The user
updates
the
records and at some point and will determine the work has been
completed
and
the record should be closed. Through a data entry form the user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting
Period)
and
reviews the data and either accepts, requests changes, additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary
Report.

I need to report how many records as: "Total number", "Open",
"Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however,
counting
records that are Open and Closed have proved to be more
difficult.
The
user
determines the "Reporting Period" value in the database, since
they
may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon
management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and 4thQTR
Reporting
Period for past FYs where the record count remaining is greater
than
zero.
In some cases the records from 1, 2 or 3 years earlier may not
have
been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record
for
each
Reporting Period. The Reporting period for each Open record will
change
from
Quarter to Quarter. My thought was to write the Open and Closed
records
to
seperate tables at the time managment initiates the Executive
Summary
Report,
then count the Open and Closed records for each Reporting Period.

Thanks!!!

:

It all starts with the data ...

It would be a fairly unusual design for a well-normalized
relational
database to keep the "Yes"s in one table and the "No"s in
another.
That
might be the way you'd have to do it if you were using a
spreadsheet,
but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying
data/table
structure,
the folks here in the newsgroup may be able to offer a bit more
specific
suggestions. You've described "how" you are trying to do
something,
but
not
"why". What will you be able to do once you know whether a
record
has
a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

I'm not understanding "append fields from form to table". Tables store data
in Access, forms display it.

I'm not understanding Open History vs Closed History ... what goes into
these fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't need to have multiple history tables. I could append the
Historical
Data(whether open or closed) in to a single table. I could then run
multiple
queries to do the count for each status, as well as the total number of
records.

I suppose I could put the "history" with the "current", but my thought was
to create [Open History] and [Closed History] tables that stored only
enough
information for me to count records as "Open" or "Closed" at the end of
each
reporting cycle.

I don't need things like [Action Officer Phone Number] in order to do a
count to see if a record is open or closed. I wouldn't think housing a
copy
of the details of every record in the database for every reporting period
(which may span 5 or more years (4 for each year)) is a very efficient way
to
work.

My thought was to append only those data elements needed to do my
"Executive
Summary Report" (Counting the records for Total, Open, Closed, Remaining
for
each Reporting Period) into the [History] table(s).

I wanted to know how to loop through a form containing multiple Yes/No
boxes
to append fields from the form to a table. I think I would need to know
that
in order to do your solution or mine.

Wouldn't I will need to "add new records" (Historical data) into the
single
table solution you suggested?

I guess I didn't understand that your "history" was in multiple tables.
If
you were using a spreadsheet, you'd pretty much have to do it that way.

But Access is a relational database -- you won't get good use of the
features/functions in it if you feed it 'sheet data.

Why not have a single table with all (current & history) records, by
adding
whatever additional field(s) you need to identify a record as
"historical"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't think so; however, anything is possible.

I run aquery for the 1st QTR FY07 and return the values of the records
for
that particular [Reporting Period].

The issue I have is the next [Reporting Period] any "OPEN" record's
[Reporting Period] value now says, "2nd QTR FY07". This means I no
longer
know what it's [STATUS] value was in the 1st QTR, as there is nothing
stored
to show what the [STATUS] was for the record in the "1st QTR FY07".

I think I can do what I want, if I know how to do a loop in the VB
code.
So
that it will continue appending the records to my "history" tables.
Right
now it only places the first "Open" and "Closed" records into the
history
tables. I guess I could store all of the "history" in one table, if
that
would help.

:

"1st QTR FY07" is not actually a date in the MS Access Date/Time
sense.
Is
there a chance this is leading to confusion on Access' (or your) part?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a questionnaire)
[Weakness] Memo (description of a flaw found during the self
assessment)
[Corrective Actions] Memo (description of the actions to be taken to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value
expressed
like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when
they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records which
are
Open
and Closed for each [Reporting Period]. This means I need to be
able
to
know
what a record's [STATUS] was/is for every [Reporting Period] until
all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date for
"Open"
records (changes every quarter until the record is closed. For
"Closed"
records, I no longer update the [Reporting Period] value. It
contains
the
quarter date the record was "Closed'.



:

Since we're not there, we can't see the underlying data structure
you
are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using,
with
example
data.

For instance, I have no idea what you are storing in a field named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or
"current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to wrap
my
head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at
different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the
2nd,
3rd,
4th periods. I loose the ablity to count the previous quarter,
since
the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end
of
each
[Reporting Period]. That is why I was thinking about storing the
data
for
the records at the end of each Reporting Period into the two
tables
(Open
and
Closed).

How did you invision using the [Current Status] and [StatusDate]
fields?
Are you suggesting adding them to the table in addition to the
[Status]
and
[Reporting Period] fields?





:

I'm not entirely clear on the data you are keeping/reporting
on...

If you are saying you need to know what status a record is in,
and
when
it
achieved that status, it seems like two fields in your "records"
table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they
are
counted
on
an Executive Summary report.

I have a table with all records (open and closed). The user
updates
the
records and at some point and will determine the work has been
completed
and
the record should be closed. Through a data entry form the
user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting
Period)
and
reviews the data and either accepts, requests changes,
additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary
Report.

I need to report how many records as: "Total number", "Open",
"Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12
3rdQTR FY07 23 12 7 5
4thQTR FY07 23 5 4 1

Counting the Total was relatively easy to accomplish; however,
counting
records that are Open and Closed have proved to be more
difficult.
The
user
determines the "Reporting Period" value in the database, since
they
may
submit their quarterly update at any time within the quarter.
Additionally,
they may need to revise their quarterly report based upon
management's
request for additional information or rejecting a closed item.

I am to show the Reporting Periods for the current FY and
4thQTR
Reporting
Period for past FYs where the record count remaining is
greater
than
zero.
In some cases the records from 1, 2 or 3 years earlier may not
have
been
completed (long term projects or just plain laziness).

I need to be able to capture and report the status of a record
for
each
Reporting Period. The Reporting period for each Open record
will
change
from
Quarter to Quarter. My thought was to write the Open and
Closed
records
to
seperate tables at the time managment initiates the Executive
Summary
Report,
then count the Open and Closed records for each Reporting
Period.

Thanks!!!

:

It all starts with the data ...

It would be a fairly unusual design for a well-normalized
relational
database to keep the "Yes"s in one table and the "No"s in
another.
That
might be the way you'd have to do it if you were using a
spreadsheet,
but
you won't get very good use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

If you'll be a bit more specific about your underlying
data/table
structure,
the folks here in the newsgroup may be able to offer a bit
more
specific
suggestions. You've described "how" you are trying to do
something,
but
not
"why". What will you be able to do once you know whether a
record
has
a
"yes" or a "no" associated with it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dsc2bjn]

My data is stored in the [SAR DATA TABLE].

At a given point in time (let us say Monday), I wish to know how many
records are Open and how many are Closed. Fine I can run a report and get
those numbers.

At another given point in time (let us say Wednesday), I wish to know how
many records are Open and how many are Closed. Fine I can run a report and
get those numbers.

Let us say instead on Friday I would give management a report that showed
THREE distinct periods in time (Monday, Wednesday, and today (Friday)).

I want to be able to tell mangement that on Monday I had 20 Total Records, 5
records were closed, 15 are open.

I want to also be able to tell management that on Wednesday I had 20 Total
records, 4 had been closed, 11 are open.

I would also want to be able to tell management that on Friday, we currently
have a total of 20 records, 6 have been closed, and 5 are open.

All in the same report.

T C O
Monday 20 5 15
Wednes 20 4 11
Friday 20 6 5

How do I do that?

I'm not understanding "append fields from form to table". Tables store data
in Access, forms display it.

I'm not understanding Open History vs Closed History ... what goes into
these fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't need to have multiple history tables. I could append the
Historical
Data(whether open or closed) in to a single table. I could then run
multiple
queries to do the count for each status, as well as the total number of
records.

I suppose I could put the "history" with the "current", but my thought was
to create [Open History] and [Closed History] tables that stored only
enough
information for me to count records as "Open" or "Closed" at the end of
each
reporting cycle.

I don't need things like [Action Officer Phone Number] in order to do a
count to see if a record is open or closed. I wouldn't think housing a
copy
of the details of every record in the database for every reporting period
(which may span 5 or more years (4 for each year)) is a very efficient way
to
work.

My thought was to append only those data elements needed to do my
"Executive
Summary Report" (Counting the records for Total, Open, Closed, Remaining
for
each Reporting Period) into the [History] table(s).

I wanted to know how to loop through a form containing multiple Yes/No
boxes
to append fields from the form to a table. I think I would need to know
that
in order to do your solution or mine.

Wouldn't I will need to "add new records" (Historical data) into the
single
table solution you suggested?

I guess I didn't understand that your "history" was in multiple tables.
If
you were using a spreadsheet, you'd pretty much have to do it that way.

But Access is a relational database -- you won't get good use of the
features/functions in it if you feed it 'sheet data.

Why not have a single table with all (current & history) records, by
adding
whatever additional field(s) you need to identify a record as
"historical"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't think so; however, anything is possible.

I run aquery for the 1st QTR FY07 and return the values of the records
for
that particular [Reporting Period].

The issue I have is the next [Reporting Period] any "OPEN" record's
[Reporting Period] value now says, "2nd QTR FY07". This means I no
longer
know what it's [STATUS] value was in the 1st QTR, as there is nothing
stored
to show what the [STATUS] was for the record in the "1st QTR FY07".

I think I can do what I want, if I know how to do a loop in the VB
code.
So
that it will continue appending the records to my "history" tables.
Right
now it only places the first "Open" and "Closed" records into the
history
tables. I guess I could store all of the "history" in one table, if
that
would help.

:

"1st QTR FY07" is not actually a date in the MS Access Date/Time
sense.
Is
there a chance this is leading to confusion on Access' (or your) part?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a questionnaire)
[Weakness] Memo (description of a flaw found during the self
assessment)
[Corrective Actions] Memo (description of the actions to be taken to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value
expressed
like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when
they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records which
are
Open
and Closed for each [Reporting Period]. This means I need to be
able
to
know
what a record's [STATUS] was/is for every [Reporting Period] until
all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date for
"Open"
records (changes every quarter until the record is closed. For
"Closed"
records, I no longer update the [Reporting Period] value. It
contains
the
quarter date the record was "Closed'.



:

Since we're not there, we can't see the underlying data structure
you
are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using,
with
example
data.

For instance, I have no idea what you are storing in a field named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or
"current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to wrap
my
head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at
different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for the
2nd,
3rd,
4th periods. I loose the ablity to count the previous quarter,
since
the
"Reporting Date" for the Open records changes with each Quarter.

I somehow need know (and maybe store) the count values at the end
of
each
[Reporting Period]. That is why I was thinking about storing the
data
for
the records at the end of each Reporting Period into the two
tables
(Open
and
Closed).

How did you invision using the [Current Status] and [StatusDate]
fields?
Are you suggesting adding them to the table in addition to the
[Status]
and
[Reporting Period] fields?





:

I'm not entirely clear on the data you are keeping/reporting
on...

If you are saying you need to know what status a record is in,
and
when
it
achieved that status, it seems like two fields in your "records"
table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the purposes of the database is to report the status to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they
are
counted
on
an Executive Summary report.

I have a table with all records (open and closed). The user
updates
the
records and at some point and will determine the work has been
completed
and
the record should be closed. Through a data entry form the
user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting
Period)
and
reviews the data and either accepts, requests changes,
additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary
Report.

I need to report how many records as: "Total number", "Open",
"Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12

 

[Jeff Boyce]

I'm sure you are quite familiar with both your data and your Access
database.

I may be dense, but I have no idea what data you are storing in your [Open]
and [Closed] fields, nor what the table structure looks like.

Answer "how do I" questions depends on understanding "what it looks like".

I don't know if I can help.

You might want to consider re-posting your question, providing as much
specific information as possible. That way, more eyes/brains can focus on
it.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

My data is stored in the [SAR DATA TABLE].

At a given point in time (let us say Monday), I wish to know how many
records are Open and how many are Closed. Fine I can run a report and get
those numbers.

At another given point in time (let us say Wednesday), I wish to know how
many records are Open and how many are Closed. Fine I can run a report
and
get those numbers.

Let us say instead on Friday I would give management a report that showed
THREE distinct periods in time (Monday, Wednesday, and today (Friday)).

I want to be able to tell mangement that on Monday I had 20 Total Records,
5
records were closed, 15 are open.

I want to also be able to tell management that on Wednesday I had 20 Total
records, 4 had been closed, 11 are open.

I would also want to be able to tell management that on Friday, we
currently
have a total of 20 records, 6 have been closed, and 5 are open.

All in the same report.

T C O
Monday 20 5 15
Wednes 20 4 11
Friday 20 6 5

How do I do that?

I'm not understanding "append fields from form to table". Tables store
data
in Access, forms display it.

I'm not understanding Open History vs Closed History ... what goes into
these fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't need to have multiple history tables. I could append the
Historical
Data(whether open or closed) in to a single table. I could then run
multiple
queries to do the count for each status, as well as the total number of
records.

I suppose I could put the "history" with the "current", but my thought
was
to create [Open History] and [Closed History] tables that stored only
enough
information for me to count records as "Open" or "Closed" at the end of
each
reporting cycle.

I don't need things like [Action Officer Phone Number] in order to do a
count to see if a record is open or closed. I wouldn't think housing
a
copy
of the details of every record in the database for every reporting
period
(which may span 5 or more years (4 for each year)) is a very efficient
way
to
work.

My thought was to append only those data elements needed to do my
"Executive
Summary Report" (Counting the records for Total, Open, Closed,
Remaining
for
each Reporting Period) into the [History] table(s).

I wanted to know how to loop through a form containing multiple Yes/No
boxes
to append fields from the form to a table. I think I would need to
know
that
in order to do your solution or mine.

Wouldn't I will need to "add new records" (Historical data) into the
single
table solution you suggested?


:

I guess I didn't understand that your "history" was in multiple
tables.
If
you were using a spreadsheet, you'd pretty much have to do it that
way.

But Access is a relational database -- you won't get good use of the
features/functions in it if you feed it 'sheet data.

Why not have a single table with all (current & history) records, by
adding
whatever additional field(s) you need to identify a record as
"historical"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't think so; however, anything is possible.

I run aquery for the 1st QTR FY07 and return the values of the
records
for
that particular [Reporting Period].

The issue I have is the next [Reporting Period] any "OPEN" record's
[Reporting Period] value now says, "2nd QTR FY07". This means I no
longer
know what it's [STATUS] value was in the 1st QTR, as there is
nothing
stored
to show what the [STATUS] was for the record in the "1st QTR FY07".

I think I can do what I want, if I know how to do a loop in the VB
code.
So
that it will continue appending the records to my "history" tables.
Right
now it only places the first "Open" and "Closed" records into the
history
tables. I guess I could store all of the "history" in one table, if
that
would help.

:

"1st QTR FY07" is not actually a date in the MS Access Date/Time
sense.
Is
there a chance this is leading to confusion on Access' (or your)
part?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a
questionnaire)
[Weakness] Memo (description of a flaw found during the self
assessment)
[Corrective Actions] Memo (description of the actions to be taken
to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value
expressed
like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when
they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records
which
are
Open
and Closed for each [Reporting Period]. This means I need to be
able
to
know
what a record's [STATUS] was/is for every [Reporting Period]
until
all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date
for
"Open"
records (changes every quarter until the record is closed. For
"Closed"
records, I no longer update the [Reporting Period] value. It
contains
the
quarter date the record was "Closed'.



:

Since we're not there, we can't see the underlying data
structure
you
are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using,
with
example
data.

For instance, I have no idea what you are storing in a field
named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or
"current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to
wrap
my
head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at
different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to
speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for
the
2nd,
3rd,
4th periods. I loose the ablity to count the previous
quarter,
since
the
"Reporting Date" for the Open records changes with each
Quarter.

I somehow need know (and maybe store) the count values at the
end
of
each
[Reporting Period]. That is why I was thinking about storing
the
data
for
the records at the end of each Reporting Period into the two
tables
(Open
and
Closed).

How did you invision using the [Current Status] and
[StatusDate]
fields?
Are you suggesting adding them to the table in addition to the
[Status]
and
[Reporting Period] fields?





:

I'm not entirely clear on the data you are keeping/reporting
on...

If you are saying you need to know what status a record is
in,
and
when
it
achieved that status, it seems like two fields in your
"records"
table
would
suffice ([CurrentStatus] and [StatusDate]).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
One of the purposes of the database is to report the status
to
management
of
records as either open or closed.

Closed records must be "accepted" by management before they
are
counted
on
an Executive Summary report.

I have a table with all records (open and closed). The
user
updates
the
records and at some point and will determine the work has
been
completed
and
the record should be closed. Through a data entry form the
user
changes
the
status of the record to closed.

Managment opens their admin form once a quarter (Reporting
Period)
and
reviews the data and either accepts, requests changes,
additional
information
or rejects the item as being closed.

All of that works fine.

The part that doesn't work for me is the Executive Summary
Report.

I need to report how many records as: "Total number",
"Open",
"Closed"
based
upon the Reporting Period.

Example: 2007 Total Open Closed Remaining
1stQTR FY07 23 20 3 17
2ndQTR FY07 23 17 5 12

 

[dsc2bjn]

Open and Close are values within a field called [STATUS].

I need to be able to count the "open" records and the "closed" records.
Right now, I can count only the current status of any given record.

I need to be able to somehow know what the [STATUS] of a record was at
differing points in time. Sometimes the point in time might be a year or
more ago.

Suggest a data structure and let me see how I might be able to work with it.

I'm sure you are quite familiar with both your data and your Access
database.

I may be dense, but I have no idea what data you are storing in your [Open]
and [Closed] fields, nor what the table structure looks like.

Answer "how do I" questions depends on understanding "what it looks like".

I don't know if I can help.

You might want to consider re-posting your question, providing as much
specific information as possible. That way, more eyes/brains can focus on
it.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

My data is stored in the [SAR DATA TABLE].

At a given point in time (let us say Monday), I wish to know how many
records are Open and how many are Closed. Fine I can run a report and get
those numbers.

At another given point in time (let us say Wednesday), I wish to know how
many records are Open and how many are Closed. Fine I can run a report
and
get those numbers.

Let us say instead on Friday I would give management a report that showed
THREE distinct periods in time (Monday, Wednesday, and today (Friday)).

I want to be able to tell mangement that on Monday I had 20 Total Records,
5
records were closed, 15 are open.

I want to also be able to tell management that on Wednesday I had 20 Total
records, 4 had been closed, 11 are open.

I would also want to be able to tell management that on Friday, we
currently
have a total of 20 records, 6 have been closed, and 5 are open.

All in the same report.

T C O
Monday 20 5 15
Wednes 20 4 11
Friday 20 6 5

How do I do that?

I'm not understanding "append fields from form to table". Tables store
data
in Access, forms display it.

I'm not understanding Open History vs Closed History ... what goes into
these fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't need to have multiple history tables. I could append the
Historical
Data(whether open or closed) in to a single table. I could then run
multiple
queries to do the count for each status, as well as the total number of
records.

I suppose I could put the "history" with the "current", but my thought
was
to create [Open History] and [Closed History] tables that stored only
enough
information for me to count records as "Open" or "Closed" at the end of
each
reporting cycle.

I don't need things like [Action Officer Phone Number] in order to do a
count to see if a record is open or closed. I wouldn't think housing
a
copy
of the details of every record in the database for every reporting
period
(which may span 5 or more years (4 for each year)) is a very efficient
way
to
work.

My thought was to append only those data elements needed to do my
"Executive
Summary Report" (Counting the records for Total, Open, Closed,
Remaining
for
each Reporting Period) into the [History] table(s).

I wanted to know how to loop through a form containing multiple Yes/No
boxes
to append fields from the form to a table. I think I would need to
know
that
in order to do your solution or mine.

Wouldn't I will need to "add new records" (Historical data) into the
single
table solution you suggested?


:

I guess I didn't understand that your "history" was in multiple
tables.
If
you were using a spreadsheet, you'd pretty much have to do it that
way.

But Access is a relational database -- you won't get good use of the
features/functions in it if you feed it 'sheet data.

Why not have a single table with all (current & history) records, by
adding
whatever additional field(s) you need to identify a record as
"historical"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't think so; however, anything is possible.

I run aquery for the 1st QTR FY07 and return the values of the
records
for
that particular [Reporting Period].

The issue I have is the next [Reporting Period] any "OPEN" record's
[Reporting Period] value now says, "2nd QTR FY07". This means I no
longer
know what it's [STATUS] value was in the 1st QTR, as there is
nothing
stored
to show what the [STATUS] was for the record in the "1st QTR FY07".

I think I can do what I want, if I know how to do a loop in the VB
code.
So
that it will continue appending the records to my "history" tables.
Right
now it only places the first "Open" and "Closed" records into the
history
tables. I guess I could store all of the "history" in one table, if
that
would help.

:

"1st QTR FY07" is not actually a date in the MS Access Date/Time
sense.
Is
there a chance this is leading to confusion on Access' (or your)
part?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The data structure includes items such as:
[Office] Text
[Assessible Unit] Text (a division within an Office)
[Self Assessment Question] Text (a question from a
questionnaire)
[Weakness] Memo (description of a flaw found during the self
assessment)
[Corrective Actions] Memo (description of the actions to be taken
to
mitigate the weakness)
[Estimated Completion Date] Date
[Actual Completion Date] Date
[Status] Text (Open; Closed)
[Reporting Period] Text (this is a date expressed type value
expressed
like
1st QTR FY07)

The user selects a quarter and a year from 2 drop down boxes when
they
submit their quarterly report for the [Reporting Period].

In the Executive Summary Report, I need to count the records
which
are
Open
and Closed for each [Reporting Period]. This means I need to be
able
to
know
what a record's [STATUS] was/is for every [Reporting Period]
until
all
records for a given year are [Closed].

Right now the [Reporting Period] stores the current quarter date
for
"Open"
records (changes every quarter until the record is closed. For
"Closed"
records, I no longer update the [Reporting Period] value. It
contains
the
quarter date the record was "Closed'.



:

Since we're not there, we can't see the underlying data
structure
you
are
working against.

It all starts with the data.

"How" you build a report depends on how the data is organized.

Please post a description of the table structure you are using,
with
example
data.

For instance, I have no idea what you are storing in a field
named
[Reporting Period].

Are you storing "point-in-time" data (e.g., historical info), or
"current"
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure you are missing anything. I am still trying to
wrap
my
head
around it.

I have two fields defined ([Status] and [Reporting Period])

I need to be able to know what the count of the records are at
different
"Reporting Period" dates.

In Quarter 1 there might be 20 open records and 5 closed ones.
In Quarter 2 an additional 8 records might be closed.

The client wants to see both as a reporting trend, so to
speak.
Open Closed Remaining
Q1 20 5 15
Q2 15 8 7

Right now the problem I have is when counting the records for
the
2nd,
3rd,
4th periods. I loose the ablity to count the previous
quarter,
since
the
"Reporting Date" for the Open records changes with each
Quarter.

I somehow need know (and maybe store) the count values at the
end
of
each
[Reporting Period]. That is why I was thinking about storing
the

 

[John W. Vinson]

I have created a form which contains multiple Yes/No boxes.

I wish to store the value of the records (both selected and not selected) in
two separate tables. The "Yes" value records will be stored in my "Closed"
table and the "No" value records will be stored in the "Open" table.

I created a button which will open a pop-up dialog box for the user to enter
a comment. I tried adding the code below to that button, but it only stores
the first No (open) and the first Yes (closed) records. How can I have it
loop through all the records on the screen?

Did you get a resolution to your question, dsc2bjn?


John W. Vinson [MVP]