Help! with a checkbox problem

[hardrock]

hey all, i have a supplier table (Supplier), that has a yes/no check box column field depending if they are active or not. About 200 suppliers in all, many of which not active.

I want to be able to automatically set the active field to True within the supplier table depending on the condition that records exist in my delivery schedule table (Schedule)

SO for instance if a supplier name "Power" exist in my delivery schedule, then i want supplier "Power" in the supplier table to be checked i.e True. I guess some form of dlookup is needed here, but not im not sure where to start. Any help appreciated!

 

[Moniker]

That's a simple update query. Hopefully you have a supplier ID of some sort and are not relying on supplier names. Regardless, the structure would look something like this:

UPDATE SupplierTable 
LEFT JOIN ScheduleTable ON SupplierTable.SupplierNameOrID = ScheduleTable.SupplierNameOrID 
SET SupplierTable.ActiveFlagFieldName = -1
;

In the query designer, just drag the supplier table and the schedule tables in to the designer, drag the field name for SupplierNameOrID from ScheduleTable on to the matching field in SupplierTable, double-click the join, and change it's type to two (that's a left join).

Change the query type to be an Update query, and then drag the Active field from the SupplierTable down into the QBE grid and set the "UpdateTo" criteria to be True.

 

[CraigDolphin]

I would look into using an update query to update the 'active' field in your supplier table to -1 (yes) if the supplier name/id appears in your delivery table, and to 0 (no) if it does not.

Search on update queries, joins, iif statements and the isnull function

[edit]Sorry, Moniker. You posted while I was typing.