I have a query that is supposed to display all the people who have made a donation. This is determined by a batch number. If some one has not made a donation before they have not been assigned a batch number. The problem I am having is getting first time donators to show up in the query.
SELECT dbo_PEOPLE.PEOPLE_CODE_ID, dbo_CODE_PREFIX.SHORT_DESC, dbo_PEOPLE.FIRST_NAME, dbo_PEOPLE.MIDDLE_NAME, dbo_PEOPLE.LAST_NAME, dbo_PEOPLE.SUFFIX, dbo_ADDRESS.ADDRESS_LINE_1, dbo_ADDRESS.ADDRESS_LINE_2, dbo_ADDRESS.ADDRESS_LINE_3, dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIP_CODE, dbo_ADDRESS.COUNTRY, dbo_ADDRESS.NO_MAIL, PeopleAlumni.MaxOfCLASS_YEAR AS CLASS_YEAR, PeopleAlumni.MaxOfFORMAL_TITLE AS FORMAL_TITLE, Left([DebitQuery].[GL_ACCOUNT],2) & "-" & Mid([DebitQuery].[GL_ACCOUNT],3,5) & "-" & Mid([DebitQuery].[GL_ACCOUNT],8,3) & "-" & Right([DebitQuery].[GL_ACCOUNT],3) AS GL_ACCOUNTD, Left([CreditQuery].[GL_ACCOUNT],2) & "-" & Mid([CreditQuery].[GL_ACCOUNT],3,5) & "-" & Mid([CreditQuery].[GL_ACCOUNT],8,3) & "-" & Right([CreditQuery].[GL_ACCOUNT],3) AS GL_ACCOUNTC, CreditQuery.AMOUNT, DebitQuery.AMOUNT, dbo_GIFTPLEDGE.COMMENTS, dbo_SALUTATION.SALUTATION, dbo_GIFTPLEDGE.GIFT_BATCH, dbo_GIFTPLEDGE.GIFT_DATE, dbo_GIFTPLEDGE.GIFT_AMOUNT, dbo_GIFTPLEDGE.GIFT_TENDER, dbo_GIFTPLEDGEDETAIL.RECEIPT_NUMBER, dbo_GIFTACKNOWLEDGE.ACKNOWLEDGE_TYPE
FROM (dbo_GIFTACKNOWLEDGE INNER JOIN (dbo_GIFTPLEDGEDETAIL INNER JOIN (((dbo_GIFTPLEDGE INNER JOIN ((dbo_CODE_PREFIX INNER JOIN (dbo_ADDRESS INNER JOIN dbo_PEOPLE ON (dbo_ADDRESS.ADDRESS_TYPE = dbo_PEOPLE.PREFERRED_ADD) AND (dbo_ADDRESS.PEOPLE_ORG_CODE_ID = dbo_PEOPLE.PEOPLE_CODE_ID)) ON dbo_CODE_PREFIX.CODE_VALUE = dbo_PEOPLE.PREFIX) INNER JOIN dbo_SALUTATION ON dbo_PEOPLE.PEOPLE_CODE_ID = dbo_SALUTATION.PEOPLE_CODE_ID) ON dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID = dbo_PEOPLE.PEOPLE_CODE_ID) INNER JOIN CreditQuery ON (dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID = CreditQuery.PEOPLE_ORG_CODE_ID) AND (dbo_GIFTPLEDGE.PLEDGE_NUMBER = CreditQuery.PLEDGE_NUMBER) AND (dbo_GIFTPLEDGE.GIFT_SEQUENCE = CreditQuery.GIFT_SEQUENCE)) INNER JOIN DebitQuery ON (dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID = DebitQuery.PEOPLE_ORG_CODE_ID) AND (dbo_GIFTPLEDGE.PLEDGE_NUMBER = DebitQuery.PLEDGE_NUMBER) AND (dbo_GIFTPLEDGE.GIFT_SEQUENCE = DebitQuery.GIFT_SEQUENCE)) ON (dbo_GIFTPLEDGEDETAIL.GIFT_SEQUENCE = dbo_GIFTPLEDGE.GIFT_SEQUENCE) AND (dbo_GIFTPLEDGEDETAIL.PLEDGE_NUMBER = dbo_GIFTPLEDGE.PLEDGE_NUMBER) AND (dbo_GIFTPLEDGEDETAIL.PEOPLE_ORG_CODE_ID = dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID)) ON (dbo_GIFTACKNOWLEDGE.PEOPLE_ORG_CODE_ID = dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID) AND (dbo_GIFTACKNOWLEDGE.PLEDGE_NUMBER = dbo_GIFTPLEDGE.PLEDGE_NUMBER) AND (dbo_GIFTACKNOWLEDGE.GIFT_SEQUENCE = dbo_GIFTPLEDGE.GIFT_SEQUENCE)) INNER JOIN PeopleAlumni ON dbo_PEOPLE.PEOPLE_CODE_ID = PeopleAlumni.PEOPLE_CODE_ID
WHERE (((dbo_GIFTPLEDGE.GIFT_BATCH)=[Enter Batch Number]))
ORDER BY dbo_PEOPLE.PEOPLE_CODE_ID;
put in OR dbo_GIFTPLEDGE.GIFT_BATCH Is Null
and it still does not return the people that have made a first donation.
I believe the problem is in the join between PeopleAlumni Query and the People Table. I have changed the joins and can not make it work like it should.
SELECT dbo_PEOPLE.PEOPLE_CODE_ID, dbo_CODE_PREFIX.SHORT_DESC, dbo_PEOPLE.FIRST_NAME, dbo_PEOPLE.MIDDLE_NAME, dbo_PEOPLE.LAST_NAME, dbo_PEOPLE.SUFFIX, dbo_ADDRESS.ADDRESS_LINE_1, dbo_ADDRESS.ADDRESS_LINE_2, dbo_ADDRESS.ADDRESS_LINE_3, dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIP_CODE, dbo_ADDRESS.COUNTRY, dbo_ADDRESS.NO_MAIL, PeopleAlumni.MaxOfCLASS_YEAR AS CLASS_YEAR, PeopleAlumni.MaxOfFORMAL_TITLE AS FORMAL_TITLE, Left([DebitQuery].[GL_ACCOUNT],2) & "-" & Mid([DebitQuery].[GL_ACCOUNT],3,5) & "-" & Mid([DebitQuery].[GL_ACCOUNT],8,3) & "-" & Right([DebitQuery].[GL_ACCOUNT],3) AS GL_ACCOUNTD, Left([CreditQuery].[GL_ACCOUNT],2) & "-" & Mid([CreditQuery].[GL_ACCOUNT],3,5) & "-" & Mid([CreditQuery].[GL_ACCOUNT],8,3) & "-" & Right([CreditQuery].[GL_ACCOUNT],3) AS GL_ACCOUNTC, CreditQuery.AMOUNT, DebitQuery.AMOUNT, dbo_GIFTPLEDGE.COMMENTS, dbo_SALUTATION.SALUTATION, dbo_GIFTPLEDGE.GIFT_BATCH, dbo_GIFTPLEDGE.GIFT_DATE, dbo_GIFTPLEDGE.GIFT_AMOUNT, dbo_GIFTPLEDGE.GIFT_TENDER, dbo_GIFTPLEDGEDETAIL.RECEIPT_NUMBER, dbo_GIFTACKNOWLEDGE.ACKNOWLEDGE_TYPE
FROM (dbo_GIFTACKNOWLEDGE INNER JOIN (dbo_GIFTPLEDGEDETAIL INNER JOIN (((dbo_GIFTPLEDGE INNER JOIN ((dbo_CODE_PREFIX INNER JOIN (dbo_ADDRESS INNER JOIN dbo_PEOPLE ON (dbo_ADDRESS.ADDRESS_TYPE = dbo_PEOPLE.PREFERRED_ADD) AND (dbo_ADDRESS.PEOPLE_ORG_CODE_ID = dbo_PEOPLE.PEOPLE_CODE_ID)) ON dbo_CODE_PREFIX.CODE_VALUE = dbo_PEOPLE.PREFIX) INNER JOIN dbo_SALUTATION ON dbo_PEOPLE.PEOPLE_CODE_ID = dbo_SALUTATION.PEOPLE_CODE_ID) ON dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID = dbo_PEOPLE.PEOPLE_CODE_ID) INNER JOIN CreditQuery ON (dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID = CreditQuery.PEOPLE_ORG_CODE_ID) AND (dbo_GIFTPLEDGE.PLEDGE_NUMBER = CreditQuery.PLEDGE_NUMBER) AND (dbo_GIFTPLEDGE.GIFT_SEQUENCE = CreditQuery.GIFT_SEQUENCE)) INNER JOIN DebitQuery ON (dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID = DebitQuery.PEOPLE_ORG_CODE_ID) AND (dbo_GIFTPLEDGE.PLEDGE_NUMBER = DebitQuery.PLEDGE_NUMBER) AND (dbo_GIFTPLEDGE.GIFT_SEQUENCE = DebitQuery.GIFT_SEQUENCE)) ON (dbo_GIFTPLEDGEDETAIL.GIFT_SEQUENCE = dbo_GIFTPLEDGE.GIFT_SEQUENCE) AND (dbo_GIFTPLEDGEDETAIL.PLEDGE_NUMBER = dbo_GIFTPLEDGE.PLEDGE_NUMBER) AND (dbo_GIFTPLEDGEDETAIL.PEOPLE_ORG_CODE_ID = dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID)) ON (dbo_GIFTACKNOWLEDGE.PEOPLE_ORG_CODE_ID = dbo_GIFTPLEDGE.PEOPLE_ORG_CODE_ID) AND (dbo_GIFTACKNOWLEDGE.PLEDGE_NUMBER = dbo_GIFTPLEDGE.PLEDGE_NUMBER) AND (dbo_GIFTACKNOWLEDGE.GIFT_SEQUENCE = dbo_GIFTPLEDGE.GIFT_SEQUENCE)) INNER JOIN PeopleAlumni ON dbo_PEOPLE.PEOPLE_CODE_ID = PeopleAlumni.PEOPLE_CODE_ID
WHERE (((dbo_GIFTPLEDGE.GIFT_BATCH)=[Enter Batch Number]))
ORDER BY dbo_PEOPLE.PEOPLE_CODE_ID;
put in OR dbo_GIFTPLEDGE.GIFT_BATCH Is Null
and it still does not return the people that have made a first donation.
I believe the problem is in the join between PeopleAlumni Query and the People Table. I have changed the joins and can not make it work like it should.