I may be going about this in the wrong way...

[Swift_74d]

I'm still a little narrow minded when it comes to access but hopefully someone here can either help guide me to solving my problem, or comming up with a smarter way to design the database to avoid the problem.

Currently i have two tables in my database. The main table contains basic information on a subject (name, address, phone number, etc) and an id field that is specific to that subject. There are about 40 fields and growing in this table as this database is a consolidation of a multitude of unessecary excel spreadsheets. The second table is a listing of payments made in regards to that subject. To enter a payment you need to enter the subjects id and then amount (amoung a few other things). I seperated the two tables because it is very common to have multiple payments for each subject.

Now here comes the problem. What i originally would of liked to do is have a field in the original table that would look for all instances of the id field associated to that entry in the payment table and store the sum. From what i could find, i gather that having calculated fields in tables is a bad idea so what would you suggest? I guess i could live with just having it represented on form and calc'd via query's for reports, but without a trusty =sumif() i'm kina lost on how to even attempt to write the expression.

Any help would be appriciated.

 

[culcuhain]

What about writing a make table query and store the output (by unique ID and total payments) in a new table. Then run a select query to join them for reporting purposes. I am not sure what you want to do with the final out, that is why I am suggest this way. good luck!!

 

[Swift_74d]

For the most part reports will be made based on the date the subject was entered into the data based, or on the area the subject is from so it will be a listing of several subjects each report. But the total amount of payments usually has to go along with them.