Data not showing up in query

Mojo1

Board Regular
Joined
Mar 6, 2003
Messages
148
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
Mar 6, 2003
Messages
148
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

Board Regular
Joined
Mar 6, 2003
Messages
148
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,133
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top