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
|
|||
|
|||
select all records on one table but limit records from 2nd table
here are the table i am working with
tbl_mstr: accession number (primary) name accession date tbl_journal1: record number (primary) accession number date task (list box) deaccession (list box) these tables have a one(tbl_mstr) to many relationship(tbl_journal1) i would like to create a query that would return all the records in tbl_mstr but from tbl_journal1 only those records where the deaccession field is populated with data. my attempts result in a query that returns dublicate results from tbl_mstr because of the many entries that one accession might have in tbl_journal1 OR if i try to limit the results from tbl_journal1 i get only those records that meet what ever criteria i enter. as you might suspect, i have very limited know how with access. TIA |
#2
|
|||
|
|||
In your query, include the where clause
Where deaccession is not null "joan" wrote: here are the table i am working with tbl_mstr: accession number (primary) name accession date tbl_journal1: record number (primary) accession number date task (list box) deaccession (list box) these tables have a one(tbl_mstr) to many relationship(tbl_journal1) i would like to create a query that would return all the records in tbl_mstr but from tbl_journal1 only those records where the deaccession field is populated with data. my attempts result in a query that returns dublicate results from tbl_mstr because of the many entries that one accession might have in tbl_journal1 OR if i try to limit the results from tbl_journal1 i get only those records that meet what ever criteria i enter. as you might suspect, i have very limited know how with access. TIA |
#3
|
|||
|
|||
Hi Chris,
this doesn't quite work. Using 'Where' does not allow me to see which records from tbl_mstr have been deaccessioned. If i add the deaccession field (from tbl_journal)a second time to the query it then returns all the journal records for any given accession number from tbl_mstr. I am not sure if this makes any sense. if you have the time please give it another look. "ChrisJ" wrote: In your query, include the where clause Where deaccession is not null "joan" wrote: here are the table i am working with tbl_mstr: accession number (primary) name accession date tbl_journal1: record number (primary) accession number date task (list box) deaccession (list box) these tables have a one(tbl_mstr) to many relationship(tbl_journal1) i would like to create a query that would return all the records in tbl_mstr but from tbl_journal1 only those records where the deaccession field is populated with data. my attempts result in a query that returns dublicate results from tbl_mstr because of the many entries that one accession might have in tbl_journal1 OR if i try to limit the results from tbl_journal1 i get only those records that meet what ever criteria i enter. as you might suspect, i have very limited know how with access. TIA |
#4
|
|||
|
|||
Could you post the sql of your query?
"joan" wrote: Hi Chris, this doesn't quite work. Using 'Where' does not allow me to see which records from tbl_mstr have been deaccessioned. If i add the deaccession field (from tbl_journal)a second time to the query it then returns all the journal records for any given accession number from tbl_mstr. I am not sure if this makes any sense. if you have the time please give it another look. "ChrisJ" wrote: In your query, include the where clause Where deaccession is not null "joan" wrote: here are the table i am working with tbl_mstr: accession number (primary) name accession date tbl_journal1: record number (primary) accession number date task (list box) deaccession (list box) these tables have a one(tbl_mstr) to many relationship(tbl_journal1) i would like to create a query that would return all the records in tbl_mstr but from tbl_journal1 only those records where the deaccession field is populated with data. my attempts result in a query that returns dublicate results from tbl_mstr because of the many entries that one accession might have in tbl_journal1 OR if i try to limit the results from tbl_journal1 i get only those records that meet what ever criteria i enter. as you might suspect, i have very limited know how with access. TIA |
#5
|
|||
|
|||
SELECT DISTINCTROW tbl_mstr.[accession number], tbl_mstr.Genus,
tbl_mstr.[accessioned date], tbl_journal1.deaccessioned FROM tbl_mstr INNER JOIN tbl_journal1 ON tbl_mstr.[accession number] = tbl_journal1.[accession number] GROUP BY tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], tbl_journal1.deaccessioned HAVING (((tbl_journal1.deaccessioned) Is Not Null)); this version only returns records that have deaccessioned. i would like to see a list of all accessions from the tbl_mstr but also the info in deaccessioned field from tbl_journal1 maybe i need to redo my tables.....yuck "ChrisJ" wrote: Could you post the sql of your query? "joan" wrote: Hi Chris, this doesn't quite work. Using 'Where' does not allow me to see which records from tbl_mstr have been deaccessioned. If i add the deaccession field (from tbl_journal)a second time to the query it then returns all the journal records for any given accession number from tbl_mstr. I am not sure if this makes any sense. if you have the time please give it another look. "ChrisJ" wrote: In your query, include the where clause Where deaccession is not null "joan" wrote: here are the table i am working with tbl_mstr: accession number (primary) name accession date tbl_journal1: record number (primary) accession number date task (list box) deaccession (list box) these tables have a one(tbl_mstr) to many relationship(tbl_journal1) i would like to create a query that would return all the records in tbl_mstr but from tbl_journal1 only those records where the deaccession field is populated with data. my attempts result in a query that returns dublicate results from tbl_mstr because of the many entries that one accession might have in tbl_journal1 OR if i try to limit the results from tbl_journal1 i get only those records that meet what ever criteria i enter. as you might suspect, i have very limited know how with access. TIA |
#6
|
|||
|
|||
SELECT DISTINCTROW tbl_mstr.[accession number], tbl_mstr.Genus,
tbl_mstr.[accessioned date], tbl_journal1.deaccessioned FROM tbl_mstr LEFT JOIN tbl_journal1 ON tbl_mstr.[accession number] = tbl_journal1.[accession number] GROUP BY tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], tbl_journal1.deaccessioned; run this query and look at the output If you are getting lines returned that you don't want, post the line you want and those you don't describing what makes the lines unwanted. you may need to go to a union query |
#7
|
|||
|
|||
accession number Genus accessioned date deaccessioned
1 Viburnum 3/1/2002 1 Viburnum 3/1/2002 dead 2 Acer 3/1/2002 2 Acer 3/1/2002 dead 3 Ostrya 3/1/2002 4 Gingko 5 not sure if you will see this in the same format but this is what i got returned using your SQL. what i would like to see is something more along these lines: accession number Genus accessioned date deaccessioned 1 Viburnum 3/1/2002 dead 2 Acer 3/1/2002 dead 3 Ostrya 3/1/2002 4 Gingko 5 this does not show all lines from tbl_journal1. that have data in fields other than deaccessioned. thanks for sticking with this! "ChrisJ" wrote: SELECT DISTINCTROW tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], tbl_journal1.deaccessioned FROM tbl_mstr LEFT JOIN tbl_journal1 ON tbl_mstr.[accession number] = tbl_journal1.[accession number] GROUP BY tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], tbl_journal1.deaccessioned; run this query and look at the output If you are getting lines returned that you don't want, post the line you want and those you don't describing what makes the lines unwanted. you may need to go to a union query |
#8
|
|||
|
|||
OK, Try this
Create a new query "qrydeaccessioned" Select [accession number], deaccessioned from tbl_journal1 WHERE deaccessioned is not null Then change your query to read SELECT DISTINCTROW tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], qrydeaccessioned.deaccessioned FROM tbl_mstr LEFT JOIN qrydeaccessioned ON tbl_mstr.[accession number] = qrydeaccessioned.[accession number] GROUP BY tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], qrydeaccessioned.deaccessioned; "ChrisJ" wrote: SELECT DISTINCTROW tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], tbl_journal1.deaccessioned FROM tbl_mstr LEFT JOIN tbl_journal1 ON tbl_mstr.[accession number] = tbl_journal1.[accession number] GROUP BY tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], tbl_journal1.deaccessioned; run this query and look at the output If you are getting lines returned that you don't want, post the line you want and those you don't describing what makes the lines unwanted. you may need to go to a union query |
#9
|
|||
|
|||
THANKS CHRIS!
It looks like it is working.....I will try it on the actual DB tomorrow when i am back at work......I've been working at home using a mock up of real database. Think positve thoughts Joan "ChrisJ" wrote: OK, Try this Create a new query "qrydeaccessioned" Select [accession number], deaccessioned from tbl_journal1 WHERE deaccessioned is not null Then change your query to read SELECT DISTINCTROW tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], qrydeaccessioned.deaccessioned FROM tbl_mstr LEFT JOIN qrydeaccessioned ON tbl_mstr.[accession number] = qrydeaccessioned.[accession number] GROUP BY tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], qrydeaccessioned.deaccessioned; "ChrisJ" wrote: SELECT DISTINCTROW tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], tbl_journal1.deaccessioned FROM tbl_mstr LEFT JOIN tbl_journal1 ON tbl_mstr.[accession number] = tbl_journal1.[accession number] GROUP BY tbl_mstr.[accession number], tbl_mstr.Genus, tbl_mstr.[accessioned date], tbl_journal1.deaccessioned; run this query and look at the output If you are getting lines returned that you don't want, post the line you want and those you don't describing what makes the lines unwanted. you may need to go to a union query |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autolookup Query | Patti Davignon | General Discussion | 12 | October 22nd, 2004 02:44 PM |
New records can't be seen | rleblanc | Using Forms | 6 | August 14th, 2004 02:43 PM |
Adding records from another table when doing a make table query | Gilkesy | Running & Setting Up Queries | 1 | July 9th, 2004 05:19 PM |
Query combining multiple records from one table can't add records | Clint Marshall | Running & Setting Up Queries | 4 | July 8th, 2004 01:25 PM |