Portals calculating $ from related tables

[im2bethe1]

Hello Any and All,

This is my first post so I am new to all of this. I am sure my problem isn't unique and if it has been answered before, please direct me to the post.

I am designing a database solution to track all contracts and spending for my company. I have one main user interface "Contacts" table which is related to several other files (grouping related material). The problem I am having is with the "Invoice" table. Invoices come in from some vendors very often, up to 10 to 12 in a one month period, each with different invoice numbers and dates. I need to not only be able to calculate the Year-to-date (YTD) spending per contract, but to be able to pick a month and have that months spending appear in a field on the User's layout. My files are related by the "serial#" primary key and when I create calculation fields to total, it only counts the first portal row, intead of all related to that serial#. If I use a "Sum" field, it total all records in the table.

I need a solution to be able to view the "By-Month" spending per contact record as well as YTD. If anyone has any idea's please let me know. I am only a little better than a novice in scripting, but am really trying to learn.

[zincb]

It may be this darned decaf, but I am confused, particularly by the similar Contact & Contract labels...

Is a serial number assigned to each Vendor or to each Contract, or to each Contact? Is a Contact the same as a Contact? Or does each Vendor represent a Contract? Or both? Are you able to attach files?

[im2bethe1]

Ok, I am sorry, I know I can be confusing sometimes...

The serial number is specific to each contract and also matches the vendor. So if someone does a search by the serial, they will pull up information specific to that contract and vendor. I have separate tables to handle the various functions of my Contracts database (i.e. Projections - Handles what they expect the contract to cost per fiscal year and per month, Encumbrances - Handles what yearly projected amounts has been approved to pay by our accounting office, Invoices - Handles what invoices where received to our area which we approved for our accounting office to pay, this is one of my problems). Since we can receive multiple invoices per month from ONE vendor (Some of them have terrible billing people), we need to be able to view the contract monthly spending. I want to do a find using the "Month" command and sort by the "Invoice Paid Date" and have it do a "Sum" for total month expenses. I am using portals on the Contracts Table to view all of the data in the related tables.

I do not know how to attach my file since I am still learning this site, plus there are 2 of them (which pertain to this problem - Contacts & Invoices) and I don't know if I am allowed to post that many.

Am I still Confusing, let me know.....