Communicating data between tables.

[jasdem]

I have 2 tables, one that has transactions and one that has individuals, for each transaction I have a Yes/No field for whether the transaction qualifies as a red flag. On the individuals table I also have a yes/no field that indicates any transaction is red flagged. Right now they are not connected. Is there a way to have the first transaction field tell the individual field that an transaction was flagged? Are there any concerns since it is a one to many relation? Should the look up be a macro, in the form properties, or table look-up?


Thanks

[June7]

What would you lookup - what would be the criteria?

There must be a connection somewhere that will link to the individuals.

Is the Transactions table dependent on a parent Orders table? Is the individual's ID in the Orders table?

[jasdem]

The individuals and transactions have IDs. The Transaction table has the individuals id attached. I want the individual's field to be checked (yes) when any transaction related to the individual is checked (yes).

[June7]

Then they are connected.

Why? Then won't the individuals field always be checked?

The check field in Individuals seems superfluous. Whether or not an individual has transactions can always be determined with query.

Don't understand what you are doing, nor the data structure.

[jasdem]

The fields are not connected though. But, yes the tables are. The tables are used to drive different forms. I agree that the check field in the individual level is not needed, but I want to call on the transaction information for the individual level form, then I should change the record source to a query with the check transaction field and all the individual fields instead of having the table alone driving it?

[jasdem]

The other problem is that database was inherited and I am unsure where the individual's check box is used and not used. I am reluctant to do a way with it if it will mess with our reporting. I agree that it is redundant, but can the individual field look to the transactions field in this way?

[June7]

Behind form for Individuals, VBA code using DLookup maybe like:

If Not IsNull(DLookup("IndID", "Transactions", "checkfield=True AND IndID=" & [IndID)) Then
Me.checkfield = True
End If

The real trick is figuring out what event to put code in.

An expression in textbox could show the same info.
=IIf(Not IsNull(DLookup("IndID", "Transactions", "checkfield=True AND IndID=" & [IndID)), True, False)