Searching relational databases

[thinbuddha]

I have built a library tracking system made up of 2 databases:

The INFORMATION database has all the information of each element in the library

The LOCATION database has the information of where each element is, and where it has been.

The location database has several entries tied to each entry in the information database. What I want to do is be able to search and find all elements that are currently at a given location- but when I try to search for that location, it gives me elements in my result that were at the location at any time- even if it is currently somewhere else.

Help!

 

[JeanW]

Can you give the names of the fields and a sample of your searchscript ?

 

[thinbuddha]

in the INFORMATION DATABASE, the fields include:
<TITLE>, <DESCRIPTION>, <LANGUAGE>, & (most importantly) a FMP generated <SERIAL NUMBER> that is tied to the LOCATION database. There are also several other fields that aren't really involved witht he problem I'm having.

In the LOCATION DATABASE, there is <LOCATION>, <CONTACT NAME>, <COURIER>, <DATE> and the <SERIAL NUMBER> that is copied from the INFORMATION DATABASE into a new record each time a library element is being moved.

As far as a search string, I'm not doing anything more sophisticated than placing the end users into search mode, and letting them fill in the fields for their own search. Ideally, the end user shouldn't have to know much about FMP to make searching work for them... In a perfect world, a user would be able to search by any field all from the same search page. The end users for this database barely know how to use a mouse, so making it as easy as possible for them is a necessity.

Thanks to anyone who can help.

 

[JeanW]

According to me you have to move all the dates, exept the current, to a 'history date' field, along with the locations
That way you can keep track of the movementtime/location, while you still have one field with the date, which is the 'current' (last movingdate) and the 'current' location (last moved to).

When searching on that datefield will give you the current (last) date and the current (last moved to) location.

You can accomplish this with a little extention of your new record script, by setting the datehistory field to itself & current datefield value. After that, set the currentdate field to the right date.
Same thing for the locationfield.
Searching on the location field will give you the current date.
Searching on the datefield will give you the current location.

 

[thinbuddha]

Ah ha- I think I see what you mean. Thanks for the idea- I'll try to implement it- seems like it should work out just fine.

Thanks!