If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
simple two-table query won't product records
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'.) |
#2
|
|||
|
|||
simple two-table query won't product records
On Thu, 5 Feb 2009 19:05:01 -0800, Jo wrote:
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'.) 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? -- John W. Vinson [MVP] |
#3
|
|||
|
|||
simple two-table query won't product records
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; "John W. Vinson" wrote: On Thu, 5 Feb 2009 19:05:01 -0800, Jo wrote: 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'.) 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? -- John W. Vinson [MVP] |
#4
|
|||
|
|||
simple two-table query won't product records
On Thu, 5 Feb 2009 20:01:01 -0800, Jo wrote:
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; 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? -- John W. Vinson [MVP] |
#5
|
|||
|
|||
simple two-table query won't product records
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" wrote: On Thu, 5 Feb 2009 20:01:01 -0800, Jo wrote: 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; 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? -- John W. Vinson [MVP] |
#6
|
|||
|
|||
simple two-table query won't product records
On Thu, 5 Feb 2009 21:49:00 -0800, Jo wrote:
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? 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?? No need, since you answered the crucial question. Don't know what a putative match is, sorry. 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. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
simple two-table query won't product records
Brilliant. Thanks. You would have thought the help subjects could have
covered that but it's always better to talk to a real person. "John W. Vinson" wrote: On Thu, 5 Feb 2009 21:49:00 -0800, Jo wrote: 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? 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?? No need, since you answered the crucial question. Don't know what a putative match is, sorry. 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|