Table Design Suggestion

[JH]

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?

 

[Jeff Boyce]

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>

 

[Guest]

I like your idea. How do you calculate the value for
Field1 given a date? An example might help me "see" this
better. Thanks.

 

[Jeff Boyce]

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.

 

[JH]

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.

 

[Jeff Boyce]

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().)

 

[Guest]

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.

 

[JH]

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?

 

[Jeff Boyce]

JH (see in-line comments)

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.