Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Counting records

Posted on 2003-11-27
13
Medium Priority
?
239 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 800 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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