I have a single table DB and want to create a report that counts the reords by month and by week within the month. Is there a custom function available for this?
Any other suggestions appreciated.
The month is pretty easy. Create a calc that concatenates the year and the month. I like to pad a leading zero on the month so it sorts nicely and avoids some potential oddities:
YearDate = Year(date) & Right("00" & Month(date);2)
Create a relationship on this field and count the records across the relationship and you are done.
One reason the week is trickier is that there aren't 4 weeks in a month. Assuming that you want to include everything after the 28th in the 4th week, this should do it:
Use the above field and concatenate on to it:
Day(date) ≤ 7; "01";
Day(date) ≤ 14; "02";
Day(date) ≤ 21; "03";
Again, create a realationship on this field and count the records accross the relationship. You could fancy these up with Let statements, but if the Date field is a stored value it shouldn't make a noticiable difference. In fact for the relationships mentioned, the date would have to be stored.
Thanks I will give that a try!
Thanks, I will give it a try. I don't have any relationships since there is only one table but I think I can set up something that would allow me to try what you suggest.
Again, much appreciated!
Christian M. Deutsch
Acuity Systems, Inc.
Please note that this e-mail and any files transmitted with it may be privileged, confidential and protected from disclosure under applicable law. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying or other use of this communication or any of its attachments is strictly prohibited. If you have received this communication in error, please notify the sender immediately by replying to this message, and deleting this message, any attachments, and all copies and backups from your computer.The information included is from sources deemed reliable. No representation is made as to the accuracy thereof and is submitted subject to errors and omissions.
Note that a table can be related to itself.
Sent from my iPhone
Retrieving data ...