create relational database

I would try this: make one table for all invoices

tblInvoices
-----------
InvoiceID (PK, autonumber)
InvoiceNumber (if you have some invoice naming rules)
InvoiceType (Advance/Final)
ContractID (FK from tblContracts, null if not refering to a contract) DuplicatesOK
AddendumID (or is it AddendumDetailID) (FK from tblAddenda or AddendaDetails, null if not refering to an Addendum) DuplicatesOK
other invoice data

and then kick out all references to invoices from all the other tables. As to only one invoice of each type for each "parent object" I would handle that in forms (in some code).
 
i will give it a try

any idea where i put the if statement (null if not refering to a contract), i was thinking of adding NULL as the default value for both fields

and making a left join when i make the relation between the tables
 
Fields in a new record contain NULL unless you put something into them.
 
Fields in a new record contain NULL unless you put something into them.

okey, well i don't know how to write the correct expression for this (null if not refering to a contract) and don't know where to put it

i tried a validation rule, but i get an error saying that it can't check other columns
 
take a look at the db now and let me know what you think

i can't have one tables for both types of invoices because i need to make a report which shows all info linked to a contract (advance and final invoices info)
 

Attachments

  • db2.JPG
    db2.JPG
    70 KB · Views: 76
What do you want to write an expression for ? Or where? All the fields are null UNLESS you put something into them. So if the invoice is for a contract then don't put addendumID in it when you create it, if it is for an addendum, don't put ContractID in it. Or explain where you want to use the "if ...."
 
#25 Then decide what you want and make two separate tables.

btw: Your jpeg is not clickable
 
i was just following your post, and i thought i had to write something specific

i changed the db now by adding extra FK and now i think it will work

thanks for the help
 
Now the jpeg worked.

You cannot create relations like you have because we are then back to square one. I think you need to do some reading on relations. You have created a closed loop where along one branch you insist on 1-to-many (contracts->addenudms-->addendum details->invoices and the other brach 1-to-1 (contracts->invoices) and that is impossible.
 

Users who are viewing this thread

Back
Top Bottom