Display Remaining Values

[pfile]

Perhaps this is outlined elsewhere, I looked! honest! But, as I can't seem to phrase my question via a query, I'll ask here.

 

I have two tables

 

"Samples" and "Results"

 

Soil samples are entered when received and identified by a unique "SampleCode" and are and sent out for testing, when results come back they are entered into the Results table.

 

To do this, a new record is created and a popup list is used to select an existing "SampleCode" from the Samples table, this is used to propagate other fields with customer data in results.

 

What I would like is for the value list in the Results layout to only show those SampleCodes that have yet to be selected, essentially narrowing the list to those samples that have yet to be received and entered.

 

To try to visualize this I have created 2 value lists and 2 global fields that contain and show all the samplecodes from the Samples table and the samplecodes that have been used from the Results table.

 

Now I want to say something like "subtract list A from list B and put the non-matching items in list C". Almost like I want to create a Value list where I am showing values NOT related to..

 

Another example of this might be something where you can select 3 favors from a value list and as you move from one popup field to the next only the remaining values are listed, so you can't select the same value for every choice.

 

Thanks!

[Weetbicks]

I started writing out the answer, but decided the best way to illustrate the solution is to create a small sample file for you, which I have attached.

 

Its fairly basic, theres 2 tables, Samples & Results.

 

Samples are straightforward, a name and sample code.

Results have a result field & samplecode.

 

Check the relationship graph. Under results I have 2 table occurrences related to it. One is to obtain a list of all samplecodes which have been used on a result record, the other is for the value list.

 

So the main components are these 2 relationships (look @ the relationship), and a calc on results which contains all the used sample codes.

 

Have a look thru it and I'm sure you'll figure out how it works, if not, let me know and Ill try to explain it further.

[pfile]

Thanks for your help

 

Could you send that File to my personal address at "upperleftdesign @ mac.com"

 

Not sure I can swing the fee here quite yet, need a job first

 

thanks

 

Paul