Data not showing up in query

  • Thread starter Thread starter SheepDog
  • Start date Start date
S

SheepDog

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.
 
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.
Click to expand...

"Like it should", or "like I want it to"? <g>

You've not provided much information about your underlying table structure,
which would help 'group readers understand better what you have to work with
in doing what you are trying to do. Could you also describe a bit more
about what you are trying to do? It sounds like you want to list all "first
time donators". You didn't mention when/where/how in your processing a
donator receives a "batch number".

Are you saying that you can enter a new donator but s/he won't have a batch
number?

More info, please...

Jeff Boyce
<Access MVP>
 
Back
Top