2 similar tables, 1 query (?)

[Stephanie]

Hi. I think I found a form/table solution to my problem.
I have volunteers that can earn "Bucks" volunteering on
VolunteerOpportunities (automatically assinged based on
shifts) or we can give "Bucks" free-form (in case we don't
want to set up the whole volunteer opportunity). So I
have one field ITABucks on my Shifts table, and one field
Bucks on the GiveBucks table. So, yes. I am
tracking "Bucks" in two different tables. Not elegant,
but it seems to give the flexibility we need.

I'm trying to design a query that can sling all
the "Bucks" together. Contacts can have either/or/both
ITABucks and Bucks. I can put together a query to find
both (in that only Contacts who have both show up- I want
ALL bucks). I'm not great at sql so I'm using the query
design view. Any suggestions? I've included the sql for
finding ITABucks only. The fields (not included in sql)
for GiveBucks are Bucks and DateEarned. Both tables have
ContactID. Can this be done?

SELECT [LastName] & ", " & Nz([NickName],[FirstName]) AS
[Member Name], Animals.AnimalName,
Volunteering.VolunteerDate, Volunteering.VolunteerName,
Organizations.OrganizationName, Shifts.ITABucks,
Shifts.ShiftEndTime, Shifts.ShiftStartTime
FROM ((((Event LEFT JOIN Animals ON Event.AnimalsID =
Animals.AnimalsID) INNER JOIN Volunteering ON
Event.VolunteeringID = Volunteering.VolunteeringID) INNER
JOIN Contacts ON Event.ContactID = Contacts.ContactID)
INNER JOIN Shifts ON (Volunteering.VolunteeringID =
Shifts.VolunteeringID) AND (Event.ShiftID =
Shifts.ShiftID)) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID) ON
Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerDate)>=[forms]![Report Date
Range]![BeginDate] And (Volunteering.VolunteerDate)<=
[forms]![Report Date Range]![EndDate]))
ORDER BY Contacts.LastName, Volunteering.VolunteerDate
DESC;

 

[John Vinson]

I'm trying to design a query that can sling all
the "Bucks" together. Contacts can have either/or/both
ITABucks and Bucks. I can put together a query to find
both (in that only Contacts who have both show up- I want
ALL bucks). I'm not great at sql so I'm using the query
design view. Any suggestions? I've included the sql for
finding ITABucks only. The fields (not included in sql)
for GiveBucks are Bucks and DateEarned. Both tables have
ContactID. Can this be done?

A UNION query would seem to be called for here: it strings together
two tables or queries "end to end" instead of "side to side" as a JOIN
does. Take a look at the online help for UNION to see.

John W. Vinson[MVP]

 

[Stephanie]

John,
Fabulous! I learned about Unions, figured it out, did it
wrong, had data mismatches, had funky output that made me
laugh and cry, finally got it right! Thanks for the
help, Stephanie

 

[John Spencer (MVP)]

Way to go Stephanie!!!!

Bet you remember this better than if you were handed the solution.

 

[Stephanie]

True! except in coding ;-)

-----Original Message-----
Way to go Stephanie!!!!

Bet you remember this better than if you were handed the solution.

.