Queries.

[Pete]

I have a table that has fields that are quite normal except for one that
contains differing values which are names of submarines that the person has
served on. There can be any number of subs in this field. I am trying to
find how many individuals have worked on submarine. I have tried queries and
filters but my knowledge of access is vert limited.
I am using Access 2000.

Pete.

 

[John Spencer]

It's fairly easy if you have ONE specific submarine in mind.

Use criteria like
Like "*" & [Enter name of submarine] & "*"

If you are trying to get this information for multiple submarines you need a
table that lists all the possible submarines (one per record, no duplicates).

Then you can join the SubmarineList to your current table

SELECT SubmarineList.Submarine, Count(CurrentTable.ServedOn) as CountOfPeople
FROM CurrentTable INNER JOIN SubmarineList
ON CurrentTable.ServedOn LIKE "*" & SubmarineList.Submarine & "*"
GROUP BY SubmarineList.Submarine

If you can only build a query in design view you can use an alternative query
== Add your table and the SubmarineList table
== Add the Submarine field and the ServedOn fields to the query
== Under the served on field enter criteria of
LIKE "*" & [SubmarineList].[Submarine] & "*"
== Select View: Totals from the menu
== Change Group By to WHERE under ServedOn field
== Add ServedOn field a second time and change Group by to Count under this field

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Oh, I forgot to mention that the table design is faulty. You should have at
least one more table to list the person and one sub for that person in each
record. SO if Joe served on the Cod and the Perch, there would be two records
for Joe in the table.

For consistent data entry, you would still want the additional table listing
all the submarines.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

Typically it's a bad idea to put multiple values in a single field. A better
table structure would be a related table that contained one record per
person.

If you want to leave your tables the same, you should be able to count the
number of records with a query like:
SELECT Count(*) as NumOf
FROM tblNoNameGiven
WHERE [Submarines] Is Not Null;

 

[Marshall Barton]

I have a table that has fields that are quite normal except for one that
contains differing values which are names of submarines that the person has
served on. There can be any number of subs in this field. I am trying to
find how many individuals have worked on submarine. I have tried queries and
filters but my knowledge of access is vert limited.
I am using Access 2000.

Are you saying the submarine field contains something like
"Nautilus Kirsk Thresher"? If so, you will have to use a
funky criteria such as:
Like "*Kirsk*"
or, if you are prompting for the sub's name:
Like "*" & [Enter sub name] & "*"

Putting more than a single value in a field is a violation
of the rules of database normalization. Your current
problem is just the tip of the iceburg that will make all
kinds of thing difficult or near impossible to do somewhere
down the road.

Since a sub has many individuals and each individual can
serve on more than one sub, you have a classic many-to-many
relationship. This kind of relationship shoud be structured
with a Submarines table with a record for each boat, an
Individuals table with a record for each individual and a
"junction" table that has a record for each individual/boat
combination. The junction table would have a compund
primary key composed of a foreign key to the individual and
a foreign key to the sub (and probably other fields about
the indivual's service on the boat: dates served, rank,
responsibility, etc).