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
|
|||
|
|||
Table Design Suggestion
I have a need to design a table that contains:
Field1, DateX Field1 will always be either "A", "B" or "C". The DateX will change. For example, for a known date, ie., January 1, 2004, I would start either that date being maybe "B", Jan 2 would be "C" and Jan 3 would be "A", Jan 4 would be "B" and so on. It will always go in that sequence. It just depends on what value (A,B or C) that I start with. I have a need to start this for at least a year ago and then go ahead into the future for at least two years. Question1: What's the best way to design this? I will reference the DateX to a date (which is date/time format) in another table in order to determine if A, B or C. Question2: If the answer to Question1 is to just setup a table and create a little code to automate the entries for a set period of time, what is that code? |
#2
|
|||
|
|||
Table Design Suggestion
JH
If the value for Field1 is calculable, given the value for DateX, you could use a query to generate the value instead of creating a table. If there are only three possibilities, consider using the Mod() function to determine if the value should be "A", or "B", ... Since Access stores dates (in date/time fields) as an integer indicating how many days elapsed from some time in the late 1800s, the Mod() function could be applied to the integer value of the date. Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
Table Design Suggestion
I like your idea. How do you calculate the value for
Field1 given a date? An example might help me "see" this better. Thanks. -----Original Message----- JH If the value for Field1 is calculable, given the value for DateX, you could use a query to generate the value instead of creating a table. If there are only three possibilities, consider using the Mod() function to determine if the value should be "A", or "B", ... Since Access stores dates (in date/time fields) as an integer indicating how many days elapsed from some time in the late 1800s, the Mod() function could be applied to the integer value of the date. Good luck Jeff Boyce Access MVP . |
#4
|
|||
|
|||
Table Design Suggestion
JH
This is an untested, conceptual approach... IIF([DateX] Mod 3 = 0, "A",IIF([DateX] Mod 3 = 1, "B","C"))) This expression would go in the Field "cell" of a query. -- Good luck Jeff Boyce Access MVP |
#5
|
|||
|
|||
Table Design Suggestion
Jeff,
This is a great concept, and it works, except: Let's say we start with 20040401 as DateX. That yields "C". And 20040402 yields "A" and 20040403 yields "B" and so on. This works good. Now when we get to the end of the month 20040430 = "B" Then when the date changes to 20040501, which is the next date in sequence, the result is "A" instead of the intended "C". Any ideas on that? The result, once started, should always go A, B, C or B,C,A or C,A,B, etc. Always in that order. -----Original Message----- JH This is an untested, conceptual approach... IIF([DateX] Mod 3 = 0, "A",IIF([DateX] Mod 3 = 1, "B","C"))) This expression would go in the Field "cell" of a query. -- Good luck Jeff Boyce Access MVP . |
#6
|
|||
|
|||
Table Design Suggestion
JH
"20040402" is not, according to Access' definition, a "Date/Time". If you check in an earlier response, I mentioned that Access stores dates as the number of days since some time in the late 1800s. One possibility would be to convert your text string (that's what the '20040402' is, a string of characters that happen to be digits) into an actual date that Access knows how to handle. Then you could apply the expression we covered earlier (you might need to also convert the date to an integer, using CInt().) -- Good luck Jeff Boyce Access MVP |
#7
|
|||
|
|||
Table Design Suggestion
Opps. I see that I goofed and used the wrong field.
I went back and applied the test to a true Date field, and it worked perfectly. Thanks for the assistance. -----Original Message----- JH "20040402" is not, according to Access' definition, a "Date/Time". If you check in an earlier response, I mentioned that Access stores dates as the number of days since some time in the late 1800s. One possibility would be to convert your text string (that's what the '20040402' is, a string of characters that happen to be digits) into an actual date that Access knows how to handle. Then you could apply the expression we covered earlier (you might need to also convert the date to an integer, using CInt().) -- Good luck Jeff Boyce Access MVP . |
#8
|
|||
|
|||
Table Design Suggestion
One additional question:
I want to add a time test to this also, and I have a separate field called ETConvert, which is a DATE/TIME field that is a Time in the format HH:MM:SS AM/PM. To determine the correct shift: A, B, or C, the expression needs to consider the following: The day that I am really testing for (to get the shift) is from 7:00:00 AM until 6:59:59 AM the next day (almost 24 hours). (Not based upon midnight to midnight.) An example would be: Date 5/1/04 from 7:00:00AM until Date 5/2/04 6:59:59 AM would be "A". From 5/2/04 7:00:00 AM until 5/3/04 6:59:59 AM would be "B" and so on. How might I be able to figure this into the expression? -----Original Message----- JH "20040402" is not, according to Access' definition, a "Date/Time". If you check in an earlier response, I mentioned that Access stores dates as the number of days since some time in the late 1800s. One possibility would be to convert your text string (that's what the '20040402' is, a string of characters that happen to be digits) into an actual date that Access knows how to handle. Then you could apply the expression we covered earlier (you might need to also convert the date to an integer, using CInt().) -- Good luck Jeff Boyce Access MVP . |
#9
|
|||
|
|||
Table Design Suggestion
JH (see in-line comments)
"JH" wrote in message ... One additional question: I want to add a time test to this also, and I have a separate field called ETConvert, which is a DATE/TIME field that is a Time in the format HH:MM:SS AM/PM. Access date/time fields hold a date (days since .... 189x, as an integer), and a time (decimal portion since midnight - e.g., noon is .5000). You may have formatted your field ETConvert to look like a time-only, but it has a date in it! To determine the correct shift: A, B, or C, the expression needs to consider the following: The day that I am really testing for (to get the shift) is from 7:00:00 AM until 6:59:59 AM the next day (almost 24 hours). (Not based upon midnight to midnight.) If you don't start your "day" at midnight, you'll need to add another expression/field in your query that calculates "your" date (or you could write a procedure and call it in the query). With all the "oh by the way" additions, you might want to consider creating a procedure that does the whole job, rather than try to add pieces onto your query. -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|