Hmmm...?? Not sure I'm following your design.
When you have tables that are in a relationship, you should use keys (primary key and Foreign key) for those relationships. Also, if a Transaction relates to an account, and Hopefully that is your design intention.
When I added a field for AcctID_FK in the [Cash/Credit Transactions] table and tried to update the field with the appropriate FK value
Code:
UPDATE [Cash/Credit Transactions] INNER JOIN Accounts
ON [Cash/Credit Transactions].[Account Name] = Accounts.[Account Name]
SET [Cash/Credit Transactions].AccTID_FK = [Accounts]![ID];
Only a few records were updated.
I then tried this query to see which Accounts were in [Cash/Credit Transactions] that were NOT in Accounts.
Code:
SELECT [Cash/Credit Transactions].[Account Name]
, [Cash/Credit Transactions].Amount
, [Cash/Credit Transactions].Trandate
FROM [Cash/Credit Transactions] LEFT JOIN Accounts
ON [Cash/Credit Transactions].[Account Name] = Accounts.[Account Name];
Then all Accounts should exist in the Accounts table. I see several transactions(1372) that are related to Accounts that do NOT exist in the Accounts table??? How were they going to be applied?
It seems to me your tables are related
1 Account may have 0,1 or Many Transactions
I think your design needs work.
You need to start with a detailed description of the business rules involved.
When I try to open the form Transactions, I get an immediate error that table Transactions does not exist???
Perhaps I am not understanding your set up. Please refine as necessary.