select all records on one table but limit records from 2nd table

[Guest]

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

 

[Guest]

In your query, include the where clause

Where deaccession is not null

 

[Guest]

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.

 

[Guest]

Could you post the sql of your query?

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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;

 

[Guest]

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

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;

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