Microsoft Access - Access 2007 Report

You probaby can set up your report to exclude non-ordering contacts, but it would be better to refine the query to exclude them.

Can you post the structure of the tables involved, and the sql of your current query?

The report should be a "main report" with a "sub report".  The main report should show Contact name and other demographic information.  The subreport should show the order information.  The main report will be based on a query of the contact table and the subreport will be based on a query of the items ordered.

Here is the SQL of my Query.  How would I refine the query to exclude the items that were not ordered by an individual and only show on the report the demographics of the individual and the items that they did order.  Thank you.
SELECT MasterNameList.FirstName, MasterNameList.FamilyName, MasterNameList.StreetAddress, MasterNameList.[Address Line 2], MasterNameList.POBox, MasterNameList.City, MasterNameList.State, MasterNameList.ZipPostal, MasterNameList.Country, MasterNameList.[Phone Home], MasterNameList.[Phone Cell], MasterNameList.ContactType, MasterNameList.EMail, MasterNameList.Walk, [2011 Walk Table].ContactID, [2011 Walk Table].RegisgrationID, [2011 Walk Table].EventID, [2011 Walk Table].Updates, [2011 Walk Table].IWalk, [2011 Walk Table].TeamName, [2011 Walk Table].TeamCaptain, [2011 Walk Table].IWalkFor1, [2011 Walk Table].IWalkFor2, [2011 Walk Table].IWalkFor3, [2011 Walk Table].IWalkFor4, [2011 Walk Table].AdultRegistration, [AdultRegistration]*25 AS AdultRegistrationAmt, [2011 Walk Table].YouthRegistration, [YouthRegistration]*15 AS YouthRegistrationAmt, [2011 Walk Table].LateAdultRegistration, [LateAdultRegistration]*30 AS LateAdultRegistrationAmt, [2011 Walk Table].LateYouthRegistration, [LateYouthRegistration]*20 AS LateYouthRegistrationAmt, [2011 Walk Table].MemorialMessageSponsorship, [MemorialMessageSponsorship]*50 AS MemorialMessageSponsorshipAmt, [2011 Walk Table].MemorialMessage, [2011 Walk Table].TouchALife, [TouchALife]*10 AS TouchALifeAmt, [2011 Walk Table].Donation, [2011 Walk Table].DonationAmt, [2011 Walk Table].ShareBear, [ShareBear]*8 AS ShareBearAmt, [2011 Walk Table].LongSleeveTShirt, [LongSleeveTShirt]*25 AS LongSleeveTShirtAmt, [2011 Walk Table].Hamburger, [Hamburger]*4 AS HamburgerAmt, [2011 Walk Table].HotDog, [HotDog]*3 AS HotDogAmt, [2011 Walk Table].[ShippingHandling $15], [ShippingHandling $15]*15 AS [ShippingHandling $15Amt], [ShippingHandling $30]*30 AS [ShippingHandling $30Amt], [AdultRegistrationAmt]+[YouthRegistrationAmt]+[LateAdultRegistrationAmt]+[LateYouthRegistrationAmt]+[MemorialMessageSponsorshipAmt]+[TouchALifeAmt]+[DonationAmt]+[ShareBearAmt]+[LongSleeveTShirtAmt]+[HamburgerAmt]+[HotDogAmt]+[ShippingHandling $15Amt]+[ShippingHandling $30Amt] AS TotalAmount, [2011 Walk Table].MethodofPayment, [2011 Walk Table].[Check Number], [2011 Walk Table].MerchandisePickup, [2011 Walk Table].AdultS, [2011 Walk Table].AdultSAmt, [2011 Walk Table].AdultM, [2011 Walk Table].AdultMAmt, [2011 Walk Table].AdultL, [2011 Walk Table].AdultLAmt, [2011 Walk Table].AdultXL, [2011 Walk Table].AdultXLAmt, [2011 Walk Table].Adult2XL, [2011 Walk Table].Adult2XLAmt, [2011 Walk Table].Adult3XL, [2011 Walk Table].Adult3XLAmt, [2011 Walk Table].YouthS, [2011 Walk Table].YouthSAmt, [2011 Walk Table].YouthM, [2011 Walk Table].YouthMAmt, [2011 Walk Table].YouthL, [2011 Walk Table].YouthLAmt, [2011 Walk Table].LSAdultS, [2011 Walk Table].LSAdultSAmt, [2011 Walk Table].LSAdultM, [2011 Walk Table].LSAdultMAmt, [2011 Walk Table].LSAdultL, [2011 Walk Table].LSAdultLAmt, [2011 Walk Table].LSAdultXL, [2011 Walk Table].LSAdultXLAmt, [2011 Walk Table].LSAdult2XL, [2011 Walk Table].LSAdult2XLAmt, [2011 Walk Table].LSAdult3XL, [2011 Walk Table].LSAdult3XLAmt, [2011 Walk Table].LSYouthS, [2011 Walk Table].LSYouthSAmt, [2011 Walk Table].LSYouthM, [2011 Walk Table].LSYouthMAmt, [2011 Walk Table].LSYouthL, [2011 Walk Table].LSYouthLAmt, [2011 Walk Table].Help
FROM [2011 Walk Table] INNER JOIN MasterNameList ON [2011 Walk Table].ContactID = MasterNameList.ContactID;

There seem to be a number of issues with your table structure, but I think you could refine your query to exclude customers that don't order anything by checking some of your value fields.  If you change the end of your SQL statement:

ON [2011 Walk Table].ContactID = MasterNameList.ContactID;

to

ON [2011 Walk Table].ContactID = MasterNameList.ContactID
WHERE [2011 Walk Table].AdultRegistration > 0
OR [2011 Walk Table].YouthRegistration > 0
OR [2011 Walk Table].LateAdultRegistration > 0

and so on down to check all the counter fields, the query will only bring up contacts that have ordered something.

Your 2011 Walk Table really seems to be overworked, typically a table such as that would not include the items ordered.  I would expect it to looks something more like this:

[2011 Walk Table].ContactID
[2011 Walk Table].RegisgrationID
[2011 Walk Table].EventID
[2011 Walk Table].Updates
[2011 Walk Table].IWalk
[2011 Walk Table].TeamName
[2011 Walk Table].TeamCaptain
[2011 Walk Table].IWalkFor1
[2011 Walk Table].IWalkFor2
[2011 Walk Table].IWalkFor3
[2011 Walk Table].IWalkFor4

and just be named something like EventContacts.  Then you would have a separate table to store the items ordered:

EventID
ContactID
ItemID
ItemQty
ItemAmt

with a separate table that would identify item pricing by event:

EventID
ItemID
ItemAmt

This is only an approximation, there are other fields in your 2011 Walk Table table that would seem to be better stored in different tables.  You should look up "Data Normalization" to get an idea of how to better organize your data.  I know I created tables like this in my early programming days, they get to be a real headache to maintain and report from.