(Msg. 1) Posted: Tue Sep 08, 2009 8:52 am
Post subject: DYNAMIC STUDENT DATABASE Archived from groups: microsoft>public>access>tablesdbdesign (more info?)
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.
(Msg. 2) Posted: Tue Sep 08, 2009 9:53 am
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Sep 8, 10:52 am, Brad Hodges <bradhod... RemoveThis @discussions.microsoft.com>
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.GraduationDate<Date() Then 'Graduation Date is before
the currentdate
Me.Status="History"
End If
End Sub
(Msg. 3) Posted: Tue Sep 08, 2009 10:05 am
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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 <bradhod....RemoveThis@discussions.microsoft.com>
> 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.GraduationDate<Date() Then 'Graduation Date is before
> the currentdate
> Me.Status="History"
> End If
> End Sub
>
>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:
(Msg. 5) Posted: Tue Sep 08, 2009 1:01 pm
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
You can put code in the AfterUpdate event of the Graduation Date field that
automatically fills in the Status field when you enter the Graduation Date.
Steve
santus.DeleteThis@penn.com
"Brad Hodges" <bradhodges.DeleteThis@discussions.microsoft.com> wrote in message
news:5BFB84A3-9783-4C56-ABDA-2EA9139B3EB5@microsoft.com...
>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.
(Msg. 6) Posted: Tue Sep 08, 2009 1:01 pm
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
(Msg. 7) Posted: Tue Sep 08, 2009 1:01 pm
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
All times are: Eastern Time (US & Canada) (change) Goto page 1, 2, 3
Page 1 of 3
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other