In query reference to variable field in table

[quodo]

Hello,

Is it possible to make a query which has a value in one field that is used to extract that specific field form another table?



What I'd like to do is this:
I need to somehow keep track of changed that are made in my table "Members" (fields like "Member number", "first name", "last name", "address", "city" etc.). And I need to perform those changes by hand in a different database (I'll only get access to that one in a web interface allowing me to change data per member *sigh*, that is doubling the work). Changes are like a new street address, or a new sports activity. Changes in the second db via webinterface have to be done by december 31 of a specific year. Changes in 'my' database have to be done 'instantly'. (That's why I'd like to spread my work)

What I thought of was to make a table "Changes" containing the fields "member nummer" (equal to its counterpart in table Members) and a field that contains the name of the field of the Members table that should be changed, so I'd get
1234 Last name
2345 Address
2345 City
3456 Phone number
for table "Changes". From that I wanted to get a list that gives me those records and the contents of the field that was changed: with a query that contains both tables "Members" and "changes" and I'd get
1234 Last name House-Cuddy
2345 City New York
3456 Phone Number 555-123456

That way I hope to get an easy list of what fields were change, and what I have to change it into. Then delete the record when the change is done.

Well, I'm also in for better ideas than this one :-)
Thanks!

[maximus]

here is my understanding of your problem:
1) When ever you make a change in the existing data in the member table you want the edit details to be stored in a table you have called change.

e.g.
if For a Member say Sam Kabir member Id 2456 you have changed the lastname to Smith.

Then in the Change table you want
memberId = 2456
FieldChanged=LastName
Change=Smith

if I am right, then let me know.

[quodo]

Then in the Change table you want
memberId = 2456
FieldChanged=LastName
Change=Smith

Yes, that is correct.

Although I thought that
- memberId = 2456
- FieldChanged=LastName
might be sufficient (without "Change=Smith"), because the new value will be stored in the "Members" table.
But then I'd need a way to get "LastName" from table "Members" by using the value for "FieldChanged" from table "Changes". And I don't know if and how that's possible (or if there is a better way)?