Microsoft Access

Garg

Howdy, I'm trying to do something that would be really easy to do in Excel, but my table exceeds the maximum number of records and fields.

I've got one field in a table that's formatted as text, but has 9-digit numbers in it. An example value is 031882354. I need to create a new field and populate it with the values of the other field, only they need to be divided by 1,000,000. So, I would want a value of 31.882354, without it dropping any decimal places.

I'm totally unfamiliar with Access. The MS Help is about as helpful as hitting myself in the head. I've got the table imported into a database, and I've been toying with queries and such, but can't get anything to permanently store these new values in the field in the table.

Anybody know how to do what I'm hoping is a pretty simple task? Thanks!

Dexter

I don't think you can do it to the primary table, but you can easily make a query in Design view, and then use an expression to create a calculated field.

Eg:

Table 1 contains Field 1 with values

Query 1 contains values from Table 1 Field 1 for it's Field 1, then for Field 2, you enter:

Expr1: [Original]/1000000

When you run that query, it will show you the original numbers and the new calculated values beside them. You'll want to tweak the properties for that field to use a Fixed integer and set the number of decimal places you want.

See the attached database in a zip file to get you started off.

lightnin

give me table schema and expected result sets and i'll help you write your queries. screw those wizards, they make it overly complicated and dont really exploit all the features of sql you might need. also, if it gets rather complex, access really does suck.

BLuKnight

MS Access is a piece of crap. If you're going to do some heavy databasing, consider using MySQL.