Copy from one field to another

[SDLevy]

Hello,

I've been working with this problem for the last two days and unable to come up with the solution myself. I am hoping it is very simple (and then I'll be very embarrassed to have bothered you!).

I am converting a File Maker Pro database to Access. Everything is great except I need to copy the MembershipID (set up as the Primary key and autonumber) in the Membership table into the PeopleID field in the same file. I know this sounds goofy.

But what I've done is manually fixed all the multiple entries of the records so only the first MembershipID that was generated was pasted in each subsequent record for that person PeopleID field (which is going to be the link back to my master table, People. )

Now I am left with 900+ records of folks who only had one membership record in FMP (actually it wasn't quite that clean which is why I am in this bind)

So what I want to do is copy the contents of MembershipID into PeopleID and move onto the next record.

Two problems I've come up with is I don't know how to translate this idea from English into Access and secondly when I was trying this in a query I couldn't figure out how to have the criteria PeopleID is Null in the same field that I wanted to paste into.

I am thinking I could probably move this into Excel and muck about some more and then move it back. Although I am not sure what will happen to my autonumbering if I move things about. But I am hoping someone would point me in the right direction. If nothing else please give me a clue as to what terms I should be hunting for in my manuals.

Thank you very much,

Susan

 

[charityg]

Myabe this will help. I am making a few assumptions. I am assuming that the table you are attempting to modify is the membership table which contains a MembershipID field and a PeopleID field. For each record where PeopleID is null, you want to assign the value from the MembershipID field. An update query such as the one below should accomplish it.

UPDATE Membership SET Membership.PeopleID = [membershipID]
WHERE (((Membership.PeopleID) Is Null));


This SQL statement in simple English says: In the membership table, update the PeopleID field to the value in the MembershipID field for each record where the PeopleID field is empty.

If I misunderstood your situation, let me know, so that I may be of further assistance.

 

[SDLevy]

Thanks!

Hi,

Your suggestion worked just fine. Thank you very much for translating English into Access for me.

If you ever need help with a knitting problem I'll be glad to reciprocate for your help!

Susan