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. |
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
DYNAMIC STUDENT DATABASE
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 "Brad Hodges" wrote in message ... 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. |
#5
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
DYNAMIC STUDENT DATABASE
Whew!
Sounds like an "adventure" Fred |
#8
|
|||
|
|||
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] |
#9
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|