use search qry result for something else

[enriquemellado]

hi
here is my question:

i have a table (tblmembers) with member data (name, last name) etc.
i found how to run a search qry with parameters to ask for name, last name.

i have another table (tblrelationships) with 3 fields
2 for member id's and 1 for the type of relationship they have

i need to run the search query for the member by name (from tblmembers) and use his member id (from the member table) to add it to a new record in the relationship table (tblrelationships).

when i run the search qry, i only get as the result the fields with the member information..
thanks

 

[ajetrumpet]

i have another table (tblrelationships) with 3 fields, 2 for member id's

Why do you have two fields for the same information (member ID field)??

i need to run the search query for the member by name (from tblmembers) and use his member id (from the member table) to add it to a new record in the relationship table tblrelationships).

Why are you adding records with the same ID of the member in the same table??
***If you don't have a member ID field in the members table I would add it now, and also add a one-to-many between your two tables.
More information on the above questions asked would be helpful in trying to help you with your issue...

 

[enriquemellado]

thanks for the reply...
the reason i have 2 fields of the same thing, is because of what i was thinking to solve my relations problem...
i need to establish family relationships.
i thought by making a table with the type of relationship and a table with 3 fields the 2 members of the relationship and the relationship type
i could solve it..
not sure this is a good approach to solving the problem... just thought i'd try

 

[Moniker]

Normalization
Normalization
Normalization

(Those are all different links -- one should make it clear.)

 

[ajetrumpet]

If you haven't already gotten what you need from Moniker's articles, or you simply don't want to read through all of that stuff...your DB should be set up like the following (in my opinion)....

tblmembers = [memberID](PK), [other data]
tblrelationships = [memberID](FK), [relationship]
***One-many relationship
1 = tblmembers (memberID) - Primary Key
MANY = tblrelationship (memberID) - Foreign Key (aka, related field)

This should set you up good enough to do the work you need to do now (or though it seems). Doing this I think would satisfy this need of yours...

i need to establish family relationships.

You should also clarify this comment so people can offer any more help on this setup if you need it....

i need to run the search query for the member by name (from tblmembers) and use his member id (from the member table) to add it to a new record in the relationship table (tblrelationships).

This quote is a bit too vague, still need more information on what you need EXACTLY...right now, to me, it seems redundant, but I don't you're meaning to say what I'm deciphering...

 

[enriquemellado]

thanks everyone for your help.. i'm finding the time to read those articles...

ajetrumpet:
thanks for the reply.
by family relationships i mean:
my db is for members (tblmembers), that contains normal member info, like name, address, b-day, etc...
i need to establish when some of these members are family related.
only when bboth people in the relationship are current members.
if a member's son isn't a member in the db then that relationship would not exist.
i was thinking of doing the following
create a table for the relationships(tblrelationships) .. with 3 fields
2 for member name or id and one for the realtionship they have
for example:

Key Member ID Relationship Member ID
1 245 father---son 246
2 123 grandpa--grandson 345

when i would need to display the relationship i could do a search query for the member id by asking for the name of the member or directly by member id

i do think its not very efficient since the query would have to go through all the table to find all of a members relationships...
but i'm still getting the hang of this db thing.......
hope i was clear enough this time thanks

 

[enriquemellado]

sorry that table came out wrong... it should look like this


Key---- Member ID -------Relationship ----------Member ID
1-------- 245------------ father---son------------- 246
2 --------123--------- grandpa--grandson---------- 345

 

[ajetrumpet]

I don't think that table setup is the best...you've got more than one data string in one field (relationship field). I would set up like this...

Key----MemberID-----Member-----Related member-----RelatedmemberID
1---------245---------father------------son----------------246

If you do it this way, or even the way that you are thinking of, I would be putting some controls on a form and be linking your query to this form...
1) so its user friendly
2) so you can organize the data you need efficiently

I can see a form set up for this that has two combos for lookup choices, one for ID and one for member name. Then I see underneath these, however many lines of text boxes you need to pull data from the query based on your combo selection. I'd say you'd need three text boxes per line (one that lists the related member's ID, one that lists the related member's name, and one that lists the relationship). This would look great and it would also give you all the information you would need... =)

Here's what I think you're form should look like...
[combo1]=memberID, [combo2]=membername
LINE 1
textbox1=memberID, textbox2=related member name, textbox3=relationship
Here is the microsoft article that tells you how to do this...http://support.microsoft.com/kb/319482/en-us