Want unqoted records to show. 1

[lars7]

Hi guys,
My recruitment department has asked me to add a form to there database that will show were the applicants heard about the vacancies but I have run into a problem.
I have a sub form that's default view is Continuous Forms linked to the main form by a reference number.
The sub form has a maximum of 14 records it can open up to and that are what I would like it to do every time like this:


Media Count

Herald/Sunday Herald 11
Daily Record/Sunday Mail 2
Metro 13
Word of Mouth 4
Local Paper 4
Job Centre 16
Vacancy Bulletin 7
Journal 8
Journal Websites 9
NHS IntrAnet 14
Referredby Manager 11
SHOW 30
Campaign 13
Open Day 14


my problem is that not all media types are are always quoted by the callers so some of the vacancies would look like this:

Vacancy Bulletin 7
Journal 8
Journal Websites 9
NHS IntrAnet 14
Referredby Manager 11
SHOW 30
Campaign 13
Open Day 14


when I would like it to look like this:

Herald/Sunday Herald 0
Daily Record/Sunday Mail 0
Metro 0
Word of Mouth 0
Local Paper 0
Job Centre 0
Vacancy Bulletin 7
Journal 8
Journal Websites 9
NHS IntrAnet 14
Referredby Manager 11
SHOW 30
Campaign 13
Open Day 14

my Sql is:

SELECT Enquire.[Job no] AS RefNo, Ads.[Ad Id], Ads.[Ad Name] AS Media, Count(Ads.[Ad Name]) AS [Count] FROM Enquire INNER JOIN Ads ON Enquire.Media=Ads.[Ad Id] GROUP BY Enquire.[Job no], Ads.[Ad Id], Ads.[Ad Name] ORDER BY Enquire.[Job no] DESC;

I have tried right joins to try and force the unquoted media types to appear with no luck. Does anyone have any ideas that I may try or an alternative. Thanks in advance.

 

[PHV]

Replace the INNER JOIN with a LEFT JOIN ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi Ph,
It got this for the first one:

RefNo Ad Id Media Count
44444 0
44444 1 Herald/Sunday Herald 16
44444 2 Daily Record/Sunday Mail 3
44444 3 Metro 1
44444 4 Word of Mouth 46
44444 6 Job Centre 13
44444 7 Vacancy Bulletin 4
44444 8 Journal 1
44444 9 Journal Websites 69
44444 10 NHS IntrAnet 4
44444 12 SHOW 130

still missing a few and the blank one appeared.
Here is a samplefrom the SQL view:

RefNo Ad Id Media Count
8456 12 SHOW 2
8454 12 SHOW 2
8451 12 SHOW 1
8450 4 Word of Mouth 1
8450 7 Vacancy Bulletin 1
8450 12 SHOW 6
8447 7 Vacancy Bulletin 1
8447 12 SHOW 2
8446 4 Word of Mouth 1
8446 7 Vacancy Bulletin 1
8446 12 SHOW 6

from this SQL:

SELECT Enquire.[Job no] AS RefNo, Ads.[Ad Id], Ads.[Ad Name] AS Media, Count(Ads.[Ad Name]) AS [Count]
FROM Enquire LEFT JOIN Ads ON Enquire.Media = Ads.[Ad Id]
GROUP BY Enquire.[Job no], Ads.[Ad Id], Ads.[Ad Name]
ORDER BY Enquire.[Job no] DESC;

 

[PHV]

Have you a table holding the 14 medias ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Yes it's called Ads, it looks like this:

Ad Id Ad Name
1 Herald/Sunday Herald
2 Daily Record/Sunday Mail
3 Metro
4 Word of Mouth
5 Local Paper
6 Job Centre
7 Vacancy Bulletin
8 Journal
9 Journal Websites
10 NHS Intranet
11 Referredby Manager
12 SHOW
13 Campaign
14 Open Day

 

[PHV]

Perhaps this ?
SELECT Enquire.[Job no] AS RefNo, Ads.[Ad Id], Ads.[Ad Name] AS Media, Count(Enquire.Media) AS [Count]
FROM Enquire RIGHT JOIN Ads ON Enquire.Media=Ads.[Ad Id]
GROUP BY Enquire.[Job no], Ads.[Ad Id], Ads.[Ad Name]
ORDER BY Enquire.[Job no] DESC, Ads.[Ad Id] ASC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi again,
No change, here is a sample:

RefNo Ad Id Media Count
8447 7 Vacancy Bulletin 1
8447 12 Show 2
8446 4 Word of Mouth 1
8446 7 Vacancy Bulletin 1
8446 12 Show 6
8445 12 Show 8
8444 12 Show 7
8443 12 Show 1
8441 12 Show 6
8438 7 Vacancy Bulletin 1
8438 12 Show 19
8435 7 Vacancy Bulletin 1
8435 12 Show 1

 

[PHV]

Could you please post your actual SQL code giving this result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Sure here it is:

SELECT Enquire.[Job no] AS RefNo, Ads.[Ad Id], Ads.[Ad Name] AS Media, Count(Enquire.Media) AS [Count]
FROM Enquire RIGHT JOIN Ads ON Enquire.Media = Ads.[Ad Id]
GROUP BY Enquire.[Job no], Ads.[Ad Id], Ads.[Ad Name]
ORDER BY Enquire.[Job no] DESC , Ads.[Ad Id];

 

[PHV]

And what about this ?
SELECT E.RefNo, Ads.[Ad Id], Ads.[Ad Name] AS Media
, (SELECT Count(*) FROM Enquire WHERE [Job no]=E.RefNo AND Media=Ads.[Ad Id]) AS [Count]
FROM Ads, (SELECT DISTINCT [Job no] AS RefNo FROM Enquire) AS E
ORDER BY E.RefNo DESC , Ads.[Ad Id];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi Ph,
Something very strange is happening.
When I copy and paste your code into the SQL it looks great, I have zeros where I want them, but as soon as I save the SQL and try to view the form I start getting an "invalid bracket of name Select destinct [job no'" and I cant get to see it again unless I delete it and recopy and past from your thread. I wont alow me to copy the SQL when I can see it either, weird.

 

[PHV]

Known bug of the query grid ...
Create a query named, say, qryRefNo:
SELECT DISTINCT [Job no] AS RefNo FROM Enquire

And now your query:
SELECT E.RefNo, Ads.[Ad Id], Ads.[Ad Name] AS Media
, (SELECT Count(*) FROM Enquire WHERE [Job no]=E.RefNo AND Media=Ads.[Ad Id]) AS [Count]
FROM Ads, qryRefNo AS E
ORDER BY E.RefNo DESC , Ads.[Ad Id];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

I managed to copy the SQL but still the Error message:


RefNo Ad Id Media Count
44444 1 Herald/Sunday Herald 16
44444 2 Daily Record/Sunday Mail 3
44444 3 Metro 1
44444 4 Word of Mouth 46
44444 5 Local Paper 0
44444 6 Job Centre 13
44444 7 Vacancy Bulletin 4
44444 8 Journal 1
44444 9 Journal Websites 69
44444 10 NHS Intranet 4
44444 11 Referred by Manager 0
44444 12 Show 130
44444 13 Campaign 0
44444 14 Open Day 0

 

[lars7]

Works perfectly, I'm not sure why this one was so difficult a right join usually works or a sub query but my thanks as always for hanging in there with me.

 

[PHV]

In fact you didn't want an outer join but a cross join (cartesian product).

 

[lars7]

I'll look that one up thanks.