This one has me stumped

[Bob Harding]

I've been struggling with this one and I wonder if anyone
can point me in the right direction ...

Pupils collect behaviour slips - call them red and green
(bad and good ...whatever)

I want to see how many slips of each type each pupil has.

I have a pupils table, a green slips table and a red lips
table.

Whenever I create any form or query combining these I
only get a small selection of pupils - those who have
both red and green. If they don't haveboth they don't
feature in the query results. I think my normalisation is
up the shoot!

The file can be found at:
www.bremhill.com/harding/bob/index.htm

I really am stuck with this and would really appreciate a
hand

Thanks folks

Bob

 

[RSGinCA]

Your normalization seems ok except that considering the data you are storing
there's really no reason to have separate tables for Red and Green slips.
Considering that you aren't storing any different info for Red and Green slips
there appears to be no reason not to combine them into a single table which
includes a code that indicates red or green.

However, having said that, you're dealing with the problem of INNER joins
versus OUTER joins. An INNER join requires a record to exist on both tables in
order to be included in the query. If you had a Red slip without a pupil it
would not be selected. Using an OUTER join you can specify that records
should be selected even though they don't have a match on the other side of the
relationship.

In your case, you are relating 3 separate tables. Since you are using an INNER
join, NO records will be selected for any pupil that doesn't have BOTH Red and
green slips.

Below is an SQL query that uses OUTER joins to select pupils that have EITHER
(or both) red slips or green slips.

SELECT *
FROM (Pupils LEFT JOIN [Green Slips] ON Pupils.[Pupil No]=[Green Slips].[Pupil
No]) LEFT JOIN [Red Slips] ON Pupils.[Pupil No]=[Red Slips].[Pupil No]
WHERE( ([Red Slips].[Slip No] Is Not NULL) AND ([Green Slips].[Slip No] Is Not
NULL));


Of course this query isn't doing exactly what you want, but it will probably
give you a starting point to do it... now that the necessary joins are
implemented.

Subject: This one has me stumped
From: "Bob Harding" (e-mail address removed)
Date: 2/5/2004 2:25 PM Pacific Standard Time
Message-id: <[email protected]>

I've been struggling with this one and I wonder if anyone
can point me in the right direction ...

Pupils collect behaviour slips - call them red and green
(bad and good ...whatever)

I want to see how many slips of each type each pupil has.

I have a pupils table, a green slips table and a red lips
table.

Whenever I create any form or query combining these I
only get a small selection of pupils - those who have
both red and green. If they don't haveboth they don't
feature in the query results. I think my normalisation is
up the shoot!

The file can be found at:
www.bremhill.com/harding/bob/index.htm

I really am stuck with this and would really appreciate a
hand

Thanks folks

Bob

Rick G

 

[Guest]

I didn't understand about inner and outer joins but your
explination seems spot on. I can see the meaning of the
SQl you've given me (though I could NEVER have come up
with anything like it).

This is a great help and has put me on the right track.
I've fiddled with the query and found that I can change
the 'join type' in the relationship in the query window
and it seems to give me the result I need.

I'm not sure what the SQl looks like but you've
certainally put me on the track to the right solution.

I also take your point about the different tables.

Thank you very much for your help - spot on. Green slip
for you certainally ;-)

Bob

-----Original Message-----
Your normalization seems ok except that considering the data you are storing
there's really no reason to have separate tables for Red and Green slips.
Considering that you aren't storing any different info for Red and Green slips
there appears to be no reason not to combine them into a single table which
includes a code that indicates red or green.

However, having said that, you're dealing with the problem of INNER joins
versus OUTER joins. An INNER join requires a record to exist on both tables in
order to be included in the query. If you had a Red slip without a pupil it
would not be selected. Using an OUTER join you can specify that records
should be selected even though they don't have a match on the other side of the
relationship.

In your case, you are relating 3 separate tables. Since you are using an INNER
join, NO records will be selected for any pupil that doesn't have BOTH Red and
green slips.

Below is an SQL query that uses OUTER joins to select pupils that have EITHER
(or both) red slips or green slips.

SELECT *
FROM (Pupils LEFT JOIN [Green Slips] ON Pupils.[Pupil No] =[Green Slips].[Pupil
No]) LEFT JOIN [Red Slips] ON Pupils.[Pupil No]=[Red Slips].[Pupil No]
WHERE( ([Red Slips].[Slip No] Is Not NULL) AND ([Green Slips].[Slip No] Is Not
NULL));


Of course this query isn't doing exactly what you want, but it will probably
give you a starting point to do it... now that the necessary joins are
implemented.

Subject: This one has me stumped
From: "Bob Harding" (e-mail address removed)
Date: 2/5/2004 2:25 PM Pacific Standard Time
Message-id: <[email protected]>

I've been struggling with this one and I wonder if anyone
can point me in the right direction ...

Pupils collect behaviour slips - call them red and green
(bad and good ...whatever)

I want to see how many slips of each type each pupil has.

I have a pupils table, a green slips table and a red lips
table.

Whenever I create any form or query combining these I
only get a small selection of pupils - those who have
both red and green. If they don't haveboth they don't
feature in the query results. I think my normalisation is
up the shoot!

The file can be found at:
www.bremhill.com/harding/bob/index.htm

I really am stuck with this and would really appreciate a
hand

Thanks folks

Bob

Rick G

.

 

[RSGinCA]

Great.

You probably CAN generate the SQL using Access's visual tools. It's not as hard
as it looks. I only include the SQL statement which I created by modeling from
an outer join query that I had handy. It probably looks like rocket science,
but it's not really as hard to create as it looks.

The hard part is actually knowing that you need a different kind of join.

So, let me take you through the VERY SIMPLE process of creating such a query
using Access's

visual tool. It really quite simple. All you do is 1) create a 'normal' query
(with inner

joins) and then 2) change the inner joins to outer joins. That 2nd step is very
easy.

So here it is, starting from scratch (Note: This is using Acess 2003.. I've
never used anything else, so I don't know if this applies to other releases,
but the important step is changing the join):

1) From the menu choose: New... Query...
2) Choose: Simple Query Wizard
3) In the 1st wizard window:
--Select the 3 tables (and the fields that you are interestedin in each table)
...
4) Continue on and Finish

Note: At this point you've created an ordinary query. It has inner joins and
will select all pupils (and only

those pupils) that have BOTH Red and Blue slips...

Now let's change the joins from INNER joins to OUTER joins...

Let's first change the join from the Pupil table to the Red Slips table.

5) Go into design mode with your query... you'll probably want to expand the
top half of the window a little bit because we're going to be working with the
relationships between the tables (the lines connecting the tables)

6) RIGHT-click on the relationship line between the Pupils table and the Red
Slips table...

If you manage to actually click the line (instead of the background), a context
menu should be displayed with 2 choises ( a) "join properties" b) "delete" )

7) Click on "join properties". This will display the "Join Properties" dialog.

8) Select Item 2 and click OK. ** THAT'S IT - YOU JUST DID IT! ** You changed
the join from the Pupil table to the Red Slips table from an INNER join to
RIGHT OUTER join (Note: I won't swear to that... maybe it's a LEFT outer
join...)

Now change the join from the Pupil table to the Green Slips table

9) Do steps 6, 7, and 8 again except do it by clicking on the line between the
Pupil table and the Green Slips table.


Note: At this point, the query is going to output a big line/record for EVERY
pupil - a line/record which includes fields for a red slip or a blue slip,
regardless of whether or not a pupil has a Red slip or Green slip, or both or
neither.

I don't know the technical terminology, but in effect the query is generating
'null data' in the portions of the output that comes from the red slips and/or
green slips for those pupils that don't really have a red slip or blue slip.


You can use that 'null' information for your purposes. Pupils that have
NEITHER a red slip nor a green slip will have both [Slip No] equal to Null.

10) Still in design mode, in the lower half of the screen... on the "Criteria"
line, enter "Is Not Null" in the 2 [Slip No] columns.

Note: At this point you've got a query that outputs ONLY those pupils that have
either a red slip or a green slip or both. Pupils that have neither will not
be output.


This is the end of Step 1 and the beginning of Step 2. You've got the basic
query that outputs what needs to be output. Now you need to figure out how to
refine it to get exactly what you want.

Subject: Re: This one has me stumped
From: (e-mail address removed)
Date: 2/5/2004 4:39 PM Pacific Standard Time
Message-id: <[email protected]>

I didn't understand about inner and outer joins but your
explination seems spot on. I can see the meaning of the
SQl you've given me (though I could NEVER have come up
with anything like it).

This is a great help and has put me on the right track.
I've fiddled with the query and found that I can change
the 'join type' in the relationship in the query window
and it seems to give me the result I need.

I'm not sure what the SQl looks like but you've
certainally put me on the track to the right solution.

I also take your point about the different tables.

Thank you very much for your help - spot on. Green slip
for you certainally ;-)

Bob

Rick G

 

[Dale]

If you check the SQL in the SQL view it should say
something similar to the code provided. The method you
found was the one I would have suggested to a novice who
is unfamiliar with SQL and works well in most situations.

-----Original Message-----
I didn't understand about inner and outer joins but your
explination seems spot on. I can see the meaning of the
SQl you've given me (though I could NEVER have come up
with anything like it).

This is a great help and has put me on the right track.
I've fiddled with the query and found that I can change
the 'join type' in the relationship in the query window
and it seems to give me the result I need.

I'm not sure what the SQl looks like but you've
certainally put me on the track to the right solution.

I also take your point about the different tables.

Thank you very much for your help - spot on. Green slip
for you certainally ;-)

Bob

-----Original Message-----
Your normalization seems ok except that considering the data you are storing
there's really no reason to have separate tables for

Red
and Green slips.

Considering that you aren't storing any different info for Red and Green slips
there appears to be no reason not to combine them into

a
single table which

includes a code that indicates red or green.

However, having said that, you're dealing with the problem of INNER joins
versus OUTER joins. An INNER join requires a record to exist on both tables in
order to be included in the query. If you had a Red slip without a pupil it
would not be selected. Using an OUTER join you can specify that records
should be selected even though they don't have a match on the other side of the
relationship.

In your case, you are relating 3 separate tables. Since you are using an INNER
join, NO records will be selected for any pupil that doesn't have BOTH Red and
green slips.

Below is an SQL query that uses OUTER joins to select pupils that have EITHER
(or both) red slips or green slips.

SELECT *
FROM (Pupils LEFT JOIN [Green Slips] ON Pupils.[Pupil

No]
=[Green Slips].[Pupil

No]) LEFT JOIN [Red Slips] ON Pupils.[Pupil No]=[Red Slips].[Pupil No]
WHERE( ([Red Slips].[Slip No] Is Not NULL) AND ([Green Slips].[Slip No] Is Not
NULL));


Of course this query isn't doing exactly what you want, but it will probably
give you a starting point to do it... now that the necessary joins are
implemented.

Subject: This one has me stumped
From: "Bob Harding" (e-mail address removed)
Date: 2/5/2004 2:25 PM Pacific Standard Time
Message-id: <[email protected]>

I've been struggling with this one and I wonder if anyone
can point me in the right direction ...

Pupils collect behaviour slips - call them red and green
(bad and good ...whatever)

I want to see how many slips of each type each pupil has.

I have a pupils table, a green slips table and a red lips
table.

Whenever I create any form or query combining these I
only get a small selection of pupils - those who have
both red and green. If they don't haveboth they don't
feature in the query results. I think my

normalisation

is

appreciate

a

Rick G

.

.