Solved combo with 2 control sources

[GPGeorge]

yeah. Actually, I've been an accountant for years. I get all this. The only reason I mentioned Quicken is because they list the accounts with the categories and split the money so. Accounts are a TRANSFER type and enclosed in brackets and Categories are either EXPENSE or INCOME type. I'll move on. Thanks for all your input.

That is where the confusion came in then. I use Quicken, but the notion that an Account is a Transfer type is where I am not able to follow your analysis.

Can you provide screen shots to illustrate that in Quicken? This is what I see:

None of those is identified as a Transfer Account. What am I missing?

 

[gemma-the-husky]

yeah. Actually, I've been an accountant for years. I get all this. The only reason I mentioned Quicken is because they list the accounts with the categories and split the money so. Accounts are a TRANSFER type and enclosed in brackets and Categories are either EXPENSE or INCOME type. I'll move on. Thanks for all your input.

Personally, I would classify accounts as either Balance Sheet or Profit and Loss, the difference being that P&L accounts clear to zero at the end of each year.

Expense/income is a poor category type, I would have said. You might need an account number strategy to group income accounts into a certain section of the accounts, and expense accounts into a different section, but in general a debit balance is an expense and a credit balance is income (negative expense) in terms of the P&L accounts.

It works like this if you regard your salary as income, and everything else pretty much as expenses. Your profit is the excess of income over expenditure.

I think the big problem is trying to provide a way for non accountants to prepare accounts in a simple way. It is simple if you know what you doing, but it's not simple until you understand double entry, and you are happy with the debit side being nearest the window. (I hope you got that the last bit was a joke)

 

[Gasman]

That is where the confusion came in then. I use Quicken, but the notion that an Account is a Transfer type is where I am not able to follow your analysis.

Can you provide screen shots to illustrate that in Quicken? This is what I see:

View attachment 113794

None of those is identified as a Transfer Account. What am I missing?

George,
I think Clara is talking about what is shown in the picture below?
Here I have transferred funds from an account to 3 others that I hold.
The category list Motor, Home, Groceries etc, also includes the account list within [ & ] as you can see.

This is on Quicken XG 2004

 

[GPGeorge]

George,
I think Clara is talking about what is shown in the picture below?
Here I have transferred funds from an account to 3 others that I hold.
The category list Motor, Home, Groceries etc, also includes the account list within [ & ] as you can see.

This is on Quicken XG 2004
View attachment 113797

Thank you. That makes sense. My turn to learn.

I think my confusion came from saying that a transfer between accounts is the same thing as an expense or income category. They're not the same thing, but Quicken uses the same entry line for both options.

I have used Quicken for years and never thought about that contradiction. I just took it for granted that behind the scenes Quicken was sorting it all out for me.

But that means the solution to putting both accounts and categories in a combo box in Access could be as simple as a union query for Accounts and Categories, using a convention like the square brackets for Accounts and using the combo boxes after update event to decide how to handle the selected value.

 

[ClaraBarton]

 

[GPGeorge]

View attachment 113798

Yes, now I get what Quicken is doing. It's humbling to realize I used Quicken for so long without noticing the importance of this interface design. I guess it shows how well developed and refined third-party software can be. It's not particularly noticeable until you stop and think about what's going on.

Quicken includes both expense categories and inter-account transfers here in the list.

Note that they are all clearly labelled by type for that reason. "Expense" is one type of transaction; "Transfer" is also a type of transaction. That does not mean they are both categories of expense, which is where we went off track. Categories remain a subset of Expenses. Transfers are not the same thing.

In their interface, Quicken comingles Expenses and Transfers for ease of data entry.

That masks, unfortunately, the fact that when you record an expense in an account, that outgoing amount goes, well, outside to a vendor. The money is deducted from your account and reduces your overall balance.

When you record a transfer, that amount has to go into a different account inside the system to maintain the integrity of the action. The money transferred does not reduce your overall balance.

Given that, I can see a combo box which uses a union query of categories of expense and income along with internal inter-account transfers, using the square bracket convention or one of your choosing to differentiate.

The after update event of the combo box would have to decide which rules to apply to the selection. I.e. if it's an expense or income category, deduct it. If it's a transfer out, deduct it, but also add the same amount to the selected account. If it's a transfer in, add it, but also deduct the same amount from the selected account.

My apologies for being so slow to catch on.

 

[ClaraBarton]

ok! on the same page! So... using after update to assign the control Source? and leave it blank in the properties? Will that work?

 

[GPGeorge]

ok! on the same page! So... using after update to assign the control Source? and leave it blank in the properties? Will that work?

Yes, leave the combo box's control source blank because it's going to depend on which type of value the user selects.

I don't think I'd even assign a control source in the VBA. I'd say you need to append the selected value to the table using an append or update query.

If Me.ComboBox.Column(2) = "Transfer" Then 'use column 2 in the combo box to indicate "Account Transfer" or "Expense Category"
' Write code to append or update the Transfer in this account and offset it in the other impacted account.
Else
'Code to append the transaction to this account (either as a debit or credit) and indicate what expense category it is
End If

In other words, I think it would be more better to follow accounting principles for debits and credits, as appropriate than to try to finesse tables.

 

[ClaraBarton]

Thank you! Exactly what I needed!

 

[gemma-the-husky]

View attachment 113798

That's a really interesting screen shot, which makes it quite clear to me that I don't need a tool of this sort. Out of interest can the usage column show value instead of items, or do you just click on a blue number to expand the contents?

A simple monthly spreadsheet is adequate for my own purposes. Occasionally I might have to search a spreadsheet to find something, which would be easier if all similar expenses were linked (ie, in a database) but it's just not critical enough for me to spend the time making a database just to have that analysis available.

 

[Gasman]

That's a really interesting screen shot, which makes it quite clear to me that I don't need a tool of this sort. Out of interest can the usage column show value instead of items, or do you just click on a blue number to expand the contents?

A simple monthly spreadsheet is adequate for my own purposes. Occasionally I might have to search a spreadsheet to find something, which would be easier if all similar expenses were linked (ie, in a database) but it's just not critical enough for me to spend the time making a database just to have that analysis available.

That is why I am still using Quicken 2004.

 

[GPGeorge]

That's a really interesting screen shot, which makes it quite clear to me that I don't need a tool of this sort. Out of interest can the usage column show value instead of items, or do you just click on a blue number to expand the contents?

A simple monthly spreadsheet is adequate for my own purposes. Occasionally I might have to search a spreadsheet to find something, which would be easier if all similar expenses were linked (ie, in a database) but it's just not critical enough for me to spend the time making a database just to have that analysis available.

It's a very powerful tool. It can be set up to connect to banks and investment companies (like Fidelity and Vanguard) and download current account data directly into the appropriate accounts, for example. Whether one would benefit from that conveniences and accuracy or not is an individual choice.

It saves me enough time, and provides enough additional analysis, that I can easily justify the cost. But, yeah, without it a handful of spreadsheets would work.

And no, it would not be worthwhile trying to create an Access solution to replicate it.

 

[Gasman]

My version no longer goes on line. However I found it easier adding the transactions myself than having to categorise them when imported from an online account anyway.
It was sufficient to run the finances as a charity treasurer for 10 years, and now just keeps my personal finances in check.

One cannot buy Quicken here in the UK anymore, and Quicken gave me this version when they abandoned us.

 

[GPGeorge]

My version no longer goes on line. However I found it easier adding the transactions myself than having to categorise them when imported from an online account anyway.
It was sufficient to run the finances as a charity treasurer for 10 years, and now just keeps my personal finances in check.

One cannot buy Quicken here in the UK anymore, and Quicken gave me this version when they abandoned us.

Is there an alternative in the UK?

I guess it feeds my lazy streak. I could do all the data entry crap manually, but then I would miss my afternoon nap.

 

[Gasman]

Probably a few others
MS stopped Money I seem to recall.
I used Quicken for a good few years, and when they left the UK, they gave me the last version possible, which was a nice touch.
It does everything I need it to, so happy to stick with it. The same would be said for Office 2007, except I could not open later dbs'
Fwiw I work a few hours a week on Xero for my old employers. I can import the bank transactions there as well, but still have to categorise them. Much prefer to do that on my own data entry.