Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need to alter column values

Posted on 2011-03-02
23
Medium Priority
?
251 Views
Last Modified: 2012-05-11
Experts, please help in to solve the situation. I will explain with example as what I have and what I need.
ID - is used to distinguish a family records(ex 010 belong to one family)
MS - it is an indicator to know who is subscriber and who are dependents
        00 is a subscriber and 01,02,03,04.... are Dependents
FN - First Name
LN- Last NAme
Add1 address 1
Add2 Address 2
req add1 - required address1 is what i need the format as
req add2 - required address 2 is what i need the format as

Id      MS     FN        LN       Add1              Add2         Req Add1                  Req Add2

010    00     Sam      pat      123 XYZ         Apt 3       123 XYZ                            Apt 3
010    01     adam     pat     123 XYZ         Apt 3        C/O Sam Pat                 123 XYZ Apt 3
010    02     eric       lank     123 XYZ         Apt 3        C/O Sam Pat                 123 XYZ Apt 3

 ( 010 is a family records with subscriber 00 and dependents 01,02 records.Look at Req Add1 for MS = 01 & 02 it should be as C/O FN LN of subscribers)

023   00       Ann      coo      235 RMS       Suite 3         235 RMS                          Suite 3
023   01       tan        wood   235 RMS       Suite 3        C/O Ann Coo                235 RMS  Suite 3
(023 is second family records)

and so on I have 1000 family records of these kind.
I am figuring out how to do in Access 2003 as I have table in access database.
0
Comment
Question by:ysssv
  • 9
  • 5
  • 4
  • +1
22 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35021834
Set the Caption property of the table in Design view to your "Friendly Name".

This will change what appears to the user , but will eave the real "Field Name" intact.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35021855
Ooops!

I thought you wanted to change the "Column Names".

Please dis-regard my post.
0
 

Author Comment

by:ysssv
ID: 35021901
I didnt understand what you meant.
I have Id,MS,FN,LN,Add1,Add2 columns only in my table.
After updating with a query Add1 should look like the values in Req Add1
Add2 should look like Req Add1
Req Add1 & Req Add2 are my required output result values
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35022030
<Add2 should look like Req Add1>
Before or After Add1 is modified?

In other words, please post an example of the exact output you are expecting, based on what you posted above...
0
 

Author Comment

by:ysssv
ID: 35022129
I am sorry human mistake......
Add1 should look like Req Add1
Add2 should look like Req Add2

Before:

Id      MS     FN        LN       Add1              Add2
       

010    00     Sam      pat      123 XYZ         Apt 3                                  
010    01     adam     pat     123 XYZ         Apt 3                      
010    02     eric       lank     123 XYZ         Apt 3                      


023   00       Ann      coo      235 RMS       Suite 3        
023   01       tan        wood   235 RMS       Suite 3        

After:

Id      MS     FN        LN       Add1                       Add2  
                       

010    00     Sam      pat      123 XYZ                   Apt 3                                  
010    01     adam     pat     C/O Sam Pat             123 XYZ Apt 3                        
010    02     eric       lank    C/O Sam Pat              123 XYZ Apt 3
 

023   00       Ann      coo      235 RMS                 Suite 3                                  
023   01       tan      wood     C/O Ann Coo         235 RMS  Suite 3                      
0
 

Author Comment

by:ysssv
ID: 35026959
Experts any comments or suggestions
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35030269
I'll look at this tonight if another expert has not chimed in...
0
 
LVL 40

Accepted Solution

by:
als315 earned 1050 total points
ID: 35079692
Test example. Qry - query with desired result.
PS. You can store common data in separate table with one record for each family and in your table only first four columns
DB26859214.mdb
0
 

Author Comment

by:ysssv
ID: 35085263
I am not able to open the database.
can you recheck once and send it again.
Thanks
0
 
LVL 40

Expert Comment

by:als315
ID: 35086061
What error you have? I can succesfully download and run it.
Try this zipped.
DB26859214.zip
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35086397
Do you actually want to change the data in the column or do you want to display the "c/o whoever" in a report and/or on a form?  It makes a good bit of difference. ;-)

You can use a "Self-Join" query to provide the Dependents for each Subscriber and, instead of actually changing the Req Add1 information, substitute the "c/o" information.

If on the other hand, you want to actually plug that information, then you will need to make 2 passes at it:
Pass 1 will make sure that the Subscriber's row gets the Add1 data plugged into the Req Add1 column.
Pass2 will then need to plug the Dependents data.   using something like the code that is attached as SQL_1.
UPDATE TestSelfJoin
INNER JOIN TestSelfJoin AS TestSelfJoin_1
   ON     TestSelfJoin.FamilyCode = TestSelfJoin_1.FamilyCode
SET    TestSelfJoin.ReaDDR1    = "c/o " & [TestSelfJoin_1]![Addr1]
WHERE (TestSelfJoin.MemberCode<>0)
  AND (TestSelfJoin_1.MemberCode=0);

Open in new window

0
 

Author Comment

by:ysssv
ID: 35086398
when i open the database i see blank screen.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35086419
Note:  I used the Addr1 column because that let me get the test working quicker.  You can substitute whatever you want in the SET line.
0
 
LVL 40

Expert Comment

by:als315
ID: 35086594
There are no forms, just open query "Qry"
0
 
LVL 40

Expert Comment

by:als315
ID: 35086624
If you need table as a result, change query type to "Make table" query, select name for new table and run it.
0
 

Author Comment

by:ysssv
ID: 35086666
can you please send the query as an attachment?
I am unable to see any thing in the database
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35087270
ysssv,

Did you take a look at the 2 queries I provided?  

They operate based upon your initial post that indicated that you want the [Req Add1] column in the table updated according to the rules you provided.

You can put those in a macro to execute them sequentially but you will need to have 2 queries on order to perform the update cleanly.  (Technically, it can can be done in one query but that one would relly look complicated . . . see the attached. ;-)
UPDATE TestSelfJoin
       INNER JOIN TestSelfJoin AS TestSelfJoin_1
       ON     TestSelfJoin.FamilyCode = TestSelfJoin_1.FamilyCode
       SET    TestSelfJoin.ReaDDR1    =
              IIF (([TestSelfJoin_1]![MemberCode]=0)
                     ,[TestSelfJoin_1]![Addr1]
                     ,"c/o " & [TestSelfJoin_1]![Addr1]
                  )
WHERE (TestSelfJoin_1.MemberCode=0);

Open in new window

0
 
LVL 40

Expert Comment

by:als315
ID: 35092618
Have you tried to open Database Window (in Access 2003) or Navigatin pane (In Access 2007 and 2010)?
Press F11, if it is hidden.
0
 

Author Comment

by:ysssv
ID: 35098413
To als315 : I tried to open the database in access 2003, access 2007 and when I tried in access 2003 it says
The database in in an unrecognised format.
and in access 2007 it shows me a blank screen with no queries,tables,forms....
Please let me know if you can send me in access 2003

To 8080 Diver:

I am looking for something like this

Before:
Table: Tiers

Id      MS     FN        LN       Add1              Add2        

010    00     Sam      pat      123 XYZ         Apt 3                                  
010    01     adam     pat     123 XYZ         Apt 3                      
010    02     eric       lank     123 XYZ         Apt 3                      


023   00       Ann      coo      235 RMS       Suite 3        
023   01       tan        wood   235 RMS       Suite 3        

After:

Id      MS     FN        LN       Add1                       Add2                          

010    00     Sam      pat      123 XYZ                   Apt 3                                  
010    01     adam     pat     C/O Sam Pat             123 XYZ Apt 3                        
010    02     eric       lank    C/O Sam Pat              123 XYZ Apt 3
 

023   00       Ann      coo      235 RMS                 Suite 3                                  
023   01       tan      wood     C/O Ann Coo         235 RMS  Suite 3  

Query:  (It is not working for me)
update tiers
       INNER JOIN tiers AS c
       ON      tiers.Id= c.ID and tiers.FN = c.FN
       SET     tiers.Add1    =
              IIF (([c]![MS]=00),[c]![Add1],(select "c/o " & [c]![First_Name]&" "& [c]![Last_Name] from tiers  WHERE MS=00))

0
 

Author Comment

by:ysssv
ID: 35098630
To als315:
can you please covert your database into old version
please look at the link what i found
http://www.accessdatabaserepair.com/kb/unrecognized-database-format.htm
0
 

Author Closing Comment

by:ysssv
ID: 35100257
Thanks for the solution
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35101323
Try this version of your query.

You don't need the INNER JOIN on the FirstName (that breaks the process) and you need the FROM and WHERE clauses outside of the IIF statement..
UPDATE tiers
INNER JOIN tiers AS c
   ON          tiers.Id     = c.ID
SET    tiers.Add1    =
       IIF (([c]![MS]=00)
              ,[c]![Add1]
              ,(SELECT "c/o " & [c]![First_Name]&" "& [c]![Last_Name])
           )
FROM    tiers
WHERE   MS=00

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question