*Please Help w/ End of Day Report*

[hondacur]

My Transactions table consists of the following fields:

-TransID (auto number)

-TransDate (creation date)

-TransType (Income or Expense)

-PayDate (entered when Transactions is paid)

-PayType (also entered when paid - There are different layout for Income and Expense Trans but are on the same table... the PayTypes are different for each type of Trans)

-PeoplePlacesID (related to PeoplePlaces table)

-VIN (related to Vehicle)

-Subtotal (Unstored calc. of TransDetails::ExtPrice)

-Discount (Unstored calc.)

-Shipping

-Tax (Unstored calc.)

-Total (Unstored calc. of Subtotal,Discount,Shippin g,Tax)

So, I am trying to create a report that will show my 'End of Day'. I would like to go to a Layout, enter the date, and retrieve the following information:

-Total Income Transactions

-Total Expense Transactions

-Profit/Loss

-Totals for each PayType (Visa/MasterCard/Cash/etc )

-Total Credit Card Batch (Sum of above-if Credit Card)

-Total Bank Deposit (Sum of cash/checks)

I thought I needed some Summary fields in the Tranactions Table but I am not really sure. Another thought that I had was to make a separate Table for End of Day... but doesn't that go against the principles of a database(storing infomation that is already stored and could just be calculated on the fly)

Am I just really off track or what?

If you need more information I will be glad to provide it.

Thank you in advance!

Edited November 18, 2007 by Guest