Linking Revenue and Expenses tables difficulty

[Penpoint]

Hi,

I have three tables which all have financial data stored inside them

Expenses Table
- Transaction Cost
- Service Provider || Row Source = SELECT [Service Providers].[Service Provider] FROM [Service Providers];
- Transaction Date

Revenue Table
- Gross Pay
- Company Client || Row Source = SELECT [Client Details].[Company Name] FROM [Client Details];
- Invoice Date

Lenders
- Lender || Row Source = SELECT [Service Providers].[Service Provider] FROM [Service Providers];
- Loan Date
- Amount

The obvious common link between these three tables would be to use [Client Details].[Client Name] or my name as the parent of the three objects, since the accounts all belong to one person/company.

The problem with this approach is that with thousands of records it becomes very repetitive.

Currently I am trying to create a report which has the "Transaction Cost" and "Gross Pay" in one report, and minus expenses from revenue to make a simple profit and loss statement.

Can anyone help with how to link the three tables so that all three related tables can be added to one report... thanks.

 

[MarkK]

Yeah, I would expect to see a transactions table which might have a field called transaction type. Then the type distinction Revenue or Expense is data in a table, not a whole new table. This vastly simplifies your problem.
Lenders might be called Organizations with a field Called OrgType where one possible value might be lender. Again, you simplify your structures.
Finally, it doesn't make sense that LoanAmount is a dimension of Lender. LoanAmount amount should be a transaction from one account to another.
Hope this helps.

 

[Penpoint]

I kind of agree with you on the OrgType field.

The reason I split them up in the first place was to separate the data entry. It would be a bit of a task to go in now and merge the three tables together.

However, having gone with the three-table setup I already have, is there a way to consolidate/merge/combine these three tables to be exported as one report, and enable the records to stagger above and below each other according to the date?

 

[gemma-the-husky]

try a union query - but it will be non-updatable

or construct summary totals query's and connect those in some way.

The problem with this approach is that with thousands of records it becomes very repetitive.

I don't understand this point - that's what queries sort out for you.