Add Data in new column

[Guest]

Hey guys...

I've created a new column in my database that reads "Year". I need to keep
this populated with the current year so that I can pull yearly data within my
queries. I noticed that I have to type them one by one and I have over 16000
to do. Is there an easier way to input them into my database minus exporting
the data into excel, quick copying and then importing it back. I could run
into a lot of integrity issues doing that.

Please help!

 

[Arvin Meyer [MVP]]

Run a query:

UPDATE tblMyData
SET tblMyData.[Year] = "2007";

Year is a reserved word, that's why I bracketed it, so you may want to
rename the column.

 

[Guest]

STANDARD WARNING: Make a backup of any tables that you will update first or
even the entire database.

First things first, would you happen to have another date field in each
record that has the year in it? You can easily extract the year from a date
using the Year() function.

Speaking of the Year() function, you've named your field Year. As Year is a
function, it's possible for Access to get confused on whether to run the
function or look into the Year field. In other words, Year is a reserved word
in Access and should not be used for a field name. I suggest naming your Year
column to something different. Here's a list of Reserved words to avoid:
http://support.microsoft.com/kb/286335/

While changing your Year column to another name, such as RecordDate, make
the RecordDate field Date/Time datatype and make the default value Date().
That way every new record will have the date it was created in it. Then you
can use the Year function to find out the year.

Now to FINALLY answer your original question! You can do this with a simple
Update query.

Assuming that you've taken my better than average advice above, create a new
query based on the table and bring down just the RecordDate field.
Go up to Query and select Update.
In the Update To field put Date() .
Run the query.
That will update all 16K records with today's date.

 

[Guest]

Hey Jerry,

Where is this "Update Query". I can't find it anywhere.

 

[Guest]

I'll expound:
Create a new query based on the table and bring down just the RecordDate
field.

While still in design view for the new query go up to Query on the menu bar
and select Update.

In the Update To field put Date() .

Run the query.

That will update all 16K records with today's date.

 

[Arvin Meyer [MVP]]

It should be in the Query menu item. In any case the code I wrote for you is
exactly the code you need to run, changing only the table name and the year
column if you've renamed it. Open an SQL window: View >>> SQL and paste it
in. Click on the Run button. You're done!

 

[Guest]

You haven't answered Jerry's crucial question as to whether you already have
a date column in the table? As the data is clearly temporal, or you
otherwise would not want to 'pull yearly data', I'd be surprised if you
don't. If you do then you not only don't need a Year column in the table,
but should not have one as it introduces redundancy and the possibility of
inconsistencies as there would be nothing to prevent a year value being
entered which conflicted with the date value.

Assuming you do have a date column lets take a hypothetical example of an
Orders table joined to an OrderDetails table from which you want to return
the number total order amount per customer per year:

SELECT CustomerID, YEAR(OrderDate) AS OrderYear,
SUM(Amount) AS TotalOrderAmount
FROM Orders INNER JOIN OrderDetails
ON OrderDetails.OrderID = Orders.OrderID
GROUP BY CustomerID, YEAR(OrderDate);

If you only want data for a specific year then you can include a parameter
in a query's WHERE clause to prompt for the year at runtime:

WHERE YEAR(OrderDate) = [Enter Year:]

Ken Sheridan
Stafford, England