how to create a Microsoft Access query that provides a default transaction type for those transactions not categorized?

Question

How could I create a query that provides a default transaction type for those transactions not categorized?

So assuming I have:

• Transactions table (with transactions)
• Categories table
• transactions_categories table - allows to allocate multiple categories (with a percentage)
• Usage is such that only non-personal categories have been applied through out data. So there is a lot of transactions with no categories applied

Aim:

• Want to create a query that creates a list of all the allocated amounts, so would include as columns: transaction.tDate, transaction.tTitle, categories.name, allocatedAmount(calculated from percentage * transaction amount)

BUT:

• How could I include in the query, the entries that cover all transaction that haven't been allocated, to a default category "personal", where the allocated Amount would be 100% of the transaction value
• And also (if it were possible), for transactions that have been categorized but not for the complete transaction value (say only 50% was allocated to a category), how to to cover this off to.

Example

Transactions
1 XXXX   $100
2 YYY    $100
3 ZZZ    $100

Categories
1 aaa  
2 bbb
3 ccc
4 PersonalDefault

Transaction-Categories (i.e. allocation)
transID  catID       %
1           1        100%
2           2        50%


Query Output I'm After
transTitle  catTitle          AllocatedAmount
xxxx         aaaaa            $100
yyyyy        bbbbbb           $50
yyyyy        PersonalDefault  $50
zzzzz        PersonalDefault  $100

Answer 1

This query will give you the desired result:

select a.transID, t.name as transname, a.catID, c.name as catname, t.amount * a.pc / 100 as amnt
from allocation a inner join transactions t on a.transID = t.id
inner join categories c on a.catID = c.id
union all 
select a.transID, t.name as transname, 4 as catID, 'PersonalDefault' as catname, t.amount * (100 - a.pc) / 100 as amnt
from allocation a inner join transactions t on a.transID = t.id
where a.pc < 100
union all
select t.id as transID, t.name as transname, 4 as catID, 'PersonalDefault' as catname, t.amount as amnt
from transactions t 
where t.id not in (select transID from allocation)

Check this SQL Fiddle (it is SQL Server, but should work in Access as well)