Table calculations. Season from Date in Hunting Harvest Database.

I am trying to set up a hunting harvest database.  I would like to add a 
column to the original data table that automatically calculates the hunting 
season, which is based on a specific harvest timeframe.  Each harvest record 
entry has a specific date.   For example, when a record of a deer harvested 
on 12-3-2007 is entered; I want the database to calculate that the date 
occurred during the fall 2007 season.  Thus, I want 2007 to be automatically 
entered for this record in a separate column.  However, our seasons last into 
the new year, so if for example a record is entered of a deer harvested on 
1-23-2008, I want the database to calculate that the date still occurred 
during the fall 2007 season.   
0
Utf
1/8/2010 3:09:01 PM
access 16762 articles. 3 followers. Follow

1 Replies
1048 Views

Similar Articles

[PageSpeed] 17

Best method would be to set up a table with three columns
tblHuntingSeasons
==fldStartDate
==fldEndDate
==fldSeasonName

Then when you need to hunting season you can either use DLookup function or 
better join the hunting season table to your hunting harvest table.

DLookup would look like the following as a calculated field in your query
Field: Season: DLookup("fldSeasonName", 
"tblHuntingSeason",Format([HarvestDateField], "\#yyyy-mm-dd\#") & " Between 
fldStartDate and fldEndDate")

The subquery would look like:

Field: Season: (SELECT First(fldSeasonName) FROM tblHuntingSeasons WHERE 
[YourTable].[HarvestDate] Between fldStartDate and fldEndDate)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tim wrote:
> I am trying to set up a hunting harvest database.  I would like to add a 
> column to the original data table that automatically calculates the hunting 
> season, which is based on a specific harvest timeframe.  Each harvest record 
> entry has a specific date.   For example, when a record of a deer harvested 
> on 12-3-2007 is entered; I want the database to calculate that the date 
> occurred during the fall 2007 season.  Thus, I want 2007 to be automatically 
> entered for this record in a separate column.  However, our seasons last into 
> the new year, so if for example a record is entered of a deer harvested on 
> 1-23-2008, I want the database to calculate that the date still occurred 
> during the fall 2007 season.   
0
John
1/8/2010 3:43:40 PM
Reply:

Similar Artilces:

Workflow on anniversary date
Hi, I would like to create a task 2 weeks prior an anniversary date. Let's say our customer anniversary date is 2001-12-31, how could I create a workflow so Dec. 15 I will get a reminder? Thanks, Val I need to specify that my account are already created. "Val" wrote: > Hi, > > I would like to create a task 2 weeks prior an anniversary date. Let's say > our customer anniversary date is 2001-12-31, how could I create a workflow so > Dec. 15 I will get a reminder? > > Thanks, > > Val Val, You can insert a wait for timer condition to your...

calculated field 12-30-09
i have a query with the following fields: year, month, customer, debit euro, credit euro and balance (debit-credit). the query returns multible entries for each customer. e.g. customer X in November 2009 has a balance of 500 euro and December 2009 has a balance of -100 euro. i want to build a field that returns 500 euro for November and 400 euro for December. can i achieve that in a query? i also want to create another field that returns a date. in the above mentioned example: if year is 2009 and month is 11 i want to add thrre months, that is the filed should return the d...

eConnectOutTemp table?
Does anyone have any documentation on this table? I sometimes get "duplicate key" errors on this table. Since it is a temp table, I guess I should be able to delete all records. But when? We have 24/7 eConnect messages from our call center and website. -- Leo Hagebeek Project Manager Top Shop Holding NL Hi Leo, There is a in parameter in most of the econnect procedure. @I_vRequesterTrx. Pass the value 1 (ONE) for this parameter. Hope this helps. Wilson Powlous "Leo" wrote: > Does anyone have any documentation on this table? I sometimes get "duplicate ...

the criteria pane and table panes have disappered.
In my excel file I can't see the criteria and table panes and the functions will not reappear. ...

Pivot Table not valid error message when formatting data 'button'.
Created Pivot table/chart, password-protected and put onto website (ie NOt saved file as Web Page). Click on link, get warning message concerning macros and prompted for password. If attempt to change data button by rightclicking on it and selecting format - get error message 'Pivot Table not valid'. Note, accessing the file directly (ie not via the web) works perfectly Ok. PS Also get visual basic error message ' Run time error 1004 :Unable to set the default property of the Pivotitem class' VB code Sub All_Years() CP = "(All)" pf = "Year First Seen"...

Pivot Table #30
Can I build a single pivot table using 2 different data sources i.e A1:C10 and F1:H10 You can create a Pivot Table from multiple consolidation ranges, but you won't get the same pivot table layout that you'd get from a single range. There's an example here: http://www.contextures.com/xlPivot08.html If possible, store your data in one table, and you'll have more flexibility in creating the pivot table. Christian Rossi wrote: > Can I build a single pivot table using 2 different data > sources i.e A1:C10 and F1:H10 -- Debra Dalgleish Excel FAQ, Tips &...

Merging tables
Hello, I do not have deep knowledge of Excel. That is why my proble might appear a bit strange: I have two tables, list1 and list2. In both tables there is a colum with unique ID numbers. The rest of the columns contains differen information in both tables. Example: list1 ----- ID name email 101 bob bob@none.com 102 rita rita@none.com ... 999 jim jim@none.com list2 ----- ID salary 102 2000 103 2500 ... 204 2100 What I would like to get as a result is a combined table, with selecte columns from both tables in it. I.e.: list3 ----- ID name salary 101 bob - 102 rita 2500 ... Anyone who can ...

Hand entered dates view wrong
A user I support is having an issue with a worksheet in Excel. It is not an urgent or crucial fix, but I am interested in people's ideas, since she says it has happened to her before. In each case she threw away the worksheet and started a new one by hand entering the data again. Here's the scenario: She has a worksheet in which she has been adding rows of data over time (6 months or so) and has only about 30 rows of data. Column A are dates, and are formatted as such. After months of opening and saving in this document once a week or so, she now has an issue - when she types...

The case of the imperfect Pivot Table
I created a data list and 8 pivot tables (all from the 1 pivot table) related to that list. (I have 1 pivot table for each column) 1. For some strange reason, the data in the second row was not shown in any of the pivot tables (even after clicking the refresh button many times). Only after I typed something different into one of the cells in that offending row and refresh did the correct data appear. 2. I notice too that if the data in all the rows is blank, then all the fields that the data is based upon are still shown. It seems that a pivot table will only show correct values if at least o...

CRM 4.0 CRMAsyncService and Physical Delete of Records from Entity Base Tables
Hello, Can someone point me to a blog, document, etc. that explains how the physical delete of entity records is accomplished through this service? I assume this .exe has replaced crmdeletionservice.exe that existed in 3.0? I'm looking for information on how often it runs and whether it can be configured, etc. Thanks, Todd Langdon Answer: there is a SQL Agent job named p_HardDeletes that does this work. On Feb 25, 2:06=A0pm, Todd Langdon <todd.lang...@gmail.com> wrote: > Hello, > > Can someone point me to a blog, document, etc. that explains how the > physical delete...

How do I set up a sheet with date headers one week apart.
I am trying to set up a spreadsheet with date headers and/or side labels, one week apart. I have tried without success. Any help gratefully received. 1/1/2006 - 8/1/2006 - 15/1/2006 etc. Frank Taylor Dell Dimension 8400 4 Gig RAM 1.3 GigHz Pentium CPU Multithreaded Windows XP Pro - SR2 Canon S530D 160 Gig Hard Drive. Hi Type your first date in A2. In the next cell use = A2+7 You can fill this across as necessary. Andy. "mossbury" <mossbury@discussions.microsoft.com> wrote in message news:B8E4DAB1-C24F-4AE1-8BDA-2E854FEE78C8@microsoft.com... >I am trying to set up...

How do make a line correspond to date
I have a chart with horizontal bars and I want to put a vertical (trend) date line that corresponds to today's date. -- DMM http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "soconfused" <soconfused@discussions.microsoft.com> wrote in message news:ADC1BD32-DF02-4A7F-B074-DCBBA9D0AAFD@microsoft.com... >I have a chart with horizontal bars and I want to put a vertical (trend) >date > line that corresponds to today's...

How to list accounts and their last balanced on date?
Hi, Is there a way to list accounts and the date they were last balanced on? I have many accounts stored in MS Money and many times I forget to reconcile some infrequently used accounts for months. Sometime it then becomes too late to figure out the missing transactions to bring the accounts upto their latest balance. I was hoping there would be a report which can tell me all open accounts and the date they were last balanced on instead of opening each account individually. BTW, I have MS Money 2001 SE. TIA. P Adhia On 5 Oct 2003 13:53:44 -0700, padhia@yahoo.com (P Adhia) wrote: >Hi, ...

How Do I Calculate My Total Stock Value At A Specific Date?
I'd like to be able to calculate how much stock I had at a paticular date/time in the past. Eg. What was the value of stock I had on the 1st of January? How can i do that? Thanks, Leigh. There is no direct report that can provide you with the stock on hand from a previous date. But there are 2 reports whereby the difference will assist with what you are trying to achieve. You can see all item movement for lets say now to last week and remove this value from the current item value 'should' in thery provide you with a previous date stock value. Hope this helps. Ivan ...

Posting from a form to a table
A have a db that is used to keep track of members that talk and when. the process is that I have a form that pulls in several names from a table "MemberName". each record is associated with a date. I would like to have the date of the record post to each of the names from the "MemberName" table. I imagine that you would need one table for the member names and another table for the talks. One member can give many talks, so the relationship is 1 to many between MemberName table and the Talk table. In the talk table, you would have the MemberID from the memb...

#DIV/0 Pivot Table Problem on Calculated Field
Hi, I have a very large Excel workbook, approximately 95 Megs, 37,000 rows and 50 columns. It has a pivot table with a number of calculated fields, all of which appear to be working fine except one. This field works up to about 10,000 records, but if I increase the number of records much beyond that it will return #DIV/0. The calculation DOES NOT have a divisor in it. It is straight subtraction. I think it is just a memory error due to the size of the workbook. Any other thoughts or work arounds? Thanks, Frank Hayes Anything is possible but it is very unlikely that memory problems ...

Date/Time Modified
Hello, My Access Database consists of many tables. The Query attached to my main Client Information Form is made up of 4 tables. -Client Table -Account Table -Batch No Table -Advisor Table Basically, I want to be able to insert a Date/Time Modified record at the top of my main form that will show a data modified if any record is changed/updated in any of these tables. Is this possible? Thank you, Mellissa -- Message posted via http://www.accessmonster.com ...

Excel 2007 dates and conditional formats
Can anyone please tell me why I can not use a conditional format (<>=) to identify if a date is greater or smaller then another. Everytime I try it, Excel does it right in the first column but misses by 2 days in all following. It also appears to have problems with the years. Example: Date of propposed compleation of a task is 10/10/07; if the date of actual completion is 10/10/07 or sooner, turn GREEN. If the date of completion is 10/11/07 or older, turn RED. Can anyone help?? Regards Mary Hi Mary, First thing to check: What does 10/11/07 mean? November 10 1907? November 10 2007? O...

Pivot Table Refresh Problems
Hi All: Hoping that someone can help. I am trying to refresh a pivot table using the following code which was inserted on the Daily Production Output Sheet(both sheets in the same workbook). I am using Excel 2003 Private Sub Worksheet_Calculate() 'If data on this worksheet changes, refresh the pivot table Sheets("Daily Production Output").PivotTables("PivotTable3").RefreshTable End Sub The Calculation has been set to Automatic. However when I try to run the above code I am getting the following error" Run Time error 1044, Application defined or Object defin...

Pivot Table fields #2
Is there a way to format multiple pivot table fields at one time and have them refresh that way, rather than having to select each column and choose the format options? Thanks CLou: I think the solution for you is to open the Pivot Table Toolbar and select the Field Settings icon. This sets the format for all results for that particular field. One limitation that I haven't found a way around is getting a preferred column size to "stick" and not reset after a table refresh. Bruce >-----Original Message----- >Is there a way to format multiple pivot table fields at ...

Offline database cant be viewed
Outlook CRM Successfully copied all datas to local database for offline browsing. but when trying to browse the offline database, it first shows authentication error and clicking on Try again shows an empty page and no records. For your information, the CRM offline database MSCRM_MSDE.mdf is now more than 450MB but still couldnt browse any records in it. How to rectify the problem? On Feb 25, 11:43 am, TNa <T...@discussions.microsoft.com> wrote: > Outlook CRM Successfully copied all datas to local database for offline > browsing. but when trying to browse the offline datab...

Posting date in general ledger
I would like to have a warning of a post date for manual entries in the general ledger module. It automatic defaults to the system date and I have posted to future months in error. Ex. I am doing adjusting journal entries for May in June and it posted to June. Since you cannot completely delete entry, my june general ledger has a entries that should not be their (along with the voids). Messy looking. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" b...

Pivot Table and Emailing
Hello Everybody, I have a pivot table that we have sent to out side locations, They are viewing this table as "read Only" although I haven't protected the worksheet. Do you guys know of any setting I need to check that could have "secretly" put this Pivot table as read only? Thankyou Hi Chelsea, Right-click the file in the Windows Explorer and choose Properties. On the General tab see if the Attribute Read Only is checked. This happens when a file is written to certain media such as CD's under certain conditions. The pivot table by itself is not Read Only,...

pivot tables #19
I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? -- tiki Hi tikitai, Look at my Excel Database Tutorial at http://edferrero.m6.net/DataTutor1.html You should be able to work it out form there. Ed Ferrero > I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? &...

Pre Decimal Currency
I want to replicate some historical bookeeping in Excel. The currency I want to use is pre-decimal Australian Pounds Shilling and Pence where 2 Halfpenny = 1 Penny, 12 Pence = 1 Shilling and 20 Shillings = 1 Pound. Does anyone know how this can be done? Ken Ken, It rather depends what you want to do with the answers. Obviously I would think that you want to get the figures you have into something that the computer can use, add them up etc. Expressing the results back as LSD might be a bit more tricky. I think that the easiest though perhaps not the prettiest way is to segregate the pound...