Exclude a field from a query

[jenks1120]

I have a table with several fields. I am trying to query the records
based on certain criteria. Example I am looking for oranges, but some
of the records also have apples. How do I show only the oranges in the
query results.

 

[Jeff Boyce]

You haven't described how "apples" and "oranges" relate to your "table with
several fields".

Are you saying that you have one field that holds only apples, and another
field that holds only oranges? If so, you have a ... spreadsheet! In a
relational database, you'd have one field for FruitType, and some rows would
have "apple" and other rows, in that same field, would have "orange".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jenks1120]

You haven't described how "apples" and "oranges" relate to your "table with
several fields".

Are you saying that you have one field that holds only apples, and another
field that holds only oranges? If so, you have a ... spreadsheet! In a
relational database, you'd have one field for FruitType, and some rows would
have "apple" and other rows, in that same field, would have "orange".

Regards

Jeff Boyce
Microsoft Office/Access MVP

The database that I have created has ten fields on a tabbed form.
Example Myfield1, myfield2, etc. Each field has a lookup to the same
table. This allows me to select 10 items from the table for the same
person. I created a query and selected criteria, such as "apples".
But when the query runs, it will also include "oranges" if they are
present in the same record. How do I prevent it from displaying
oranges.

 

[jenks1120]

You haven't described how "apples" and "oranges" relate to your "table with
several fields".

Are you saying that you have one field that holds only apples, and another
field that holds only oranges? If so, you have a ... spreadsheet! In a
relational database, you'd have one field for FruitType, and some rows would
have "apple" and other rows, in that same field, would have "orange".

Regards

Jeff Boyce
Microsoft Office/Access MVP

The database that I have created has ten fields on a tabbed form.
Example Myfield1, myfield2, etc. Each field has a lookup to the same
table. This allows me to select 10 items from the table for the same
person. I created a query and selected criteria, such as "apples".
But when the query runs, it will also include "oranges" if they are
present in the same record. How do I prevent it from displaying
oranges.

 

[Jeff Boyce]

I'm still thinking you have described a spreadsheet. Having ten fields in
your table, each linked with the same (other) table for lookup is a
description of a spreadsheet, not a relational database.

The problem with a design like this (10 "repeating" fields) is that as soon
as you need to have eleven fields (or nine, or ...) you will have to modify
your table structure, your queries, your forms, your reports, and any code
that refers to the fields. Do you really want to have to do that much
maintenance?!

Please re-read my suggestion about a single field for "FruitType", and using
one row per record, rather than 10 (or 11, or 9, or !) columns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jenks1120]

I'm still thinking you have described a spreadsheet. Having ten fields in
your table, each linked with the same (other) table for lookup is a
description of a spreadsheet, not a relational database.

The problem with a design like this (10 "repeating" fields) is that as soon
as you need to have eleven fields (or nine, or ...) you will have to modify
your table structure, your queries, your forms, your reports, and any code
that refers to the fields. Do you really want to have to do that much
maintenance?!

Please re-read my suggestion about a single field for "FruitType", and using
one row per record, rather than 10 (or 11, or 9, or !) columns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

What I am trying to develop is a crime stat database. I have a crime
table, but one person may have numerous crimes which are listed in the
crime table. With my limited knowledge of MS Access, I placed 10 combo
boxes listed as crime1, crime2, etc. The data all comes from the crime
table. It worked but when I ran a query for "Homicide" I also got
"assault", because it was part of the same record. So with your
suggestion, it looks like I will need to return to design and
structure. If so, any suggestion on setting up the table. I already
have subject, role, and incident tables. Thanks for the help

 

[Jeff Boyce]

(sorry for the delay, I've been out of town)

Most any table you have set up as a spreadsheet (crime1, crime2, ...) can be
converted to a well-normalized design, more "deep" than "wide". Here's a
rough idea...

tblPersonAccusation
PersonAccusationID
PersonID (foreign key from Person table - who's accused of a
crime)
CrimeID (foreign key from Crime table - which crime)
AccusationDate
...

This design requires one row per valid Person/Crime combination... but
doesn't have "empty" columns to hold "future" crimes.

Check "normalization" and "3rd normal form" as topics for further research.

Regards

Jeff Boyce
Microsoft Office/Access MVP