Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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
0
mvwmail
Asked:
mvwmail
  • 7
  • 6
1 Solution
 
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
0
 
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?
0
 
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...
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 !
0
 
anandkpCommented:
use the (+) - its meant for records with 0 pictures !
0
 
anandkpCommented:
what DB are u using ???

the (+) works for ORACLE !
0
 
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
0
 
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 ...
0
 
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 !
0
 
mvwmailAuthor Commented:
sorry that was CANT find an equivalent
0
 
mvwmailAuthor Commented:
The expression 'Clever Bas**rd' comes to mind... spot ON!!!!

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

and here it is!

Rough form, will design the page properly soon

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

Cheers
Anand
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now