Complex Query (atleast for me)

[Samantha]

Hi all, I am learning and have been for a few years so I am familiar with a little bit of everything. I am using Access 2010. I am not quite sure how to complete what I want - I am half way there.

With the holidays approaching I am trying to generate a Holiday Mailing Report to make my life easier this year. So my database has all my customers info and jobs (proposals) quoted. I started by creating a qry from tblProposals including the company name, contact, and date of anything after Jan 2012. So this returns all of people we have worked with over the time period specified.

SELECT DISTINCT tblProposals.Company, tblProposals.Contact, tblProposals.DateGenerated, Contacts.ContactName
FROM (((tblLookupCityState INNER JOIN tblLookupServiceAddress ON tblLookupCityState.City = tblLookupServiceAddress.City) INNER JOIN tblProposals ON tblLookupServiceAddress.ServiceAddress = tblProposals.ServiceAddress.Value) LEFT JOIN tblWOH ON tblProposals.JobNumber = tblWOH.JobNumber) INNER JOIN Contacts ON (tblLookupCityState.City = Contacts.City) AND (tblProposals.Contact = Contacts.ContactID)
WHERE (((tblProposals.DateGenerated)>#1/1/2012#));

Next, I created a table "tblHoliday" it includes HolidayID (PK) and ContactID (FK), Comments, then also 6 more fields (yes/no) Gift & Card for the past 3 years -2011, 2012, & 2013. I created a qry based on this table to tie back in the contact name and addresses and eliminate any inactive clients as well.

SELECT DISTINCT Contacts.ContactName, tblHoliday.ContactID, tblHoliday.HolidayID, tblHoliday.Comments, tblHoliday.[2013 Gift], tblHoliday.[2013 Card], tblHoliday.[2012 Gift], tblHoliday.[2012 Card], tblHoliday.[2011 Card], tblHoliday.[2011 Gift], Contacts.Company, Contacts.Address, Contacts.State, Contacts.ZipCode, Contacts.City
FROM tblHoliday INNER JOIN Contacts ON tblHoliday.ContactID = Contacts.ContactID
WHERE (((Contacts.Inactive) Is Not Null));

From here I have made a third "qryCustomersGettingCards" to tie the two previous together

SELECT DISTINCT qryHolidayCards.ContactName, qryHolidayCards.Comments, qryHolidayCards.[2013 Gift], qryHolidayCards.[2013 Card], qryHolidayCards.[2012 Gift], qryHolidayCards.[2012 Card], qryHolidayCards.[2011 Card], qryHolidayCards.[2011 Gift], qryHolidayCards.Company, qryHolidayCards.Address, qryHolidayCards.State, qryHolidayCards.ZipCode, qryHolidayCards.City
FROM qryFrequentCustomers LEFT JOIN qryHolidayCards ON qryFrequentCustomers.Contacts.ContactName = qryHolidayCards.ContactName;

This all works just fine - my problem is I don't want to eliminate anyone that received a card in a previous year and didn't have work done in the time frame specified. My thoughts and from searching are that I need some type of ingenious vba statement where it would cycle through each of the 6 fields and if one is true it would return that record. I am lost on how to accomplish this I would guess in my second query?
Any help is greatly appreciated!

So taking that approach a step further I changed the second qry to the following

SELECT DISTINCT Contacts.ContactName, tblHoliday.ContactID, tblHoliday.HolidayID, tblHoliday.Comments, tblHoliday.[2013 Gift], tblHoliday.[2013 Card], tblHoliday.[2012 Gift], tblHoliday.[2012 Card], tblHoliday.[2011 Card], tblHoliday.[2011 Gift], Contacts.Company, Contacts.Address, Contacts.State, Contacts.ZipCode, Contacts.City
FROM tblHoliday INNER JOIN Contacts ON tblHoliday.ContactID = Contacts.ContactID
WHERE (((tblHoliday.[2012 Gift])=True)) OR (((tblHoliday.[2012 Card])=True)) OR (((tblHoliday.[2011 Card])=True)) OR (((tblHoliday.[2011 Gift])=True) AND ((Contacts.Inactive) Is Not Null));

Which does return the information I want - but now to smush them together into that third qry there is no option for all records - just equal, right, or left join

 

[Cronk]

You can combine the results from 2 (or more queries) using UNION in a third query
(Select * from Query1) UNION (select * from Query2)

However, the field structure in the two queries has to be exactly the same.

You then might need to use DISTINCT or grouping so as not to display duplicate Contacts.

 

[Samantha]

The field structure is different for the queries, any other ideas?

 

[redalert]

It seems to me the tblHoliday table that is causing the problem so you need a Union query that will make the data appear to be normalised.

Try something like this: SELECT Contacts.ContactName, tblHoliday.ContactID, tblHoliday.HolidayID, tblHoliday.Comments, 2013 AS TheYear,tblHoliday.[2013 Gift] AS Gift, tblHoliday.[2013 Card] As Card FROM tblHoliday UNION SELECT Contacts.ContactName, tblHoliday.ContactID, tblHoliday.HolidayID, tblHoliday.Comments, 2012 AS TheYear ,tblHoliday.[2012 Gift] AS Gift, tblHoliday.[2012 Card] As Card FROM tblHoliday UNION SELECT Contacts.ContactName, tblHoliday.ContactID, tblHoliday.HolidayID, tblHoliday.Comments, 2011 AS TheYear,tblHoliday.[2011 Gift] AS Gift, tblHoliday.[2011 Card] As Card FROM tblHoliday

I have not got Access in front of me so excuse me if I have not got the syntax
100% correct.

You can obviously add other fields in there and use the query instead of the tblHolidays table.

You really need to normalise your data structure and people on here can help you do that.

 

[Cronk]

Red, precisely.

Samantha
The union query posted by Red will give you as many occurrences of Contact as the years in which they received gifts.

If you only want one occurrence per Contact, save the query as posted by Red as say, qryGiftList and use another query as follows
select ContactName from qryGiftList Group on ContactName;

If you want one record per Contact and the years concatanated in a string, you will need a function in VBA.

If you want to make the query more generic and not hard code the year, you could replace the 2013, 2012, 2011 with year(date()), year(date())-1, year(date())-2

Don't think I have any more other ideas