| Author |
Topic |
|
psgrn
Starting Member
1 Post |
 Posted - 2007-01-24 : 19:28:08
|
| Below are the two tables (with sample records) that I am trying to include into my query:[Journal] (ID, EntryDate, Description)1, 1/24/2007, Fred Meyer[Journal_Splits] (ID, JournalID, AccountID, Amount, Type)1, 1, 11, 19.95, 22, 1, 8, 24.99, 23, 1, 3, 5.49, 2The AccountID refers to another account table that holds income and expense categories such as Groceries, Leisure, Clothing, Household, etc... I want to create a query that will return the following:[JE Query]ID, EntryDate, Comment, Income, ExpenseID, EntryDate and Comment are all easily returned from the [Journal] table. The problem I am having is with the Income and Expense fields. For the Income field, I want to SUM the Journal_Split.Amount fields where Journal_Splts.JEID = Journal.ID AND where Journal_Splits.Type = 1 (1 being the code for Income). The Expense field is just the same, except the Journal_Splits.Type will be equal to 2. When the query is done there should only be as many rows as there are in the [Journal] table, for the [Journal_Splits] table can contain up to 3 records for each [Journal] record. [Journal_Splits] just holds the individual amounts split out to different income/expense categories for a single transaction.This to me has a lot of conditions that I can't even seem to build with access's query builder (yes I am using access for now.. will change over to sql at a later time).Anyone have a good idea how to do this? Or have a better idea for structuring the database to make is easier to query? Right now I have it working, but instead of an Amount field, I have 1 Income and 1 Expense field, but since a transaction can only be either income or expense, one field will always be 0, which seems like a waste of space.That may be a bit confusing so let me know if I can clarify. Much appreciated. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-01-24 : 19:38:26
|
[code]select ID, EntryDate, Description as Comment, Income = sum(case when Type = 1 then Amount else 0 end), Expense = sum(case when Type = 2 then Amount else 0 end)from Journal j inner join Journal_Splits s on j.ID = s.IDgroup by ID, EntryDate, Description[/code]
KH |
 |
|
|
|
|
|
Complex Query (for me)