Relational database problem 1

[tonyA1DL]

we have one database for "jobs" (allocated and to be allocated to suppliers) and another database for suppliers.

in the main view of the "jobs" database, we select the "supplier" who will be allocated the job

within the suppliers database there is a "status" field and we want to restrict the "jobs" database to only be able to view and allocated work to "current" status suppliers.

at the moment, you can see all suppliers and allocate jobs to any supplier, irrespective of their status.

Before making any change I need to be mindful that the database stores several years history therefore needs to be able to display suppliers whose status may no longer be current, but was current, say at the time of their last "job". I could add "current status from/to" field sto the suppliers database if that would help

any ideas?

 

[paulbent]

Create a variable field, option Text and give it a default value of "Current" or whatever the matching entry in the status field is.

In the drop down list properties, set a filter using Suppliers.Status where it matches the value in the variable field.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk