S
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;
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;