History

[dsc2bjn]

I need to create a database which will in part allow me to "count" the number
of records based upon their [STATUS] at multiple points in time.

At the end of each month, I want to know how many records have been [Closed]
during the month and how many are still [Open] (where [Closed] and [Open] are
values of the [STATUS]).

Example of how I need the REPORT to look:

Total Open Closed Remaining
Jan 20 20 2 18
Feb 20 18 3 15
Mar 20 15 5 10
Apr 20 10 4 6


I am looking for suggestions as to how to write queries, VB code and/or how
to structure the data that would allow me to report a running "history".

Any suggestions would be greatly appreciated.

 

[Allen Browne]

Presumably your table has fields like this:
FKID foreign key field indicating what this record relates to
Status Text, containing only "Open" or "Closed", never null
StatusDate Date/Time when this status record was entered.

Over time, any particular FKID value has multiple status records indicating
when it is opened and closed. Theoretically, a closed record could be opened
again and then closed again. Therefore you want to know:
a) The Status of the FKID at the end of the month
b) The Status of the FKID at the beginning of the month
ignoring any other status changes for this FKID during the month. From that,
you can calculate how many changed status from open to closed.

Here's a useful article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
The solution uses subqueries to get the value of a related field (the Status
field), at a point in time. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[dsc2bjn]

I read both articles and your suggested layout.

I still have some questions.

Are you suggesting I create a separate table to store the FKID, Status and
StatusDate which is linked to the MasterTable or append a new record to the
table and populate the FKID with the CurrentID from the MasterTable value
each time the record is changed?

Presumably your table has fields like this:
FKID foreign key field indicating what this record relates to
Status Text, containing only "Open" or "Closed", never null
StatusDate Date/Time when this status record was entered.

Over time, any particular FKID value has multiple status records indicating
when it is opened and closed. Theoretically, a closed record could be opened
again and then closed again. Therefore you want to know:
a) The Status of the FKID at the end of the month
b) The Status of the FKID at the beginning of the month
ignoring any other status changes for this FKID during the month. From that,
you can calculate how many changed status from open to closed.

Here's a useful article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
The solution uses subqueries to get the value of a related field (the Status
field), at a point in time. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need to create a database which will in part allow me to "count" the
number
of records based upon their [STATUS] at multiple points in time.

At the end of each month, I want to know how many records have been
[Closed]
during the month and how many are still [Open] (where [Closed] and [Open]
are
values of the [STATUS]).

Example of how I need the REPORT to look:

Total Open Closed Remaining
Jan 20 20 2 18
Feb 20 18 3 15
Mar 20 15 5 10
Apr 20 10 4 6


I am looking for suggestions as to how to write queries, VB code and/or
how
to structure the data that would allow me to report a running "history".

Any suggestions would be greatly appreciated.

 

[Allen Browne]

I guess I don't understand what data structure you already have.

I've assumed that you already have a table with the 3 fields suggested, and
that you add a new record each time there is a change, perhaps disallowing
edits for existing records. Therefore, this table has a complete history of
the status each time it changes. That's the usual way to create a history
table.

It would be possible to interface that in such as way that you appear to be
letting the user edit existing records, but you actually cancel the edit and
execute an append query to create the new record instead. I'm not convinced
that's really a good interface though, since what is really going on does
not match what the user thinks is going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I read both articles and your suggested layout.

I still have some questions.

Are you suggesting I create a separate table to store the FKID, Status and
StatusDate which is linked to the MasterTable or append a new record to
the
table and populate the FKID with the CurrentID from the MasterTable value
each time the record is changed?

Presumably your table has fields like this:
FKID foreign key field indicating what this record relates
to
Status Text, containing only "Open" or "Closed", never null
StatusDate Date/Time when this status record was entered.

Over time, any particular FKID value has multiple status records
indicating
when it is opened and closed. Theoretically, a closed record could be
opened
again and then closed again. Therefore you want to know:
a) The Status of the FKID at the end of the month
b) The Status of the FKID at the beginning of the month
ignoring any other status changes for this FKID during the month. From
that,
you can calculate how many changed status from open to closed.

Here's a useful article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
The solution uses subqueries to get the value of a related field (the
Status
field), at a point in time. If subqueries are new, here's an
introduction:
http://allenbrowne.com/subquery-01.html

I need to create a database which will in part allow me to "count" the
number
of records based upon their [STATUS] at multiple points in time.

At the end of each month, I want to know how many records have been
[Closed]
during the month and how many are still [Open] (where [Closed] and
[Open]
are
values of the [STATUS]).

Example of how I need the REPORT to look:

Total Open Closed Remaining
Jan 20 20 2 18
Feb 20 18 3 15
Mar 20 15 5 10
Apr 20 10 4 6


I am looking for suggestions as to how to write queries, VB code and/or
how
to structure the data that would allow me to report a running
"history".

Any suggestions would be greatly appreciated

 

[dsc2bjn]

Right now I am looking for advise on how to go about making a table which
allows me to know the entire hitory of each record. From the user
perspective they only deal with the current values. Management, from time to
time, will want to know the entire "history" of the record or (in most cases)
want to see a report of counts showing progress over time.

I have a structure in place, but can change it to fit whatever works.

I currently allow the user to add new records and change existing ones, but
when they change the existing records it overwrites the existing data. If
there is a better way to create a running [History], I am all for it.

I guess I don't understand what data structure you already have.

I've assumed that you already have a table with the 3 fields suggested, and
that you add a new record each time there is a change, perhaps disallowing
edits for existing records. Therefore, this table has a complete history of
the status each time it changes. That's the usual way to create a history
table.

It would be possible to interface that in such as way that you appear to be
letting the user edit existing records, but you actually cancel the edit and
execute an append query to create the new record instead. I'm not convinced
that's really a good interface though, since what is really going on does
not match what the user thinks is going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I read both articles and your suggested layout.

I still have some questions.

Are you suggesting I create a separate table to store the FKID, Status and
StatusDate which is linked to the MasterTable or append a new record to
the
table and populate the FKID with the CurrentID from the MasterTable value
each time the record is changed?

Presumably your table has fields like this:
FKID foreign key field indicating what this record relates
to
Status Text, containing only "Open" or "Closed", never null
StatusDate Date/Time when this status record was entered.

Over time, any particular FKID value has multiple status records
indicating
when it is opened and closed. Theoretically, a closed record could be
opened
again and then closed again. Therefore you want to know:
a) The Status of the FKID at the end of the month
b) The Status of the FKID at the beginning of the month
ignoring any other status changes for this FKID during the month. From
that,
you can calculate how many changed status from open to closed.

Here's a useful article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
The solution uses subqueries to get the value of a related field (the
Status
field), at a point in time. If subqueries are new, here's an
introduction:
http://allenbrowne.com/subquery-01.html

I need to create a database which will in part allow me to "count" the
number
of records based upon their [STATUS] at multiple points in time.

At the end of each month, I want to know how many records have been
[Closed]
during the month and how many are still [Open] (where [Closed] and
[Open]
are
values of the [STATUS]).

Example of how I need the REPORT to look:

Total Open Closed Remaining
Jan 20 20 2 18
Feb 20 18 3 15
Mar 20 15 5 10
Apr 20 10 4 6


I am looking for suggestions as to how to write queries, VB code and/or
how
to structure the data that would allow me to report a running
"history".

Any suggestions would be greatly appreciated

 

[Allen Browne]

Okay, so you want to stay with the current records, but build a history of
the values that used to be there in another table.

See:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Right now I am looking for advise on how to go about making a table which
allows me to know the entire hitory of each record. From the user
perspective they only deal with the current values. Management, from time
to
time, will want to know the entire "history" of the record or (in most
cases)
want to see a report of counts showing progress over time.

I have a structure in place, but can change it to fit whatever works.

I currently allow the user to add new records and change existing ones,
but
when they change the existing records it overwrites the existing data.
If
there is a better way to create a running [History], I am all for it.

I guess I don't understand what data structure you already have.

I've assumed that you already have a table with the 3 fields suggested,
and
that you add a new record each time there is a change, perhaps
disallowing
edits for existing records. Therefore, this table has a complete history
of
the status each time it changes. That's the usual way to create a history
table.

It would be possible to interface that in such as way that you appear to
be
letting the user edit existing records, but you actually cancel the edit
and
execute an append query to create the new record instead. I'm not
convinced
that's really a good interface though, since what is really going on does
not match what the user thinks is going on.

I read both articles and your suggested layout.

I still have some questions.

Are you suggesting I create a separate table to store the FKID, Status
and
StatusDate which is linked to the MasterTable or append a new record to
the
table and populate the FKID with the CurrentID from the MasterTable
value
each time the record is changed?

:

Presumably your table has fields like this:
FKID foreign key field indicating what this record
relates
to
Status Text, containing only "Open" or "Closed", never null
StatusDate Date/Time when this status record was entered.

Over time, any particular FKID value has multiple status records
indicating
when it is opened and closed. Theoretically, a closed record could be
opened
again and then closed again. Therefore you want to know:
a) The Status of the FKID at the end of the month
b) The Status of the FKID at the beginning of the month
ignoring any other status changes for this FKID during the month. From
that,
you can calculate how many changed status from open to closed.

Here's a useful article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
The solution uses subqueries to get the value of a related field (the
Status
field), at a point in time. If subqueries are new, here's an
introduction:
http://allenbrowne.com/subquery-01.html

I need to create a database which will in part allow me to "count"
the
number
of records based upon their [STATUS] at multiple points in time.

At the end of each month, I want to know how many records have been
[Closed]
during the month and how many are still [Open] (where [Closed] and
[Open]
are
values of the [STATUS]).

Example of how I need the REPORT to look:

Total Open Closed Remaining
Jan 20 20 2 18
Feb 20 18 3 15
Mar 20 15 5 10
Apr 20 10 4 6


I am looking for suggestions as to how to write queries, VB code
and/or
how
to structure the data that would allow me to report a running
"history".

Any suggestions would be greatly appreciated