Adding new fields problem

[MikeDuffield]

Hello all,

I'm pretty new to the world of SQL queries, I've always stuck to VB and Excel in the past... As a result, I'm struggling on things that I'm sure most would find easy. I've Google'd this to no avail, so now it's time to cry for help!

I have added two new fields to an SQL query, as follows:

Actual: [SalesTbl].[Manufactured]+[Factored]

So, here we add a field called Actual, that is the total of two other existing fields in a table. this works fine. Another new field is also added:

Prcnt Budget: [SalesTbl].[Actual]/[Budget]

The problem I'm facing is that I assume it tries to calculate everything at once, as it can't find a value for "Actual". I thought it would create the field Actual first, then try and calculate Prcnt Budget, but it seems not.

In the query, I would have assumed it calculates each field one at a time before moving on to the next, but perhaps I've got the fundamentals of a query entirely wrong...

Can anyone enlighten me?


Thanks in advance
Mike.

 

[sxschech]

How about this then? Any reason you have [SalesTbl]. only once? Are Factored and Budget not part of SalesTbl?

 Prcnt Budget: ([SalesTbl].[Manufactured]+[Factored])/[Budget]

 

[MikeDuffield]

sxschech:

Thanks for that, I never thought to bracket them both, I'll use that in future!

Pat:

Thanks for the detailed response, I wasn't sure how it would behave if the field was null. So far I haven't had any null fields so it wasn't an issue, but once this is up and running I would imagine there will be so thanks for that. I'll have to remember that Nz function!


Thanks again for the help folks!

Mike.