Re: Calculating Query

bolow wrote: 
Hi:

Thanks for responding but I really don't understand your answer. If possible please clarify it for me.

"MGFoster" wrote:


bolow wrote:

Hi All:

I have created a query that pulls information from two tables and calculates. The query pulls one column from table A and one from table B. The calculation is in this form A-B = C (column c is the result of the subtraction taking place between the values in columns A and B). I am also using a left join since Table B will have more information than table A. My problem is that in order for the calculations to be correct I need to build an expression that will insert a zero when no data is present. Since Table B will have more rows the corresponding column from table A will be blank. I need the blank spots from table A to appear as zeros in the query. I have included the SQL version for clarification. Any help would be greatly appreciated.

SELECT [Table B].[Job No], [Table B].[Cost], [Table A].[Actual Costs], [Table B].[Cost]-[Table A].[Actual Costs] AS Calculated_Costs, [Table B].[Earned Revenue], [Table A].[Revenue Earned], [Table B].[Earned Revenue]-[Table A].[Revenue Earned] AS Calculated_Revenue
FROM [Table B] LEFT JOIN [Table A] ON [Table B].[Job No] = [Table A].[Job No];



Use Nz(column,0).  E.g.:

  Nz([Table A].[Revenue Earned],0) As RevenueEarned

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If the column is NULL, which it will be if there isn't any matching data
in the right-side table (of a LEFT JOIN), then the Nz() function, as
shown above, returns a zero.  E.g.:

If the value of the column IS NULL:

Nz(column,"this is null")   -> the string "this is null"
Nz(column,0) -> the numeric value 0 [zero]
Nz(column1, column2) -> the value that is in column2

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQw4me4echKqOuFEgEQIKfgCg/1upg4gi9Lzc3pMTy4M188AttToAoK4F
J2RkfHeLbbPjYpc3wtMDQO52
=1jSS
-----END PGP SIGNATURE-----
.

Relevant Pages

  • Re: Calculating Query
    ... possible please clarify it for me. ... >> I have created a query that pulls information from two tables and ... The query pulls one column from table A and one from table B. ... >> an expression that will insert a zero when no data is present. ...
    (microsoft.public.access.queries)
  • Re: Sorting issue
    ... > The sql line for the query looks like ... ... >>Oakland, CA ... >>Victor wrote: ... >>>field in a monthly format. ...
    (microsoft.public.access.queries)
  • Re: Need help with Date Query
    ... Oakland, CA ... PGP for Personal Privacy 5.0 ... Your whole query needs to be re-written. ...
    (microsoft.public.access.queries)
  • Re: Uing the In clause in a parameter query
    ... >>>I've tried it with quotation marks around each criteria within the ... >>>parentheses as well as no quotation marks. ... >>show the whole query ... >>Oakland, CA ...
    (microsoft.public.access.queries)
  • Re: Need Help with Formula Expansion
    ... Please copy & post your formula and the starting date you enter when the query runs. ... Oakland, CA ... The user has to enter twice a single Review Date to get the result of the count. ... Now I would like to expand the formula to allow a count for the quarter, which means I would like to be able to get a count of files for 3 selected review dates instead of just 1. ...
    (microsoft.public.access.queries)