Adding columns with equal number of records

S

sebastico

I have 3 Qrys: QHInd_12_01_VacasTotales, QHInd_12_02_CargaAnimal,
QHInd_12_03_Natalidad%

QHInd_12_01_VacasTotales
SELECT F02_Hato.CodProd, Sum(F02_Hato.Cabezas) AS TotalVacas
FROM F02_Hato
WHERE (((F02_Hato.CodAnimal) In ("1","2","3")))
GROUP BY F02_Hato.CodProd, F02_Hato.CodPer
HAVING (((F02_Hato.CodPer)="12"));

QHInd_12_02_CargaAnimal
SELECT QH_12_01_Hato.CodProd,
QH_12_01_Hato!UnidsAnims/QH_12_07_TotalPastos!AreaTotPastos AS CargAnimal
FROM QH_12_01_Hato INNER JOIN QH_12_07_TotalPastos ON QH_12_01_Hato.CodProd
= QH_12_07_TotalPastos.CodProd;

QHInd_12_03_Natalidad%
SELECT QH_12_02_VacasParidas.CodProd, [QH_12_02_VacasParidas]![Vacas
Paridas]/[QHInd_12_01_VacasTotales]![TotalVacas]*100 AS [Natalidad(%)]
FROM QH_12_02_VacasParidas INNER JOIN QHInd_12_01_VacasTotales ON
QH_12_02_VacasParidas.CodProd = QHInd_12_01_VacasTotales.CodProd;

QHInd_12_01_VacasTotales has 43 records (CodProd)
QHInd_12_02_CargaAnimal has 39 records (CodProd)
QHInd_12_03_Natalidad% has 42 records (CodProd)

If I run a new Qry with these 3 qrys

SELECT QHInd_12_01_VacasTotales.CodProd,
QHInd_12_01_VacasTotales.TotalVacas, QHInd_12_02_CargaAnimal.CargAnimal,
[QHInd_12_03_Natalidad(%)].[Natalidad(%)]
FROM (QHInd_12_01_VacasTotales INNER JOIN QHInd_12_02_CargaAnimal ON
QHInd_12_01_VacasTotales.CodProd = QHInd_12_02_CargaAnimal.CodProd) INNER
JOIN [QHInd_12_03_Natalidad(%)] ON QHInd_12_02_CargaAnimal.CodProd =
[QHInd_12_03_Natalidad(%)].CodProd;

The table shows only 38 records with the columns CodProd, VacasTotales,
CargaAnimal, Natalidad(%). Nevertheless, I want all columns with 43 CodProd
records even though columns QHInd_12_02_CargaAnimal will be with 3 empty
records and QHInd_12_03_Natalidad% with 1 record. How I can do this?

Thank you for your help
 
S

sebastico

Hi Chris2. Excellent. Many Thanks
Chris2 said:
sebastico said:
I have 3 Qrys: QHInd_12_01_VacasTotales, QHInd_12_02_CargaAnimal,
QHInd_12_03_Natalidad%

QHInd_12_01_VacasTotales
SELECT F02_Hato.CodProd, Sum(F02_Hato.Cabezas) AS TotalVacas
FROM F02_Hato
WHERE (((F02_Hato.CodAnimal) In ("1","2","3")))
GROUP BY F02_Hato.CodProd, F02_Hato.CodPer
HAVING (((F02_Hato.CodPer)="12"));

QHInd_12_02_CargaAnimal
SELECT QH_12_01_Hato.CodProd,
QH_12_01_Hato!UnidsAnims/QH_12_07_TotalPastos!AreaTotPastos AS CargAnimal
FROM QH_12_01_Hato INNER JOIN QH_12_07_TotalPastos ON QH_12_01_Hato.CodProd
= QH_12_07_TotalPastos.CodProd;

QHInd_12_03_Natalidad%
SELECT QH_12_02_VacasParidas.CodProd, [QH_12_02_VacasParidas]![Vacas
Paridas]/[QHInd_12_01_VacasTotales]![TotalVacas]*100 AS [Natalidad(%)]
FROM QH_12_02_VacasParidas INNER JOIN QHInd_12_01_VacasTotales ON
QH_12_02_VacasParidas.CodProd = QHInd_12_01_VacasTotales.CodProd;

QHInd_12_01_VacasTotales has 43 records (CodProd)
QHInd_12_02_CargaAnimal has 39 records (CodProd)
QHInd_12_03_Natalidad% has 42 records (CodProd)

If I run a new Qry with these 3 qrys

SELECT QHInd_12_01_VacasTotales.CodProd,
QHInd_12_01_VacasTotales.TotalVacas, QHInd_12_02_CargaAnimal.CargAnimal,
[QHInd_12_03_Natalidad(%)].[Natalidad(%)]
FROM (QHInd_12_01_VacasTotales INNER JOIN QHInd_12_02_CargaAnimal ON
QHInd_12_01_VacasTotales.CodProd = QHInd_12_02_CargaAnimal.CodProd) INNER
JOIN [QHInd_12_03_Natalidad(%)] ON QHInd_12_02_CargaAnimal.CodProd =
[QHInd_12_03_Natalidad(%)].CodProd;

The table shows only 38 records with the columns CodProd, VacasTotales,
CargaAnimal, Natalidad(%). Nevertheless, I want all columns with 43 CodProd
records even though columns QHInd_12_02_CargaAnimal will be with 3 empty
records and QHInd_12_03_Natalidad% with 1 record. How I can do this?

Thank you for your help
Click to expand...

sebastico,

Your Issue:

If you want to preserve all records from QHInd_12_01_VacasTotales, try
using two LEFT JOIN operators instead of INNER JOIN operators.

Be prepared to deal with NULLs from the two other tables, possibly
using the Nz() function as necessary.



Possibly Unnecessary Comments:

I strongly recommend the use of Table aliases (which apply equally to
Queries) because of your very long Query names.

In the QBE, they can be set easily (and should update the SQL
automatically) by clicking on a Table or Query box, selecting
Properties, and entering a value.

This:

SELECT QHInd_12_01_VacasTotales.CodProd
,QHInd_12_01_VacasTotales.TotalVacas
,QHInd_12_02_CargaAnimal.CargAnimal
,[QHInd_12_03_Natalidad(%)].[Natalidad(%)]
FROM (QHInd_12_01_VacasTotales
INNER JOIN QHInd_12_02_CargaAnimal
ON QHInd_12_01_VacasTotales.CodProd =
QHInd_12_02_CargaAnimal.CodProd)
INNER JOIN
[QHInd_12_03_Natalidad(%)]
ON QHInd_12_02_CargaAnimal.CodProd =
[QHInd_12_03_Natalidad(%)].CodProd;

Changes To This:

SELECT VT1.CodProd
,VT1.TotalVacas
,CA1.CargAnimal
,N1.[Natalidad(%)]
FROM (QHInd_12_01_VacasTotales AS VT1
INNER JOIN
QHInd_12_02_CargaAnimal A CA1
ON VT1.CodProd = CA1.CodProd)
INNER JOIN
[QHInd_12_03_Natalidad(%)] AS N1
ON CA1.CodProd = N1.CodProd;

The second example is far cleaner and easier to read.


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

sebastico said:
I have 3 Qrys: QHInd_12_01_VacasTotales, QHInd_12_02_CargaAnimal,
QHInd_12_03_Natalidad%

QHInd_12_01_VacasTotales
SELECT F02_Hato.CodProd, Sum(F02_Hato.Cabezas) AS TotalVacas
FROM F02_Hato
WHERE (((F02_Hato.CodAnimal) In ("1","2","3")))
GROUP BY F02_Hato.CodProd, F02_Hato.CodPer
HAVING (((F02_Hato.CodPer)="12"));

QHInd_12_02_CargaAnimal
SELECT QH_12_01_Hato.CodProd,
QH_12_01_Hato!UnidsAnims/QH_12_07_TotalPastos!AreaTotPastos AS CargAnimal
FROM QH_12_01_Hato INNER JOIN QH_12_07_TotalPastos ON QH_12_01_Hato.CodProd
= QH_12_07_TotalPastos.CodProd;

QHInd_12_03_Natalidad%
SELECT QH_12_02_VacasParidas.CodProd, [QH_12_02_VacasParidas]![Vacas
Paridas]/[QHInd_12_01_VacasTotales]![TotalVacas]*100 AS [Natalidad(%)]
FROM QH_12_02_VacasParidas INNER JOIN QHInd_12_01_VacasTotales ON
QH_12_02_VacasParidas.CodProd = QHInd_12_01_VacasTotales.CodProd;

QHInd_12_01_VacasTotales has 43 records (CodProd)
QHInd_12_02_CargaAnimal has 39 records (CodProd)
QHInd_12_03_Natalidad% has 42 records (CodProd)

If I run a new Qry with these 3 qrys

SELECT QHInd_12_01_VacasTotales.CodProd,
QHInd_12_01_VacasTotales.TotalVacas, QHInd_12_02_CargaAnimal.CargAnimal,
[QHInd_12_03_Natalidad(%)].[Natalidad(%)]
FROM (QHInd_12_01_VacasTotales INNER JOIN QHInd_12_02_CargaAnimal ON
QHInd_12_01_VacasTotales.CodProd = QHInd_12_02_CargaAnimal.CodProd) INNER
JOIN [QHInd_12_03_Natalidad(%)] ON QHInd_12_02_CargaAnimal.CodProd =
[QHInd_12_03_Natalidad(%)].CodProd;

The table shows only 38 records with the columns CodProd, VacasTotales,
CargaAnimal, Natalidad(%). Nevertheless, I want all columns with 43 CodProd
records even though columns QHInd_12_02_CargaAnimal will be with 3 empty
records and QHInd_12_03_Natalidad% with 1 record. How I can do this?

Thank you for your help
Click to expand...

sebastico,

Your Issue:

If you want to preserve all records from QHInd_12_01_VacasTotales, try
using two LEFT JOIN operators instead of INNER JOIN operators.

Be prepared to deal with NULLs from the two other tables, possibly
using the Nz() function as necessary.



Possibly Unnecessary Comments:

I strongly recommend the use of Table aliases (which apply equally to
Queries) because of your very long Query names.

In the QBE, they can be set easily (and should update the SQL
automatically) by clicking on a Table or Query box, selecting
Properties, and entering a value.

This:

SELECT QHInd_12_01_VacasTotales.CodProd
,QHInd_12_01_VacasTotales.TotalVacas
,QHInd_12_02_CargaAnimal.CargAnimal
,[QHInd_12_03_Natalidad(%)].[Natalidad(%)]
FROM (QHInd_12_01_VacasTotales
INNER JOIN QHInd_12_02_CargaAnimal
ON QHInd_12_01_VacasTotales.CodProd =
QHInd_12_02_CargaAnimal.CodProd)
INNER JOIN
[QHInd_12_03_Natalidad(%)]
ON QHInd_12_02_CargaAnimal.CodProd =
[QHInd_12_03_Natalidad(%)].CodProd;

Changes To This:

SELECT VT1.CodProd
,VT1.TotalVacas
,CA1.CargAnimal
,N1.[Natalidad(%)]
FROM (QHInd_12_01_VacasTotales AS VT1
INNER JOIN
QHInd_12_02_CargaAnimal A CA1
ON VT1.CodProd = CA1.CodProd)
INNER JOIN
[QHInd_12_03_Natalidad(%)] AS N1
ON CA1.CodProd = N1.CodProd;

The second example is far cleaner and easier to read.


Sincerely,

Chris O.
 
S

sebastico

Chris O. Hello again. One more question.
How can I get more information on line about the use of Joins: INNER JOIN,
LEFT JOIN, RIGTH JOIN, OUTER JOIN,Semi-Join, Theta Join,setc. What is the
Nz() function, could you tell me the meaning and use?

Warm regards from Costa Rica.

sebastico said:
Hi Chris2. Excellent. Many Thanks
Click to expand...

sebastico,

You're welcome. :)


Sincerely,
 
C

Chris2

sebastico said:
Chris O. Hello again. One more question.
How can I get more information on line about the use of Joins: INNER JOIN,
LEFT JOIN, RIGTH JOIN, OUTER JOIN,Semi-Join, Theta Join,setc. What is the
Nz() function, could you tell me the meaning and use?

Warm regards from Costa Rica.
Click to expand...

sebastico,

INNER JOIN rows only rows in both tables that meet the ON conditions
listed.

LEFT JOIN retains all rows in the "left hand" table, and all rows in
the "right hand" table that meet the ON conditions listed. Every time
a "right hand" row has no ON match, NULLs get filled in in the
resultset for any "right hand" columns on the SELECT clause.

RIGHT JOIN retains all rows in the "right hand" table, and all rows in
the "left hand" table that meet the ON conditions listed. Every time
a "left hand" row has no ON match, NULLs get filled in in the
resultset for any "left hand" columns on the SELECT clause.


OUTER JOIN, aka FULL OUTER JOIN (not supported in JET) retains all
rows in from both tables, filling in Nulls for any column from either
table appearing on the SELECT clause when no match is found based on
the ON conditions. (And no, a UNIONed LEFT join and RIGHT join do not
make a FULL OUTER JOIN. Or at least they don't in Access 2000.)


Wondering what "left" and "right" handedness are?

Examples:

FROM Table1 INNER JOIN Table2

FROM Table1
INNER JOIN
Table2

Table1 is on the "left hand" side of the INNER JOIN operator, and
Table2 is on the "right hand" side of the INNER JOIN operator. Yes,
it is referring to which side of the join operator that you have
placed the table name.

In the second example, Table1 is still on the "left hand" side (even
though, visually speaking it is on top). Table2 is still on the
"right hand" side.


Theta joins are <, >, =>, etc., in other words, ON conditions that
don't use =.

FROM Table1 AS T1
INNER JOIN
Table2 AS T2
ON T1.MyKey => T2.MyKey

That's a Theta join.


FROM Table1 AS T1
INNER JOIN
Table1 AS T2
ON T1.MyKey => T2.MyKey

That's a self-join.


You can Google for more extensive defintions. (There are many.)


Nz() is just an Access specific function.

Nz(<expression>, <result if expression evaluates NULL>)

Nz(MyNumericolumn, 0)

The last line waits for a NULL in MyNumericColumn, and puts a 0 in the
output resultset in place of the NULL.



Sincerely,

Chris O.
 
S

sebastico

Hello Chris O. Thank you very much indeed.
Chris2 said:
sebastico,

INNER JOIN rows only rows in both tables that meet the ON conditions
listed.

LEFT JOIN retains all rows in the "left hand" table, and all rows in
the "right hand" table that meet the ON conditions listed. Every time
a "right hand" row has no ON match, NULLs get filled in in the
resultset for any "right hand" columns on the SELECT clause.

RIGHT JOIN retains all rows in the "right hand" table, and all rows in
the "left hand" table that meet the ON conditions listed. Every time
a "left hand" row has no ON match, NULLs get filled in in the
resultset for any "left hand" columns on the SELECT clause.


OUTER JOIN, aka FULL OUTER JOIN (not supported in JET) retains all
rows in from both tables, filling in Nulls for any column from either
table appearing on the SELECT clause when no match is found based on
the ON conditions. (And no, a UNIONed LEFT join and RIGHT join do not
make a FULL OUTER JOIN. Or at least they don't in Access 2000.)


Wondering what "left" and "right" handedness are?

Examples:

FROM Table1 INNER JOIN Table2

FROM Table1
INNER JOIN
Table2

Table1 is on the "left hand" side of the INNER JOIN operator, and
Table2 is on the "right hand" side of the INNER JOIN operator. Yes,
it is referring to which side of the join operator that you have
placed the table name.

In the second example, Table1 is still on the "left hand" side (even
though, visually speaking it is on top). Table2 is still on the
"right hand" side.


Theta joins are <, >, =>, etc., in other words, ON conditions that
don't use =.

FROM Table1 AS T1
INNER JOIN
Table2 AS T2
ON T1.MyKey => T2.MyKey

That's a Theta join.


FROM Table1 AS T1
INNER JOIN
Table1 AS T2
ON T1.MyKey => T2.MyKey

That's a self-join.


You can Google for more extensive defintions. (There are many.)


Nz() is just an Access specific function.

Nz(<expression>, <result if expression evaluates NULL>)

Nz(MyNumericolumn, 0)

The last line waits for a NULL in MyNumericColumn, and puts a 0 in the
output resultset in place of the NULL.



Sincerely,

Chris O.
Click to expand...
 
Top