Solved

Counting records

Posted on 2003-11-27
13
234 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
iis reverse proxy virtual directory 8 266
Website Test Question 6 136
Question to locate the problem 18 148
System Analysis 5 71
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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