Linking Revenue and Expenses tables to stagger records
I have three tables which all have financial data stored inside them
- Transaction Cost
- Service Provider || Row Source = SELECT [Service Providers].[Service Provider] FROM [Service Providers];
- Transaction Date
- Gross Pay
- Company Client || Row Source = SELECT [Client Details].[Company Name] FROM [Client Details];
- Invoice Date
- Lender || Row Source = SELECT [Service Providers].[Service Provider] FROM [Service Providers];
- Loan Date
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 minuses 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.