Query for Profit/Loss

[reddevil1]

My simplified test database has the following:-

INCOME TABLE
IncomeID
AmountMoneyReceived
DateMoneyReceived

EXPENDITURE TABLE
ExpenditureID
AmountMoneySpent
DateMoneySpent


I need a Query to produce a Profit/Loss Report which will look something like this:-

ID Amount Recvd Amount Spent Date
1 10,000 1/1/12
2 12,000 15/1/12
3 10,000 22/1/12
1 5,000 23/1/12
4 15,000 24/1/12
2 20,000 30/1/12


Please could someone advise the easiest way to do this?

Thanks in advance.

 

[plog]

The easiest way to do this is with properly structured tables. Instead of an expenditure and income table, you should have a Transactions table that is structured like this:

TransactionID, TransactionType, TransactionAmount, TransactionDate
1, Income, 500, 1/1/12
2, Income, 300, 2/1/12
3, Expenditure, 200, 1/1/12

If you put them all in the same table, generating what you want becames trivial.

 

[reddevil1]

Thanks, plog.

It is a difficult decision whether to have my Income and Expenditure in 1 or 2 Tables.

In my case, if it is Income, then the Form needs to have about 15 fields.
If it is Expenditure then the Form needs to have 15 different fields.
It does not seem correct to put 30 different fields on ONE form?
That is why I chose 2 different Tables and use 2 different forms?

 

[plog]

Forget about forms, let's talk tables. Can you give me all the field names and some sample data of all the fields in each of those 2 tables?

 

[reddevil1]

Thanks for helping, plog. It is appreciated.

My Income Form has:-

1. Income ID

2. Date Money Received
3. Amount Money Received
4. Transaction Type 1 (Rental Income, Deposit, Annual Fee etc)
5. Commission Percentage
6. Commission Amount (Auto calculated)
7. Money Received From (Renter, Owner etc)
8. Money Received By (Employee)
9. Method of Payment (cheque, cash, credit card)
10. Cheque Number
11. Cheque Date

12. For which Contract

13. Date Money To Bank
14. Money Deposited By (Employee)


MyExpendituire Form has:-

1. ExpenditureID

INCOMING MONEY
2. Transaction Type 2 (Add to Maintenance Float, Transfer From Current Balance etc)
3. Date Money Received
4. Amount Money Received
5. Money Received From (Owner, Renter, Other) to Pay for below costs Money Received By (name of employee)
6. Method of Receipt (cash, cheque, credit card etc)
7. Cheque No.
8. Cheque Date

OUTGOING MONEY
9. Transaction Type 3 (Repair Work, Phone, Electricity etc)
10. Date Money Out
11. Money Paid To (phone company, work contractor etc)
12. Money Out By (name of employee)
13. Method of payment (cash, cheque, credit card etc)
14. Cheque No.
15. Cheque Date

WORK ORDER DETAILS (if payment is to a Contractor)
16. WorkContractorID
17. Description of Work
18. Date Work Completed

19. General Comments
20. Search Box

 

[plog]

My Income Form has:-

Again, I don't care about your forms. I want to know about the fields in your Income and Expenditure tables. So, let me repeat my request:

Can you give me all the field names and some sample data of all the fields in each of those 2 tables?

 

[reddevil1]

Sorry for the misunderstanding.

The fields in my Income Table are exactly the same as my fields on my Income Form. Same goes for the Expenditure Table and Expenditure Form.

I have given examples of what kind of data will go in these. Are you looking for upload of some sample database also?

 

[plog]

Last chance.

Can you give me all the field names and some sample data of all the fields in each of those 2 tables?

I will even give you my prefered format:

IncomeTable
IncomeID, AmountMoneyReceived, DateMoneyReceived,...
1, 500, 1/1/12
2, 300, 2/1/12
.
.
.

You can upload your database if your prefer.

 

[Alansidman]

Here is a data model that might help you

http://www.databaseanswers.org/data_models/accounting_systems/index.htm

It follows the direction that Plog is taking you. Help us to help you by answering requests with what is asked for.

Alan

 

[reddevil1]

Sorry guys, hopefully I have answered you better this time.
I thought it was best to upload my database with some sample data.

My Income Table is actually called tblRentalIncome
My Expenditure Table is actually called tblPropertyManagementExpenditure

Thanks in advance for your suggestions.

 

[plog]

You've got some structural issues that you need to sort out, and when you do, it looks like you will be able to put all your transactions into one table. Here's some things I see:

1. In tblRentalIncome you have PropertyID, ContractID, OwnerID, and RenterID. I'm fairly certain 3 of those fields are unnecessary, ContractID being the only ID you need to capture. This is because tblContract already establishes a relationship among Property, Owner and Renter, which means putting this data in tblRentalIncome is redundant.

2. There's also redundancy somewhere concerning PropertyID and OwnerID. You have this relationship established in 2 places--tblContract and tblProperty. It only needs to be established in one place, which means either tblContract needs to lose OwnerID or tblProperty needs to lose it. You shouldn't establish the same relationships in multiple places.


3. I bet Commission Percent In tblRentalIncome is also redundant for the same reason all those ID's are--because this data is already captured in tblContracts. Is that true? If so, this field needs to be removed as well.

4. Is the field Commission in tblRentalIncome a function of the Commission Percent and AmountReceived? If so, this field needs to go as well, because you don't store calculated values--you calculate them when you need them.

5. What's happening in tblPropertyManagementExpenditure? You've got fields for money coming in and going out. Expenditure usually just means money out. What exactly does this table tell you?

6. tblPropertyManagementExpenditure has a foreign key to just PropertyID, which means it doesn't belong to any contract or renter--is that correct? Is the data in this table not related to any contract or renter?

 

[reddevil1]

Plog, thanks very much for your comments and suggestions. They have been very useful indeed to set me on the correct path.

It seems to be easier now that it is possible to have all the Transactions in one Table. I guess that makes it easier to create Queries and Forms if the data is coming from only one Table?

Hopefully, I have understood everything you have mentioned. and I have revised my database as follows:-

1. In tblRentalIncome you have PropertyID, ContractID, OwnerID, and RenterID. I'm fairly certain 3 of those fields are unnecessary, ContractID being the only ID you need to capture. This is because tblContract already establishes a relationship among Property, Owner and Renter, which means putting this data in tblRentalIncome is redundant.

1. Yes, ContractID is the most important. I had included the
others (PropertyID, OwnerID and RenterID) because I wanted to make a Relationship between the four Tables (tblProperty, tblOwner, tblRenter and tblRentalIncome). However, although I have deleted the three IDs, I am now unsure how to keep those Relationships with the tblContract?

2. There's also redundancy somewhere concerning PropertyID and OwnerID. You have this relationship established in 2 places--tblContract and tblProperty. It only needs to be established in one place, which means either tblContract needs to lose OwnerID or tblProperty needs to lose it. You shouldn't establish the same relationships in multiple places.

2. Yes, it is not absolutely necessary for tblContract to have
OwnerID. I have now deleted it.

3. I bet Commission Percent In tblRentalIncome is also redundant for the same reason all those ID's are--because this data is already captured in tblContracts. Is that true? If so, this field needs to be removed as well.

3. Yes, Commission Percent is a part of tblContract. However,
some Income (such as a Deposit, money to pay for repairs etc) will not be subject to any Commission, but Rental income will be subject to the Commission Percent as stated in tblContract. Therefore, I think the Commission Percent should remain because the Commission Percent can change depending on (the TransactionType of) the money received.

4. Is the field Commission in tblRentalIncome a function of the Commission Percent and AmountReceived? If so, this field needs to go as well, because you don't store calculated values--you calculate them when you need them.

4. Yes, the field Commission is a calculated field. I have now deleted it.

5. What's happening in tblPropertyManagementExpenditure? You've got fields for money coming in and going out. Expenditure usually just means money out. What exactly does this table tell you?

5. Yes, tblPropertyManagementExpenditure has fields incoming and
outgoing money. Hopefully, with the new structure, this error will be eliminated.

6. tblPropertyManagementExpenditure has a foreign key to just PropertyID, which means it doesn't belong to any contract or renter--is that correct? Is the data in this table not related to any contract or renter?

6. Having thought more about this, the tblPropertyManagementExpenditure should be related to a ContractID (eg. A contract between Owner and the rental company). The Renter is not required to pay for repairs/maintenance.

I have attached a revised database which I hope you would be able to review and comment on. Some of the fields were duplicated in both Tables and so I deleted one of them.

Due to my lack of experience, I now have a fear of how to create an Input Form which will be simple enough for Users to input both the Income and the Expenditure.

 

[plog]

Does tblFinance now contain all your Income and Expenditure data? Does it have all the fields you need? If so, it looks good. If not, let's find a home for the fields you need.

As for an Input Forms, you don't have to have just one. If you feels it makes sense to create one form for Income and another for Expenditures, that is fine--they key to any database is a proper structure.

 

[reddevil1]

Yes. I think tblFinance contains all my Income and Expenditure data. I cannot think of any other Fields that are required, that are not already linked/included in other Tables.

My only question is whether the WorkOrderID needs to be included on tblFinance? My intention was to create a separate WorkOrder Table with details of all works (eg. maintenance work, pay electricity bill, pay property tax and any other work). The WorkOrderID is linked to a ContractID (between Owner and rental company). But, the way I have been thinking, is that when inputting data on the Expenditure Form, it would require the User to make a ComboBox selection for ContractID and (if WorkOrder was selected as a Transaction Type) this would require another Combo Box selection to choose the related WorkOrderID. Maybe it is just a small matter, but I was trying to think of a way where only one selection was required?

I am not sure if I am trying to run too quickly here? But I assumed the next step would be to create a basic Income Form and a basic Expenditure Form. I couldn’t think of a suitable way to have the Income and Expenditure on one Form only?

So I have created both basic Forms on the attached database. I have included tblTransactionType which is linked to the Combo Box on the two new Forms. It isn’t perfect and still needs a better Filter to only show the Income records on the Income Form, and only show the Expenditure records on the Expenditure Form.

 

[plog]

How can you tell by looking at tblFinance if a record contains Income or Expenditure data?

 

[reddevil1]

plog, i really do appreciate your time reviewing and commenting on my database.

I have updated tblFinance to include:-

1. TransactionTypeID (not sure if this field is required?)
2. TransactionType1 (this seems to work fine, especially with the ComboBox on frmIncome)
3. IncomeOrExpenditure (this gives an easy view of whether it is Income or Expenditure) but is this field required?

 

[plog]

In tblFinance the fields TransactionType1 and NoIncomeOrExpenditure are not needed, since you have that data in tblTransactionType.

 

[reddevil1]

plog,
I can see your message much clearer now (about how the 'base' table should be trimmed as much as possible). thanks.

I think that Phase 1 of the revised database (attached) might be completed now? I wondered if it is time to move onto Phase 2?

Are there any thoughts on that? Have I missed anything?

 

[plog]

Actually, it looks like what you initially came here for still isn't properly set up. You still have 2 amount fields, and only need one. Instead of AmountReceived and AmountOut, you simply need an Amount field. The TransactionType field will let you know which direction that money is headed.

Once you get the amount into 1 field it becomes really simple to get balances.

Also, it seems you might be able to further reduce your fields because a lot of them have In/Out counterparts.

 

[reddevil1]

Plog – thanks again for your direction. I think my database is going to be awesomely efficient J

I have updated the database.

However, my logical mind is NOT able to understand why it is considered 'easier' to combine two Fields together (eg. AmountIn and AmountOut)? With my limited Access knowledge, it seems to be much easier and clearer (for the non-professional programmer) to use two distinctive Fields in a Query/Report(?) rather than having to write code for every Field/Query to say (IF FieldA = Income, then Amount = Positive, but IF FieldA = Expenditure, then Amount = Negative). I have not been able to compute why this is easier?

However, even my logical mind DOES understand that reducing the number of Fields in a Table will reduce the size of the database and therefore it should run/process quicker. But I am not aware of any other benefits?

PS – DateMoneyToBank and MoneyDepositedBy are necessary fields on frmIncome to try and stop fraud.