Data not showing up in query

[Mojo1]

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.

 

[SydneyGeek]

Plenty of fields! :wink:
My guess is that you need another query to find those with donations but no batch #. It would need to be an outer join so you can see the nulls, which would need to be filled in. Then the main query can give you the big picture.
It's tricky to work out your structure from such a large SQL string, so providing a simple breakdown of how PEOPLE links to GIFTPLEDGES and DONATIONS would help.

Regards
Denis

 

[Mojo1]

This query PeopleAlumni will display the people I am wanting and it is linked in the main query. The problem is that I can not get in the main query these people to show up.

SELECT [dbo_PEOPLE].[PEOPLE_CODE_ID], Max([dbo_ALUMNICLASS].[CLASS_YEAR]) AS MaxOfCLASS_YEAR, Max([dbo_ALUMNICLASS].[FORMAL_TITLE]) AS MaxOfFORMAL_TITLE
FROM dbo_PEOPLE LEFT JOIN dbo_ALUMNICLASS ON [dbo_PEOPLE].[PEOPLE_CODE_ID]=[dbo_ALUMNICLASS].[PEOPLE_CODE_ID]
GROUP BY [dbo_PEOPLE].[PEOPLE_CODE_ID];

 

[Mojo1]

I created a query that will list all the people end the batch number they are part of. My problem is that I do not know where to join to have them show up