Solved

Need to alter column values

Posted on 2011-03-02
23
237 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
23 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
 
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 39

Accepted Solution

by:
als315 earned 350 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 39

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 39

Expert Comment

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

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 39

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now