select query --limiting results

[Mary]

Hello everyone, thanks ahead of time for any insight offered!

I am working on two forms. One for members who are retired (which are
printed in one color) and the other form is for members who are not retired
(printed in a different color).

My query issue is this:
We have checkboxes for retired, volunteer, full time and part time work.
This shows us for each employment if the job was ft, pt, volunteer or if the
member is retired. (I know checkboxes aren't great, but it makes it easy for
the members to update the forms).
The query problem is that many members are retired but still volunteering.

At this point I have the query returning the correct results for the
retirees. The report shows the retired status and the volunteer efforts. But
if the checkbox is not checked for retired then it returns the member as
working. If we also check the box for retired on the volunteer record, then
we end up with too many parameters on the job report.

Example:

Baker Smith's Bakery Sunnyvale CA 10/23/03-1/15/06 FT
Chef Japanese Steakhouse Sunnyvale CA 01/20/06-02/26/09 FT
Cook Pete's Soup Kitchen Sunnyvale CA 09/13/02-02/26/09 PTVOL

02/26/09-03/15/09 RET

(we really don't need to have RET show up on the line for Pete's soup
kitchen).

I need to eliminate the members who are retired but still volunteering from
the working reports. Any thoughts?

 

[Jeff Boyce]

Mary

"How" depends on "what" ... how you design your query depends on what data
structure you have.

You've described the form, not the tables.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mary]

Thank you for your reply. Sorry I took so long to respond, I couldn't find
the thread again - kept getting a blank page when I tried to link to it even
after signing in.

The ministry information is in a single table. The table lists individual
records per ministry. For example:
(field 1) (field 2) (field 3) (f4,
f5, f6 , f7)
Name Occupation BusinessName FT PT VOL RET
Jane Doe Nurse Practitioner St. Vincent's Hospital x x
Jane Doe Registered Nurse Mayo Clinic x
Mary Doe Teacher Area Elementary

With ft, pt, vol and ret as checkboxes. There are more fields including
start date, end date, city, state, zip, etc. The members entire work history
is included, so there may be 23 records per person, give or take. At this
time the query rejects any record wtih an end date, which is what I need it
to do.

The report has several subqueries that work fine, they include other
information pulled from other tables.

The name field is a lookup field based on primary key for our main member
table.

The table was structured this way because our 400+ members may work at one
place for two months, jump to another for three weeks, move to another after
that, etc. There isn't consistency in where they work or for how long, and
some move around a dozen times in a year. Some members work in the same
place for the same department, but that isn't the norm and sometimes a member
will hop back and forth between two places. We structured the table this way
because it was easier to deal with all the moves. That said, if I have to
restructure something I may be open to that (as long as I can wrap my head
around it), although my lower skill level may require more questions if I am
faced with that.



Thanks!

 

[Mary]

I wonder if part of the problem is that the status (part time, full time,
volunteer) is really a status of the position, and "retired" is a status of
the person. Is there a way to form a query that looks at the retired members
and says "we won't count the retirees even if they have a position status."

Is it better to change the job status to a drop down with the choice of pt,
ft, or volunteer? If I do this, is there a way to generate checkboxes that
show pt as checked or ft as checked for positions, on the forms for the
members to fill out.

My brain is starting to wrap around this, it just needs some nudging. At
this point we have about 3000 records but I see that as a number that is not
insurmountable to tweak if I need to restructure the jobs table.