Looks easy but is MUCH harder

[BremhillBob]

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

Pupils (in a school) 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 slips 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 have
both 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 - I've spent
hours on it

Thanks folks

Bob

 

[Arvin Meyer]

Quick response without even looking at you code ... you'll probably figure
it out. You are AND ing in your query instead of OR ing. Does that make
sense? Look at the SQL and wherever it shows an "AND" in the criteria (not
the Joins) change it to an "OR"
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Tony]

Hi Bob,

I would create a combined pupil table (Red and Green) that
holds both these values. I then would create a table
tblCardtype with just the Red and Green values. In your
form you can refer to tblCardType within a combo box. Red
= 1 Green =2. This way when you run your query it's
pulling from the same table.

HTH

 

[Van T. Dinh]

Yes, I agree with Tony's suggestion. It should be 1 Table for Slips rather
than 2.

In the mean time, try changing the SQL String of the Query "Green Slips
Totals" to:

SELECT [Green Slips].[Pupil No], Pupils.[First Name], Pupils.Surname,
[Green Slips].Subject, Count([Green Slips].[Slip No]) AS [Count Slips]
FROM Pupils LEFT JOIN [Green Slips]
ON Pupils.[Pupil No] = [Green Slips].[Pupil No]
GROUP BY [Green Slips].[Pupil No], Pupils.[First Name],
Pupils.Surname, [Green Slips].Subject
ORDER BY [Green Slips].[Pupil No];