I am going to address these two comments:
The table that has the tracking information for each
job has the job item name as the field name.
and
My next question is, can I increase the number of rows
for the OR statements on my grid? One of my tables has
more than 20 fields that I need to be able to do this for.
It sure sounds to me like your related (child) table is not a normalized design. If you needed to
accommodate a new Job Item in the related table, could you do so by simply adding a record? Or,
would you need to go into table design and add a new field. If your answer is that you need to
add a new field to accommodate this data, then you definitely do not have a normalized design.
The related table should look something like this:
pkJobItemNo (primary key--I suggest autonumber)
fkJobCode (foreign key related to pkJobCode in your master table)
JobItemNo (likely an integer field)
JobCompleted (yes/no field)
Sample data would look like this:
pkJobItemNo fkJobCode JobItemNo JobCompleted
1 100 1 True
2 100 2 False
3 100 3 True
4 101 1 True
5 101 2 False
6 102 1 True
7 102 2 False
8 102 3 True
9 102 4 True
10 102 5 False
etc.
With this design, you can add an unlimited number of JobItem numbers. You would need to apply the
criteria to only one field: JobCompleted.
Tom
______________________________________
Tom,
Forgot about using the different rows for the OR command. That helps a
LOT.
As for your other point, the example I gave was not a very good one. The
table where I am tracking each job item is actually linked back to a master
table that contains the Job_Code, Customer, Address, etc. The table that has
the tracking information for each job has the job item name as the field
name. I didn't use the "#", just as an example.
My next question is, can I increase the number of rows for the OR statements
on my grid? One of my tables has more than 20 fields that I need to be able
to do this for.
______________________________________
Digger -
That's because you placed the criteria on the same row in the QBE grid. If you place the False
(or 0) criteria for your Yes/No fields on separate rows, the resulting SQL statement will include
the "OR" keyword.
This brings up another point....it appears as if you have separate fields for Job Item1, Job
Item2, etc. If this is so, then you do not have a normalized database design. One Job# (which, by
the way is not a good field name with the # sign), can have many Job Item numbers, so you have a
one-to-many (1:M) relationship. The Job Item records should be in a related table whose parent
includes Job#, Customer and Address.
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335
Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp
Database design documents
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")
http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533
Tom
_________________________________
This actually doesn't give me what I'm looking for.
Here's a short example of what I need. I have the following fields to track:
Job#
Customer
Address
Job Item1, Job Item2, etc.
Job Items are checked off as they are completed.
I want a query or report that will show me for each Job# only the Job Items
that are not checked. If I do as below, and set "No" as the criteria, I will
only get back records that have all of these items unchecked, because this
works like an "AND" statement, saying Job Item1 AND Job Item2 AND.... = "No".
_________________________________
Create a new query combining all the fields you want in the report. If you
want data from two or more tables, the tables must be related.
The 'checked' info should be tracked via yes/no fields. Set the criteria in
the query for 'no' and create a report based on that query.
_________________________________
I am using Access to track jobs my company is doing out in the field. For
each job there are a set number of actions that need to be performed. When a
field associate checks the job he fills out a checksheet and this data is
transferred to the database. Often there are things that are not checked
off and need follow up. I would like to create a report that shows all of
the relevant job info (address, contact, etc) and ONLY the actions that were
NOT checked off for each job. This way we only see the outstanding
activities that we need to fix. Any suggestions on how to accomplish this?
