simple two-table query won't product records

  • Thread starter Thread starter Jo
  • Start date Start date
J

Jo

I've used the query wizard to extract info from two tables into a database
for mail merge. By playing with the relationship line options I can make
records from one or other of the tables appear. I gather the top/first
option is the one that should make all records appear, but I'm getting
nothing.

I'm only new at Access so I'm hoping this might be a simple problem someone
can give me direction on without too much technical speak! (Haven't had any
joy with 'help'.)
 
I've used the query wizard to extract info from two tables into a database
for mail merge. By playing with the relationship line options I can make
records from one or other of the tables appear. I gather the top/first
option is the one that should make all records appear, but I'm getting
nothing.

I'm only new at Access so I'm hoping this might be a simple problem someone
can give me direction on without too much technical speak! (Haven't had any
joy with 'help'.)
Click to expand...

We'll help you if you'll help us. We can't see your tables and have no idea
what you've tried.

The default join option (INNER JOIN) will return all records where there is a
matching value of the joining field in both tables. If there is no match,
you'll see no data - so it's the most restrictive join, not the most
inclusive!

Could you post a description of your tables, and perhaps the SQL view of the
query that you tried? Are there matching records on the field you're using for
the join?
 
Thank you John

One table is a list of schools with address columns. The second is a list
of staff and their titles. I have joined the tables on the "school" field
which has the school name listed exactly the same in each (except one school
is not included in table one). Here's the SQL view:

SELECT Schools.School, Schools.Address1, Schools.Address2, Schools.Town,
Schools.PostCode, StaffNames.[Principal First], StaffNames.[Principal
Surname], StaffNames.[Other First], StaffNames.[Other Surname],
StaffNames.[Other Title]
FROM Schools INNER JOIN StaffNames ON Schools.School = StaffNames.School;
 
Thank you John

One table is a list of schools with address columns. The second is a list
of staff and their titles. I have joined the tables on the "school" field
which has the school name listed exactly the same in each (except one school
is not included in table one). Here's the SQL view:

SELECT Schools.School, Schools.Address1, Schools.Address2, Schools.Town,
Schools.PostCode, StaffNames.[Principal First], StaffNames.[Principal
Surname], StaffNames.[Other First], StaffNames.[Other Surname],
StaffNames.[Other Title]
FROM Schools INNER JOIN StaffNames ON Schools.School = StaffNames.School;
Click to expand...

Hrm. Could you post a few rows of the actual data in each table, including a
putative match? Is the School field possibly a Lookup Field (in which case
it's actually a concealed number, not the school name)? Might one of the
tables have blanks before (or less likely, after) the school name?
 
Yes! It IS a lookup field. (That's how I make sure the values will always
be exactly the same). Can I still make a query using the field? Is there a
way of attaching data to this thread?? Don't know what a putative match is,
sorry.

Appreciate your time, thanks John

John W. Vinson said:
Thank you John

One table is a list of schools with address columns. The second is a list
of staff and their titles. I have joined the tables on the "school" field
which has the school name listed exactly the same in each (except one school
is not included in table one). Here's the SQL view:

SELECT Schools.School, Schools.Address1, Schools.Address2, Schools.Town,
Schools.PostCode, StaffNames.[Principal First], StaffNames.[Principal
Surname], StaffNames.[Other First], StaffNames.[Other Surname],
StaffNames.[Other Title]
FROM Schools INNER JOIN StaffNames ON Schools.School = StaffNames.School;
Click to expand...

Hrm. Could you post a few rows of the actual data in each table, including a
putative match? Is the School field possibly a Lookup Field (in which case
it's actually a concealed number, not the school name)? Might one of the
tables have blanks before (or less likely, after) the school name?
Click to expand...
 
Yes! It IS a lookup field. (That's how I make sure the values will always
be exactly the same). Can I still make a query using the field?
Click to expand...

Sure... but you need to join the (concealed) numeric schoolID to the the
Schools table primary key ID, rather than trying to join the school name. You
assumed that the school name would match in your query but it won't because
your table doesn't CONTAIN a school name, only a (hidden) school ID.

That's one of MANY drawbacks to the misdesigned, misleading Lookup Field type.
What you see is NOT what's there in the table, and it causes problems like
this! See http://www.mvps.org/access/lookupfields.htm for a critique.
Is there a way of attaching data to this thread??
Click to expand...

No need, since you answered the crucial question.
Don't know what a putative match is,
sorry.
Click to expand...

sorry for being tendentious myself <bg>... "putative" = "supposed, expected to
be, claimed to be".

Perhaps you could open each table in design view and post the names and
datatypes of the first few fields (including the school field), if the above
doesn't help.
 
Brilliant. Thanks. You would have thought the help subjects could have
covered that but it's always better to talk to a real person.
 
Back
Top