(Msg. 9) Posted: Tue Sep 08, 2009 2:18 pm
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: microsoft>public>access>tablesdbdesign (more info?)
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
> <bradhodges.DeleteThis@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.
>
> 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]
>
>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
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".
(Msg. 11) Posted: Tue Sep 08, 2009 4:14 pm
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
John,
I'm sorry. I know the difference. I should have used quotes--the name of
the query is "admin table" which is created by using the allstudentdata which
currently is the "catch-all" redundant unnormalized table. Every fiscal year
each new class starts over with a different number. "Basic Writing"
001-2009", "002-2009" and so on, and as of 01 October, Basic Writing
"001-2010", "002-2010" and so son. I have commencement dates, graduation
dates, status field, and a whole lot more. I've broken it down to classes,
with classID PK, and session with classID FK, but fuzzy after that. I'll
repost this question in the appropriate blog.
Thank you so much John. You're fantastic. Where do I put the NZ code?
"John W. Vinson" wrote:
> On Tue, 8 Sep 2009 14:18:26 -0700, Brad Hodges
> <bradhodges.DeleteThis@discussions.microsoft.com> 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]
>
>John,
>
>I'm sorry. I know the difference. I should have used quotes--the name of
>the query is "admin table" which is created by using the allstudentdata which
>currently is the "catch-all" redundant unnormalized table. Every fiscal year
>each new class starts over with a different number. "Basic Writing"
>001-2009", "002-2009" and so on, and as of 01 October, Basic Writing
>"001-2010", "002-2010" and so son. I have commencement dates, graduation
>dates, status field, and a whole lot more. I've broken it down to classes,
>with classID PK, and session with classID FK, but fuzzy after that. I'll
>repost this question in the appropriate blog.
>Thank you so much John. You're fantastic. Where do I put the NZ code?
As a calculated field somewhere in the query [admin table], I presume...
--
(Msg. 13) Posted: Wed Sep 09, 2009 7:50 am
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
John,
It worked like a charm! Thank you very much. If only I can figure out the
DCOUNT function, I will be in pretty good shape.
"John W. Vinson" wrote:
> On Tue, 8 Sep 2009 16:14:10 -0700, Brad Hodges
> <bradhodges RemoveThis @discussions.microsoft.com> wrote:
>
> >John,
> >
> >I'm sorry. I know the difference. I should have used quotes--the name of
> >the query is "admin table" which is created by using the allstudentdata which
> >currently is the "catch-all" redundant unnormalized table. Every fiscal year
> >each new class starts over with a different number. "Basic Writing"
> >001-2009", "002-2009" and so on, and as of 01 October, Basic Writing
> >"001-2010", "002-2010" and so son. I have commencement dates, graduation
> >dates, status field, and a whole lot more. I've broken it down to classes,
> >with classID PK, and session with classID FK, but fuzzy after that. I'll
> >repost this question in the appropriate blog.
> >Thank you so much John. You're fantastic. Where do I put the NZ code?
>
> As a calculated field somewhere in the query [admin table], I presume...
> --
>
> John W. Vinson [MVP]
>
(Msg. 14) Posted: Wed Sep 09, 2009 9:58 am
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
>It worked like a charm! Thank you very much. If only I can figure out the
>DCOUNT function, I will be in pretty good shape.
If you'll post what you're trying to do with DCount() and in what context,
we'll be glad to try to help. It's pretty easy:
DCount("*", "table or query name", "optional criteria")
will count the number of records in a table or query which meet the criteria
you specify. The criterea need to be a String which is a valid SQL WHERE
clause; if you can build a query to retrieve the records you want, you can use
View... SQL to see what the WHERE clause looks like.
--
(Msg. 15) Posted: Wed Sep 09, 2009 12:09 pm
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Brad,
Just my 2 cents. You might want to consider using a naming convention so
that when you look at an object name, you can tell what it is. I would also
recommend (for future use) not putting spaces in table, field, or any other
object names.
I use the prefix "tbl_" for all my tables and the prefix "qry_" for all my
queries.
If a query is for a form (either the form or a control on the form), I use
something like: qry_frm_Admin, or qry_frm_Admin_cbo_Students
Do the same for queries related to forms. This allows me to look at my list
of queries and know exactly what they are being used for.
----
HTH
Dale
"Brad Hodges" wrote:
> John,
>
> I'm sorry. I know the difference. I should have used quotes--the name of
> the query is "admin table" which is created by using the allstudentdata which
> currently is the "catch-all" redundant unnormalized table. Every fiscal year
> each new class starts over with a different number. "Basic Writing"
> 001-2009", "002-2009" and so on, and as of 01 October, Basic Writing
> "001-2010", "002-2010" and so son. I have commencement dates, graduation
> dates, status field, and a whole lot more. I've broken it down to classes,
> with classID PK, and session with classID FK, but fuzzy after that. I'll
> repost this question in the appropriate blog.
> Thank you so much John. You're fantastic. Where do I put the NZ code?
>
> "John W. Vinson" wrote:
>
> > On Tue, 8 Sep 2009 14:18:26 -0700, Brad Hodges
> > <bradhodges.DeleteThis@discussions.microsoft.com> 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]
> >
(Msg. 16) Posted: Wed Sep 09, 2009 5:03 pm
Post subject: Re: DYNAMIC STUDENT DATABASE [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Amen to the naming convention! I use the same convention except I don't use
the underline; it's just extra baggage. Further, I separate subforms from
main forms by using SFrm as a prefix for subforms. Likewise, main reports
are Rpt... and subreports are SRpt.
Steve
santus.TakeThisOut@penn.com
"Dale Fye" <dale.fye.TakeThisOut@nospam.com> wrote in message
news:33A28813-0E4D-49F3-B154-E36F93E420E2@microsoft.com...
> Brad,
>
> Just my 2 cents. You might want to consider using a naming convention so
> that when you look at an object name, you can tell what it is. I would
> also
> recommend (for future use) not putting spaces in table, field, or any
> other
> object names.
>
> I use the prefix "tbl_" for all my tables and the prefix "qry_" for all my
> queries.
> If a query is for a form (either the form or a control on the form), I use
> something like: qry_frm_Admin, or qry_frm_Admin_cbo_Students
>
> Do the same for queries related to forms. This allows me to look at my
> list
> of queries and know exactly what they are being used for.
>
> ----
> HTH
> Dale
>
>
>
> "Brad Hodges" wrote:
>
>> John,
>>
>> I'm sorry. I know the difference. I should have used quotes--the name
>> of
>> the query is "admin table" which is created by using the allstudentdata
>> which
>> currently is the "catch-all" redundant unnormalized table. Every fiscal
>> year
>> each new class starts over with a different number. "Basic Writing"
>> 001-2009", "002-2009" and so on, and as of 01 October, Basic Writing
>> "001-2010", "002-2010" and so son. I have commencement dates, graduation
>> dates, status field, and a whole lot more. I've broken it down to
>> classes,
>> with classID PK, and session with classID FK, but fuzzy after that. I'll
>> repost this question in the appropriate blog.
>> Thank you so much John. You're fantastic. Where do I put the NZ code?
>>
>> "John W. Vinson" wrote:
>>
>> > On Tue, 8 Sep 2009 14:18:26 -0700, Brad Hodges
>> > <bradhodges.TakeThisOut@discussions.microsoft.com> 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]
>> >
All times are: Eastern Time (US & Canada) (change) Goto page Previous1, 2, 3
Page 2 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