Re: Calculating Query
- From: "bolow" <bolow@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 25 Aug 2005 12:53:07 -0700
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)
>
.
- Follow-Ups:
- Re: Calculating Query
- From: MGFoster
- Re: Calculating Query
- References:
- Calculating Query
- From: bolow
- Re: Calculating Query
- From: MGFoster
- Calculating Query
- Prev by Date: RE: Record is not displayed in query result
- Next by Date: Re: Calculating Query
- Previous by thread: Re: Calculating Query
- Next by thread: Re: Calculating Query
- Index(es):
Relevant Pages
|
