A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

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

DYNAMIC STUDENT DATABASE



 
 
Thread Tools Display Modes
  #1  
Old September 8th, 2009, 04:52 PM posted to microsoft.public.access.tablesdbdesign
Brad Hodges
external usenet poster
 
Posts: 13
Default DYNAMIC STUDENT DATABASE

I am an administrator over military courses. I'm trying to slim down an
overly redundant database. I would like to make it as dynamic as possible.
I have two records in the same table; Graduation date and status.
Can I have a graduation date automatically another field called status which
currently is either "active" or "history" without using a manual "update
query" process? I would assuse it's a simple select statement in the status
field but need help.
  #2  
Old September 8th, 2009, 05:53 PM posted to microsoft.public.access.tablesdbdesign
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default DYNAMIC STUDENT DATABASE

On Sep 8, 10:52*am, Brad Hodges
wrote:
I am an administrator over military courses. *I'm trying to slim down an
overly redundant database. *I would like to make it as dynamic as possible. *
I have two records in the same table; *Graduation date and status.
Can I have a graduation date automatically another field called status which
currently is either "active" or "history" without using a manual "update
query" process? *I would assuse it's a simple select statement in the status
field but need help.


Sounds like you mean two columns, not two records.

If so, then you can use the AfterUpdate event of the graduation date
do something like

Sub GraduationDate_AfterUpdate()
If Me.GraduationDateDate() Then 'Graduation Date is before
the currentdate
Me.Status="History"
End If
End Sub
  #4  
Old September 8th, 2009, 06:05 PM posted to microsoft.public.access.tablesdbdesign
Brad Hodges
external usenet poster
 
Posts: 13
Default DYNAMIC STUDENT DATABASE

Piet Linden,

Thank you very much for the prompt response. Where do I insert that sub at?
I've taken a VB course, but very little training on VB in Access.

"Piet Linden" wrote:

On Sep 8, 10:52 am, Brad Hodges
wrote:
I am an administrator over military courses. I'm trying to slim down an
overly redundant database. I would like to make it as dynamic as possible.
I have two records in the same table; Graduation date and status.
Can I have a graduation date automatically another field called status which
currently is either "active" or "history" without using a manual "update
query" process? I would assuse it's a simple select statement in the status
field but need help.


Sounds like you mean two columns, not two records.

If so, then you can use the AfterUpdate event of the graduation date
do something like

Sub GraduationDate_AfterUpdate()
If Me.GraduationDateDate() Then 'Graduation Date is before
the currentdate
Me.Status="History"
End If
End Sub

  #5  
Old September 8th, 2009, 06:51 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default DYNAMIC STUDENT DATABASE

Brad,

It's quite possible that when you examine your other particulars, you may
find that "status" needn't or shouldn't be a field. E.G. if it is purely
derived from other fields, that derivation could be done when the status info
is needed.
  #6  
Old September 8th, 2009, 07:13 PM posted to microsoft.public.access.tablesdbdesign
Brad Hodges
external usenet poster
 
Posts: 13
Default DYNAMIC STUDENT DATABASE

Fred,

Thank you very much. I agree. The only reason I let this field remain is
that the existing database has counters for the two types of students which
populates data in the default form currently being used as a quasi
switchboardt based off an "admin table" which is a query off the main table.
Like I said initially, alot of redundancies!!!

"Fred" wrote:

Brad,

It's quite possible that when you examine your other particulars, you may
find that "status" needn't or shouldn't be a field. E.G. if it is purely
derived from other fields, that derivation could be done when the status info
is needed.

  #7  
Old September 8th, 2009, 07:18 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default DYNAMIC STUDENT DATABASE

On Tue, 8 Sep 2009 08:52:02 -0700, Brad Hodges
wrote:

I am an administrator over military courses. I'm trying to slim down an
overly redundant database. I would like to make it as dynamic as possible.
I have two records in the same table; Graduation date and status.
Can I have a graduation date automatically another field called status which
currently is either "active" or "history" without using a manual "update
query" process? I would assuse it's a simple select statement in the status
field but need help.


If the status is "active" up until the graduation date, and "history"
thereafter, then the Status field should *simply not exist* in your table at
all. It can be calculated on the fly in a query:

Status: IIF(NZ([Graduation date], Date()) = Date(), "Active", "History")
--

John W. Vinson [MVP]
  #8  
Old September 8th, 2009, 07:56 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default DYNAMIC STUDENT DATABASE

Whew!

Sounds like an "adventure"

Fred

  #9  
Old September 8th, 2009, 10:18 PM posted to microsoft.public.access.tablesdbdesign
Brad Hodges
external usenet poster
 
Posts: 13
Default DYNAMIC STUDENT DATABASE

John,

Thank you very much. I'm not sure what this creates. I have an admin table
(query) which is derived from an allstudent table. How do I eliminate the
status column yet allow the query to function? What is "NZ" in your example?
Is this a pseudoname? Thanks again.

"John W. Vinson" wrote:

On Tue, 8 Sep 2009 08:52:02 -0700, Brad Hodges
wrote:

I am an administrator over military courses. I'm trying to slim down an
overly redundant database. I would like to make it as dynamic as possible.
I have two records in the same table; Graduation date and status.
Can I have a graduation date automatically another field called status which
currently is either "active" or "history" without using a manual "update
query" process? I would assuse it's a simple select statement in the status
field but need help.


If the status is "active" up until the graduation date, and "history"
thereafter, then the Status field should *simply not exist* in your table at
all. It can be calculated on the fly in a query:

Status: IIF(NZ([Graduation date], Date()) = Date(), "Active", "History")
--

John W. Vinson [MVP]

  #10  
Old September 8th, 2009, 10:59 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default DYNAMIC STUDENT DATABASE

On Tue, 8 Sep 2009 14:18:26 -0700, Brad Hodges
wrote:

John,

Thank you very much. I'm not sure what this creates. I have an admin table
(query) which is derived from an allstudent table. How do I eliminate the
status column yet allow the query to function? What is "NZ" in your example?
Is this a pseudoname? Thanks again.


Is your admin table a table, or a query? They are NOT THE SAME THING: a table
is actual stored data on disk, and a query is a volatile derived object
extracting data from a table (or tables). My suggestion is a Query which will
include a field named Status, containing either "active" or "history",
depending on the value of the date field.

NZ() is a built in function in Access that converts a NULL value to its second
argument, so

IIF(NZ([Graduation date], Date()) = Date(), "Active", "History")

will use today's date in the comparison if [Graduation date] is NULL; this
will ensure that a record with no graduation date specified is shown as Active
(along with those with a graduation date in the future). Graduation dates that
are in the past will be shown as "History".

--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

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

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


All times are GMT +1. The time now is 09:52 AM.


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