Counting records

I cant get my head around this...

Table 1
******
DiaryID (autonumber)
DiaryTitle (text)
DiaryDate (date)
DiaryDetail (memo)

Table 2
******

PictureID (autonumber)
PictureText (text)
DiaryID (link to diary ID)

This allows multiple pictures to be assigned to single diary entries..

Easy enough huh. I can display the Diary Entries and corresponding picture(s) easily on a detail page... HOWEVER on the main page where I list all the DiaryTitle's as URL to the detail page, I want to show the number of photos specific to the entry..next to the entry itself.

i.e.

27/11/2003 Went to Dentist (2 photos)
26/11/2003 Went to shops
25/11/2003 Went home (5 photos)

I know tis is going to include some kind of RecordCount, and GROUP BY, but how can I achieve this....

Thanks
LVL 3
mvwmailAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anandkpCommented:
<cfquery name="piccount">
Select D.DiaryTitle, D.DiaryDate, Count(P.PictureID) as picctr
from Tbl_Diary D, Tbl_Picture P
where D.DiaryID = P.DiaryID(+)
group by D.DiaryTitle, D.DiaryDate
</cfquery>

<cfoutput query="piccount">
     #DiaryDate# : #DiaryTitle# (#picctr#)
</cfoutput>

let me know ...

K'Rgds
Anand
mvwmailAuthor Commented:
Just so I understand before i try... Knowledge is Power etc

What is the D. and P. in front of record names... is this shorthand for the tablename, should I replace them with the full Tablename?
mvwmailAuthor Commented:
OK, had a fiddle, getting there... silly question above, please ignore, I did not include the table names.

The query is fine up until the

where Diary.EntryID = Pictures.EntryID(+), however I left out the plus and this seemed to work.. excellent.

Getting there... ONE IDDY BIDDY Problem

If there are no pictures, there is no entry.. I want counts of Zero to be included also, not all diary entries have a picture... do I need a seperate Query which includes the results of the previous? For example the counter only generates a results table of Diary.EntryID and Piccount.Picctr (if possible)

Oh, by the way, points increased to 200 for extra work...
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

anandkpCommented:
D & P is a alias for each table ... usefull in refering tables ...
yes u can use full table names - if u feel comfortable with that !
anandkpCommented:
use the (+) - its meant for records with 0 pictures !
anandkpCommented:
what DB are u using ???

the (+) works for ORACLE !
mvwmailAuthor Commented:
My database is just an MDB... I have strated working with SQL server, but still not comfy with it yet.

The (+) upsets my webpage...

Did a search on google, can find an equivalent. and my SQL bible (hah!) does not even cover this subject... bugger
anandkpCommented:
if its MSSQL use this query

<cfquery name="piccount">
Select D.DiaryTitle, D.DiaryDate, Count(P.PictureID) as picctr
from Tbl_Diary D LEFT JOIN Tbl_Picture P ON D.DiaryID = P.DiaryID
group by D.DiaryTitle, D.DiaryDate
</cfquery>

let me know ...
anandkpCommented:
This query shld work fine on MSAccess as well !

<cfquery name="piccount">
Select D.DiaryTitle, D.DiaryDate, Count(P.PictureID) as picctr
from Tbl_Diary D LEFT JOIN Tbl_Picture P ON D.DiaryID = P.DiaryID
group by D.DiaryTitle, D.DiaryDate
</cfquery>

try it & let me know !

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mvwmailAuthor Commented:
sorry that was CANT find an equivalent
mvwmailAuthor Commented:
The expression 'Clever Bas**rd' comes to mind... spot ON!!!!

Thanks
mvwmailAuthor Commented:
http://amy.mvwportal.com/pages/diarymain.cfm

and here it is!

Rough form, will design the page properly soon

Thanks
anandkpCommented:
Saw the link : Way to go :)

Cheers
Anand
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.