help with update query

[harleyberger]

I have a table with 8 columns and 2 million rows. One of the columns is AIR DATE (date/time format) which consists of a single date, 1/9/2012 for example. The column next to it is DAY OF YEAR. I need a query that will update the DAY OF YEAR field based on the AIR DATE field. So, for AIR DATE = 1/9/2012, the DAY OF YEAR would = 9.

[alansidman]

Try this function in your Day of Year Field

=datediff("d",dateserial(1,1,Year([Air Date])),[Air Date])

[harleyberger]

Try this function in your Day of Year Field

I set the field as a calculated field and entered the function. I'm getting this error...

The expression DateDiff("d",dateserial(1,1,Year([Air Date])),[Air Date]) cannot be used in a calculated column

[jzwp11]

Since the day of the year is a calculated value based on the date, it should not be store in the table but calculated on the fly when needed in queries, reports or forms.

[harleyberger]

Since the day of the year is a calculated value based on the date, it should not be store in the table but calculated on the fly when needed in queries, reports or forms.

I'd like it to be stored in the table. I'm using the table in an Excel pivot table and I need to use the DAY OF YEAR field in the pivot table.

[jzwp11]

The better approach is to use a query that pulls the data from the table and you would add a calculated field to the query that handles the day of the year and you would link that to your Excel pivot table.

[alansidman]

Do this in a query, not in the table. Even though AC 2010 allows calculated fields, it is not a good database practice. Calculations should in a real database world only be performed in Queries and Forms.

Alan

[jzwp11]

If you insist on updating the table then this is what the query would look like. You would have to change the name of the table (Table2) to your actual table name. Note the query below will only work for dates in 2012. If you have other years involved, you will need to replace the 12/31/2011 with the last date of year prior to year of the respective air date. The dateserial() function should help with that.

UPDATE Table2 SET Table2.[DAY OF YEAR] = DateDiff("d",#12/31/2011#,[Air Date]);

[alansidman]

You should avoid posting the same question twice as it causes confusion.

As a matter of information, you can export a query with its results to an excel file. Additionally, you can build a pivot table in Access from a query and then export it to Excel also. As jswp11 has indicated, it is bad form to save calculations in a table.

[harleyberger]

If you insist on updating the table then this is what the query would look like. You would have to change the name of the table (Table2) to your actual table name. Note the query below will only work for dates in 2012. If you have other years involved, you will need to replace the 12/31/2011 with the last date of year prior to year of the respective air date. The dateserial() function should help with that.

UPDATE Table2 SET Table2.[DAY OF YEAR] = DateDiff("d",#12/31/2011#,[Air Date]);

My table name is PRESIDENT. So i created a query...

UPDATE PRESIDENT SET PRESIDENT.[DAY OF YEAR] = DateDiff("d",#12/31/2011#,[Air Date]);

Upon running it, I get a dialog box that opens up asking me to enter a parameter value for PRESIDENT.[DAY OF YEAR]. What do I need to enter here?

[jzwp11]

Upon running it, I get a dialog box that opens up asking me to enter a parameter value for PRESIDENT.[DAY OF YEAR]. What do I need to enter here?

You should not be prompted for a parameter value; you have a spelling error or you are not referencing a table/field name correctly.

Remember that the query I posted will only work correctly if your air dates are 2012 dates.

I would make sure you have a backup of the database before you run the update query.