Query on Two Tables

[Pam]

I have 2 tables ... a table with contributor information (name, address,
etc.) called tblContributorInfo. A second table is linked to the first and
is the table that holds all the contribution information (Contributor ID,
date of contribution, amount, etc).

I want to create a report that will send a letter out to ALL contributors
monthly, but only add a line about their contribution if they indeed made one
that month. So, I am able to write the query that gives me all contributors
and their info OR I am able to write a query that asks for user input on
Month and Year and I can get all the info about the contributor and their
contribution for that month. But I can't seem to figure out how to do BOTH
in the same query.

For example, for December 2008, I would like the query to return ALL
contriubutors and their addresses, and in the column for contribution amount,
put a zero (or nothing) for those that didn't make a contribution ... and put
the amount of the December contribution if they did. Then, I can write the
report and ask it to suppress the line thanking them for their contribution
if the amount is 0.

Is there any way to do this?

Thanks!

 

[bismuth83]

You'll want to use a Left Join in the SQL instead of Inner Join. If
you're using the design view of the query, double-click the relation
between the tables to see the join properties.

 

[John Spencer]

A query like the following should work.

SELECT C.*
, (SELECT Sum(Amount)
FROM Contributions
WHERE [Contributions].[ContributorID] = C.[ContributorID
AND [Date of Contribution] between #2008-12-01# and #2008-12-31#) as
ContributionAmount
FROM tblContributorInfo as C

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a LEFT JOIN:

PARAMETERS [StartDate] Date, [EndDate] Date;
SELECT ...
FROM tblContributorInfo As CI LEFT JOIN tblContributions AS C
ON CI.contributor_id = C.contributor_id
WHERE C.contribution_date BETWEEN [StartDate] And [EndDate]
OR C.amount IS NULL

The LEFT side of the join is the table that will show "everything." The
RIGHT side of the join is the "child" table and will only show data that
fits the criteria.

The "OR amount IS NULL" ensures that all non-contributions are
considered.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXe4XoechKqOuFEgEQKEaQCgoV40svG0DACHYqFgTU/53MckPR0AoOq5
INQizmpejI2sHVSPifwPg6sP
=gYEe
-----END PGP SIGNATURE-----

 

[Pam]

Thank you to all who answered my question. However, I don't think the LEFT
JOIN will work in this case. I have tried using your examples and I still
get the same output. I believe the reason is that in my table of
Contributions, it only holds the information of those who actually made a
contribution ... in either the current or previous months. So, there are
some (many) people who have NEVER made a contribution and they wouldn't have
any entries in the Contributions table.

To explain it further, I would like to send this letter to ALL of my
contributors (listed in the tblContributorInfo) ... and for those who made a
contribution in that certain month (as listed in tblContributions) ... I
would like to add an additional line to the report thanking them for their
contribution. Can this be done with one query and one report, or would I
have to do first a query for all contributors and then another for those who
made a contribution that month? This is something I would be doing monthly.

Sorry for the confusion!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a LEFT JOIN:

PARAMETERS [StartDate] Date, [EndDate] Date;
SELECT ...
FROM tblContributorInfo As CI LEFT JOIN tblContributions AS C
ON CI.contributor_id = C.contributor_id
WHERE C.contribution_date BETWEEN [StartDate] And [EndDate]
OR C.amount IS NULL

The LEFT side of the join is the table that will show "everything." The
RIGHT side of the join is the "child" table and will only show data that
fits the criteria.

The "OR amount IS NULL" ensures that all non-contributions are
considered.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXe4XoechKqOuFEgEQKEaQCgoV40svG0DACHYqFgTU/53MckPR0AoOq5
INQizmpejI2sHVSPifwPg6sP
=gYEe
-----END PGP SIGNATURE-----

I have 2 tables ... a table with contributor information (name, address,
etc.) called tblContributorInfo. A second table is linked to the first and
is the table that holds all the contribution information (Contributor ID,
date of contribution, amount, etc).

I want to create a report that will send a letter out to ALL contributors
monthly, but only add a line about their contribution if they indeed made one
that month. So, I am able to write the query that gives me all contributors
and their info OR I am able to write a query that asks for user input on
Month and Year and I can get all the info about the contributor and their
contribution for that month. But I can't seem to figure out how to do BOTH
in the same query.

For example, for December 2008, I would like the query to return ALL
contriubutors and their addresses, and in the column for contribution amount,
put a zero (or nothing) for those that didn't make a contribution ... and put
the amount of the December contribution if they did. Then, I can write the
report and ask it to suppress the line thanking them for their contribution
if the amount is 0.

Is there any way to do this?

Thanks!

 

[John Spencer (MVP)]

Did you take a look at my proposed solution? It should do what you want by
using a subquery to get the value. You could also use DSUM function to get
the value.

If you need more detail on the contribution, you can use a left join and a
subquery on the contributions table.
If you will supply some detail on the field names in the contributions table
(and the name of the contributions table) I can help you construct a query.

If your field and table names consist of only letters, numbers, and the
underscore character then this can be done in one query. If the names contain
spaces or other characters then you will need to use two queries - a saved
query to get the contribution information for the time period. THen you will
use the tblContributorInfo with a LEFT JOIN to the saved query.

Query ONe saved as QContributions
SELECT *
FROM [Contributions]
WHERE [Contribution Date] Between #2008-12-01# and #2008-12-31#

That query can be set up so you can vary the dates.

NOW using that query along with your table

SELECT *
FROM tblContributorInfo as C LEFT JOIN QContributions as Q
ON C.[Contributor ID] = Q.[Contributor ID]

You can choose to limit the returned fields by populating the select list with
the names of the fields you want returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[bismuth83]

Access also uses RIGHT JOIN, depending the table order used in the
SQL. But the query examples from other posters should also point you
in the right direction. What you're looking for is possible, it's
just a matter of finding the right syntax.

 

[Pam]

Thank you very much. This worked perfectly! I have it set up so that the
user can enter the year and the month and it is giving me all the
contributors for that month and year, as well as those that did not make a
contribution for that month and year.

Now, I have another question. I would like to add a YTD field so that the
reports will show how much they've given so far for the current year or
actually, for the year that I'm querying on.

Is there any way to "capture" just the year that the user inputs and then
use that to retrieve the total of receipts for each person for that year?


Pam

Did you take a look at my proposed solution? It should do what you want by
using a subquery to get the value. You could also use DSUM function to get
the value.

If you need more detail on the contribution, you can use a left join and a
subquery on the contributions table.
If you will supply some detail on the field names in the contributions table
(and the name of the contributions table) I can help you construct a query.

If your field and table names consist of only letters, numbers, and the
underscore character then this can be done in one query. If the names contain
spaces or other characters then you will need to use two queries - a saved
query to get the contribution information for the time period. THen you will
use the tblContributorInfo with a LEFT JOIN to the saved query.

Query ONe saved as QContributions
SELECT *
FROM [Contributions]
WHERE [Contribution Date] Between #2008-12-01# and #2008-12-31#

That query can be set up so you can vary the dates.

NOW using that query along with your table

SELECT *
FROM tblContributorInfo as C LEFT JOIN QContributions as Q
ON C.[Contributor ID] = Q.[Contributor ID]

You can choose to limit the returned fields by populating the select list with
the names of the fields you want returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you to all who answered my question. However, I don't think the LEFT
JOIN will work in this case. I have tried using your examples and I still
get the same output. I believe the reason is that in my table of
Contributions, it only holds the information of those who actually made a
contribution ... in either the current or previous months. So, there are
some (many) people who have NEVER made a contribution and they wouldn't have
any entries in the Contributions table.

 

[Pam]

Did you take a look at my proposed solution? It should do what you want by
using a subquery to get the value. You could also use DSUM function to get
the value.

If you need more detail on the contribution, you can use a left join and a
subquery on the contributions table.
If you will supply some detail on the field names in the contributions table
(and the name of the contributions table) I can help you construct a query.

If your field and table names consist of only letters, numbers, and the
underscore character then this can be done in one query. If the names contain
spaces or other characters then you will need to use two queries - a saved
query to get the contribution information for the time period. THen you will
use the tblContributorInfo with a LEFT JOIN to the saved query.

Query ONe saved as QContributions
SELECT *
FROM [Contributions]
WHERE [Contribution Date] Between #2008-12-01# and #2008-12-31#

That query can be set up so you can vary the dates.

NOW using that query along with your table

SELECT *
FROM tblContributorInfo as C LEFT JOIN QContributions as Q
ON C.[Contributor ID] = Q.[Contributor ID]

You can choose to limit the returned fields by populating the select list with
the names of the fields you want returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you to all who answered my question. However, I don't think the LEFT
JOIN will work in this case. I have tried using your examples and I still
get the same output. I believe the reason is that in my table of
Contributions, it only holds the information of those who actually made a
contribution ... in either the current or previous months. So, there are
some (many) people who have NEVER made a contribution and they wouldn't have
any entries in the Contributions table.

 

[Pam]

Did you take a look at my proposed solution? It should do what you want by
using a subquery to get the value. You could also use DSUM function to get
the value.

If you need more detail on the contribution, you can use a left join and a
subquery on the contributions table.
If you will supply some detail on the field names in the contributions table
(and the name of the contributions table) I can help you construct a query.

If your field and table names consist of only letters, numbers, and the
underscore character then this can be done in one query. If the names contain
spaces or other characters then you will need to use two queries - a saved
query to get the contribution information for the time period. THen you will
use the tblContributorInfo with a LEFT JOIN to the saved query.

Query ONe saved as QContributions
SELECT *
FROM [Contributions]
WHERE [Contribution Date] Between #2008-12-01# and #2008-12-31#

That query can be set up so you can vary the dates.

NOW using that query along with your table

SELECT *
FROM tblContributorInfo as C LEFT JOIN QContributions as Q
ON C.[Contributor ID] = Q.[Contributor ID]

You can choose to limit the returned fields by populating the select list with
the names of the fields you want returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you to all who answered my question. However, I don't think the LEFT
JOIN will work in this case. I have tried using your examples and I still
get the same output. I believe the reason is that in my table of
Contributions, it only holds the information of those who actually made a
contribution ... in either the current or previous months. So, there are
some (many) people who have NEVER made a contribution and they wouldn't have
any entries in the Contributions table.

 

[Pam]

Thank you, that worked perfectly. I modified it so that the user must enter
the month and the year separately.

I would like to also add a ytd field on the reports that get generated from
this query. Is there any way to "capture" the YEAR that the user puts in and
use that to retrieve the total of contributions for each contributor for that
year from the receipts table? I've tried adding a third query and joining
that to the other two, but it always asks for the year twice and then it
doesn't give me the sum of contributions for that specific month anymore.

Thanks!

Did you take a look at my proposed solution? It should do what you want by
using a subquery to get the value. You could also use DSUM function to get
the value.

If you need more detail on the contribution, you can use a left join and a
subquery on the contributions table.
If you will supply some detail on the field names in the contributions table
(and the name of the contributions table) I can help you construct a query.

If your field and table names consist of only letters, numbers, and the
underscore character then this can be done in one query. If the names contain
spaces or other characters then you will need to use two queries - a saved
query to get the contribution information for the time period. THen you will
use the tblContributorInfo with a LEFT JOIN to the saved query.

Query ONe saved as QContributions
SELECT *
FROM [Contributions]
WHERE [Contribution Date] Between #2008-12-01# and #2008-12-31#

That query can be set up so you can vary the dates.

NOW using that query along with your table

SELECT *
FROM tblContributorInfo as C LEFT JOIN QContributions as Q
ON C.[Contributor ID] = Q.[Contributor ID]

You can choose to limit the returned fields by populating the select list with
the names of the fields you want returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you to all who answered my question. However, I don't think the LEFT
JOIN will work in this case. I have tried using your examples and I still
get the same output. I believe the reason is that in my table of
Contributions, it only holds the information of those who actually made a
contribution ... in either the current or previous months. So, there are
some (many) people who have NEVER made a contribution and they wouldn't have
any entries in the Contributions table.

 

[Pam]

Thank you, that worked perfectly! I modified it so that the user must input
the month and year separately.

Now what I would like to do is add a YTD field so that it will be printed on
the reports that are generated from this query.

Is there any way to "capture" the year that the user enters and use that to
retrieve contributions for the year for each contributor from the receipts
table?

I've tried adding a third query but it then asks for the year twice and
leaves out some records and also the monthly sum is now blank.

Maybe there is some way to do this directly on the report itself? But I'm
not sure how to capture and save the year that the user puts in so that it
doesn't ask for it a second time.

Thanks!

Did you take a look at my proposed solution? It should do what you want by
using a subquery to get the value. You could also use DSUM function to get
the value.

If you need more detail on the contribution, you can use a left join and a
subquery on the contributions table.
If you will supply some detail on the field names in the contributions table
(and the name of the contributions table) I can help you construct a query.

If your field and table names consist of only letters, numbers, and the
underscore character then this can be done in one query. If the names contain
spaces or other characters then you will need to use two queries - a saved
query to get the contribution information for the time period. THen you will
use the tblContributorInfo with a LEFT JOIN to the saved query.

Query ONe saved as QContributions
SELECT *
FROM [Contributions]
WHERE [Contribution Date] Between #2008-12-01# and #2008-12-31#

That query can be set up so you can vary the dates.

NOW using that query along with your table

SELECT *
FROM tblContributorInfo as C LEFT JOIN QContributions as Q
ON C.[Contributor ID] = Q.[Contributor ID]

You can choose to limit the returned fields by populating the select list with
the names of the fields you want returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you to all who answered my question. However, I don't think the LEFT
JOIN will work in this case. I have tried using your examples and I still
get the same output. I believe the reason is that in my table of
Contributions, it only holds the information of those who actually made a
contribution ... in either the current or previous months. So, there are
some (many) people who have NEVER made a contribution and they wouldn't have
any entries in the Contributions table.