Records not displaying

[Guest]

I have a table called Contacts. I have another table called Insurance. I
want to show in a query which Contacts have a particular insurance. If I
query the Contacts table ONLY, I get all the records. If I add the Insurance
table to the query, I get nothing. I'm sure it has something to do with the
relationship, but I can't figure it out.

Can someone PLEASE help?

 

[Chaim]

Your going to have to share more information. What is the relation that you
have set up between the two tables? Also, if you can show the SQL it would
help. Right click in the query design window and select 'SQL View', then
copy and paste into a new post.

 

[Guest]

Thank you so much for responding! I think the Relationship is where my
problem is because I don't even have the Insurance table fields selected in
my Query!

SELECT Contacts.AgentID, Contacts.Agent AS Contacts_Agent, Contacts.[Agent
Name], Contacts.Address, Contacts.City, Contacts.ST, Contacts.Zip,
Contacts.[Phone#], Contacts.Airport, Contacts.CSP, Contacts.[CSP Signed],
Contacts.[CSP Notes], Contacts.[DIM Factor], Contacts.[DIM Factor Notes],
Contacts.[DIM Signed], Contacts.SOA, Contacts.[SOA Signed], Contacts.[SOA
Notes], Contacts.[TSA Agmt], Contacts.[TSA Signed], Contacts.[TSA Notes]
FROM Contacts INNER JOIN Insurance ON Contacts.AgentID = Insurance.[Agent ID]
WHERE (((Contacts.[Agent Name]) Like [Type Last Name or hit ENTER for all] &
"*"));

 

[SteveS]

I have a table called Contacts. I have another table called Insurance. I
want to show in a query which Contacts have a particular insurance. If I
query the Contacts table ONLY, I get all the records. If I add the Insurance
table to the query, I get nothing. I'm sure it has something to do with the
relationship, but I can't figure it out.

Can someone PLEASE help?

Teri,

How are the two tables related?

Let's say that table Contacts has a Primary Key (PK) field named ContactID.
And table Insurance has a PK named InsuranceID.

To relate the two tables, you need a field common to both tables. So in table
Insurance, we add a field to store the value of table Contacts PK. Let's call
it ContactId_FK. This is called a foreign key (FK).

If you have a contact "Buggs Bunny" and he has 3 types of insurance - Life,
Auto and HO, then there will be 3 records in table insurance.

table
Contacts ContactID L_Name F_Name
--------- 1 Bunny Buggs

table
Insurance InsuranceID ContactID_FK InsurType
--------- 1 1 Auto
2 1 Life
3 1 HO


In your query, add tables Contacts and Insurance. Drag ContactID from table
contacts to ContactID_FK in table Insurance. This links the two tables.

From table Contacts, add ContactID, F_Name and L_Name to the design grid. From
table Insurance, add InsuranceID and InsurType to the design grid.

Run the query (Click on the red "!" in the toolbar). You should see 3 lines:

ContactID F_Name L_Name InsuranceID InsurType
1 Buggs Bunny 1 Auto
1 Buggs Bunny 2 Life
1 Buggs Bunny 3 HO


HTH

 

[SteveS]

Teri,

How are the two tables related?

Let's say that table Contacts has a Primary Key (PK) field named ContactID.
And table Insurance has a PK named InsuranceID.

To relate the two tables, you need a field common to both tables. So in
table Insurance, we add a field to store the value of table Contacts PK.
Let's call it ContactId_FK. This is called a foreign key (FK).

If you have a contact "Buggs Bunny" and he has 3 types of insurance -
Life, Auto and HO, then there will be 3 records in table insurance.

table
Contacts ContactID L_Name F_Name
--------- 1 Bunny Buggs

table
Insurance InsuranceID ContactID_FK InsurType
--------- 1 1 Auto
2 1 Life
3 1 HO


In your query, add tables Contacts and Insurance. Drag ContactID from
table contacts to ContactID_FK in table Insurance. This links the two
tables.

From table Contacts, add ContactID, F_Name and L_Name to the design
grid. From table Insurance, add InsuranceID and InsurType to the design
grid.

Run the query (Click on the red "!" in the toolbar). You should see 3
lines:

ContactID F_Name L_Name InsuranceID InsurType
1 Buggs Bunny 1 Auto
1 Buggs Bunny 2 Life
1 Buggs Bunny 3 HO


HTH

Sorry if my previous post was too basic. Couldn't tell from your first post how
experienced you were.

From looking at your reply to Chaim, it looks like you need to stop and
re-evaluate (Normalize) your table structure for Contacts.

It looks like you might need 4 or 5 additional tables.

 

[Guest]

Steve, thanks so much for the DETAILED reply!! I have created the tables per
your instructions and I get an error message: data mismatch in expression.
Have no idea where to go from here.....

 

[SteveS]

Steve, thanks so much for the DETAILED reply!! I have created the tables per
your instructions and I get an error message: data mismatch in expression.
Have no idea where to go from here.....

:

So what do the tables look like now?

You did try this on a COPY of your database, Right???

 

[Guest]

Actually, no. I just created the new tables in the current database and only
used those new tables in the query. I apologize if I'm just too
inexperienced for this. I really appreciate your taking the time to help me
but understand if you don't want to see this through...

 

[SteveS]

Actually, no. I just created the new tables in the current database and only
used those new tables in the query. I apologize if I'm just too
inexperienced for this. I really appreciate your taking the time to help me
but understand if you don't want to see this through...

:

OK, what tables did you add?