SQL JOIN simplifying

[cjg.groups]

Hello, I am trying to get the effect of a LEFT JOIN, but my method
seems unnecessarily complex. Please help if there is a better way to
do this.

The effect I need, from Access help, is:
"Left outer joins include all of the records from the first (left) of
two tables, even if there are no matching values for records in the
second (right) table."

The data looks like:
ID Value YrHr
1 200 Year
1 300 Year
2 20 Hour
6 400 Year
6 10 Hour
6 20 Hour
The Value field was overloaded so YrHr indicates what type of data it
contains. Bad, I know.

The query must split Years and Hours into different fields. It will
also SUM all Values with the same ID. But, data in the displayed
fields must line up horizontally with the ID.

The end result should look like:
ID Year Hr
1 500
2 20
6 400 30
ID 1 does not have data for Month, so a blank space is left in that
cell.

Currently, I am using LEFT JOIN on nested SQL queries, each with their
own similar-but-different WHERE clause. The ID list is the left most
query in the JOIN. ie:

SELECT DISTINCT table.ID, tmp1.Year, tmp2.Hour
FROM table LEFT JOIN
[SELECT SUM(Value) As Year FROM table WHERE YrHr = "Year" AND
<conditionals> GROUP BY ID]. AS tmp1 ON table.ID = tmp1.ID
WHERE <conditionals>;
The above actually contains another join to create the Hour field, but
it would muddy my example and I haven't even created it yet.

Is there a way to not use complete nested SQL statements, but simply
apply the "WHERE YrHr" conditional to each field in a SELECT
statement? Is there a better way to do this? Thank you.

 

[Dale Fye]

What you really need is a Cross-tab query.

1. Start out by creating a new query, add your table to the query.

2. Add the ID, YrHr, and Value fields to the grid

3. Change the query type to a cross-tab query.

4. In the Row of the query grid labeled Total, set ID and YrHr columns to
"Group By" and the [Value] column to Sum

5. In the Row of the query grid labeled CrossTab, set the ID column to "Row
Heading", the YrHr column to "Column Heading", and the [Value] column to Value

Run the query.

You can also do it as a SELECT query, something like:

SELECT ID,
SUM(IIF([YrHR] = "Year", [Value], 0)) as [Year],
SUM(IIF([YrHr] = "Hour", [Value], 0)) as [Hour]
FROM yourTable
GROUP BY ID
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[cjg.groups]

Brilliant, thanks!! I would have never known or thought to look for
the CROSSTAB solution.

Here's how the cross-tab looks in SQL (since I'm afraid of the Access
query designer):

TRANSFORM SUM(Value) AS SumOfValue
SELECT ID
FROM table
WHERE <conditionals>
GROUP BY ID
PIVOT YrHr;

Amazingly simple. The above doesn't yet let me take ID as a parameter
passed by code, but I'll work on that.

Your second solution using IIf is equally brilliant, but is it as
"ethical" to use VB in the SELECT? I do that often, honestly. That
query shows 0 in the cells which should have no data, because of the
IIf. Some tweaks will resolve that.

If you have time, can you briefly explain just how the SUM(IIf( ))
works in SELECT? When preparing the query for view/use, does it read
the source table one row at a time and keep running sums in each query
cell? I can more use from this concept when I fully understand it.

Thanks again for providing two great solutions. I've learned a lot
from this.

What you really need is a Cross-tab query.

1.  Start out by creating a new query, add your table to the query.

2.  Add the ID, YrHr, and Value fields to the grid

3.  Change the query type to a cross-tab query.

4.  In the Row of the query grid labeled Total, set ID and YrHr columnsto
"Group By" and the [Value] column to Sum

5.  In the Row of the query grid labeled CrossTab, set the ID column to"Row
Heading", the YrHr column to "Column Heading", and the [Value] column to Value

Run the query.

You can also do it as a SELECT query, something like:

SELECT ID,
            SUM(IIF([YrHR] = "Year", [Value], 0)) as [Year],
            SUM(IIF([YrHr] = "Hour", [Value], 0)) as [Hour]
FROM yourTable
GROUP BY ID
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hello, I am trying to get the effect of a LEFT JOIN, but my method
seems unnecessarily complex.  Please help if there is a better way to
do this.

The effect I need, from Access help, is:
"Left outer joins include all of the records from the first (left) of
two tables, even if there are no matching values for records in the
second (right) table."

The data looks like:
ID  Value  YrHr
1    200    Year
1    300    Year
2    20      Hour
6    400    Year
6    10      Hour
6    20      Hour
The Value field was overloaded so YrHr indicates what type of data it
contains.  Bad, I know.

The query must split Years and Hours into different fields.  It will
also SUM all Values with the same ID.  But, data in the displayed
fields must line up horizontally with the ID.

The end result should look like:
ID   Year   Hr
1    500
2             20
6    400    30
ID 1 does not have data for Month, so a blank space is left in that
cell.

Currently, I am using LEFT JOIN on nested SQL queries, each with their
own similar-but-different WHERE clause.  The ID list is the left most
query in the JOIN.  ie:

SELECT DISTINCT table.ID, tmp1.Year, tmp2.Hour
FROM table LEFT JOIN
[SELECT SUM(Value) As Year FROM table WHERE YrHr = "Year" AND
<conditionals> GROUP BY ID]. AS tmp1 ON table.ID = tmp1.ID
WHERE <conditionals>;
The above actually contains another join to create the Hour field, but
it would muddy my example and I haven't even created it yet.

Is there a way to not use complete nested SQL statements, but simply
apply the "WHERE YrHr" conditional to each field in a SELECT
statement?  Is there a better way to do this?  Thank you.

 

[cjg.groups]

In reality, I am not using a SUM like my simplified example showed. I
am using AVG to get the average. Using AVG(IIf( )) does not return
the correct result. It divides the sum of all Values which match the
YrHr criteria, by the total number of records with the same ID. Maybe
you could manually do SUM(IIf( ))/CountIf( ) to get the proper counts,
if Countif() even exists in Access (it's in Excel).

I'm not too worried about this because I will just use the cross-tab,
which averages properly and doesn't show 0 in cells which should have
no data.

Thanks again.

Brilliant, thanks!!  I would have never known or thought to look for
the CROSSTAB solution.

Here's how the cross-tab looks in SQL (since I'm afraid of the Access
query designer):

TRANSFORM SUM(Value) AS SumOfValue
SELECT ID
FROM table
WHERE <conditionals>
GROUP BY ID
PIVOT YrHr;

Amazingly simple.  The above doesn't yet let me take ID as a parameter
passed by code, but I'll work on that.

Your second solution using IIf is equally brilliant, but is it as
"ethical" to use VB in the SELECT?  I do that often, honestly.  That
query shows 0 in the cells which should have no data, because of the
IIf.  Some tweaks will resolve that.

If you have time, can you briefly explain just how the SUM(IIf( ))
works in SELECT?  When preparing the query for view/use, does it read
the source table one row at a time and keep running sums in each query
cell?  I can more use from this concept when I fully understand it.

Thanks again for providing two great solutions.  I've learned a lot
from this.

What you really need is a Cross-tab query.

1.  Start out by creating a new query, add your table to the query.

2.  Add the ID, YrHr, and Value fields to the grid

3.  Change the query type to a cross-tab query.

4.  In the Row of the query grid labeled Total, set ID and YrHr columns to
"Group By" and the [Value] column to Sum

5.  In the Row of the query grid labeled CrossTab, set the ID column to "Row
Heading", the YrHr column to "Column Heading", and the [Value] column to Value

Run the query.

You can also do it as a SELECT query, something like:

SELECT ID,
            SUM(IIF([YrHR] = "Year", [Value], 0)) as [Year],
            SUM(IIF([YrHr] = "Hour", [Value], 0)) as [Hour]
FROM yourTable
GROUP BY ID

email address is invalid
Please reply to newsgroup only.

Hello, I am trying to get the effect of a LEFT JOIN, but my method
seems unnecessarily complex.  Please help if there is a better way to
do this.
The effect I need, from Access help, is:
"Left outer joins include all of the records from the first (left) of
two tables, even if there are no matching values for records in the
second (right) table."
The data looks like:
ID  Value  YrHr
1    200    Year
1    300    Year
2    20      Hour
6    400    Year
6    10      Hour
6    20      Hour
The Value field was overloaded so YrHr indicates what type of data it
contains.  Bad, I know.
The query must split Years and Hours into different fields.  It will
also SUM all Values with the same ID.  But, data in the displayed
fields must line up horizontally with the ID.
The end result should look like:
ID   Year   Hr
1    500
2             20
6    400    30
ID 1 does not have data for Month, so a blank space is left in that
cell.
Currently, I am using LEFT JOIN on nested SQL queries, each with their
own similar-but-different WHERE clause.  The ID list is the left most
query in the JOIN.  ie:
SELECT DISTINCT table.ID, tmp1.Year, tmp2.Hour
FROM table LEFT JOIN
[SELECT SUM(Value) As Year FROM table WHERE YrHr = "Year" AND
<conditionals> GROUP BY ID]. AS tmp1 ON table.ID = tmp1.ID
WHERE <conditionals>;
The above actually contains another join to create the Hour field, but
it would muddy my example and I haven't even created it yet.
Is there a way to not use complete nested SQL statements, but simply
apply the "WHERE YrHr" conditional to each field in a SELECT
statement?  Is there a better way to do this?  Thank you.

 

[cjg.groups]

My query challenge goes deeper as it scales outward. In reality, I
have multiple groups of Year/Hour data pairs.

The end result must actually look like this:
ID YearA HrA YearB HrB
1 500 40
2 20 100 90
6 400 30

I've created a separate cross-tab query for the Year/Hr A group and
the B group. I've also created a query to show DISTINCT IDs. Then, I
created a master query to INNER JOIN them all together. Problem is,
ID 6 in the above example doesn't show up because it has no data for
the B group.

Then I tried to LEFT JOIN all of the cross-tab queries to the ID
query, so it will always show all IDs even when they have no data.
Unfortunately, "an INNER JOIN may not be nested inside a LEFT JOIN".

I thought I could use:
WHERE ID IN [SELECT ID FROM table WHERE <conditionals>]
to force the queries to display all rows, even with blank data.

How can I force these queries to show every row? Thanks.

In reality, I am not using a SUM like my simplified example showed.  I
am using AVG to get the average.  Using AVG(IIf( )) does not return
the correct result.  It divides the sum of all Values which match the
YrHr criteria, by the total number of records with the same ID.  Maybe
you could manually do SUM(IIf( ))/CountIf( ) to get the proper counts,
if Countif() even exists in Access (it's in Excel).

I'm not too worried about this because I will just use the cross-tab,
which averages properly and doesn't show 0 in cells which should have
no data.

Thanks again.

Brilliant, thanks!!  I would have never known or thought to look for
the CROSSTAB solution.

Here's how the cross-tab looks in SQL (since I'm afraid of the Access
query designer):

TRANSFORM SUM(Value) AS SumOfValue
SELECT ID
FROM table
WHERE <conditionals>
GROUP BY ID
PIVOT YrHr;

Amazingly simple.  The above doesn't yet let me take ID as a parameter
passed by code, but I'll work on that.

Your second solution using IIf is equally brilliant, but is it as
"ethical" to use VB in the SELECT?  I do that often, honestly.  That
query shows 0 in the cells which should have no data, because of the
IIf.  Some tweaks will resolve that.

If you have time, can you briefly explain just how the SUM(IIf( ))
works in SELECT?  When preparing the query for view/use, does it read
the source table one row at a time and keep running sums in each query
cell?  I can more use from this concept when I fully understand it.

Thanks again for providing two great solutions.  I've learned a lot
from this.

What you really need is a Cross-tab query.
1.  Start out by creating a new query, add your table to the query.
2.  Add the ID, YrHr, and Value fields to the grid
3.  Change the query type to a cross-tab query.
4.  In the Row of the query grid labeled Total, set ID and YrHr columns to
"Group By" and the [Value] column to Sum
5.  In the Row of the query grid labeled CrossTab, set the ID column to "Row
Heading", the YrHr column to "Column Heading", and the [Value] columnto Value
Run the query.
You can also do it as a SELECT query, something like:
SELECT ID,
            SUM(IIF([YrHR] = "Year", [Value], 0)) as [Year],
            SUM(IIF([YrHr] = "Hour", [Value], 0)) as [Hour]
FROM yourTable
GROUP BY ID
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
:
Hello, I am trying to get the effect of a LEFT JOIN, but my method
seems unnecessarily complex.  Please help if there is a better way to
do this.
The effect I need, from Access help, is:
"Left outer joins include all of the records from the first (left) of
two tables, even if there are no matching values for records in the
second (right) table."
The data looks like:
ID  Value  YrHr
1    200    Year
1    300    Year
2    20      Hour
6    400    Year
6    10      Hour
6    20      Hour
The Value field was overloaded so YrHr indicates what type of data it
contains.  Bad, I know.
The query must split Years and Hours into different fields.  It will
also SUM all Values with the same ID.  But, data in the displayed
fields must line up horizontally with the ID.
The end result should look like:
ID   Year   Hr
1    500
2             20
6    400    30
ID 1 does not have data for Month, so a blank space is left in that
cell.
Currently, I am using LEFT JOIN on nested SQL queries, each with their
own similar-but-different WHERE clause.  The ID list is the left most
query in the JOIN.  ie:
SELECT DISTINCT table.ID, tmp1.Year, tmp2.Hour
FROM table LEFT JOIN
[SELECT SUM(Value) As Year FROM table WHERE YrHr = "Year" AND
<conditionals> GROUP BY ID]. AS tmp1 ON table.ID = tmp1.ID
WHERE <conditionals>;
The above actually contains another join to create the Hour field, but
it would muddy my example and I haven't even created it yet.
Is there a way to not use complete nested SQL statements, but simply
apply the "WHERE YrHr" conditional to each field in a SELECT
statement?  Is there a better way to do this?  Thank you.

 

[Dale Fye]

You can concatenate fields for your column header designator:

The SQL might look like:

TRANSFORM SUM(Value) AS SumOfValue
SELECT ID
FROM table
WHERE <conditionals>
GROUP BY ID
PIVOT [YrHr] & [GroupField]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

My query challenge goes deeper as it scales outward. In reality, I
have multiple groups of Year/Hour data pairs.

The end result must actually look like this:
ID YearA HrA YearB HrB
1 500 40
2 20 100 90
6 400 30

I've created a separate cross-tab query for the Year/Hr A group and
the B group. I've also created a query to show DISTINCT IDs. Then, I
created a master query to INNER JOIN them all together. Problem is,
ID 6 in the above example doesn't show up because it has no data for
the B group.

Then I tried to LEFT JOIN all of the cross-tab queries to the ID
query, so it will always show all IDs even when they have no data.
Unfortunately, "an INNER JOIN may not be nested inside a LEFT JOIN".

I thought I could use:
WHERE ID IN [SELECT ID FROM table WHERE <conditionals>]
to force the queries to display all rows, even with blank data.

How can I force these queries to show every row? Thanks.

In reality, I am not using a SUM like my simplified example showed. I
am using AVG to get the average. Using AVG(IIf( )) does not return
the correct result. It divides the sum of all Values which match the
YrHr criteria, by the total number of records with the same ID. Maybe
you could manually do SUM(IIf( ))/CountIf( ) to get the proper counts,
if Countif() even exists in Access (it's in Excel).

I'm not too worried about this because I will just use the cross-tab,
which averages properly and doesn't show 0 in cells which should have
no data.

Thanks again.

Brilliant, thanks!! I would have never known or thought to look for
the CROSSTAB solution.

Here's how the cross-tab looks in SQL (since I'm afraid of the Access
query designer):

TRANSFORM SUM(Value) AS SumOfValue
SELECT ID
FROM table
WHERE <conditionals>
GROUP BY ID
PIVOT YrHr;

Amazingly simple. The above doesn't yet let me take ID as a parameter
passed by code, but I'll work on that.

Your second solution using IIf is equally brilliant, but is it as
"ethical" to use VB in the SELECT? I do that often, honestly. That
query shows 0 in the cells which should have no data, because of the
IIf. Some tweaks will resolve that.

If you have time, can you briefly explain just how the SUM(IIf( ))
works in SELECT? When preparing the query for view/use, does it read
the source table one row at a time and keep running sums in each query
cell? I can more use from this concept when I fully understand it.

Thanks again for providing two great solutions. I've learned a lot
from this.

On Feb 10, 4:57 pm, Dale Fye <[email protected]> wrote:

What you really need is a Cross-tab query.

1. Start out by creating a new query, add your table to the query.

2. Add the ID, YrHr, and Value fields to the grid

3. Change the query type to a cross-tab query.

4. In the Row of the query grid labeled Total, set ID and YrHr columns to
"Group By" and the [Value] column to Sum

5. In the Row of the query grid labeled CrossTab, set the ID column to "Row
Heading", the YrHr column to "Column Heading", and the [Value] column to Value

Run the query.

You can also do it as a SELECT query, something like:

SELECT ID,
SUM(IIF([YrHR] = "Year", [Value], 0)) as [Year],
SUM(IIF([YrHr] = "Hour", [Value], 0)) as [Hour]
FROM yourTable
GROUP BY ID

email address is invalid
Please reply to newsgroup only.

:
Hello, I am trying to get the effect of a LEFT JOIN, but my method
seems unnecessarily complex. Please help if there is a better way to
do this.

The effect I need, from Access help, is:
"Left outer joins include all of the records from the first (left) of
two tables, even if there are no matching values for records in the
second (right) table."

The data looks like:
ID Value YrHr
1 200 Year
1 300 Year
2 20 Hour
6 400 Year
6 10 Hour
6 20 Hour
The Value field was overloaded so YrHr indicates what type of data it
contains. Bad, I know.

The query must split Years and Hours into different fields. It will
also SUM all Values with the same ID. But, data in the displayed
fields must line up horizontally with the ID.

The end result should look like:
ID Year Hr
1 500
2 20
6 400 30
ID 1 does not have data for Month, so a blank space is left in that
cell.

Currently, I am using LEFT JOIN on nested SQL queries, each with their
own similar-but-different WHERE clause. The ID list is the left most
query in the JOIN. ie:

SELECT DISTINCT table.ID, tmp1.Year, tmp2.Hour
FROM table LEFT JOIN
[SELECT SUM(Value) As Year FROM table WHERE YrHr = "Year" AND
<conditionals> GROUP BY ID]. AS tmp1 ON table.ID = tmp1.ID
WHERE <conditionals>;
The above actually contains another join to create the Hour field, but
it would muddy my example and I haven't even created it yet.

Is there a way to not use complete nested SQL statements, but simply
apply the "WHERE YrHr" conditional to each field in a SELECT
statement? Is there a better way to do this? Thank you.