Aliases in SQL query 1

[TheSpoon]

I have a query that looks like this:

SELECT NB_CLIENTS.C_ID, NB_CLIENTS.C_Name, NB_CLIENTS.Transit_ID, QDef_1.Cost AS P1, QDef_2.Cost AS P2, QDef_3.Cost AS P3, [P3]-[P2] AS VAR, IIf([P2]=0,0,([P3]-[P2])/[P2]) AS FLUCT, IIf([P1]=0,'INACTIVE','ACTIVE') AS STATUS INTO NB_ANALYSIS
FROM ((NB_CLIENTS LEFT JOIN QDef_3 ON NB_CLIENTS.C_ID = QDef_3.C_ID) LEFT JOIN QDef_2 ON NB_CLIENTS.C_ID = QDef_2.C_ID) LEFT JOIN QDef_1 ON NB_CLIENTS.C_ID = QDef_1.C_ID
WHERE ((([P1])<>'')) OR ((([P2])<>'')) OR ((([P3])<>''));

Access is complaining that it doesn't know what P1, P2, or P3 are when I reference them in the WHERE clause. Why is that, and what can I do about it?

Thanks

 

[lespaul]

it needs to be:

SELECT NB_CLIENTS.C_ID, NB_CLIENTS.C_Name, NB_CLIENTS.Transit_ID, QDef_1.Cost AS P1, QDef_2.Cost AS P2, QDef_3.Cost AS P3, [P3]-[P2] AS VAR, IIf([P2]=0,0,([P3]-[P2])/[P2]) AS FLUCT, IIf([P1]=0,'INACTIVE','ACTIVE') AS STATUS INTO NB_ANALYSIS
FROM ((NB_CLIENTS LEFT JOIN QDef_3 ON NB_CLIENTS.C_ID = QDef_3.C_ID) LEFT JOIN QDef_2 ON NB_CLIENTS.C_ID = QDef_2.C_ID) LEFT JOIN QDef_1 ON NB_CLIENTS.C_ID = QDef_1.C_ID
WHERE ((([QDef_1.Cost])<>'')) OR ((([QDef_2.Cost])<>'')) OR ((([QDef_3.Cost])<>''));

Leslie

 

[SiJP]

Leave this query as it is, but leave out the where criteria/

Create another query based upon the above ones, and add the where criteria to this.

e.g.

Table1 feeds into Query1 (No criteria)
Query1 feeds into Query2, which has a WHERE clause...

Access wont allow you to query against alias fields, in the same query.

------------------------
Hit any User to continue

 

[SiJP]

Same for performing expressions against these aliases.. create the alias in the first query.. perform the expression against them in the second query...



------------------------
Hit any User to continue

 

[TheSpoon]

Yeah I know that'll work, I guess I didn't express my question clearly: Why do aliases get forgotten after some point, and is there any way to reference "forgotten" aliases, or create aliases that don't get forgotten?

Thanks

 

[TheSpoon]

Eek, my reply was to Leslie.

 

[SiJP]

is there any way to reference "forgotten" aliases

As per my above reply!!

------------------------
Hit any User to continue

 

[TheSpoon]

Yep, I'll do what you said SiJP, thanks.