Can understand access code

[SQL newbie]

Could someone help me document this code so me, the newbie can learn
something?
I tried to document what i could.

Andrew

Here is some code I am trying to understand:
I put comments in a 2nd copy below where I thought I would try to
comment the code but not touch this code so I don’t make it hard to
read.
Sample code:
SELECT ep.Week, sum(ep.Num_Emp_Per_Tenure * iif(sep.Avg_Prod is not
null, sep.Avg_Prod, 505)) AS Week_Prod, sum(ep.Num_Emp_Per_Tenure * iif
(sep.Avg_Defect is not null, sep.Avg_Defect, 6)) AS Week_Defect,
Week_Defect / Week_Prod AS Week_Defect_Rate
FROM (select *
from (
select Tenure_Week, count(*) as Num_Emp_Per_Tenure
from EmplProd3
where Prod_Week = 26
group by Tenure_Week
) ep,
(
select distinct Prod_Week as Week
from EmplProd3
)
) AS ep LEFT JOIN ProdAvgByTenWeek_Ass_Part1 AS sep ON ep.Tenure_Week
+ ep.Week = sep.Tenure_Week
GROUP BY ep.Week;
The code again but commented by me:
\\ what does the ep and sep signify? Sounds like its an alias.
\\What is the iff doing? Sounds like avg_prod is tested for null, if
not null use avg_prod, else use 505?
How do you put aliases together in access?
How do I know what ep.Week is?
SELECT ep.Week, sum(ep.Num_Emp_Per_Tenure * iif(sep.Avg_Prod is not
null, sep.Avg_Prod, 505)) AS Week_Prod, sum(ep.Num_Emp_Per_Tenure * iif
(sep.Avg_Defect is not null, sep.Avg_Defect, 6)) AS Week_Defect,
Week_Defect / Week_Prod AS Week_Defect_Rate

\\I understand the join part here but I don’t understand what is
going on in this hair code starting this the from?
FROM (select *
from (
select Tenure_Week, count(*) as Num_Emp_Per_Tenure
from EmplProd3
where Prod_Week = 26
group by Tenure_Week
) ep,
(
select distinct Prod_Week as Week
from EmplProd3
)
) AS ep LEFT JOIN ProdAvgByTenWeek_Ass_Part1 AS sep ON ep.Tenure_Week
+ ep.Week = sep.Tenure_Week
GROUP BY ep.Week;

 

[Ken Snell]

ep and sep are aliases assigned to source query/table in the query. Your
posted query actually is using ep two times as an alias, a practice that
might lead to confusion on ACCESS' part. In general, one should have unique
aliases in a full query.


FROM (select *
from (
select Tenure_Week, count(*) as Num_Emp_Per_Tenure
from EmplProd3
where Prod_Week = 26
group by Tenure_Week
) ep,
(
select distinct Prod_Week as Week
from EmplProd3
)
) AS ep


See the two uses of ep above? The first instance should be aliased as
something else, perhaps ep1. You also should have an alias on the second
internal query:

,
(
select distinct Prod_Week as Week
from EmplProd3
)

In the SELECT clause at beginning of the query, the ep refers to the second
use of ep as an alias:

FROM (select *
from (
select Tenure_Week, count(*) as Num_Emp_Per_Tenure
from EmplProd3
where Prod_Week = 26
group by Tenure_Week
) ep,
(
select distinct Prod_Week as Week
from EmplProd3
)
) AS ep

So ep.Week is the Prod_Week field from the EmplProd3 table/query. In the
above, the ep represents the cartesian join result of the two subqueries:

(
select Tenure_Week, count(*) as Num_Emp_Per_Tenure
from EmplProd3
where Prod_Week = 26
group by Tenure_Week
)

and

(
select distinct Prod_Week as Week
from EmplProd3
)



--

Ken Snell
http://www.accessmvp.com/KDSnell/



Could someone help me document this code so me, the newbie can learn
something?
I tried to document what i could.

Andrew

Here is some code I am trying to understand:
I put comments in a 2nd copy below where I thought I would try to
comment the code but not touch this code so I don’t make it hard to
read.
Sample code:
SELECT ep.Week, sum(ep.Num_Emp_Per_Tenure * iif(sep.Avg_Prod is not
null, sep.Avg_Prod, 505)) AS Week_Prod, sum(ep.Num_Emp_Per_Tenure * iif
(sep.Avg_Defect is not null, sep.Avg_Defect, 6)) AS Week_Defect,
Week_Defect / Week_Prod AS Week_Defect_Rate
FROM (select *
from (
select Tenure_Week, count(*) as Num_Emp_Per_Tenure
from EmplProd3
where Prod_Week = 26
group by Tenure_Week
) ep,
(
select distinct Prod_Week as Week
from EmplProd3
)
) AS ep LEFT JOIN ProdAvgByTenWeek_Ass_Part1 AS sep ON ep.Tenure_Week
+ ep.Week = sep.Tenure_Week
GROUP BY ep.Week;
The code again but commented by me:
\\ what does the ep and sep signify? Sounds like its an alias.
\\What is the iff doing? Sounds like avg_prod is tested for null, if
not null use avg_prod, else use 505?
How do you put aliases together in access?
How do I know what ep.Week is?
SELECT ep.Week, sum(ep.Num_Emp_Per_Tenure * iif(sep.Avg_Prod is not
null, sep.Avg_Prod, 505)) AS Week_Prod, sum(ep.Num_Emp_Per_Tenure * iif
(sep.Avg_Defect is not null, sep.Avg_Defect, 6)) AS Week_Defect,
Week_Defect / Week_Prod AS Week_Defect_Rate

\\I understand the join part here but I don’t understand what is
going on in this hair code starting this the from?
FROM (select *
from (
select Tenure_Week, count(*) as Num_Emp_Per_Tenure
from EmplProd3
where Prod_Week = 26
group by Tenure_Week
) ep,
(
select distinct Prod_Week as Week
from EmplProd3
)
) AS ep LEFT JOIN ProdAvgByTenWeek_Ass_Part1 AS sep ON ep.Tenure_Week
+ ep.Week = sep.Tenure_Week
GROUP BY ep.Week;

 

[SQL newbie]

Ken,
Thank you for commenting.
I have few more questions but i have to expand this out. Its pretty
long and i am not used to such long queries.
I will post later. I left this question kind of open but the alias
part was a mystery. Thank you for posting!

Andrew