Viewing problem

[Sam Summers]

Hi there,

I am trying to view all records that pertain to a particular Cert No.

The problem is that there is a single table (Equipment) but four different types of equipment that are contained in this table. On entering new equipment the user selects the type which is acheived by using a Lookup table for each type (CradleNet, Access, Rigging, NDT). Now I retrieve the data using a continuous form, however I can't seem to pull up all the records relating to the selected Cert No because the Item Description uses the Lookup table to insert the appropriate Name.

Can I have four Lookup tables in one query? Or is there another way of doing this?

Thank you in advance.

 

[llkhoutx]

I suppose you could have a large number of lookups in your query. However, if you had a large number of rows, your query would be glacially slow.

One or more public functions would probably be faster that multiple lookups.

 

[Pat Hartman]

I guess I don't understand why you have 4 separate lookup tables rather than 1. Doesn't the result end up in the same column regardless of which table provides the value? Or, have you made 4 columns only one of which is filled in?

 

[Sam Summers]

Im not too sure about how to go about the Public Function way, but I'll look into it.

Its an awkward one.

The reason I have four Lookup tables is so that the users only see the names of the particular group or type of equipment they are selecting i.e. Electrical - Drill ; Rigging - Sling
Otherwise when the user wanted to enter a new Drill for example they would see in the combobox list the sling of Rigging type which would be no use.

I could use a workaround which would be for the users to select the type of equipment they wish to view the Cert No for and then create a query that just uses the 'Equipment' Table and the appropriate Lookup Table?

Thanks for your time and help guys

 

[Pat Hartman]

If you want to shorten the combo list, use two combos. The first chooses from 4 items, and the second chooses the final value. Look up cascading combo for lots of posts on the topic.

 

[Sam Summers]

Thanks again Pat

I'll look into that.