Query help please...

  • Thread starter Thread starter David
  • Start date Start date
D

David

Sort of new to acces so please be kind. Here is my
question:

How do I get a query to search for duplicate information
in the same field but in different records without knowing
what that information may be?

Explanation:

I set up a database to coordinate league schedules in
city parks. Some leagues might have more than one sports
field at a time. I need to query the records but
sometimes the records are entered with more than one
sports field entered into the (field) field. Searching
with the LIKE has too many variables to input. For
example:

Data might be entered as - 1 or 134 or 124 or ab1e or any
combination as there are numerous sports fields in alot of
parks all named with a number or letter.

Hope I explained this right. Thank you in advance for
your help.

David
 
Dear David:

I'm not sure you'll think I'm being kind. Sorry.

Someone has entered a combination of SportsFields (I'm calling it that
to avoid complications of the term "field") into one column (yes, this
is a field as well, but I won't use the term to avoid
misunderstanding).

This is the root problem. If a league has reserved several
SportsFields at the same time, your data structure really needs to
record these in separate rows.

Somewhere in the design there should be a table of the various
SportsFields. When assigning them, a combo box should draw on this
list and force the user to select one SportsField from the list. I
think it may be apparent how this would relieve the problem you have
now. I expect the user's are just being permitted to type in whatever
they wish.

Now, I can offer some slim hope of succeeding to some degree with what
you have. But this will depend very strongly on the typing skills and
consistency of those who have used the system.

If it always says "space/or/space" between the names in the list, a
LIKE command CAN be built do split this up.

To search for SportsField "134" you can search for "*or 134 or*".
This will work whenever 134 is not the first or last in the list in
the SportsFields column. If, however, you add "or " at the beginning
of each SportsFields column value, and add " or" at the end, then you
can search even if it is the first or last. Like this:

WHERE "or " & SportsFields & " or" LIKE "or " & "134" & " or"

You must substitute your column names for SportsFields and for "134"
in the above. It just might work. But it may not be very dependable,
which will be determined by what users have actually typed.

Not a very pretty solution, but the best I can offer. Does it at
least make sense?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
It does make sense. Thank You.
-----Original Message-----
Dear David:

I'm not sure you'll think I'm being kind. Sorry.

Someone has entered a combination of SportsFields (I'm calling it that
to avoid complications of the term "field") into one column (yes, this
is a field as well, but I won't use the term to avoid
misunderstanding).

This is the root problem. If a league has reserved several
SportsFields at the same time, your data structure really needs to
record these in separate rows.

Somewhere in the design there should be a table of the various
SportsFields. When assigning them, a combo box should draw on this
list and force the user to select one SportsField from the list. I
think it may be apparent how this would relieve the problem you have
now. I expect the user's are just being permitted to type in whatever
they wish.

Now, I can offer some slim hope of succeeding to some degree with what
you have. But this will depend very strongly on the typing skills and
consistency of those who have used the system.

If it always says "space/or/space" between the names in the list, a
LIKE command CAN be built do split this up.

To search for SportsField "134" you can search for "*or 134 or*".
This will work whenever 134 is not the first or last in the list in
the SportsFields column. If, however, you add "or " at the beginning
of each SportsFields column value, and add " or" at the end, then you
can search even if it is the first or last. Like this:

WHERE "or " & SportsFields & " or" LIKE "or " & "134" & " or"

You must substitute your column names for SportsFields and for "134"
in the above. It just might work. But it may not be very dependable,
which will be determined by what users have actually typed.

Not a very pretty solution, but the best I can offer. Does it at
least make sense?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.
Click to expand...
 
Back
Top