Calculating Query

[bolow]

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];

 

[MGFoster]

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

 

[bolow]

Hi:

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

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]

Hi:

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

:

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

-----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-----

 

[bolow]

Thanks. It works. I really appreciate the clarification and thanks again
for your help.

Hi:

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

:

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

-----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-----