Trying to create an auto Lookup Query

[aglaia761]

Hi,

I have my participants contact info in one table. I have another
table that is travel information for most not all of these
participants. I'd like to create an auto lookup query that will
automatically enter data from the contact info table into the travel
info table so I don't have to retype it


If anyone could help me figure out what I'm doing wrong I'd appreciate
it.

Here is my SQL info.

SELECT [Contact Info].ID, [Travel Information].[First Name], [Travel
Information].[Last Name], [Travel Information].Address, [Travel
Information].City, [Travel Information].State, [Travel
Information].Zipcode, [Travel Information].[Phone Number], [Travel
Information].[Fax Number], [Travel Information].[Email Address]
FROM [Travel Information] INNER JOIN [Contact Info] ON ([Travel
Information].[Email Address] = [Contact Info].ID) AND ([Travel
Information].[Fax Number] = [Contact Info].ID) AND ([Travel
Information].[Phone Number] = [Contact Info].ID) AND ([Travel
Information].Zipcode = [Contact Info].ID) AND ([Travel
Information].State = [Contact Info].ID) AND ([Travel Information].City
= [Contact Info].ID) AND ([Travel Information].Address = [Contact
Info].ID) AND ([Travel Information].[Last Name] = [Contact Info].ID)
AND ([Travel Information].[First Name] = [Contact Info].ID) AND
([Travel Information].ID = [Contact Info].ID);

 

[Douglas J. Steele]

How can we help you figure out what you're doing wrong when you don't say
what the problem is!

Your SQL query selects the data from both tables. You shouldn't be storing
the contact information redundantly in the travel information table.

If your problem is that you're not seeing all of the rows in the travel
information table (i.e.: those rows where there isn't a corresponding entry
in the contact information table), change the INNER JOIN to LEFT JOIN.

 

[aglaia761]

How can we help you figure out what you're doing wrong when you don't say
what the problem is!

Your SQL query selects the data from both tables. You shouldn't be storing
the contact information redundantly in the travel information table.

If your problem is that you're not seeing all of the rows in the travel
information table (i.e.: those rows where there isn't a corresponding entry
in the contact information table), change the INNER JOIN to LEFT JOIN.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi,

I have my participants contact info in one table. I have another
table that is travel information for most not all of these
participants. I'd like to create an auto lookup query that will
automatically enter data from the contact info table into the travel
info table so I don't have to retype it

If anyone could help me figure out what I'm doing wrong I'd appreciate
it.

Here is my SQL info.

SELECT [Contact Info].ID, [Travel Information].[First Name], [Travel
Information].[Last Name], [Travel Information].Address, [Travel
Information].City, [Travel Information].State, [Travel
Information].Zipcode, [Travel Information].[Phone Number], [Travel
Information].[Fax Number], [Travel Information].[Email Address]
FROM [Travel Information] INNER JOIN [Contact Info] ON ([Travel
Information].[Email Address] = [Contact Info].ID) AND ([Travel
Information].[Fax Number] = [Contact Info].ID) AND ([Travel
Information].[Phone Number] = [Contact Info].ID) AND ([Travel
Information].Zipcode = [Contact Info].ID) AND ([Travel
Information].State = [Contact Info].ID) AND ([Travel Information].City
= [Contact Info].ID) AND ([Travel Information].Address = [Contact
Info].ID) AND ([Travel Information].[Last Name] = [Contact Info].ID)
AND ([Travel Information].[First Name] = [Contact Info].ID) AND
([Travel Information].ID = [Contact Info].ID);

That makes sense that I shouldn't be storing data redundantly.

What I really want to do is to create a report that links peoples
contact information and their travel information to export to Word to
be printed for the travel agent.

I know that there needs to be a relationship between the tables, but
I'm not sure which fields need to be linked

 

[Douglas J. Steele]

That makes sense that I shouldn't be storing data redundantly.

What I really want to do is to create a report that links peoples
contact information and their travel information to export to Word to
be printed for the travel agent.

I know that there needs to be a relationship between the tables, but
I'm not sure which fields need to be linked

You've asked the same question in multiple places. While I'd prefer to keep
the discussion here in the Microsoft group, I've already provided a more
detailed answer in UtterAccess, so let's keep the discussion there.

 

[dbahooker]

Douglas is a known troll for whom the only answer is MDB, regardless
of the question