Anyone use Microsoft Access?

[SCW]

I need some basic MS Access help. I've a very good background in spatial databases (ArcGIS) and VB/VBA/.NET, as well as a good understanding of programming higer-functioning Excel spreadsheets. But....Access sucks rocks. I have a simple task to complete on various flat files that will look pretty much the same. These are ASCII files with tables in this format-

X, Y, Z, Time, A, B, C, ....i

I need to make a new column and populate depending on the value of the A, B, C...i columns. These are just toggle columns with only one of the columns having a "1" and the rest having "0". For example, I want to make the value of the new column something like-
Table looks like this

X Y Z TIME A B C
234.12 654.32 12.2 13.25 0 1 0
675.43 987.32 45.2 13.45 1 0 0


if A = 1 then NEW = bob
elseif B = 1 then NEW = fred
elseif c = 1 then NEW = george
....
end if


So-

Now I need to do this in Access (tables are far too large for ArcGIS or Excell), but I can't figure how to write such a function, or where.

Any help? I have a new book published by Microsoft, but it's pretty heavy stuff and nobody in the office knows Access so there really isn't anywhere to ask person-to-person here. Also, I have no experience with SQL thus far, but I'm not afraid of diving in.

Thanks-
Shane

[Zebaru]

Since nobody else is helping, I will - though I hate throwing my SQL anywhere for the world to see. I am more of a self taught hack than anything, but I can always get my SQL to work, even it if isn't pretty or as efficient as possible...

If this is something you want to done once, the simplest way to do it is to use a basic update statement.

UPDATE tablename
SET new = 'bob' WHERE a = 1;

Then you can just replace the fieldnames and run it multiple times until you have gone through the iterations...

(you should probably change your fieldnames to something different than x,y,z, and a,b,c to avoid confusion but...)

If you have just a few fields (like the three in your example), a little nested if statement will also work:

UPDATE tablename
SET new = iif(a = 1,'bob', iif(b = 1, 'fred', iif(c= 1, 'george',null)));

basic syntax or iif : iif(if, then, else)

As fas as I know, you can nest these indefinitely, but it can get pretty messy and easy to lose track of... None of the real programmers I work with use nested ifs for this sort of thing (or anything?), but I do it all the time, and it works.

If you were going to be working outside of Access (like Oracle), the SQL is somewhat different, and it is generally easier to develop much cleaner, faster statements to do this sort of thing - especially if the tables get large.

Travis

[SCW]

Since nobody else is helping, I will - though I hate throwing my SQL anywhere for the world to see. I am more of a self taught hack than anything, but I can always get my SQL to work, even it if isn't pretty or as efficient as possible...

If this is something you want to done once, the simplest way to do it is to use a basic update statement.

UPDATE tablename
SET new = 'bob' WHERE a = 1;

Then you can just replace the fieldnames and run it multiple times until you have gone through the iterations...

(you should probably change your fieldnames to something different than x,y,z, and a,b,c to avoid confusion but...)

If you have just a few fields (like the three in your example), a little nested if statement will also work:

UPDATE tablename
SET new = iif(a = 1,'bob', iif(b = 1, 'fred', iif(c= 1, 'george',null)));

basic syntax or iif : iif(if, then, else)

As fas as I know, you can nest these indefinitely, but it can get pretty messy and easy to lose track of... None of the real programmers I work with use nested ifs for this sort of thing (or anything?), but I do it all the time, and it works.

If you were going to be working outside of Access (like Oracle), the SQL is somewhat different, and it is generally easier to develop much cleaner, faster statements to do this sort of thing - especially if the tables get large.

Travis


Excellent, thanks. I was on the right track but didn't have many syntax components yet, like UPDATE and SET.

What would you use in place of the nested "if"? This will be run very frequently on extremely large files, but at the most there will only be 11 fields so I might simply use a large nested "if" statement.


Could I use something like this-
Select[NEW]From[Table1]Where[a=1,bob OR b=1, fred OR......]

I figured out where to put the SQL statement, now it's just learning SQL!


Thanks again-
Shane

[Zebaru]

I dont really know... In Access, I always play with the ifs, and quite frankly we never really run into the situation like you have. There may be a great command to use, but I am not aware of it. There are a ton of SQL books out there that have alot of examples - if you expect to be using this stuff often it might be worthwhile to get one. Just beware that SQL is not quite universal, and what works in a system like Oracle doesn't always work in Access (and vice versa...).

For now, if you dont feel like messing with a deeply nested if statement, it might be easiest to just create a bunch of individual update scripts and throw together a quick macro or similar script to run them all with a single command or something. This will keep the scripts alot easier to work on in the future and you don't have to worry about trying to keep track of the nesting.

Travis

[XJ Dreamin']

Create a form. It need contain only the fields you're interested in: a, b, c, and new.
Create a button for the form.
Attach this module to the 'click' event for the button.

Option Compare Database
Private Sub Command8_Click()
Dim x, y, z As Integer
Dim a, b As String
Set dbs = CurrentDb
Set rec = dbs.OpenRecordset("table1", dbOpenDynaset)
With rec
.Edit
.MoveLast
a = .Bookmark
.MoveFirst
b = .Bookmark
Do
If Me![a] = 1 Then
Me![new] = "bob"
ElseIf Me![b] = 1 Then
Me![new] = "fred"
ElseIf Me![C] = 1 Then
Me![new] = "george"
End If
DoCmd.RunCommand (acCmdRecordsGoToNext)
If Me.NewRecord = True Then
Exit Do
End If
Loop
rec.close
dbs.close
End With
End Sub

That will populate 'new' as you specified.
Of course, change the field names as necessary. You can add as many Else If clauses as you like.

I tested this on a small example table. If this is happening over network on very large tables there are faster ways to it, but for a single run through on even thousands of records it shouldn't be too bad.

note that 'command8' is the name of the button tn the form that I made for testing. Yours will be different.

[SCW]

Thanks, I'm getting an error that tells me the acCmdRecordsGoToNext is not availble, but I'm pretty sure it's because my pointer to the table is not working. When I check the value of my first field I get a null, when I know that it is a zero.

I'm finding the VBA environment in Access somewhat different than I used to.

[Lou]

I've wasted alot of time writing code in Access when it wasn't really necessary. It is an unintuitive pig.

1) Use the import function. You've probably already done that.

2) Create a table with the fill-in values (e.g. translate_table)

3) Create an Update query as shown below.

4) run it.


http://www3.sympatico.ca/scrapco/Update_qry.JPG

[XJ Dreamin']

Thanks, I'm getting an error that tells me the acCmdRecordsGoToNext is not availble, but I'm pretty sure it's because my pointer to the table is not working. When I check the value of my first field I get a null, when I know that it is a zero.

I'm finding the VBA environment in Access somewhat different than I used to.

Aargh! You Probably need to load a different version of the DAO. It's under the Tools menu. It's still SQL based in the background, but the VBA shorthand is pretty arbitrary.

Use Lou's solution. I'll have to look at that for some of my stuff. I will soon need to migrate my Access stuff over to what the USDA is calling INFRA - a SQL database on the UNIX network that has been integrated with other databases to provide web based data entry and queries. fun, fun, fun.

Good Luck. That code is what I could cobble together in a couple of hours, but Lou's solution is way more elegant and should be a whole heap of a lot faster.

[SCW]

Thanks all. When I try to run the query I get a message box equesting a parameter value. the field I want to populate is populated with whatever I put in this message box.

Any idea where I went wrong? I'm really on the bottom of the learning curve with Access, I didn't think it would be this bad.

Here is what I am doing-
http://img.photobucket.com/albums/v294/utahmom/Shane/Access_Query.jpg

This is the message box I get-
http://img.photobucket.com/albums/v294/utahmom/Shane/ParameterValue.jpg

If I put "bob" in the message box, all values in the feature code field (the new field I'm trying to populate) turn to "bob"


Thanks again-
Shane

[SCW]

Nevermind. I'm retarded, I had a space in the table name, it works great.

Thanks again!