Microsoft Access - Access 2007 Report - Asked By D on 30-May-11 04:20 PM

 I need to create a Report and am having trouble.
I have a query that has fields for Contact names and other demographic information along with T-Shirt Order information like sizes: adultL, adultM, youthS, etc. and also other merchandise including quantities and cost (all in separate fields). The query returns results for all Contacts in the database.  Not all Contacts have ordered all merchandise.  So I need a Report that for each Contact it will print out ONLY the order information for that Contact, and not print out those fields where the Contact did not order any merchandise (that would be null).  How would I set up this Report?
wally eye replied to D on 31-May-11 11:39 AM
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?
Pat Hartman replied to D on 31-May-11 02:42 PM
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.

The wizard will build this structure for you.  Just select columns from two tables and choose the main/subreport option.  Once the report is built, you can modify it to suit your artistic taste.
D replied to wally eye on 31-May-11 05:34 PM
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;
wally eye replied to D on 02-Jun-11 06:30 PM
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.