Use of 7 day periods instead on weeks. (Biological recording d-base) (1 Viewer)

Richard-B

New member
Local time
Today, 13:06
Joined
Dec 28, 2021
Messages
4
OK a normal year has 365 and a leap year 366. If you want to smooth your data in periods similar to weeks that relate to the biological year instead of the commercial week, which has either 52, 53 and every 30 years a 54 weeks. If you use 7 day periods (because people relate to weeks) you end up with a problem what to do with day 366 (ie 31st of December) and leap years. Some people have used 5 day periods because it is divisible into 365.

My solution to the issue is to use a table with day/month (missing out the Year) this becomes a text with a period number (biological week).
However I have come to this solution a bit late.

I have added a text field to my main table (Field Notebook) "dd/mm" (call "DayMonth")
To populate this field using the "Date" (format = Date) from the "Field Notebook" I was trying to use an append query and failing.

I convert the date using - Week: Format$([Date],"dd/mm")
However I am doing something wrong. Can anyone help me
1640731232321.png



1640729765393.png
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:06
Joined
Jan 23, 2006
Messages
15,364
The error message says you have 1648278 key violations.
It might help readers if you could post your table designs and your relationships window.

Via Google:
Key violations You may be trying to append data into one or more fields that are part of the table's primary key, such as the ID field. Check the design of the destination table to see if the primary key (or any index) has the No Duplicates property set to Yes
 

plog

Banishment Pending
Local time
Today, 08:06
Joined
May 11, 2011
Messages
11,613
Can anyone help me

With what? Long, somewhat correct missive about dates, then some quick code, then non-specific request for help.

What is it you are trying to achieve? What is it that is occuring? How are the two different?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:06
Joined
Feb 19, 2002
Messages
42,981
Week is a reserved word (as are month, day, year, and name to mention a few of the most misused names).
You should store the month and day separately or store it as mm/dd so at least it will sort correctly.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:06
Joined
May 7, 2009
Messages
19,175
you can change the Expression to:

Week: Format$(Date(),"dd/mm")
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:06
Joined
Feb 19, 2002
Messages
42,981
Date() is the function that returns todays date.
[Date] is table column with a really bad name since it conflicts with the name of a function and therefore causes confusion at best and bad data at worst.
 

Richard-B

New member
Local time
Today, 13:06
Joined
Dec 28, 2021
Messages
4
Week is a reserved word (as are month, day, year, and name to mention a few of the most misused names).
You should store the month and day separately or store it as mm/dd so at least it will sort correctly.
Thanks for that. It would be best to sort the period at mm/dd, The period attached to this field is numerical sorted.

The thing I am trying to sort is a smoothed biological period of 7 days. do not think week.
It is a way of assign a date to a period. Animals do not use a calendar. if a bird using triggers should as day length and temperature to time its migration. You can use a single date but this is very messy if you do not have enough data so smoothing to data into periods helps manage the data,

Richard B
 

Richard-B

New member
Local time
Today, 13:06
Joined
Dec 28, 2021
Messages
4
The error message says you have 1648278 key violations.
It might help readers if you could post your table designs and your relationships window.

Via Google:
Key violations You may be trying to append data into one or more fields that are part of the table's primary key, such as the ID field. Check the design of the destination table to see if the primary key (or any index) has the No Duplicates property set to Yes
I need to populate the DayMonth field using the date field.
1640773182143.png



The "Day/month" will be used in combination with the T\week table (please do not think week but period instead)
The 01/01/yyyy = day one in period one.

1640773446448.png
 

Attachments

  • 1640773160967.png
    1640773160967.png
    24.4 KB · Views: 241

Minty

AWF VIP
Local time
Today, 13:06
Joined
Jul 26, 2013
Messages
10,355
Please don't call a table T\Week - any special symbols can get interpreted as a mathematical operator, take out the spaces as well as it will save you a bunch of typing, and tracking down weird errors.
If you want to use a naming convention (please do) identify an object as a table use tb_Week or simply tbWeek

Your query should be exactly like arnelgp posted, but I too would advise two fields DateMonth and DateWeek you can then join on them without having to jump through hoops in a query to get it match up.

If you are having just one field use MM/DD as it will sort correctly in ascending order.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:06
Joined
Feb 19, 2002
Messages
42,981
You CANNOT store a string in a date field if that is what you are trying to do.

As a string, the best format is mm/dd unless you want your data to sort by day before month

The datetime data type is a double precision number with the integer being the number of days since 12/30/1899 and the decimal being the elapsed time since midnight.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:06
Joined
Jan 23, 2006
Messages
15,364
If your "period of interest" is 7 days, what is the connection with "commercial week"?
Can you not just use incremental "Periods" of 7 days? Take note of Minty's comments on naming convention.
What is the ultimate purpose of this database?
 

Users who are viewing this thread

Top Bottom