0

I have a database with a 'General Ledger', 'Account Types' and 'Chart of Accounts' tables. The layout is as follows:

'Account Types':

AcctTypeID Autonumber Long Integer Not Null Primary Key
TypeName Text(50) Not Null
Description Memo
Credit Yes/No Not Null Default Yes

'Chart of Accounts':

AcctID Long Integer Not Null Primary Key
AcctName Text(30) Not Null
Description Memo
AcctType Long Integer Not Null

'General Ledger':

EntryID Autonumber Long Integer Not Null Primary Key
EntryDate Date/Time Not Null Default #1/1/1900#
EntryType Text(12) Not Null Default "DC"
Description Memo
FromAcct Long Integer Not Null
ToAcct Long Integer Not Null
Taxable Yes/No Not Null Default No

Relationships Between Tables:

 One-to-Many from 'Chart of Accounts'->AcctID to 'General Ledger'->FromAcct
 One-to-Many from 'Chart of Accounts'->AcctID to 'General Ledger'->ToAcct
 One-to-Many from 'Account Types'->TypeID to 'Chart of Accounts'->AcctType

I'm needing to start by fulfilling the following requirements in two different SQL queries:

  1. If ToAcct.AcctType == (Income OR Asset) then add 'General Ledger'.Amount to a TotalRevenue

  2. If ToAcct.AcctType == (Expense OR Liability) then add 'General Ledger'.Amount to TotalExpenses

I've tried these two SQL queries, before I modified my 'Account Types' to include whether they are income/assets or expenses/liabilities:

  1. SELECT SUM(IIF((Credit = TRUE), Amount, 0)) AS RevenueTotal FROM GeneralLedger;

  2. SELECT SUM(IIF((Credit = FALSE), Amount, 0)) AS ExpenseTotal FROM GeneralLedger

These queries worked, but I had the extra checkbox on the General Ledger form for "Credit". In normalizing, I figured it would be better for the account to determine whether it is a debit or a credit account.

After multiple searches on Google and reading I-don't-know-how-many questions and answers, I'm not seeing anything that is similar to what I'm trying to do here.

The output should give me the information needed for creating the Profit & Loss, Income Statement, Business and Tax reports. I believe I can get the SQL queries going for pulling the amounts based on their account, but I'm not sure how to get at the account types' 'Debit' field to determine whether to add or subtract the amount.

If necessary, I may do this inside the C# application that this database will underpin. If I can do it at the SQL Query level, I'd be able to use it in the project as a view, instead of having to code it. Any help at all is greatly appreciated.

sean
  • 1
  • 2
  • An Asset is not a revenue account and Liability is not an expense account so why would you include those? Your question is far too broad for SO. Why reinvent the wheel when QuickBooks is cheap? – June7 Jun 25 '19 at 23:00
  • Kind of reinventing the wheel because QuickBooks doesn't provide revenue and expense breakdown that is useful for owner/operator truck drivers, like revenue per mile, expenses per mile, profit per mile, etc. – sean Jun 25 '19 at 23:22
  • So you are building your own double-entry bookkeeping app? – June7 Jun 25 '19 at 23:46
  • "**_Sort of_**" building my own double-entry bookkeeping app. That's just a small part of the whole project. – sean Jun 27 '19 at 02:40

1 Answers1

0

After a couple more searches, I found my answer as being that I need to do 4 different queries to accomplish what I was attempting. The appropriate queries turned out to be:

For Total Expenses:

SELECT SUM(Amount) AS TotalExpense
FROM GeneralLedger INNER JOIN ChartOfAccounts ON ChartOfAccounts.AcctID=GeneralLedger.ToAcct
WHERE ChartOfAccounts.AcctType = 4;

For Total Revenue:

SELECT SUM(Amount) AS TotalRevenue
FROM GeneralLedger INNER JOIN ChartOfAccounts ON ChartOfAccounts.AcctID=GeneralLedger.ToAcct
WHERE ChartOfAccounts.AcctType = 3;

For Total Liabilities:

SELECT SUM(Amount) AS TotalLiabilities
FROM GeneralLedger INNER JOIN ChartOfAccounts ON ChartOfAccounts.AcctID = GeneralLedger.ToAcct
WHERE ChartOfAccounts.AcctType = 2;

For Total Assets:

SELECT SUM(Amount) AS TotalAssets
FROM GeneralLedger INNER JOIN ChartOfAccounts ON ChartOfAccounts.AcctID = GeneralLedger.ToAcct
WHERE ChartOfAccounts.AcctType = 1;

It was just a matter of getting my INNER JOIN working with my WHERE clause properly. It's the simple things that kill...

The link that proved most helpful was: StackOverflow: sql - INNER JOIN WHERE clause.

sean
  • 1
  • 2