Invoice Issues 1

[cheyenne2002]

I'm trying to create a report that will be an invoice.

I am creating invoices for pledges (donations). My donors may have given us a pledge of 5,000 but they might wish to pay in increments. So I created a record for each date that they are planing on paying.

I need to be able to have all the unpaid pledges show up on the invoice and then a total due. I think I have accomplished that.

My problem is that I get a separate invoice for each record and they all show all the pledges for that donor.

I can't figure out what I have done wrong. Any ideas.

Thanks in advance.
Sharon

 

[Remou]

What is the SQL of your report?

 

[cheyenne2002]

Remou,

This is the main report:
--------
SELECT Donors.DonorFirstName, Donors.DonorLastName, Donors.DonorCompany, Donors.Address, Donors.City, Donors.State, Donors.PostalCode, Donors.DonorCountry, Donors.BusinessPhone, Donors.[NPCMember#], Donors.AssignedSolicitor, Donations.DateOfDonation, Donations.AmountOfDonation, Donations.ExpectedDate, Donations.CampaignName, Donations.DatePaid, Donors.DonorID
FROM Donors INNER JOIN Donations ON Donors.DonorID = Donations.DonorID
GROUP BY Donors.DonorFirstName, Donors.DonorLastName, Donors.DonorCompany, Donors.Address, Donors.City, Donors.State, Donors.PostalCode, Donors.DonorCountry, Donors.BusinessPhone, Donors.[NPCMember#], Donors.AssignedSolicitor, Donations.DateOfDonation, Donations.AmountOfDonation, Donations.ExpectedDate, Donations.CampaignName, Donations.DatePaid, Donors.DonorID
HAVING (((Donations.CampaignName)=32) AND ((Donations.DatePaid) Is Null));

-----
This is the subreport:
-------
SELECT Donations.DateOfDonation, Donations.CampaignName, Donations.ExpectedDate, Donations.AmountOfDonation, Donations.DonorID
FROM Donors INNER JOIN Donations ON Donors.DonorID = Donations.DonorID
GROUP BY Donations.DateOfDonation, Donations.CampaignName, Donations.ExpectedDate, Donations.AmountOfDonation, Donations.DonorID, Donors.DonorID
HAVING (((Donations.CampaignName)=32));
-----
I do not know SQL that well and rely on the query grid for my queries.

Sharon

 

[Remou]

You seem to have too many fields from the donations table in the main report SQL. Try:

SELECT DISTINCT Donors.DonorFirstName, Donors.DonorLastName, Donors.DonorCompany, Donors.Address, Donors.City, Donors.State, Donors.PostalCode, Donors.DonorCountry, Donors.BusinessPhone, Donors.[NPCMember#], Donors.AssignedSolicitor, Donations.DateOfDonation, Donations.AmountOfDonation, Donations.ExpectedDate, Donations.CampaignName, Donations.DatePaid, Donors.DonorID
FROM Donors INNER JOIN Donations ON Donors.DonorID = Donations.DonorID
WHERE Donations.CampaignName=32 AND Donations.DatePaid Is Null

 

[cheyenne2002]

I copied the above code and put it in the main report. However, it did not fix the problem.

hmmmm

Sharon

 

[dhookom]

I would totally remove the Donations table from the main report's record source since this information is included in the subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cheyenne2002]

I'm not sure that worked. Instead of about 55 records I now have 1025.

And I lost the info in the subreport.

hmmm
Sharon

 

[cheyenne2002]

I think the problem is now that it does not know which campaign I want the invoice on and it is giving me all of them. I also lost the total, but I think I just have that in the wrong section.

 

[Remou]

Sorry, the cut-and-paste monster strikes again:

SELECT DISTINCT Donors.DonorFirstName, Donors.DonorLastName, Donors.DonorCompany, Donors.Address, Donors.City, Donors.State, Donors.PostalCode, Donors.DonorCountry, Donors.BusinessPhone, Donors.[NPCMember#], Donors.AssignedSolicitor, Donors.DonorID
FROM Donors INNER JOIN Donations ON Donors.DonorID = Donations.DonorID
WHERE Donations.CampaignName=32 AND Donations.DatePaid Is Null

 

[cheyenne2002]

Thank you, It works like a charm. Now I just need to figure out where the total goes and how to get the name of the campaign instead of the number and I'll be all set.

Thank you again.
Sharon

PS: this group is GREAT!!!

 

[cheyenne2002]

Okay, now I have an issue with getting a total. If I put it on the main report, I get an error because the field [AmountOfDonation] is not in the query any more.

And if I put it on the sub report in the detail section I get a total for every line item, and it does not show up at all if I put it in the footer section of the sub report.

Any ideas.
Sharon

 

[Remou]

How about summing the amount donated in the report footer of the subreport?

 

[cheyenne2002]

That worked this time.

I have one last question. When my expected dates are listed in the sub report (for those that have more than one) I want them to be sorted so the ones that are due now are on top. I sorted the query but it does not seem to be working right. I have listed the SQL code below.

----
SELECT Donations.DateOfDonation, Campaign.CampaignName, Donations.ExpectedDate, Donations.AmountOfDonation, Donations.DonorID
FROM Donors RIGHT JOIN (Campaign INNER JOIN Donations ON Campaign.CampaignID = Donations.CampaignName) ON Donors.DonorID = Donations.DonorID
GROUP BY Donations.DateOfDonation, Campaign.CampaignName, Donations.ExpectedDate, Donations.AmountOfDonation, Donations.DonorID, Donations.CampaignName, Donors.DonorID
HAVING (((Donations.CampaignName)=32))
ORDER BY Donations.ExpectedDate;
------

Any clue?
Sharon

 

[Remou]

Try:

SELECT Donations.DateOfDonation, Campaign.CampaignName, Donations.ExpectedDate, Donations.AmountOfDonation, Donations.DonorID
FROM Donations INNER JOIN Campaign ON Campaign.CampaignID = Donations.CampaignName
WHERE Donations.CampaignName=32
ORDER BY Donations.ExpectedDate

You should really put the sort order in the subreports sorting and grouping options.

 

[cheyenne2002]

I think that is going to work, however, it has the payments for 2008 on the top and the 2007 on the bottom.

I do not know how to do the sort in acending order.

Sharon

 

[Remou]

The default is ascending, so that is a little strange, you need to add DESC to sort descending. Did you use the Sort Order property of the subreport?

 

[cheyenne2002]

Is there a place online where I can find the right way to word SQL. I tried to add the DESC but one way gave me an error and adding it after the field to order by did nothing.

I'd like to know if there is a website that gives examples of SQL code for different queries.

Thanks
Sharon

 

[Remou]

Here is one link:

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

There are a vast number of articles on SQL and a lot of the ideas can be transferred to Jet SQL, which is the Access version. However, for reports, the sorting of the SQL is less important than Sorting and Grouping in the report, which will override the SQL sort.

 

[cheyenne2002]

Listed below is the SQL code in my subreport query. I can not however, get the {ExpectedDate] to Sort with the ones due on the top......

hmmmmm
Any suggestions.
Sharon

 

[cheyenne2002]

I'm having a good day, I forgot to paste the code.

duh!
----

SELECT Donations.DateOfDonation, Campaign.CampaignName, Donations.ExpectedDate, Donations.AmountOfDonation, Donations.DonorID
FROM Donations INNER JOIN Campaign ON Campaign.CampaignID=Donations.CampaignName
WHERE Donations.CampaignName=32
ORDER BY [Donations.ExpectedDate] DESC;
---

Thanks
Sharon