Beginner need some pointer on Queries.

[oxyi]

Hi,

I am new with access , currently using access 2007.

I was given a building record query, and was asked to append a value -
RM_TYPE_DESC, and depends on what the value is and assigned another value to this USE_CD field.

For example, if the value for RM_TYPE_DESC is CUSTODIAL, then it would assign a value of 335 to USE_CD. If the value for RM_TYPE_DESC is BSV , then it should assign NAJ to USE_CD.

The concept of it seem very easy, I can program this in C++ or Java with IF/Else statement, but I am not sure how to do it in access at all. I am completely lost in term of what I should do..do I write a formula ? do I do it in macro ? How should I approach this problem ?

Any help is appreciated !

 

[Rabbie]

Try looking up Append Queries in Access Help

 

[oxyi]

Hi thanks for the help !

I tried to do the Query Update function, select the Field I need to update, and put in criteria, and select the field that I need to update to.

It seem to be working but after I press run, it came back with a message stating that I am about to update 1858080 rows !!

When I just did a simple query, it only has 1440 record showed up, is there something I need to specific to only update the 1440 records ?

thanks again !

 

[Rabbie]

It sounds like you have an issue with your criteria and it looks like you have a cartesian join to get such a large number. It would be helpful if you post the SQL of your query so we can see what is going on.

 

[oxyi]

Here is the query I wrote to update
UPDATE SDRecord SET SDRecord.USE_CD = "820"
WHERE (((SDRecord.RM_STD)="CLI"));

 

[Rabbie]

I can see no reason why that query isn't working correctly. I have tested something similar and i gives the same corect totals both as a Select query and as an Update query.

 

[maxmangion]

do you have more than 1 table in your query? if yes, did you create the correct relationship between the tables? Otherwise your SQL statement seems to be fine.

 

[oxyi]

Hi, thanks for the reply.

I am looking at the tables now. there indeed has 3 SDRecord tables
but they are named differently tho.

SDRecord - which is the one I want to work on
SDRecord_062607 - I assume this is a backup
SDRecordBackup - I assume this is a backup as well.
NewSDRecord - not sure what this is

Can it be that my query is doing it to all 3 tables ? but I thought I am only doing it to SDRecord...

thanks !

 

[oxyi]

Update: by deleting the extra SDRecord table, it is now running correctly ! thanks

For future refrence, is there any way that I can just specific I want SDRecord only and not other with similar name ?

 

[Rabbie]

Your query should only look at table SDRecord. That's the correct way to do it.