A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old November 2nd, 2004, 07:07 PM
joan
external usenet poster
 
Posts: n/a
Default 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  
Old November 2nd, 2004, 08:28 PM
ChrisJ
external usenet poster
 
Posts: n/a
Default

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  
Old November 2nd, 2004, 08:46 PM
joan
external usenet poster
 
Posts: n/a
Default

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  
Old November 2nd, 2004, 09:03 PM
ChrisJ
external usenet poster
 
Posts: n/a
Default

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  
Old November 2nd, 2004, 10:46 PM
joan
external usenet poster
 
Posts: n/a
Default

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  
Old November 2nd, 2004, 11:15 PM
ChrisJ
external usenet poster
 
Posts: n/a
Default

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  
Old November 3rd, 2004, 01:00 AM
joan
external usenet poster
 
Posts: n/a
Default

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  
Old November 3rd, 2004, 02:02 AM
ChrisJ
external usenet poster
 
Posts: n/a
Default

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  
Old November 3rd, 2004, 02:38 AM
joan
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 06:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.