How to select from many fields to find one field

[sukisue]

Ok- this is going to be tough to explain!

I have a database that is used for storing data about a large collection of images. Among others, I have a table that contains our category heirarchy system.

For example: a photograph of the Guggenheim museum would have a "category" = Architecture, a "Worktype" = Cultural Buildings and a "SubWorktype" = Museums.

There are currently 450 different configurations like this (ie 450 records in my table).

So, in my main table (records for each image) I currently have it set up so that you select each of these fields from lists that are pulled from the table.

But it would take up much less space if I could just select from lists, like it is now, but that the data wasn't stored- just a categoryID was stored.

So, how can i do an ID lookup based on 3 fields and then how do I clear the fields I used to select the categories etc.? Is there a way to use a portal to select?

If this makes any sense- I'd greatly appreciate any suggestions.

Thanks!

[RalphL]

I assume that second table is used to make conditional value lists.

It sounds like you want to a find for all items that match your 3 categories. Have a layout that shows these fields only format the fields as a popup list. Enter the find mode, the fields will be empty enter the first value, then the second and then the third. Press the enter key and only records that match will show.

I would be concerned about using some sort of an ID. The users would have to remember what the ID's mean.

You said, " But it would take up much less space if I could just select from lists, like it is now, but that the data wasn't stored- just a categoryID was stored." I don't understand what you mean by this.

[sukisue]

Yes- the 2nd table is used to make conditional value lists...

Changing layouts and entering find - would slow down the entry process (this is only 1 of about 15 different topic areas that need to be entered for each image. Besides- this would be about the same as looking it up in the table and copying the id from there.

Also, what I didn't mention before, is that there are other things that are related to the category combo- like codes that have to do with how we physically file each image (kinda like a call Number)... so ideally, I would want to select the different category fields, have an ID lookup and then based on that ID, the codes would show in a related field (::categorycode etc).

I have something set up - but it's a more simple area and won't work for the category section... For my artist entry- I have a table of artist names, their birth and death dates, nationality, affiliations and more.

In the entry table theres just one field that i call CreatorEntry which is a list of artist names that is pulled from an index from the table. When you select an artist, the creatorID (lookup) fills in and then the rest of the fields (dates etc) fill in - but are not stored there. Then when the database is closed, the creatorentry field is emptied. So all that is stored is the one ID

This is what I mean by the space saving comment- It takes up much less space to store one id than all that other information that is in the table- Isn't that the whole reason for using tables? You only have to store data once.

In my example of the Guggenheim- there are probably about 3000 images of museums. So instead of storing 3 fields worth of data for each of these- I should be able to just store 1 ID for each but still view the data I need from the table.

Sorry I don't know my terminology so well and for going on and on!

thanks