Design question linking table to query

[tom]

I have a table, let's call it an adjustment table with three
fields

invoice Debit Credit

And then I have another more complicated query with
an invoice field and various other calculated fields based
on several tables. lets call this query complex.

I want to design an adjustment form, but if I create a form
with the adjustment table as the main form and the complex query as the
child form and link on invoice,
it doesnt seem to work.

Also if I create a select query with the adjustment table and the complex
query again linking (or rather joining) invoice I can no longer add
adjustments to it.

What is the best solution to this problem?

 

[JK]

Tom,

If your db is to do with Accounting, my *strong* recommendation is to use
*one* table for *all* transactions, with all the required info (payee,
dates, description, amounts etc) *and* a field for transaction Type. In this
way you are always looking in one table for all transactions types, using
separate form for each transaction type to update the transaction type.

I would create a table for transaction types, storing information about the
particular type.

Example:
tblTrans
---------
tran_ID (key)
TranTypeID (foreign key)
Customer_ID (ditto)
TranDate
TranRef
TranDesc
TranAmount (or TranDebit & TranCrdit)
etc

TblTranTypes
-----------
TranType_ID (Key)
TranTypeDesc (text -eg Invoice, Credit Note, Adjustment, Receipt etc)
TranDebit (Boolean) whether to invert the sign (positive to negative and
vice-versa)
LastRefNum (tex or number)
some more

Regards/JK