Query result based upon two field conditions

[diane802]

If I have two tables.



One shows First Name, and Last Name as two separate fields.

Second shows First Name and Last name in one field, with a space separating. The second table has information on it, that I want to relate back into the first table.

How can I combine these two?

How i did it was, I created a query which concatenates First Name and Last name, into a new control field called Full Name. I created a join relationship between Full Name, and the Second Table's primary key "Full Name".

I then created a second query, which brings in the Full Names, and then the related information in the table #2.

However - in this second query, i am unable to actually update say the First and Last Name at all - and have it autopopulate the table #2 updated data. It says I cannot update the recordset.

[RuralGuy]

Typically you would only put the PrimaryKey of the 1st table in the 2nd table as a ForeignKey. Then you can display whatever you want whenever you want.

[diane802]

The query is displaying the results - however, it won't let me in this query, say change the First name from Michael to Mary, and show me the updated result. I woul need to update the First name in the main table, or in query #1 first, and then run this query.

This query says I cannot edit the record.

[RuralGuy]

This link should help explain that issue.

[diane802]

I have been reading over that site several times.

I think my issue is "The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields."

Query#2 which combines First and Last Name, does not have a "primary key" - can I identify a primary key within a query? Right now, it just links to Table #2's Full Name.... I noticed that if I set the query #3 that I am in, to "Dynaset (inconsistent updates)" - it lets me alter the first and last name separately, but it does not update the "Full Name" from Table #2... it does update it in the concatenated version in query #2.

[RuralGuy]

When truly normalized, the first and last name are only in one table. All of the other tables that need this value just use a FK to the other table.

[diane802]

sorry - not sure I understand, so in my table #1, I have first and last name only in that table.

Let's say I need to combine the two in order to use this combination - to look up information from table#2 - where the primary key is one field that has First and Last name combined - what do I do?

[RuralGuy]

Are you in control of the structure for table #2?

[diane802]

yes - i am.

[RuralGuy]

Then my post #6 still applies. What you want can be done but you will struggle with problems. Can you add an AutoNumber field as a PK and a LongInteger as a FK (which points to a record in table #1) and remove the First and Last name from this table?

[MAF4Fam6]

I completely agree with RuralGuy.
Without a Primary Key other the member's Full Name (i.e. Social Security Number, CustomerID, EmployeeID, etc.) you will more than likely run into problems, particularly in your queries. The possibilty exists between your two tables to have more than one person with the same first and last name, not to mention inconistencies between the same record in two tables with different spellings of first names due to long names, short names, or nicknames.

-RC

[diane802]

MAF4Fam6 and RuralGuy - you both make complete sense.

But what if in this table, I have fields such as City and Job Function, and the combination of the two lead me to determine the Business they sit in.

So I have Table #1 which has all the main employee including City and Job Function (in two separate fields). And then I have Table #2 - which shows the aligned business is lets say City is new York, and Job Function is Sales.

How would you go about creating the lookup?

Right now, I created a query that combines City and Job Function into one field (ie New York / Sales). In Table #2, I already have a field in there that shows New York / Sales that is the primary key. When I do a second query to do the lookup, this second query is automatically marking as RecordSet Not Updateable - unless i change the record type to Dynaset (inconsistent updates). However, if I do this - I need to actually hit "Refresh All" on the home button, in order to have the record refresh.

[RuralGuy]

I'm sorry Diane, but create a lookup for what? What are you trying to lookup in Table#2? Why do you have a text field that contains "New York / Sales" as a primary key. Were you just trying to let Access eliminate duplicates?

[diane802]

Basically, table#2 shows City, Function - and then it aligns to a Business.

So lets say in Table #1, I show that a person sits in New York, and there function is Sales. Table #2 will tell us that they work in New York Distribution.

For every City and Function combination - there is only one match to a Business.

So I want to look up this Business Name for each individual.

I made that text field a primary key in Table #2 - because that is unique, and there are no duplicates.

[RuralGuy]

Does table #2 have a 1:1 relationship with table #1?