Query help needed

[mamusonda]

I am pretty new to SQL and database concepts. Heres my problem: I have a sales info. table and an expenses info table with invoice number being the field relating the two tables (1:N). The sales table has details about each job done for a specific client, ie. a breakdown of the income recieved from each particular job. Expenses Info. table has several expenses incurred for each job, that is to say, some jobs done require outsourcing which creates a job-specific expense. Some invoices have 1 or 2 expenses related to them while most have none because most of the work is done in-house. Now, i have created a query to calculate the profits realised per job but it is only calculating profits for the jobs with an expense attached to it. For the other jobs which didnt incurr any expenses, the profit is supposed to equal the sales but instead it is coming up blank. What can i do to get the calculations right? I am trying to avoid entering Zero manually into the records coz it'll be tedious (theyre are simply thousands of transactions) and it will take up memory unnecessarily right?

[NeoPa]

Without the code you've used to check, I can only guess that joins between tables need looking at.
Perhaps you use an INNER JOIN somewhere (against the Expenses table) which should really be a LEFT JOIN.

[mamusonda]

Used the wizard to create this :

SELECT SALES.DATE, SALES.[INVOICE NUMBER], CLIENTS.NAME, SALES.SUBTOTAL AS INCOME, [EXPENSE TOTALS PER INVOICE].[Sum Of TOTAL (KES)] AS EXPENSES, SALES!SUBTOTAL-[JOB EXPENSES]![TOTAL (KES)] AS PROFITS
FROM (CLIENTS INNER JOIN SALES ON CLIENTS.NAME = SALES.CLIENT) LEFT JOIN ([EXPENSE TOTALS PER INVOICE] RIGHT JOIN [JOB EXPENSES] ON [EXPENSE TOTALS PER INVOICE].[INVOICE #] = [JOB EXPENSES].[INVOICE #]) ON SALES.[INVOICE NUMBER] = [JOB EXPENSES].[INVOICE #]
GROUP BY SALES.DATE, SALES.[INVOICE NUMBER], CLIENTS.NAME, SALES.SUBTOTAL, [EXPENSE TOTALS PER INVOICE].[Sum Of TOTAL (KES)], SALES!SUBTOTAL-[JOB EXPENSES]![TOTAL (KES)]
ORDER BY SALES.DATE;

[mamusonda]

Heres the code so you can help me correct it:

SELECT SALES.DATE, SALES.[INVOICE NUMBER], CLIENTS.NAME, SALES.SUBTOTAL AS INCOME, [EXPENSE TOTALS PER INVOICE].[Sum Of TOTAL (KES)] AS EXPENSES, SALES!SUBTOTAL-[JOB EXPENSES]![TOTAL (KES)] AS PROFITS
FROM (CLIENTS INNER JOIN SALES ON CLIENTS.NAME = SALES.CLIENT) LEFT JOIN ([EXPENSE TOTALS PER INVOICE] RIGHT JOIN [JOB EXPENSES] ON [EXPENSE TOTALS PER INVOICE].[INVOICE #] = [JOB EXPENSES].[INVOICE #]) ON SALES.[INVOICE NUMBER] = [JOB EXPENSES].[INVOICE #]
GROUP BY SALES.DATE, SALES.[INVOICE NUMBER], CLIENTS.NAME, SALES.SUBTOTAL, [EXPENSE TOTALS PER INVOICE].[Sum Of TOTAL (KES)], SALES!SUBTOTAL-[JOB EXPENSES]![TOTAL (KES)]
ORDER BY SALES.DATE;

[NeoPa]

Mamusonda,

Unfortunately, there are more tables in here that you haven't included in any explanation.
I'm gussing you aren't able to strip down the unnecessary code in this SQL so you'd better explain the table structure more fully instead - particularly how [EXPENSE TOTALS PER INVOICE] and [JOB EXPENSES] fit in.

Before you do all that though, try this (I may have had lucky guesses as to what's going on) :-

SELECT SALES.DATE, SALES.[INVOICE NUMBER],
	CLIENTS.NAME, SALES.SUBTOTAL AS INCOME,
	[EXPENSE TOTALS PER INVOICE].[Sum Of TOTAL (KES)] AS EXPENSES,
	SALES!SUBTOTAL-[JOB EXPENSES]![TOTAL (KES)] AS PROFITS
FROM (CLIENTS INNER JOIN SALES ON CLIENTS.NAME = SALES.CLIENT) 
	LEFT JOIN ([EXPENSE TOTALS PER INVOICE] LEFT JOIN [JOB EXPENSES] ON 
	[EXPENSE TOTALS PER INVOICE].[INVOICE #] = [JOB EXPENSES].[INVOICE #]) ON
	SALES.[INVOICE NUMBER] = [JOB EXPENSES].[INVOICE #]
GROUP BY SALES.DATE, SALES.[INVOICE NUMBER], CLIENTS.NAME,
	SALES.SUBTOTAL, [EXPENSE TOTALS PER INVOICE].[Sum Of TOTAL (KES)],
	SALES!SUBTOTAL-[JOB EXPENSES]![TOTAL (KES)]
ORDER BY SALES.DATE;

[VALIS]

I am pretty new to SQL and database concepts. Heres my problem: I have a sales info. table and an expenses info table with invoice number being the field relating the two tables (1:N). The sales table has details about each job done for a specific client, ie. a breakdown of the income recieved from each particular job. Expenses Info. table has several expenses incurred for each job, that is to say, some jobs done require outsourcing which creates a job-specific expense. Some invoices have 1 or 2 expenses related to them while most have none because most of the work is done in-house. Now, i have created a query to calculate the profits realised per job but it is only calculating profits for the jobs with an expense attached to it. For the other jobs which didnt incurr any expenses, the profit is supposed to equal the sales but instead it is coming up blank. What can i do to get the calculations right? I am trying to avoid entering Zero manually into the records coz it'll be tedious (theyre are simply thousands of transactions) and it will take up memory unnecessarily right?

Does your query contain a formula driven field? I.E [PROFIT]:[SALES]-[EXPENSES]
If the value returned from the expenses field is null the profit result will be null.
Work around this by re-writing the formula
[PROFIT]:[SALES]-IIF([ISNULL([EXPENSES]),0,[EXPENSES])
This should return values for all entries

[mamusonda]

Thanx everyone for the help. Tried using the IIF statement suggestion although it was giving me errors. However, finally managed to get it working by rearranging the statement this way:

PROFITS: [SALES]![SUBTOTAL]-IIf(([EXPENSE TOTALS PER INVOICE]![Sum Of TOTAL (KES)]) Is Null,0,([EXPENSE TOTALS PER INVOICE]![Sum Of TOTAL (KES)]))