Forgot password? | Forgot username? | Register

Searching for records with more than one entry in a table

Searching for records with more than one entry in a table

In order to report another funky problem we are having to EMu, I realized I needed to be able to search for records that had more than one entry in tables (such as when we cam add more than one term to Object ID and have it in the Object Identification History table).  So, I need a Boolean AND operator to function with just wildcards, but also for these to appear as separate entries in the table.

Anyone done this before?

Also, if I enter the * wildcard in a field and add a second * in the line below it (implying an OR relationship with the wildcards) I get records that have NO information in that field.  Am I missing some logic?  That doesn't make any sense to me.

Melissa Stoltz Bechhoefer
Anthropology Collections Manager and
NAGPRA Coordinator
Denver Museum of Nature & Science

Melissa Bechhoefer
Anthro Collections Manager
useravatar
Offline
5 Posts
Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Searching for records with more than one entry in a table

Hi Melissa

If I've correctly understood what you're asking you can do it like this:

The thing to do is check that the field in the second row of the table has a value. 

Enter all the other bits of your search, say prefix or department or whatever to narrow it down.

Get the back-end name for the field in the table that you are interested in (Using the question-mark arrow or F1).

Choose "Show Search" from the File menu

From the search that appears there
1. change "where true and" to "where"
2. add "and exists FIELDBACKENDNAME{2}" to the end

FIELDBACKENDNAME{2} means FIELDBACKENDNAME on the second row of the table.

To show an example: if I want to search for all our Ichthyology records that have two "other numbers" recorded I fill in "Ichthyology" in the collection field and select "Show Search". That gives me:

select all
from ecatalogue
where true and
(
    exists
    (
        CatCollectionName_tab
        where
        (
            CatCollectionName contains '"Ichthyology"'
        )
    )
)

I then remove "true and" and add on the appropriate field name with {2} after it to the end, giving

select all
from ecatalogue
where
(
    exists
    (
        CatCollectionName_tab
        where
        (
            CatCollectionName contains '"Ichthyology"'
        )
    )
)
and exists CatOtherNumbersSource_tab{2}

Which gets up the results I was after

Emu really doesn't like these types of searches so after your results are returned you need to update the amount of records to an accurate result. You do that by, in list view, going to the last entry in the results and then back to the first. This will allow EMu to get an accurate count of the records.

I hope that does what you need

Regards
Keith

Keith Maguire
South Australian Museum
useravatar
Offline
11 Posts
Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Searching for records with more than one entry in a table

You could also try a count statement such as

and
count (CatOtherNumbersSource_tab) > 1  (you might need parentheses around this statement.

Regards,
Deb

Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic

Board Info

Board Stats
 
Total Topics:
599
Total Polls:
0
Total Posts:
1362
Posts this week:
4
User Info
 
Total Users:
812
Newest User:
Vilasack
Members Online:
3
Guests Online:
238