You can create two tables one is transaction table and one is summary table. Both scripts are here:
Table 1
USE [DBNAME]
GO
/****** Object: Table [dbo].[tbl_transactions] Script Date: 02/13/2014 19:21:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_transactions](
[InvoiceID] [nchar](10) NULL,
[act_date] [date] NULL,
[ItemID] [int] NULL,
[AccountID] [int] NULL,
[ItemAmount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
Table 2
USE [DBNAME]
GO
/****** Object: Table [dbo].[tbl_summary] Script Date: 02/13/2014 19:23:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_summary](
[AccountID] [int] NULL,
[InvoiceID] [int] NULL,
[Amount_Received] [decimal](18, 0) NULL,
[Amount_Balance] [decimal](18, 0) NULL,
[Invoice_paid] [bit] NULL
) ON [PRIMARY]
GO
At the time of saving data you save summary from your transaction tables
Steps:
1. First save data in your Table 1 (Transaction Table)
2. use this query in your select statement (Remember in your selection criteria you have not used group by clause and act_date have multiple values so use group by clause)
select SUM(itemamount) as total, InvoiceID from tbl_transactions where AccountID =1 group by InvoiceID
3. now insert above data in summary table.
4. Now you make a form for receiving payments where you input AccountID, InvoiceID and Received Amount.
5. For next visit when you enter AccountID select data from summary table according your accountID and invoice_paid.
for Save update functions please read my article
http://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=8171172[
^]