Criteria across 2 fields

[NigelB]

I have a couple of fields QtyYear & QtyAmount.
QtyYear contains the year (2002, 2003 etc) for the given amount (QtyAmount).
However, if there was no amount for that year, then there is no QtyYear & no
QtyAmount.
For example you may have:
2002 166
2003 134
2004 56
2005 99
These fields are part of a table joined to another table that provides donor
information like name, address etc.
In my query I only want to see those donors who did not give in 2005 (ie
2005 not in table for that donor) and amount >99 for any of the other Donor
years. Hope that makes sense. Thanks

 

[Tom Ellison]

Dear Nigel:

You will need a list of all possible years. This could be a table, but it
may suffice to use a query:

SELECT DISTINCT QtyYear
FROM SomeTable

The table may be faster and more effective. Just one column to put the
years in.

I'll call the query above, or the table I would prefer, MyYears. This will
be used in the following.

I will assume you have a table of donors. I call it Donor (the "table that
provides donor information").

A third table is the one with Year and Amount. I'll call it the YearAmount
table.

A cross-product of all donors with all years can be compared with the
YearAmount table to find missing ones.

This is the most general solution, showing all donors and the years they did
not contribute:

SELECT D.Donor, Y.AYear
FROM Donor D, MyYears Y
WHERE NOT EXISTS (
SELECT *
FROM YearAmount YA
WHERE YA.Donor = D.Donor
AND YA.AYear = Y.AYear)

To use this, you must figure out all the table names and column names I have
used, and correlate them with what you actually have. If you give full
details in your post, I can put your names in and save you time.

A simple version for just one year is:

SELECT D.Donor
FROM Donor D
WHERE NOT EXISTS (
SELECT *
FROM YearAmount YA
WHERE YA.Donor = D.Donor
AND YA.AYear = 2005)

Tom Ellison

 

[NigelB]

Tom thanks for the update. My existing two tables are called TblOrg & TblQty.
Within TblOrg is Donor, Address, City etc. Within TblQty is QtyYear, QtyLoc,
QtyAmount. I think I see how to substitiute the names but provided them here
to check. One last thing as I'm fair basic with Access, the "Select ... from
.... where" solution how would I enter that in a SQL query ? Thanks so much
Nigel

 

[Tom Ellison]

Dear Nigel:

Likely you are only familiar with the design view of a query.

A button at the upper left of the screen (unless you have re-arranged them)
has a downward arrow to the right of it. This gives various views for a
query.

The easiest way to get to the SQL view would be to create a new query in
Design View (the default). Close the dialog about tables without selecting
one. The control will then read SQL. Click here. Past the query I
provided OVER the text that shows (replace it or delete it). Save this
query with a new name and try it (altering it first if required, as will be
stated in my previous post).

Since this is a text form of the query, and since many queries cannot even
be viewed in Design View, this is the best format for conveying queries in a
newsgroup.

Tom Ellison

 

[NigelB]

Thanks again. So with my names in the following, I would change Donor to
TblOrg & YearAmount to TblQty ? :

SELECT D.Donor, Y.AYear
FROM Donor D, MyYears Y
WHERE NOT EXISTS (
SELECT *
FROM YearAmount YA
WHERE YA.Donor = D.Donor
AND YA.AYear = Y.AYear)

I will try to get to check this out this week.

 

[Tom Ellison]

Dear Nigel:

About your table names, I don't know.

TblOrg sounds like it would be a table of orgainizations. Is this a table
of Donors?

TblQty sounds like a table of quantities. I have no idea what a quantity
has to do with what you are doing.

Only you would know what table names represent what entities in this
database.

Tom Ellison

 

[NigelB]

Okay here are the table details:
TblOrg
OrgId (PK)
Donor
Address
City
State
Zip

TblQty
OrgId (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

TblYear
MissYear

TblOrg contains info about the Donors & has a relationship to TblQty which
lists the year they gave (QtyYear) followed by amount, date of gift &
location. TblYear has one field MissYear with 4 records - 2002, 2003,2004 &
2005.

Here is what I tried:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.Donor = D.Donor
AND YA.QtyYear = Y.MissYear);

But I get a pop up asking for parameter YA.Donor. I guess I'm not
understaning what you mean with the D. & the Y. in the select ? Maybe I
substiutied my names wrong. Thanks

 

[Tom Ellison]

Dear Nigel:

The pop-up is because the query references Donor in YA, which is TblQuery.
There is not Donor in that table.

These things I did not know before.

Your tables are "joined" on OrdID instead. I expect that is what you need
here:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Am I perhaps getting close?

Tom Ellison

 

[NigelB]

Thanks for continuing to work on this one, I appreciate it. Yes, after I had
written back I released I should be using OrgId as that is how the tables are
joined.
Can you help me understand some of the syntax in the SELECT:
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
This selects field Donor from TblOrg & labels TblOrg as D & field MissYear
from TblYear & labels TblYear as Y ?

WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
This labels TblQty as YA ?


WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Thanks

 

[Tom Ellison]

Dear Nigel:

The D, Y, and YA are called aliases. In queries that deal with more than
one table, this is useful to keep the typing and reading to a minimum. In
many queries, they are essential. When a query makes reference to a table
more than once, the two instances of that table must be kept separate. This
is not the case here, but it's still a useful shorthand. It keeps you from
having such long table references to write and to read.

You can drop the aliasing and substitute the full table names in each place
if you prefer.

Tom Ellison

 

[NigelB]

Thankyou, thankyou. I have tetsed both queries, they are as follows:
Just for 2005 ...
SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005))=False))
ORDER BY D.Donor;

& all years did not contribute ...
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = Y.MissYear))=False))
ORDER BY D.Donor;

However, I would like to expand the first query (2005 only) to also display
the following fields from TblQty QtyYear, QtyAmount & QtyLoc. So along with
the Donor info which is showing, I show what they gave in these other years.
Each time I try to expand the query, for example I changed the FROM statement:
old FROM TblOrg AS D
new FROM TblOrg AS D, TblQty AS Q
then the query goes nuts, runs for ages & returns thousands of records.
I have all the "D" fields ( D.Donor, D.City, D.State, D.[Contact Phone #],
D.[First Name], D.[Last Name], D.[Phone #], D.Email), I just want to display
3 more fields from table TblQty.
Am I making sense ? Thanks

 

[Tom Ellison]

Dear Nigel:

When you do this:

FROM TblOrg AS D, TblQty AS Q

you are telling the query to consider every combination of Donor with every
donation made.

Likely, within this set of thousands of combinations, you really only want
to consider donations made by the same donor. I'm saying you don't want to
consider the donations made by John when you're looking at the row for Mark.
So, you would filter to remove those unwanted combinations. This will
greatly reduce the number of rows returned and may improve performance. May
I suggest this:

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D, TblQty Q
WHERE D.OrgID = Q.OrgID
AND NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

If my assumptions are any good, this should be a step in the right
direction.

Now, the above is actually equivalent to an INNER JOIN

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
INNER JOIN TblQty Q
ON D.OrgID = Q.OrgID
WHERE NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

Any help? I tried!

Tom Ellison

Thankyou, thankyou. I have tetsed both queries, they are as follows:
Just for 2005 ...
SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005))=False))
ORDER BY D.Donor;

& all years did not contribute ...
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = Y.MissYear))=False))
ORDER BY D.Donor;

However, I would like to expand the first query (2005 only) to also
display
the following fields from TblQty QtyYear, QtyAmount & QtyLoc. So along
with
the Donor info which is showing, I show what they gave in these other
years.
Each time I try to expand the query, for example I changed the FROM
statement:
old FROM TblOrg AS D
new FROM TblOrg AS D, TblQty AS Q
then the query goes nuts, runs for ages & returns thousands of records.
I have all the "D" fields ( D.Donor, D.City, D.State, D.[Contact Phone #],
D.[First Name], D.[Last Name], D.[Phone #], D.Email), I just want to
display
3 more fields from table TblQty.
Am I making sense ? Thanks

Dear Nigel:

The D, Y, and YA are called aliases. In queries that deal with more than
one table, this is useful to keep the typing and reading to a minimum.
In
many queries, they are essential. When a query makes reference to a
table
more than once, the two instances of that table must be kept separate.
This
is not the case here, but it's still a useful shorthand. It keeps you
from
having such long table references to write and to read.

You can drop the aliasing and substitute the full table names in each
place
if you prefer.

Tom Ellison

 

[NigelB]

Thanks for the update. I had managed to figure it out by reviewing other
queries. I have one additional question. First of all here is what I have:

SELECT TblOrg.Donor, TblOrg.City, TblOrg.State, TblOrg.[Contact Phone #],
TblOrg.[First Name], TblOrg.[Last Name], TblOrg.[Phone #], TblOrg.Email,
TblQty.QtyYear, TblQty.QtyAmount, TblQty.QtyLoc
FROM TblOrg INNER JOIN TblQty ON TblOrg.OrgId=TblQty.OrgId
WHERE (((TblOrg.Donor)<>("Dbl Springs Cumberland Presb. Church-RLY")) AND
((TblQty.QtyAmount)>99) AND ((Exists (SELECT * FROM TblQty YA
WHERE YA.OrgId =TblOrg.OrgId AND YA.QtyYear = 2005))=False))
ORDER BY TblOrg.Donor;

In the where clause above, I am checking Donor to be not equal to "Dbl
Springs ... .. ". I would also like to check Donor for "Life Care Center of
Bruceton-RLY" so that I ignore the record if Donor is either of these. I
can't seem to add in the check for "Life Care Center of Bruceton-RLY"

This would complete it, thanks

Dear Nigel:

When you do this:

FROM TblOrg AS D, TblQty AS Q

you are telling the query to consider every combination of Donor with every
donation made.

Likely, within this set of thousands of combinations, you really only want
to consider donations made by the same donor. I'm saying you don't want to
consider the donations made by John when you're looking at the row for Mark.
So, you would filter to remove those unwanted combinations. This will
greatly reduce the number of rows returned and may improve performance. May
I suggest this:

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D, TblQty Q
WHERE D.OrgID = Q.OrgID
AND NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

If my assumptions are any good, this should be a step in the right
direction.

Now, the above is actually equivalent to an INNER JOIN

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
INNER JOIN TblQty Q
ON D.OrgID = Q.OrgID
WHERE NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

Any help? I tried!

Tom Ellison

Thankyou, thankyou. I have tetsed both queries, they are as follows:
Just for 2005 ...
SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005))=False))
ORDER BY D.Donor;

& all years did not contribute ...
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = Y.MissYear))=False))
ORDER BY D.Donor;

However, I would like to expand the first query (2005 only) to also
display
the following fields from TblQty QtyYear, QtyAmount & QtyLoc. So along
with
the Donor info which is showing, I show what they gave in these other
years.
Each time I try to expand the query, for example I changed the FROM
statement:
old FROM TblOrg AS D
new FROM TblOrg AS D, TblQty AS Q
then the query goes nuts, runs for ages & returns thousands of records.
I have all the "D" fields ( D.Donor, D.City, D.State, D.[Contact Phone #],
D.[First Name], D.[Last Name], D.[Phone #], D.Email), I just want to
display
3 more fields from table TblQty.
Am I making sense ? Thanks

Dear Nigel:

The D, Y, and YA are called aliases. In queries that deal with more than
one table, this is useful to keep the typing and reading to a minimum.
In
many queries, they are essential. When a query makes reference to a
table
more than once, the two instances of that table must be kept separate.
This
is not the case here, but it's still a useful shorthand. It keeps you
from
having such long table references to write and to read.

You can drop the aliasing and substitute the full table names in each
place
if you prefer.

Tom Ellison


Thanks for continuing to work on this one, I appreciate it. Yes, after
I
had
written back I released I should be using OrgId as that is how the
tables
are
joined.
Can you help me understand some of the syntax in the SELECT:
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
This selects field Donor from TblOrg & labels TblOrg as D & field
MissYear
from TblYear & labels TblYear as Y ?

WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
This labels TblQty as YA ?


WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Thanks

:

Dear Nigel:

The pop-up is because the query references Donor in YA, which is
TblQuery.
There is not Donor in that table.

These things I did not know before.

Your tables are "joined" on OrdID instead. I expect that is what you
need
here:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Am I perhaps getting close?

Tom Ellison


Okay here are the table details:
TblOrg
OrgId (PK)
Donor
Address
City
State
Zip

TblQty
OrgId (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

TblYear
MissYear

TblOrg contains info about the Donors & has a relationship to TblQty
which
lists the year they gave (QtyYear) followed by amount, date of gift
&
location. TblYear has one field MissYear with 4 records - 2002,
2003,2004
&
2005.

Here is what I tried:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.Donor = D.Donor
AND YA.QtyYear = Y.MissYear);

But I get a pop up asking for parameter YA.Donor. I guess I'm not
understaning what you mean with the D. & the Y. in the select ?
Maybe I
substiutied my names wrong. Thanks


:

Dear Nigel:

About your table names, I don't know.

TblOrg sounds like it would be a table of orgainizations. Is this
a
table
of Donors?

TblQty sounds like a table of quantities. I have no idea what a
quantity
has to do with what you are doing.

Only you would know what table names represent what entities in
this
database.

Tom Ellison


Thanks again. So with my names in the following, I would change
Donor
to
TblOrg & YearAmount to TblQty ? :

SELECT D.Donor, Y.AYear
FROM Donor D, MyYears Y
WHERE NOT EXISTS (
SELECT *
FROM YearAmount YA
WHERE YA.Donor = D.Donor
AND YA.AYear = Y.AYear)

I will try to get to check this out this week.
:

Dear Nigel:

Likely you are only familiar with the design view of a query.

A button at the upper left of the screen (unless you have
re-arranged
them)
has a downward arrow to the right of it. This gives various
views
for
a
query.

The easiest way to get to the SQL view would be to create a new
query
in
Design View (the default). Close the dialog about tables
without
selecting
one. The control will then read SQL. Click here. Past the
query
I
provided OVER the text that shows (replace it or delete it).
Save
this
query with a new name and try it (altering it first if required,
as
will
be
stated in my previous post).

Since this is a text form of the query, and since many queries
cannot
even
be viewed in Design View, this is the best format for conveying
queries
in a
newsgroup.

Tom Ellison


Tom thanks for the update. My existing two tables are called
TblOrg
&
TblQty.
Within TblOrg is Donor, Address, City etc. Within TblQty is
QtyYear,
QtyLoc,
QtyAmount. I think I see how to substitiute the names but

 

[Tom Ellison]

Dear Nigel:

How about

Donor NOT IN ("Dbl Springs Cumpbeland Presb. Church-RLY", "Life Care
Center of Bruceton-RLY")

Tom Ellison

Thanks for the update. I had managed to figure it out by reviewing other
queries. I have one additional question. First of all here is what I have:

SELECT TblOrg.Donor, TblOrg.City, TblOrg.State, TblOrg.[Contact Phone #],
TblOrg.[First Name], TblOrg.[Last Name], TblOrg.[Phone #], TblOrg.Email,
TblQty.QtyYear, TblQty.QtyAmount, TblQty.QtyLoc
FROM TblOrg INNER JOIN TblQty ON TblOrg.OrgId=TblQty.OrgId
WHERE (((TblOrg.Donor)<>("Dbl Springs Cumberland Presb. Church-RLY")) AND
((TblQty.QtyAmount)>99) AND ((Exists (SELECT * FROM TblQty YA
WHERE YA.OrgId =TblOrg.OrgId AND YA.QtyYear =
2005))=False))
ORDER BY TblOrg.Donor;

In the where clause above, I am checking Donor to be not equal to "Dbl
Springs ... .. ". I would also like to check Donor for "Life Care Center
of
Bruceton-RLY" so that I ignore the record if Donor is either of these. I
can't seem to add in the check for "Life Care Center of Bruceton-RLY"

This would complete it, thanks

Dear Nigel:

When you do this:

FROM TblOrg AS D, TblQty AS Q

you are telling the query to consider every combination of Donor with
every
donation made.

Likely, within this set of thousands of combinations, you really only
want
to consider donations made by the same donor. I'm saying you don't want
to
consider the donations made by John when you're looking at the row for
Mark.
So, you would filter to remove those unwanted combinations. This will
greatly reduce the number of rows returned and may improve performance.
May
I suggest this:

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D, TblQty Q
WHERE D.OrgID = Q.OrgID
AND NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

If my assumptions are any good, this should be a step in the right
direction.

Now, the above is actually equivalent to an INNER JOIN

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
INNER JOIN TblQty Q
ON D.OrgID = Q.OrgID
WHERE NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

Any help? I tried!

Tom Ellison

Thankyou, thankyou. I have tetsed both queries, they are as follows:
Just for 2005 ...
SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005))=False))
ORDER BY D.Donor;

& all years did not contribute ...
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = Y.MissYear))=False))
ORDER BY D.Donor;

However, I would like to expand the first query (2005 only) to also
display
the following fields from TblQty QtyYear, QtyAmount & QtyLoc. So along
with
the Donor info which is showing, I show what they gave in these other
years.
Each time I try to expand the query, for example I changed the FROM
statement:
old FROM TblOrg AS D
new FROM TblOrg AS D, TblQty AS Q
then the query goes nuts, runs for ages & returns thousands of records.
I have all the "D" fields ( D.Donor, D.City, D.State, D.[Contact Phone
#],
D.[First Name], D.[Last Name], D.[Phone #], D.Email), I just want to
display
3 more fields from table TblQty.
Am I making sense ? Thanks

:

Dear Nigel:

The D, Y, and YA are called aliases. In queries that deal with more
than
one table, this is useful to keep the typing and reading to a minimum.
In
many queries, they are essential. When a query makes reference to a
table
more than once, the two instances of that table must be kept separate.
This
is not the case here, but it's still a useful shorthand. It keeps you
from
having such long table references to write and to read.

You can drop the aliasing and substitute the full table names in each
place
if you prefer.

Tom Ellison


Thanks for continuing to work on this one, I appreciate it. Yes,
after
I
had
written back I released I should be using OrgId as that is how the
tables
are
joined.
Can you help me understand some of the syntax in the SELECT:
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
This selects field Donor from TblOrg & labels TblOrg as D & field
MissYear
from TblYear & labels TblYear as Y ?

WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
This labels TblQty as YA ?


WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Thanks

:

Dear Nigel:

The pop-up is because the query references Donor in YA, which is
TblQuery.
There is not Donor in that table.

These things I did not know before.

Your tables are "joined" on OrdID instead. I expect that is what
you
need
here:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Am I perhaps getting close?

Tom Ellison


Okay here are the table details:
TblOrg
OrgId (PK)
Donor
Address
City
State
Zip

TblQty
OrgId (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

TblYear
MissYear

TblOrg contains info about the Donors & has a relationship to
TblQty
which
lists the year they gave (QtyYear) followed by amount, date of
gift
&
location. TblYear has one field MissYear with 4 records - 2002,
2003,2004
&
2005.

Here is what I tried:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.Donor = D.Donor
AND YA.QtyYear = Y.MissYear);

But I get a pop up asking for parameter YA.Donor. I guess I'm not
understaning what you mean with the D. & the Y. in the select ?
Maybe I
substiutied my names wrong. Thanks


:

Dear Nigel:

About your table names, I don't know.

TblOrg sounds like it would be a table of orgainizations. Is
this
a
table
of Donors?

TblQty sounds like a table of quantities. I have no idea what a
quantity
has to do with what you are doing.

Only you would know what table names represent what entities in
this
database.

Tom Ellison


Thanks again. So with my names in the following, I would
change
Donor
to
TblOrg & YearAmount to TblQty ? :

SELECT D.Donor, Y.AYear
FROM Donor D, MyYears Y
WHERE NOT EXISTS (
SELECT *
FROM YearAmount YA
WHERE YA.Donor = D.Donor
AND YA.AYear = Y.AYear)

I will try to get to check this out this week.
:

Dear Nigel:

Likely you are only familiar with the design view of a query.

A button at the upper left of the screen (unless you have
re-arranged
them)
has a downward arrow to the right of it. This gives various
views
for
a
query.

The easiest way to get to the SQL view would be to create a
new
query
in
Design View (the default). Close the dialog about tables
without
selecting
one. The control will then read SQL. Click here. Past the
query
I
provided OVER the text that shows (replace it or delete it).
Save
this
query with a new name and try it (altering it first if
required,
as
will
be
stated in my previous post).

Since this is a text form of the query, and since many
queries
cannot
even
be viewed in Design View, this is the best format for
conveying
queries
in a
newsgroup.

Tom Ellison


Tom thanks for the update. My existing two tables are
called
TblOrg
&
TblQty.
Within TblOrg is Donor, Address, City etc. Within TblQty is
QtyYear,
QtyLoc,
QtyAmount. I think I see how to substitiute the names but

 

[NigelB]

Thanks again Tom, but I don't see where in the SQL to code that ?

Dear Nigel:

How about

Donor NOT IN ("Dbl Springs Cumpbeland Presb. Church-RLY", "Life Care
Center of Bruceton-RLY")

Tom Ellison

Thanks for the update. I had managed to figure it out by reviewing other
queries. I have one additional question. First of all here is what I have:

SELECT TblOrg.Donor, TblOrg.City, TblOrg.State, TblOrg.[Contact Phone #],
TblOrg.[First Name], TblOrg.[Last Name], TblOrg.[Phone #], TblOrg.Email,
TblQty.QtyYear, TblQty.QtyAmount, TblQty.QtyLoc
FROM TblOrg INNER JOIN TblQty ON TblOrg.OrgId=TblQty.OrgId
WHERE (((TblOrg.Donor)<>("Dbl Springs Cumberland Presb. Church-RLY")) AND
((TblQty.QtyAmount)>99) AND ((Exists (SELECT * FROM TblQty YA
WHERE YA.OrgId =TblOrg.OrgId AND YA.QtyYear =
2005))=False))
ORDER BY TblOrg.Donor;

In the where clause above, I am checking Donor to be not equal to "Dbl
Springs ... .. ". I would also like to check Donor for "Life Care Center
of
Bruceton-RLY" so that I ignore the record if Donor is either of these. I
can't seem to add in the check for "Life Care Center of Bruceton-RLY"

This would complete it, thanks

Dear Nigel:

When you do this:

FROM TblOrg AS D, TblQty AS Q

you are telling the query to consider every combination of Donor with
every
donation made.

Likely, within this set of thousands of combinations, you really only
want
to consider donations made by the same donor. I'm saying you don't want
to
consider the donations made by John when you're looking at the row for
Mark.
So, you would filter to remove those unwanted combinations. This will
greatly reduce the number of rows returned and may improve performance.
May
I suggest this:

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D, TblQty Q
WHERE D.OrgID = Q.OrgID
AND NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

If my assumptions are any good, this should be a step in the right
direction.

Now, the above is actually equivalent to an INNER JOIN

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
INNER JOIN TblQty Q
ON D.OrgID = Q.OrgID
WHERE NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

Any help? I tried!

Tom Ellison


Thankyou, thankyou. I have tetsed both queries, they are as follows:
Just for 2005 ...
SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005))=False))
ORDER BY D.Donor;

& all years did not contribute ...
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = Y.MissYear))=False))
ORDER BY D.Donor;

However, I would like to expand the first query (2005 only) to also
display
the following fields from TblQty QtyYear, QtyAmount & QtyLoc. So along
with
the Donor info which is showing, I show what they gave in these other
years.
Each time I try to expand the query, for example I changed the FROM
statement:
old FROM TblOrg AS D
new FROM TblOrg AS D, TblQty AS Q
then the query goes nuts, runs for ages & returns thousands of records.
I have all the "D" fields ( D.Donor, D.City, D.State, D.[Contact Phone
#],
D.[First Name], D.[Last Name], D.[Phone #], D.Email), I just want to
display
3 more fields from table TblQty.
Am I making sense ? Thanks

:

Dear Nigel:

The D, Y, and YA are called aliases. In queries that deal with more
than
one table, this is useful to keep the typing and reading to a minimum.
In
many queries, they are essential. When a query makes reference to a
table
more than once, the two instances of that table must be kept separate.
This
is not the case here, but it's still a useful shorthand. It keeps you
from
having such long table references to write and to read.

You can drop the aliasing and substitute the full table names in each
place
if you prefer.

Tom Ellison


Thanks for continuing to work on this one, I appreciate it. Yes,
after
I
had
written back I released I should be using OrgId as that is how the
tables
are
joined.
Can you help me understand some of the syntax in the SELECT:
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
This selects field Donor from TblOrg & labels TblOrg as D & field
MissYear
from TblYear & labels TblYear as Y ?

WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
This labels TblQty as YA ?


WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Thanks

:

Dear Nigel:

The pop-up is because the query references Donor in YA, which is
TblQuery.
There is not Donor in that table.

These things I did not know before.

Your tables are "joined" on OrdID instead. I expect that is what
you
need
here:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Am I perhaps getting close?

Tom Ellison


Okay here are the table details:
TblOrg
OrgId (PK)
Donor
Address
City
State
Zip

TblQty
OrgId (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

TblYear
MissYear

TblOrg contains info about the Donors & has a relationship to
TblQty
which
lists the year they gave (QtyYear) followed by amount, date of
gift
&
location. TblYear has one field MissYear with 4 records - 2002,
2003,2004
&
2005.

Here is what I tried:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.Donor = D.Donor
AND YA.QtyYear = Y.MissYear);

But I get a pop up asking for parameter YA.Donor. I guess I'm not
understaning what you mean with the D. & the Y. in the select ?
Maybe I
substiutied my names wrong. Thanks


:

Dear Nigel:

About your table names, I don't know.

TblOrg sounds like it would be a table of orgainizations. Is
this
a
table
of Donors?

TblQty sounds like a table of quantities. I have no idea what a
quantity
has to do with what you are doing.

Only you would know what table names represent what entities in
this
database.

Tom Ellison


Thanks again. So with my names in the following, I would
change
Donor
to
TblOrg & YearAmount to TblQty ? :

SELECT D.Donor, Y.AYear
FROM Donor D, MyYears Y
WHERE NOT EXISTS (
SELECT *
FROM YearAmount YA
WHERE YA.Donor = D.Donor
AND YA.AYear = Y.AYear)

I will try to get to check this out this week.
:

Dear Nigel:

Likely you are only familiar with the design view of a query.

 

[NigelB]

Thanks Tom, I have it working. Thanks for all you help. Nigel

Thanks again Tom, but I don't see where in the SQL to code that ?

Dear Nigel:

How about

Donor NOT IN ("Dbl Springs Cumpbeland Presb. Church-RLY", "Life Care
Center of Bruceton-RLY")

Tom Ellison

Thanks for the update. I had managed to figure it out by reviewing other
queries. I have one additional question. First of all here is what I have:

SELECT TblOrg.Donor, TblOrg.City, TblOrg.State, TblOrg.[Contact Phone #],
TblOrg.[First Name], TblOrg.[Last Name], TblOrg.[Phone #], TblOrg.Email,
TblQty.QtyYear, TblQty.QtyAmount, TblQty.QtyLoc
FROM TblOrg INNER JOIN TblQty ON TblOrg.OrgId=TblQty.OrgId
WHERE (((TblOrg.Donor)<>("Dbl Springs Cumberland Presb. Church-RLY")) AND
((TblQty.QtyAmount)>99) AND ((Exists (SELECT * FROM TblQty YA
WHERE YA.OrgId =TblOrg.OrgId AND YA.QtyYear =
2005))=False))
ORDER BY TblOrg.Donor;

In the where clause above, I am checking Donor to be not equal to "Dbl
Springs ... .. ". I would also like to check Donor for "Life Care Center
of
Bruceton-RLY" so that I ignore the record if Donor is either of these. I
can't seem to add in the check for "Life Care Center of Bruceton-RLY"

This would complete it, thanks

:

Dear Nigel:

When you do this:

FROM TblOrg AS D, TblQty AS Q

you are telling the query to consider every combination of Donor with
every
donation made.

Likely, within this set of thousands of combinations, you really only
want
to consider donations made by the same donor. I'm saying you don't want
to
consider the donations made by John when you're looking at the row for
Mark.
So, you would filter to remove those unwanted combinations. This will
greatly reduce the number of rows returned and may improve performance.
May
I suggest this:

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D, TblQty Q
WHERE D.OrgID = Q.OrgID
AND NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

If my assumptions are any good, this should be a step in the right
direction.

Now, the above is actually equivalent to an INNER JOIN

SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
INNER JOIN TblQty Q
ON D.OrgID = Q.OrgID
WHERE NOT EXISTS(SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005)
ORDER BY D.Donor;

Any help? I tried!

Tom Ellison


Thankyou, thankyou. I have tetsed both queries, they are as follows:
Just for 2005 ...
SELECT D.Donor, D.City, D.State, D.[Contact Phone #], D.[First Name],
D.[Last Name], D.[Phone #], D.Email
FROM TblOrg AS D
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2005))=False))
ORDER BY D.Donor;

& all years did not contribute ...
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = Y.MissYear))=False))
ORDER BY D.Donor;

However, I would like to expand the first query (2005 only) to also
display
the following fields from TblQty QtyYear, QtyAmount & QtyLoc. So along
with
the Donor info which is showing, I show what they gave in these other
years.
Each time I try to expand the query, for example I changed the FROM
statement:
old FROM TblOrg AS D
new FROM TblOrg AS D, TblQty AS Q
then the query goes nuts, runs for ages & returns thousands of records.
I have all the "D" fields ( D.Donor, D.City, D.State, D.[Contact Phone
#],
D.[First Name], D.[Last Name], D.[Phone #], D.Email), I just want to
display
3 more fields from table TblQty.
Am I making sense ? Thanks

:

Dear Nigel:

The D, Y, and YA are called aliases. In queries that deal with more
than
one table, this is useful to keep the typing and reading to a minimum.
In
many queries, they are essential. When a query makes reference to a
table
more than once, the two instances of that table must be kept separate.
This
is not the case here, but it's still a useful shorthand. It keeps you
from
having such long table references to write and to read.

You can drop the aliasing and substitute the full table names in each
place
if you prefer.

Tom Ellison


Thanks for continuing to work on this one, I appreciate it. Yes,
after
I
had
written back I released I should be using OrgId as that is how the
tables
are
joined.
Can you help me understand some of the syntax in the SELECT:
SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
This selects field Donor from TblOrg & labels TblOrg as D & field
MissYear
from TblYear & labels TblYear as Y ?

WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
This labels TblQty as YA ?


WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Thanks

:

Dear Nigel:

The pop-up is because the query references Donor in YA, which is
TblQuery.
There is not Donor in that table.

These things I did not know before.

Your tables are "joined" on OrdID instead. I expect that is what
you
need
here:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.OrgID = D.OrgID
AND YA.QtyYear = Y.MissYear);

Am I perhaps getting close?

Tom Ellison


Okay here are the table details:
TblOrg
OrgId (PK)
Donor
Address
City
State
Zip

TblQty
OrgId (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

TblYear
MissYear

TblOrg contains info about the Donors & has a relationship to
TblQty
which
lists the year they gave (QtyYear) followed by amount, date of
gift
&
location. TblYear has one field MissYear with 4 records - 2002,
2003,2004
&
2005.

Here is what I tried:

SELECT D.Donor, Y.MissYear
FROM TblOrg AS D, TblYear AS Y
WHERE NOT EXISTS (
SELECT *
FROM TblQty YA
WHERE YA.Donor = D.Donor
AND YA.QtyYear = Y.MissYear);

But I get a pop up asking for parameter YA.Donor. I guess I'm not
understaning what you mean with the D. & the Y. in the select ?
Maybe I
substiutied my names wrong. Thanks


:

Dear Nigel:

About your table names, I don't know.

TblOrg sounds like it would be a table of orgainizations. Is
this
a
table
of Donors?

TblQty sounds like a table of quantities. I have no idea what a
quantity
has to do with what you are doing.

Only you would know what table names represent what entities in
this
database.

Tom Ellison


Thanks again. So with my names in the following, I would
change
Donor
to
TblOrg & YearAmount to TblQty ? :

SELECT D.Donor, Y.AYear
FROM Donor D, MyYears Y
WHERE NOT EXISTS (
SELECT *
FROM YearAmount YA
WHERE YA.Donor = D.Donor
AND YA.AYear = Y.AYear)

I will try to get to check this out this week.
: