Use ODB LastQuery does not work if selection is on Payments

When running an MS Access Report via ODB, if the "Use ODB LastQuery" is checked, the process will fail with an invalid SQL statement if the last ODB search was on Payments rather than People. This is because the ODBQuery function in the QuerySupport module assumes that the filter criteria saved in lastquery.txt should be applied to the People table.

Does anyone have a fix for this?

hi Shelly -- The reporting

Fri, 03/07/2008 - 3:26pm

hi Shelly --
The reporting module requires a people table query, but if you need to pull from the payment table to determine who to include in your report you may be able to do it using:

people.ID in (Insert payment query here)

This is easy to set up, you just put in the payment query using the basic pmt query interface, then click on the subquery button, then switch to the people table, and you have the query you need.

Here is an example query from the database of Organizers' Collaborative, ODB's fiscal sponsor:

Select * from people WHERE People.ID in (Select People.ID from people inner join donations on donations.PeopleID = people.id WHERE Category = 'C1' GROUP BY Name, email, stnum, stname, city, zip, streetsort, first, last, People.ID )

This is the full query text (saved in lastquery.txt); this is a people query with nothing checked off but the box on the bottom of the screen containing the following language:

People.ID in (Select People.ID from people inner join donations on donations.PeopleID = people.id WHERE Category = 'C1' GROUP BY Name, email, stnum, stname, city, zip, streetsort, first, last, People.ID )

Please let me know if this solves the issue for you!

Thanks,
-rich cowan
ODB Project Director

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.