linking

[Guest]

ok. I have 3 tables in a MS Access database. They were imported from a text
file. Anyway. Table A has field 1 in common with field 1 in Table B. So they
need to be linked. Table B also has field 30 incommon with field 1 in Table
C. So they need to be linked. When I create a relationship and run a query of
only Table A field 1, Table B fields 1 and 30, and table C field 1 there are
no results. Please Help.

 

[Pat Hartman \(MVP\)]

1. Save yourself a lot of grief and rename the columns so the foreign keys
are all named the same.
2. When you join a-b, the result set returns only rows where BOTH a and b
have a matching row. When you join a-b-c, the result set returns only rows
where ALL THREE tables have matching rows. Not knowing what your tables
are, I can't tell if what you are trying to do is logical but substituting
Left Joins for Inner Joins may solve your problem.

 

[Guest]

Here is the SQL

SELECT Build.Field1, Parcel.Field1, Parcel.Field30, Taxname.Field1
FROM (Parcel INNER JOIN Build ON Parcel.Field1=Build.Field 1) INNER JOIN
Taxname ON (Build.ID= Taxname.ID) AND (Parcel.Field30=Taxname.Field1);

 

[Guest]

Anyone got any suggestions?

Here is the SQL

SELECT Build.Field1, Parcel.Field1, Parcel.Field30, Taxname.Field1
FROM (Parcel INNER JOIN Build ON Parcel.Field1=Build.Field 1) INNER JOIN
Taxname ON (Build.ID= Taxname.ID) AND (Parcel.Field30=Taxname.Field1);

 

[Tony Toews [MVP]]

1. Save yourself a lot of grief and rename the columns so the foreign keys
are all named the same.

Not that this debate is appropriate for this thread but I respectfully
disagree.

1) Duplicate field names in different tables sometimes require extra
work while in VBA code as you have to prefix the field name with the
table name.

2) I prefer to use my own naming standard which guarantees that field
names will never be duplicate within the database.
Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews [MVP]]

Anyone got any suggestions?

In the query builder click on the line joining the tables. Change the
join type to include all records from one table and only those records
from another table where the joined fields are equal.

The idea here is that you may have some records in table A that don't
have child records in table B. Thus for data to appear in your query
for those records in table A you must choose the above option.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[John W. Vinson]

Anyone got any suggestions?

Pat had some good ones; perhaps if you could answer her questions
she'd be able to help. We have NO way to know what results your query
should be expected to provide, since we have no way to see what's *in*
your tables.

The query that you posted:

SELECT Build.Field1, Parcel.Field1, Parcel.Field30, Taxname.Field1
FROM (Parcel INNER JOIN Build ON Parcel.Field1=Build.Field 1)
INNER JOIN Taxname
ON (Build.ID= Taxname.ID) AND (Parcel.Field30=Taxname.Field1);

will first select all records from Parcel where there is a match
between Parcel.Field1 and Build.Field1; of those records, it will find
all cases where both ID and Field30 have matches in Taxname. If either
Build.ID or Taxname.ID is a Lookup Field you'll get nothing, because
what you see in the datasheet view isn't actually what's in the table;
do you have any lookups?

John W. Vinson [MVP]

 

[Pat Hartman \(MVP\)]

". Save yourself a lot of grief and rename the columns so the foreign keys
are all named the same." - I left off the important qualification which is "
all named the same as the primary keys to which they refer." Obviously (or
maybe not so obviously) I don't name all my primary keys "ID" as the MS
samples do.

If you still have an argument with my suggestion then I think we're going to
have to disagree on this one. We can still go drinking together though. In
queries that join two tables, I only select the foreign key field so I don't
have an issue with duplicate names and in code, when I'm working with
multiple recordsets, the field names are all qualified by the recordset name
so they don't also need to have the table name embedded in them. I
specifically use short recordset names and never use the "With - End With"
construct because I like to see the qualification. It keeps me from having
to scroll up to see what object I'm working with in long sections of code.

 

[Tony Toews [MVP]]

". Save yourself a lot of grief and rename the columns so the foreign keys
are all named the same." - I left off the important qualification which is "
all named the same as the primary keys to which they refer." Obviously (or
maybe not so obviously) I don't name all my primary keys "ID" as the MS
samples do.

Right. I name my fields cID for the customer auto number primary key
and the customer id foreign key field on the invoice header table
would be named ihCustomerID.

If you still have an argument with my suggestion then I think we're going to
have to disagree on this one.

That's fine. I like throwing in some different viewpoints.

We can still go drinking together though.

<chuckle>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm