I am an MS Access idiot

by

So I have this database at work that we use as a job log. Whenever someone gets assigned a new job, that person uses the job log to…well, log that project. It works pretty well. The underlying main table is a bit of a monster, complexity-wise, owing to my rudimentary understanding on Access and my total ignorance of SQL and VBA.

I presently have a task I’d like to do that I just can’t figure out how to accomplish. I’ve posted about it a couple different places, but I’m wondering if I just don’t have a strong enough grasp of the Access vernacular to describe what I’m trying to do. So I thought I’d write it out here, partly as exercise, partly in the hope that some rogue Access DBA will stumble across this post and take pity on me. Anyway. Here goes.

I created a query that pulls the following fields from my main table using “staff name” as a criterion:

job #; staff member; requester; job title; date in; date out; time spent

One of the fields in my main table is “job type.” I’d like to include code in my query that references a checkbox on an input form to exclude a certain job type from my results. In plain language, it’d be something like, “show me a list of all the jobs done by staff member X, excluding jobs of type “list request.”

This seems like it should be a simple thing to accomplish, and no doubt there’s more than one way to get it done. I just don’t know what any of those ways are. Yet.

Advertisements

7 Responses to “I am an MS Access idiot”

  1. jim Says:

    i think i have your answer. in the gui for building your query make sure to include all the fields including the ones you want to exclude. on line one of the query select your INCLUDED in the correct field and in the field you want to exclude put list name (that means not equal).

    this should work, good luck

  2. jim Says:

    whoops, looks like your websites text editor “excluded” the most important part of the comment. I’ll try again here with quotes;

    on line one of the query select your INCLUDED in the correct field and in the field you want to exclude put: “list name” (that means not equal).

  3. jim Says:

    damn! your editor is now annoying me!!!!!!!!!!!!!!!

    BEFORE THE EXCLUDED NAME, INPUT THE SYMBOL FOR GREATER THAN FOLLOWED BY THE SYMBOL FOR LESS THAN, NO SPACES. THOSE ARE THE KEYS NEXT TO THE “m” KEY ON YOUR KEYBOARD. I hope you can understand what I’m gittin at!

  4. jim Says:

    P.s. check out my blog too. p.p.s. I am white. p.p.p.s. Doc sez: sorry its genetic!

  5. nicheplayer Says:

    I see what you’re saying, but your solution is missing a piece. I need to run the query this way *only* when there’s a checkbox checked on my input page. So I have to figure a way to reference that checkbox in the query criteria somewhere…. Get it? Thanks for your replies!

  6. jim Says:

    ok. I get it, but is the checkbox contents contained in the database in any way? hey thanks for checking out my blog!

  7. nicheplayer Says:

    Here’s some more detail on what I’m trying to do.

    I need a way for a user creating a report to toggle whether they want to see records that contain a certain value. The checkbox only appears on the report creation form, and then it’s referenced in the query that feeds the report. A “Yes” value for the checkbox excludes records that contain the value, and a “No” value allows all records to be returned.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: