Populate a new field in an existing table

Hi all

Access 2003:

I have an existing Access table, containing 10,000+ rows called "Printing" 
and I have decided to insert a new column called "Financial Year".  However, 
I am struggling of finding a way to instantly populate this field with the 
value "2009-10", without going into each row (I have three more tables to do 
this to with 30,000+ rows)

Going forward it will be populated with all the other fields, but I just 
need to bring them all up to date first.

Can anyone help out?

Thanks for reading.

DominicB
0
Utf
5/27/2010 8:59:37 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

3 Replies
2467 Views

Similar Articles

[PageSpeed] 58

Write a query to do an insert into that column for each row...

Update MyTable Set [Financial Year] = "2009-10" Where [Financial Year] IS 
NULL;

By the way, I strongly advise against using any column name with an embedded 
space.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"Dominic" wrote:

> Hi all
> 
> Access 2003:
> 
> I have an existing Access table, containing 10,000+ rows called "Printing" 
> and I have decided to insert a new column called "Financial Year".  However, 
> I am struggling of finding a way to instantly populate this field with the 
> value "2009-10", without going into each row (I have three more tables to do 
> this to with 30,000+ rows)
> 
> Going forward it will be populated with all the other fields, but I just 
> need to bring them all up to date first.
> 
> Can anyone help out?
> 
> Thanks for reading.
> 
> DominicB
0
Utf
5/27/2010 8:52:41 PM
On Thu, 27 May 2010 13:59:37 -0700, Dominic
<Dominic@discussions.microsoft.com> wrote:

>Hi all
>
>Access 2003:
>
>I have an existing Access table, containing 10,000+ rows called "Printing" 
>and I have decided to insert a new column called "Financial Year".  However, 
>I am struggling of finding a way to instantly populate this field with the 
>value "2009-10", without going into each row (I have three more tables to do 
>this to with 30,000+ rows)
>
>Going forward it will be populated with all the other fields, but I just 
>need to bring them all up to date first.
>
>Can anyone help out?
>
>Thanks for reading.
>
>DominicB

This field should simply NOT EXIST AT ALL, if it can be derived from other
data in the table - e.g. a date field.

The Update query Dorian suggests will work nicely, but I really question
whether you're solving the wrong problem!
-- 

             John W. Vinson [MVP]
0
John
5/27/2010 11:36:27 PM
Thank you for the advice guys.  The information in the field isn't available 
elsewhere.

Dorian, the query worked perfectly, and thank you for the heads up on spaces 
in column names.  This has now been amended.

Dominic

"John W. Vinson" wrote:

> On Thu, 27 May 2010 13:59:37 -0700, Dominic
> <Dominic@discussions.microsoft.com> wrote:
> 
> >Hi all
> >
> >Access 2003:
> >
> >I have an existing Access table, containing 10,000+ rows called "Printing" 
> >and I have decided to insert a new column called "Financial Year".  However, 
> >I am struggling of finding a way to instantly populate this field with the 
> >value "2009-10", without going into each row (I have three more tables to do 
> >this to with 30,000+ rows)
> >
> >Going forward it will be populated with all the other fields, but I just 
> >need to bring them all up to date first.
> >
> >Can anyone help out?
> >
> >Thanks for reading.
> >
> >DominicB
> 
> This field should simply NOT EXIST AT ALL, if it can be derived from other
> data in the table - e.g. a date field.
> 
> The Update query Dorian suggests will work nicely, but I really question
> whether you're solving the wrong problem!
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
5/28/2010 1:53:01 PM
Reply:

Similar Artilces:

new-item -type directory inquiry
I've creating a script that inventories a machine and then copies the information out to a netowrk share. Here is the beginning of the script: $MachineName = hostname $Path = '\\server\share\Machine_Inventory\' + $MachineName $DirectoryExists = Test-Path $Path - PathType Container If ($DirectoryExists = 'False') {New-Item \\server\share\Machine_Inventory\$MachineName -type directory} This part works fine if it finds the directory doesn't exist. It creats the directory and then continues along and inventories the machine and places the inventory results ...

New Cell Styles
Can anyone enlighten me as to the purpose of the "Good,Bad and Neutral" formating in Cell Styles and also the "Data and Model" formatting? Thanks........... In XL2007, Microsoft has provided some suggest colours schemes that seem to go together in a theme. Suppose you have monthly sales; maybe you want to colour good months with one colour and bad with another. Date and Model are similar. One can use one colour for cell with formulas (calculations); another for raw data. All that is being offered is a palette of colours; it is up to the user to decide if and when to ...

Populating Excel from CSV
Hello all, I need to know how to populate an Excel template with data from a external CSV file. Let's say I have this nice pretty colourful templat that is far more pleasing to the eye than the usual chunk of importe CSV data, what are some methods to populate it with data from a CS file?? Any ideas or suggestions or redirections would be much appreciated. Thanks in advance : -- AJMorgan59 ----------------------------------------------------------------------- AJMorgan591's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2777 View this thread: http://www.ex...

Creating new record types in CRM 3.0 possible?
Hi, I was wondering if it is possible to create a new record type within CRM 3.0 because although the default CRM record types will be used (like client and business) our current database has all of the information for both in the same database. The idea is to create a 'temp' record type for us to add the database to, and then allow us to go through the database and move/link the information from the 'temp' record type into the correct record type afterwards. Is this at all possible or is there another simple solution to this problem? Thanks Hez Sorry if this question has ...

My New NG post never get there, but they show in "Sent"
Recently I noticed that any new post will never get posted, but it did show up in my sent folder. I wonder if it has anything to do with me changing from OE to Outlook for my email. Your help is appreciated............Peter Nothing to do with OL, OE is the default newsreader, though it may be accessed within OL or IE. I do see a post in this ng from you, dated 2nd "PeterM" <rpm@NOSPAM.dcn.org> wrote in message news:q9udnYzlyfK8ILDfRVn-sQ@omsoft.com... > Recently I noticed that any new post will never get posted, but it did show > up in my sent folder. I wonder if it...

Sequence Not populated for the MO Numbers during Data Collection
When data collection is done for the MO Numbers by using the Data Collection Window under Manufaturing, the Sequnce look up does not show the whole list, it only shows NOTES, for the user to select and enter the necessary data. why does this happen in the case of some MO Numbers and in the others it shows all the sequnces as NOTES,MAKE READY, PRESS RUN, WASH UP, MAINTENANCE, REPAIRS, REWIND/INSPECTION, etc. Any information would be much appreciated -- Thanks AshaM It should show all of the router steps. Are some of your steps possibly marked to backflush labor? These steps would not...

Unable to click on charts & unable to create any new charts Excel
Hi Just started using Microsoft Excel 2007 and I seem to have done something that means the chart option is unavailable. The chart section is now grey and all charts I have created are 'locked' I cannot click on them anywhere. I'm working on a project and need it finished by Monday. Do you mean that if you click on a chart it does not get a border to show it is activated and there are no range-finder borders around the data that makes the chart? This is a bit odd. Have you tried a new workbook to see if the problem is with just this one? My advice with all odd things is...

Addding notes and rating fields to a report
I just started experimenting with creating some reports using vb.net 2003 and the SRS. So far getting most of the data on the report has been pretty easy but I cant seem to get certain data on there. I would like to have columns available for the notes and also rating as of yet I have not been able to find them. Any ideas? ------=_NextPart_0001_666CDF65 Content-Type: text/plain Content-Transfer-Encoding: 7bit <xxdcmast@discussions.microsoft.com> wrote: > I just started experimenting with creating some reports using vb.net 2003 and > the SRS. > > So far getting mos...

eConnect
In eConnect, I tried adding an additional XML node to the existing XML schema. I like to have these to be added in line item level. When I tried this, it worked fine when I added a node (along with a procedure in db) at the header level. But it fails to perform at line item level. Please help me with the steps to achieve the same. Thanks in advance. ...

how to replace the "new" and "delete" operator funtion define in mfc.
I am trying to overload the global new and delelte operator function in order to use my own memory allocation mechanism.Every time i do it smoothly until i get to a MFC project. The problem is that there are already a set of new and delete operator function defined in afxmem.cpp of MFC. While i'm trying to replace them with my own,the new operator call the MFC's operator new function and call my operator delete function.This is a surelost. How can i replace them completely? Booker, Best regards. It is strongly recommended that you do not overload operator new/delete....

Outlook 2003 'New Mail Desktop Alert' Doesn't Work
I have this option enabled in Outlook 2003 Advanced E-mail Options, running on WinXP Pro SP1, yet I've never once seen the actual Alert appear (transparent message box) when e-mail is received. The Preview Alert works fine. I have NAV 2003 Standard running with the Office Plug-in enabled & scanning of incoming & outgoing mails, yet the problem persists even when I disable the NAV Auto-Protect. Is there a conflict between the Outlook Alert & NAV 2003, or is it something else? the alerts don't fire if something is at full screen. -- Diane Poremsky [MVP - Outlook] Author...

how do populate empty cells with the contents of populated cells .
I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow...

!!!!!!! new a.r rahman albems !!!!!!!!!
http://www.winnersdifferce.blogspot.com ...

Excel 2000 w/pivot tables corrupted when saved ?
I have a user that has a spreadsheet (Office 2000 SP3) that was saved and when she tried to open it again, she got the message "Unable to read the file". When I tried to open the same file with Excel 2002 (SP3) I recieved the same message but then excel continued to open the file and tried to fix the problems. Once the spreadsheet was viewed, I noticed some data corruption in the pivot table. Some field names were replaced with unreadable characters. I am assuming that Excel 2000 corrupted the file on save since it could not open it back up immediately. But why and why no messag...

Checking for new records
I am new to VBA and need to call "TravelForm" from the "MeetingsForm" It should open in to a new travel with travel info from "tbltravel" filled in. I tried the following code in the "on Click" property of the calling button in the MeetingsForm. Private Sub btnTravel_Click() On Error GoTo Err_btnTravel_Click Dim DocName As String DocName = "frmTravel" DoCmd.OpenForm DocName, OpenArgs:=Me![Order_Num] Exit_btnTravel_Click: Exit Sub Err_btnTravel_Click: MsgBox Error$ Resume Exit_btnTravel_Click End Sub Then on the T...

Converting older "File As" Last, First contacts to new Default "File As" (First Last) order, all at once?
Hi, I have 500+ contacts, that were originally created with "File As" being Last, First. My new "File As" order is First Last. Is there a any way to select all my existing contacts and convert the older Last, First contacts to the new "File As", without going through them all one_by_one and doing it manually? TIA. Bill Burke http://www.adondo.net Hello Bill, you can use the Tool "ChangeNames" (http://www.marchert.de/redirect.php?location=software_cn) to change the order automatically. Peter -- Peter Marchert [EDP-Service Marchert] Homepage: htt...

pivot table version issue
A client created a pivot table in Excel 2003. In 2003, the filter dropdown arrows are visible and working. Anyone that opens the file in Excel 2000 does not see the dropdown filters. When you click on the pivot table, Excel does recognize it as a pivot table since the ptable toolbar is active and I am able to access the layout, etc. I copied the data sheet to a new file (using Excel 2003 - different PC), recreated the pivot table and now users with Excel 2000 can see the dropdowns. It is either a setting on the client's PC or a setting within the pivot table itself - but I can't...

Add New Record 02-20-08
I created a Form / Subform using Access wizrd. I want to be able to go to a new record on the Form when I hit enter on a control on the subform - which will goto a new blank record on the Form & Subform. Do I do this in Code and how would I do this? My VB skills from 1-10 (10=expert) is about 3-4. -- Gator Docmd.GoToRecord acForm, Me.Name, acNewRec -- Dave Hargis, Microsoft Access MVP "Gator" wrote: > I created a Form / Subform using Access wizrd. I want to be able to go to a > new record on the Form when I hit enter on a control on the subform - which > w...

Autoplay for Pictures Not Populated
I inserted my XD card in to my card reader today and the autoplay pop up window asking what I wanted Windows to do was blank. For Video, Music and Mixed content the options are there but not for Pictures - it is just blank. I have tried running autofix.exe but this has not fixed it. Any ideas? Is this a Microsoft problem or has something else caused this? It worked fine until today. Thanks. Peachypumpkin wrote: > I inserted my XD card in to my card reader today and the autoplay > pop up window asking what I wanted Windows to do was blank. For > Video, Music and Mi...

New User Question 1 of 2
We just installed RMS (Jan 31 2005), and had LOTS of problems with the installation, due entirely to the general inept way our account was handled by the MS partner who sold us the system and (attempt to) perform the data conversion. As a result, we are out a significant amount of money and have an untrained staff. I was wondering if anyone could recommend a different partner to work with, other than the one in Portland. Thanks in advance. -- Bud Izen Hi Bud :-) Here are some ideas for you... and sorry to hear you are starting out a bit bumpy... but you will be fine. First you sh...

from field upload file problem
www.icingpictures.co.uk win xp FP 2003 online order form url http://www.icingpictures.co.uk/online_order_form.htm Problems with online order form - upload files form field Everything on form works fine 100% except one aspect. Maximum file size it can upload. I can successfully upload up to 8 files simultaneously using the from providing the file sizes are small (under 2MB). If I try and upload either one file or a number of files and the combined size is around 2MB plus – I get a user name and password prompt – obviously customers using the form would not know this and th...

how do i arrange survey data best for a pivot table?
There are pivot table instructions and links on Jon Peltier's web site: http://www.peltiertech.com/Excel/Pivots/pivotstart.htm Mormonchick wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Creating a new mailbox
When I create a userid object for a new employee in 'Users and Computers' the NT account is created. I usually wait about 3-5 minutes and the email address are created. Yesteday when I created a new user the email address took about 15 minutes, but Exchange 2003 only created a X500 email address. No smtp and email adress for the different domain names I support on Exchange 2003. I deleted the entry and recreated the same user with the same result. However once when attempting to edit the object in Exchange's 'Users and Computers' tool I received the following errror ...

New window underneath in IE8
When I open (left click) an email in IE8 it opens in a new window that is underneath (hidden) the current window. How / where do I permanently change a setting so the email opens on TOP of the current screen. By right clicking on email then "open in new window" it opens on top. PSRumbagh wrote: > When I open (left click) an email in IE8 it opens in a new window that is > underneath (hidden) the current window. How/where do I permanently change > a setting so the email opens on TOP of the current screen. By right clicking > on email then "open in n...

City and State Populated by Zip Code
Does anyone know of a way to load a program that when you type in the Zip Code the City and State are automatically populated? I have seen a MSCRM add-in from a company called QAS (http://www.qas.com/). This is more than just Zip Code/City&State. It's a full address validator along with streamlined data entry. They have integrated with CRM via an ISV pop-up and it seems to work fairly smoothly. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On 15 Apr 2005 11:13:56 -0700, "Morgan" <tzeoli@att.net> wr...