Using Access 2007, I need a report that serves as a ledger. The database tracks a bank account used to pay for projects. The account receives deposits, and I write checks against the account to pay for the projects.
The account has a one-to-many relationship with the deposits (the credits of the ledger), so I have a table (tbl_Deposits) listing the deposits for the account.
The account’s relationship with the checks is less straightforward. In our office, checks are tracked by the projects they fund, and the checks and projects have a many-to-many relationship. So, I use a form/subform combination to enter checks and link them to their corresponding project (projects on the main form, checks in the subform). Projects and checks are stored in separate tables but connect through a juncture table. In this way, checks are linked back to the account.
The problem is creating a single clean report tracking the balance. At present, I have a main report/subreport structure. The main report displays the deposits, and the subreport lists the checks and their related projects. I use the difference of both totals to obtain the account balance.
I don’t like my current report. It seems like “forcing” a solution. The problem probably lies with the awkward relationship structure of the tables. I would appreciate any help in determining whether this is the best way to structure the relationships and, ultimately, the ledger report. I am happy to provide more details if necessary as I realize this is confusing.
Thank you!
The account has a one-to-many relationship with the deposits (the credits of the ledger), so I have a table (tbl_Deposits) listing the deposits for the account.
The account’s relationship with the checks is less straightforward. In our office, checks are tracked by the projects they fund, and the checks and projects have a many-to-many relationship. So, I use a form/subform combination to enter checks and link them to their corresponding project (projects on the main form, checks in the subform). Projects and checks are stored in separate tables but connect through a juncture table. In this way, checks are linked back to the account.
The problem is creating a single clean report tracking the balance. At present, I have a main report/subreport structure. The main report displays the deposits, and the subreport lists the checks and their related projects. I use the difference of both totals to obtain the account balance.
I don’t like my current report. It seems like “forcing” a solution. The problem probably lies with the awkward relationship structure of the tables. I would appreciate any help in determining whether this is the best way to structure the relationships and, ultimately, the ledger report. I am happy to provide more details if necessary as I realize this is confusing.
Thank you!
