Updating Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there
I want to be able update a yes/no field which has a unique ID attached ,when a field in a linked table which can have numerous records with that ID because using date as joint primary key with ID becomes 0 , but I only want the yes/no to be altered only when all the records with that ID become 0 not just when 1 becomes 0.
Thanks
 
Tass

I'm pretty sure I didn't follow your explanation. Are you asking for a way
to update a Yes/No field in a table, based on the value in another table?

If so, I would start by building a query that found all the records matching
the criteria that you've set for that (second) table. When that query is
working, you could use the IDs from that query to update the Yes/No field in
your first table, for matching ID.
 
You could use the DCount Function in a query to do this

UPDATE MainTable
Set FieldYN = DCount("*","TheOtherTable","IDField=" & Maintable.IdField & " AND
SomeField=0") <> 0
WHERE MainTable.IdField = 12345

You could also do this with two queries for the entire table. Sample to set to True.

UPDATE MainTable
Set FieldYN = True
WHERE 0 =(SELECT Count(*)
FROM TheOtherTable
WHERE TheOtherTable.ForeignKeyID = MainTable.ID
And TheOtherTable.SomeField = 0)
 
Back
Top