Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Need Help On Access.


  • Please log in to reply
5 replies to this topic

#1 MaMister

MaMister

  • Members
  • 101 posts
  • OFFLINE
  •  
  • Local time:07:50 PM

Posted 25 September 2006 - 05:10 AM

I have created a database using access and now our company decided to add a extra 0 infront of the existing member number

eg. the old number is 1234 now is 01234.

How can I change for the existing few hundreds records?

BC AdBot (Login to Remove)

 


#2 Tsubota

Tsubota

  • Members
  • 3 posts
  • OFFLINE
  •  
  • Local time:05:50 PM

Posted 25 September 2006 - 11:23 AM

First you will need to make sure that the field type (in the design view of the table) is text.

Then create a new query. It will need to be an update query. Select the field name that contains the field you want to change. In the Update row of the query panel type

"0" & [fieldname]

Now run the query.

If you want to check this first do a select query. Select the field you are changing. In the next blank column, type a name like NewFieldValue followed by a colon (:thumbsup: now type in the same formula I referenced above and run the query. You will be able to see the current value and what the value would be using this formula. I generally always run a select query first to make sure things look like I want them to before running an update query.

#3 MaMister

MaMister
  • Topic Starter

  • Members
  • 101 posts
  • OFFLINE
  •  
  • Local time:07:50 PM

Posted 26 September 2006 - 07:45 AM

Thanks. The method works!

I have another question:

I have:

Database A with fax numbers
- 1. John. W
- 2. Mark. Y
- 3. Sean .S

I exported to a dbf file to do some mass faxing and realised that Mark's fax number is no long in use.

I could export the log of the fax numbers that couldn't fax through to a dbf.

Is there a way to make use of this dbf to update and remove Mark's record from Database A?

#4 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,588 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:07:50 PM

Posted 26 September 2006 - 01:30 PM

You need to run an UPDATE query on the main database based on related, matching, records (by field name or some key that makes sense) from the newly created table with faxes to be invalidated.

Link the two tables and take it from there through Access Help. Do run trial runs without updating initially.

You may want to add a field to your first, main table where you can flag items which are not current. Unless you plan to delete the records in the first place. There are many ways to skin a cat.

Anyway, Run Access Help for "Update query", and then see their topic "Update one table based on another table". Have fun :thumbsup:

#5 MaMister

MaMister
  • Topic Starter

  • Members
  • 101 posts
  • OFFLINE
  •  
  • Local time:07:50 PM

Posted 26 September 2006 - 07:56 PM

I read the help section but it didn't say much...

Btw, what can I put in the update query?

If [faxfail].[faxno] = [faxout].[faxno] then [delete][wholerecord] ?

I am just guessing... :thumbsup:

#6 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,588 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:07:50 PM

Posted 26 September 2006 - 10:00 PM

See if this Microsoft article is a more readable help http://office.microsoft.com/en-us/assistan...1887101033.aspx
There are more syntax examples on simple linked tables. You're close.

Remember I don't know where you going nor what exactly you now have, so this advice can only be logic. What I'd be doing in the original table is just flagging to change/delete in a new field (true/false type). Then after I'm happy with what I see (correct records flagged), I'd just select those records (manually or Delete query) where the flag is set and ... get rid of them, or edit the values or whatever you want to do.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users