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 |
#11
|
|||
|
|||
DYNAMIC STUDENT DATABASE
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 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] |
#12
|
|||
|
|||
DYNAMIC STUDENT DATABASE
On Tue, 8 Sep 2009 16:14:10 -0700, 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? As a calculated field somewhere in the query [admin table], I presume... -- John W. Vinson [MVP] |
#13
|
|||
|
|||
DYNAMIC STUDENT DATABASE
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 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] |
#14
|
|||
|
|||
DYNAMIC STUDENT DATABASE
On Wed, 9 Sep 2009 07:50:11 -0700, Brad Hodges
wrote: 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. -- John W. Vinson [MVP] |
#15
|
|||
|
|||
DYNAMIC STUDENT DATABASE
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 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] |
#16
|
|||
|
|||
DYNAMIC STUDENT DATABASE
|
#17
|
|||
|
|||
DYNAMIC STUDENT DATABASE
|
#18
|
|||
|
|||
DYNAMIC STUDENT DATABASE
Dale,
Thank you very much for your "two cents" which in my small disorganized brain is fifty dollars!! I totally admire your style, yet wonder how many of those MVP's out there actually using naming conventions these days. "Dale Fye" wrote: 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 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] |
#19
|
|||
|
|||
DYNAMIC STUDENT DATABASE
Dale,
Thank you very much for your "two cents" which in my small disorganized brain is fifty dollars!! I totally admire your style, yet wonder how many of those MVP's out there actually using naming conventions these days. "Dale Fye" wrote: 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 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 | |
|
|