Comparing Results of Two Queries with User Input

[pleming]

I have an Access DB for our non-profit Children's Home (we give kids that are having problems a home and help them graduate from HS) that keeps track of contributors and contributions. I have created a query that asks user for date input (start and end date) and will give a list of all contributions and contributor information that were made between that date range.

However, I now need to be able to COMPARE this information for TWO different date ranges and put it into a report.

I can't seem to figure out how to do this. I've tried to make two separate queries and join them but it never asks for the 2nd date range in anything I've tried.

I need to get this information for a grant that we have applied for and they want to see contributor information from last year and part of this year for each contributor so that we can show which ones gave MORE this period and they will then match that number for the amount of the grant - so this is huge for us.

ANY information on this would be helpful. I don't even know where to start on this one. Thank you!

[NeoPa]

Database work relies on precision. This is particularly true of the specification or expression of the requirement.

I'm afraid your specification is too vague to do much with ATM. Can you be more precise and detailed? An explanation of how and where your data is stored is fundamental before any really helpful advice can be given. IE. that is specific and not too general.

I would guess that you want, for each contributor, to compare the total amount donated within the first date range with the that same total within the second date range. Possibly adding a value that indicates the difference.

Assuming a table of contributors called [tblContributor] and a table of contributions called [tblContribution] you might be looking at something like :

SELECT   [tCr].[ContributorID]
       , [tCr].[ContributorName]
       , Sum(IIf([tCn].[ConDate]Between [Enter Start of First Period] And [Enter End of First Period],[tCn].[ConValue],0)) AS [TotFirst]
       , Sum(IIf([tCn].[ConDate]Between [Enter Start of Second Period] And [Enter End of Second Period],[tCn].[ConValue],0)) AS [TotSecond]
       , [TotSecond]-[TotFirst] AS [MoreThan]
FROM     [tblContributor] AS [tCr]
         INNER JOIN
         [tblContribution] AS [tCn]
  ON     [tCr].[ContributorID]=[tCn].[ContributorID]
WHERE    ([tCn].[ConDate] Between [Enter Start of First Period] And [Enter End of First Period])
   OR    ([tCn].[ConDate] Between [Enter Start of Second Period] And [Enter End of Second Period])
GROUP BY [tCr].[ContributorID]
       , [tCr].[ContributorName]

In this instance the query would run and the operator be prompted for the four date values that specify the two date ranges. This is not the only way to handle this but in the absence of any more information from you about the design it is an illustrative example at least.

[pleming]

Thank you for your reply! Yes, you are correct that I want to compare, for each contributor the total amount donated within the first date range to the total amount donated in the second date range and with a value that indicates the difference. Ideally, it would only show the contributors that donated MORE in the second date range and filter out any that gave the same or less.

I think your code is a good start and I will try to input my own table (and other names) for the ones you have here (although they are VERY close!).

Information on my DB is:

tblContributor with the following:
ContributorID
LastName/OrganizationName
FirstName

and

tblReceipts with
ContibutorID
ContributionDate
Amount

I will try to put MY names into the code you posted and see how it works. Again, thank you so much! I will post back with my results.

[pleming]

Here is my code ... see below. When I run the query, it asks for the two sets of dates (start and end of first period, start and end of second period). However, it then asks for

1. tblReceipts.Amount
2. Enter End of First Period
3. tblRecipts.ContributionDate
4. Enter End of Second Period

I tried entering some data into those and ran the query and it gives me TotFirst with contribution amounts and TotSecond with 0 for contribution amounts. And it does the math and gives me results in the MoreThan column.

On your code, I wasn't sure what this part was doing:

FROM     [tblContributor] AS [tCr]
         INNER JOIN
         [tblContribution] AS [tCn]
  ON     [tCr].[ContributorID]=[tCn].[ContributorID]

So I entered my table names as you will see below.

Here is my query:

SELECT   [tblContributor].[ContributorID]
       , [tblContributor].[LastName/OrganizationName]
       , Sum(IIf([tblReceipts].[ContributionDate]Between [Enter Start of First Period] And [Enter End of First Period],[tblReceipts].[Amount],0)) AS TotFirst
       , Sum(IIf([tblReceipts].[ContributionDate]Between [Enter Start of Second Period] And [Enter End of Second Period],[tblRecipts].[Amount],0)) AS TotSecond
       , [TotSecond]-[TotFirst] AS MoreThan
FROM     tblContributor AS tblContributor
         INNER JOIN
         tblReceipts AS tblReceipts
  ON     [tblContributor].[ContributorID]=[tblReceipts].[ContributorID]
WHERE    ([tblReceipts].[ContributionDate] Between [Enter Start of First Period] And [Enter End of First Period])
   OR    ([tblRecipts].[ConributionDate] Between [Enter Start of Second Period] And [Enter End of Second Period])
GROUP BY [tblContributor].[ContributorID]
       , [tblContributor].[LastName/OrganizationName];

[NeoPa]

PLeming:
On your code, I wasn't sure what this part was doing:

The AS keyword in SQL is an alternative for ALIAS. That means that wherever we want to refer to the long table name [tblContributor] we can use the shorter version, [tCn], instead. This saves filling the overall SQL string with lots of copies of a value that gives the reader a lot of work to do in order to interpret what it's doing. SQL doesn't care, but humans can work more easily without all the extra data to process. It can be used to give alternative names to tables as well as to give usable names to calculated values such as [MoreThan].

Other than that, and from the information you've told us, you've actually made a perfect job of translating my example into your system. It would seem though, that the information you've shared isn't 100% accurate. Had it been, because I can tell that you've transcribed the logic perfectly, it would certainly not have complained about the references to [tblReceipts].[Amount] & [tblReceipts].[ContributionDate].

My first thought was that [tblReceipts] is actually [tblReceipt]. Consistent with the other table name. However, that would have caused all references to any of its fields to fail but apparently [tblReceipts].[ContributorID] didn't. Therefore I can only guess that the other two fields are misspelled in some way. Certainly this is the area to look at closely for the problems and certainly, also, being prompted for those values indicates a problem.

[pleming]

THANK YOU!!! You were right, I did have a couple of misspellings. I fixed those and now it works perfectly!

I do have a question, though. From the results that I get, I don't see anything for contributors who have 0 donations during the two ranges. Is that built in to the query? It will only retrieve data from contributors who had donations in one or both of the two ranges? If so, that is exactly what I needed as I didn't want a list of ALL contributors if they didn't make any donations.

Thank you so much for all your help!!!!

[NeoPa]

PLeming:
From the results that I get, I don't see anything for contributors who have 0 donations during the two ranges. Is that built in to the query? It will only retrieve data from contributors who had donations in one or both of the two ranges? If so, that is exactly what I needed as I didn't want a list of ALL contributors if they didn't make any donations.

Yes. Indeed.

To change this so that all contributors are included you would need to change two things in the current SQL that ensure only those with contributions within either of the time frames are included.

1. INNER JOIN would need to be changed to LEFT JOIN.
   The first means that data is only returned when records from both tables match each other. IE. A Contributor and a Receipt match up. The second will return all Contributor data and any Receipt data that does match. Where there is no match then all Receipt fields will be returned as Nulls.
   See SQL JOINs for more on that.
2. The WHERE clause would need to be changed to ensure that Nulls (Remember how non-existent Receipt records are returned from point #1 above.) are also accepted as valid values for [tblReceipt].[ContributionDate].

NB. I switched the posts flagged as the Best Answer as it's mainly to help other people looking for solutions for similar questions.

[pleming]

Thank you for the additional information. And also for flagging the correct answer as "Best". I realized afterward that the post didn't contain the whole thread. :)