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