How do you show records with no matching values?

[DAHMB]

I am not sure if I worded my question correctly. But what I want to do is run a report from two related tables as follows:

The first table is called Category - it has one field, called Category, that is also the primary key. There are 18 categries listed in the table.

The second table is called CaseManagement, with no primary key, and multiple fields including Category which is taken from the related Category table.

They are related with a one to many link from Category to CaseManagement.

In my report I want to list all the categories from the Category table followed by the number of cases in each category from the CaseManagement table.

My problem is that if a categoy does not have any related data in the CaseManagement table, the category is not listed in my report. I want the report to include every category from the Cattegory table and show a 0 for it if it does not have any related data in the CaseManagement table.

Thanks for any help
Dan

[wvmitchell]

SELECT Category.Category, Count(CaseManagement.Category) AS CountOfCategory
FROM Category LEFT JOIN CaseManagement ON Category.Category = CaseManagement.Category
GROUP BY Category.Category;

[DAHMB]

SELECT Category.Category, Count(CaseManagement.Category) AS CountOfCategory
FROM Category LEFT JOIN CaseManagement ON Category.Category = CaseManagement.Category
GROUP BY Category.Category;

Thankyou for your response but where do I place this code? Please excuse my ignorance.

Thank you for your help.
Dan

[wvmitchell]

From the Database Window, select the Queries tab.

Click New, and at the New Query dialog box select Design View and then click OK.

At the Show Table dialog box, click Close.

From the Access menu bar, click View ~ SQL View and paste the code into that window.

From the Access menu bar, click View ~ Design View and you'll see the query in the graphical design window. Save the query.

When you run the query you'll see the results.

[DAHMB]

From the Database Window, select the Queries tab.

Click New, and at the New Query dialog box select Design View and then click OK.

At the Show Table dialog box, click Close.

From the Access menu bar, click View ~ SQL View and paste the code into that window.

From the Access menu bar, click View ~ Design View and you'll see the query in the graphical design window. Save the query.

When you run the query you'll see the results.

Thank you very much for you reply but I am geting a Syntexc error in Join Operation. I have typed the code as follows the table names are different because I was trying to be as clear as possible on my original post but these are the actual names:

SELECT tblOffenses.Offenses, Count(tblCaseManagement.Offense) AS CountOfOffense FROM Offense LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense GROUP BY tblOffenses.Offenses;

Thank you again for your time and help.
Dan

[wvmitchell]

You have a typo - you wrote

SELECT tblOffenses.Offenses, Count(tblCaseManagement.Offense) AS CountOfOffense FROM Offense LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense GROUP BY tblOffenses.Offenses;

but it should be

SELECT tblOffenses.Offenses, Count(tblCaseManagement.Offense) AS CountOfOffense FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense GROUP BY tblOffenses.Offenses;

[DAHMB]

You have a typo - you wrote

SELECT tblOffenses.Offenses, Count(tblCaseManagement.Offense) AS CountOfOffense FROM Offense LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense GROUP BY tblOffenses.Offenses;

but it should be

SELECT tblOffenses.Offenses, Count(tblCaseManagement.Offense) AS CountOfOffense FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense GROUP BY tblOffenses.Offenses;

Thank you so very much!!! I have been racking my brains for a week on this! I reealy appreciate it. I will try it tommorrow when I go to work and let you know how I made out. Thank you for your help please check back for me in case I have any more issues.
Dan

[DAHMB]

OK Great now I understand how to do this. But now I have a form where I list twwo dates to limit a search between. Now if I add the search line it gives me a resuly with just the fields with data again. I need the fields from tblOffense to show with 0 in the limited search as well. my sql with the date search looks like this:

SELECT tblOffenses.Offenses, Count(tblCaseManagement.Offense) AS CountOfOffense
FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense
GROUP BY tblOffenses.Offenses
Having (((Count(tblCaseManagement.Offense)) Between [Forms]![frmIntakeSearch]![Date1] And [Forms]![frmIntakeSearch]![Date2]));


What am I doing wrong?
Thanks again
Dan

[DAHMB]

Sorry I posted the wrong sql it looks like this:

SELECT tblOffenses.Offenses, Count(tblCaseManagement.Offense) AS CountOfOffense
FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense
WHERE (((tblCaseManagement.DateOpened) Between [Forms]![frmIntakeSearch]![Date1] And [Forms]![frmIntakeSearch]![Date2]))
GROUP BY tblOffenses.Offenses;

[wvmitchell]

Dan,

I don't fully understand the logical relationship between your two tables, but when you apply criteria to the tblCaseManagement table, the effect is that you're making it act like an INNER JOIN, because you are requiring a matching record in tblCaseManagement.

In any event, can you try this & see if it gives you the desired results:

SELECT tblOffenses.Offenses, Count(tblCaseManagement.Offense) AS CountOfOffense
FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense
WHERE (((tblCaseManagement.DateOpened) Between [Forms]![frmIntakeSearch]![Date1] And [Forms]![frmIntakeSearch]![Date2]))
OR (tblCaseManagement.DateOpened) IS NULL
GROUP BY tblOffenses.Offenses;

[DAHMB]

Thank you it did not work correctly it did not show every record and there was no method to the ones it did not show. What I did was create two queries as below:
the first:

SELECT tblOffenses.Offenses, tblCaseManagement.DateOpened
FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense
WHERE (((tblCaseManagement.DateOpened) Between [Forms]![frmIntakeSearch]![Date1] And [Forms]![frmIntakeSearch]![Date2]));

the second:

SELECT tblOffenses.Offenses, Count(qryMonthlyIntakeSUB.Offenses) AS CountOfOffenses
FROM tblOffenses LEFT JOIN qryMonthlyIntakeSUB ON tblOffenses.Offenses = qryMonthlyIntakeSUB.Offenses
GROUP BY tblOffenses.Offenses;

Do you thionk I could do just one query with the above in a Union query? and would I need anything to be different if I did?

Dan

[wvmitchell]

Dan, is this what you want in the results:

Do you always want to see every record from tblOffenses.Offenses, along with a count of tblCaseManagement.Offense records that fall within the date range?

If that is the case, you would use two queries. The first query would filter for the records that fall inside your date range. Let's call it qryCaseFilter:

SELECT tblCaseManagement.Offense
FROM tblCaseManagement
WHERE (((tblCaseManagement.DateOpened) Between [Forms]![frmIntakeSearch]![Date1] And [Forms]![frmIntakeSearch]![Date2]));

The second query would be similar to my original solution, except that we use qryCaseFilter instead of the full table, something like this:

SELECT tblOffenses.Offenses, Count(qryCaseFilter.Offense) AS CountOfOffense FROM tblOffenses LEFT JOIN qryCaseFilter ON tblOffenses.Offenses = qryCaseFilter.Offense
GROUP BY tblOffenses.Offenses;

[DAHMB]

Yes thank you that is exactly what I was looking for. Thankyou.

Since I am here I have another question on reports. I will ask it here post it normally because I am not sure if that is a required protocal on this site..

I have a report based on a query, in the report I have three columns

Column1
=Sum(Abs([qryMonthlyReport]![ClearanceCode] Like "*"))

Column2
=Sum(Abs([qryMonthlyReport]![ClearanceCode]="O"))

Column3
=Sum(Abs([qryMonthlyReport]![ClearanceCode]<>"O"))

My problem is I also want to limit the columns based on a date range in a form such as:
Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2]

I just can't figure out the correct way I keep getting errors.
Thanks
Dan

[wvmitchell]

Hi Dan, glad I could help.

As far as your new question, I'm also fairly new to this forum, but I think if you start a new thread you'll get more views - others will see the highlighted "new post" tag and be more likely to read & respond. There's usually more than one way to solve a given problem in Access, maybe somebody else might have a better idea.

Back to your new question, the general approach could be like this:

Column2
=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2],1,0)))

[DAHMB]

Hi Dan, glad I could help.

As far as your new question, I'm also fairly new to this forum, but I think if you start a new thread you'll get more views - others will see the highlighted "new post" tag and be more likely to read & respond. There's usually more than one way to solve a given problem in Access, maybe somebody else might have a better idea.

Back to your new question, the general approach could be like this:

Column2
=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2],1,0)))

It won't work, it won't even let me exit out of the expression box. Any ideas?
Dan

[wvmitchell]

I misplaced a parenthesis in the IIf statement:

Column2
=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2],1,0)))

should be

Column2
=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2]),1,0))

[DAHMB]

I misplaced a parenthesis in the IIf statement:

Column2
=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2],1,0)))

should be

Column2
=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2]),1,0))

That was it Thankyou!!!!!!!
Dan