Solved

Counting records

Posted on 2003-11-27
13
236 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:mvwmail
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 17

Expert Comment

by:anandkp
ID: 9830459
<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
0
 
LVL 3

Author Comment

by:mvwmail
ID: 9830652
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?
0
 
LVL 3

Author Comment

by:mvwmail
ID: 9830830
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...
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 17

Expert Comment

by:anandkp
ID: 9830877
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 !
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9830899
use the (+) - its meant for records with 0 pictures !
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9830907
what DB are u using ???

the (+) works for ORACLE !
0
 
LVL 3

Author Comment

by:mvwmail
ID: 9830995
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
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9831002
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 ...
0
 
LVL 17

Accepted Solution

by:
anandkp earned 200 total points
ID: 9831014
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 !
0
 
LVL 3

Author Comment

by:mvwmail
ID: 9831016
sorry that was CANT find an equivalent
0
 
LVL 3

Author Comment

by:mvwmail
ID: 9831080
The expression 'Clever Bas**rd' comes to mind... spot ON!!!!

Thanks
0
 
LVL 3

Author Comment

by:mvwmail
ID: 9831111
http://amy.mvwportal.com/pages/diarymain.cfm

and here it is!

Rough form, will design the page properly soon

Thanks
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9831151
Saw the link : Way to go :)

Cheers
Anand
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question