MS access refusing to combine two tables?

Question

I have an Access 2007 database where I have two tables, exp_cash and exp_cheque. Both are linked to exp_cat, where exp_cat contains categories list, and exp_cash and exp_cheque contain id, cat_id, exp_date, exp_amount, and exp_note.

When I tried to combine them nothing appears in the result unless I remove the cat_name from my query. I used a simple query:

SELECT DISTINCT
    exp_cat.cat_name,
    exp_cash.exp_amount,
    exp_cheque.exp_amount
FROM (exp_cat INNER JOIN exp_cash
    ON exp_cat.ID = exp_cash.exp_cat_id)
    INNER JOIN exp_cheque
    ON exp_cat.ID = exp_cheque.exp_cat_id;

Table exp_cat contains

exp_cat_name
exp_cat_id

    exp_cat_id ----- exp_cat_name
        1      -----    Salary
        2      -----    Electricity
        3      -----    Water Bill
        4      -----    Loan

Table exp_cash contains

exp_cash_id
exp_date
exp_cat_id
exp_cash_amount
exp_invoice_no
    exp_cash_id ----- exp_date ---- exp_cat_id ---- exp_cash_amount ---- exp_invoice_no
        1      -----  15/05/2010 --   2        ----  200            ----   25AB5245
        2      -----  17/05/2010 --   1        ----  50             ----   58624AA

Table exp_cheue contains

exp_cheque_id
exp_date
exp_cat_id
exp_cheque_amount
exp_invoice_no

    exp_cheque_id ----- exp_date ---- exp_cat_id -- exp_cheque_amount -- exp_invoice_no
        1      -----  15/05/2010 --   3          --  120              --   25AB5245
        2      -----  17/05/2010 --   4          --  500              --   58624AA

Answer 1

I think what you want is left joins instead of inner joins:

SELECT exp_cat.exp_cat_name, 
       exp_cheque.exp_cash_amount, 
       exp_cash.exp_cheque_amount
FROM (exp_cat LEFT JOIN exp_cash 
        ON exp_cat.exp_cat_id = exp_cash.exp_cat_id) 
    LEFT JOIN exp_cheque 
        ON exp_cat.exp_cat_id = exp_cheque.exp_cat_id;

Otherwise you must have the same exp_cat_id in both the exp_cash and exp_cheque tables. The Inner joins only show what exists in both tables. The left join shows everything in the left table, and anything that happens to match in the right hand table (or null values if nothing matches).

Here's what I get when I run that query with your data:

exp_cat_name   exp_cash_amount   exp_cheque_amount
------------   ---------------   -----------------
Salary                            50
Electricity                      200
Water Bill     120
Loan           500

Answer 2

I would strongly advise you to think about implementing a generalisation in your database model. It will allow you to merge both 'exp_cash' and 'exp_cheque' table. You could for example add a field such as 'exp_type' in a 'exp' table. By adding another field to follow-up if the operation is either an expense or a revenue, you could even have a more generic table that we could call "ope" (it stands for 'expenses and revenue operations') with the following fields:

• ope_id
• ope_status (either "exp" or "rev")
• ope_type (cash, cheque, but also bank transfer, etc)
• ope_amount (you could choose to have positive fro revenue/negative for expenses numbers)
• ope_date
• ...

In this way, you'll be able to calculate balances (per type, period, etc.) without making it a nightmare!

some theory on database generalisation here