Hi, I have a checkboxlist which i pull the options from a database on the page and I have got it submitting multiple checked values into the database, but im having problems creating the page to display which values have been checked. Could someone please help? The look up table is: Table - refCommittee CommitteId - int CommitteeName - varchar Active - bit The table holding inserted values Table - tblApprovingCommGroup ID - int PolicyID - int CommitteID - int Many thanks :-O

Hi Thanks for your reply, unfortunatley not, ive not worked with COALESCE before and im not sure how you mean to go about putting this in my sqldatasource? The table is just made of 2 columns.

membersemail
id - int
email - varchar

Thanks Adam

Hi I am wanting to create a form that someone can send an email to a list of people being pulled out of my database, but struggling to work out how i can bring all the email addresses back? Is there a way that i can bring all of them back into a label/textbox and seperate them with a ; and use that for my To? Or would it be best to loop through the records? Any help would be appreciated. Regards Adam

We got there in the end! SELECT d.DrugTherapy, ISNULL(c.count, 0) AS count FROM DrugTherapy AS d LEFT OUTER JOIN (SELECT COUNT(ReferralDrugTherapy.DTRID) AS count, ReferralDrugTherapy.DTID FROM CardiacReferrals INNER JOIN ReferralDrugTherapy ON CardiacReferrals.ReferralID = ReferralDrugTherapy.ReferralID WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) GROUP BY ReferralDrugTherapy.DTID) AS c ON c.DTID = d.DTID Many many thanks to everyone :-\

No need to apologies, your help has been much appreciated, your not the only person it seems to have stumped! :sigh:

Hi and thanks. Afraid not though....

Many thanks, if i crack it in the mean time without blowing my head off ill let you know!

My date settings are already set to that. The query that you have given me also just gives the total made, no matter what dates you enter you always get the total, it doesnt calculate any differences. Thanks for your help so far!

Yes, so if there is none made, it to display a 0 so i can display reports on a page. Thanks

Well say if i put in 01/01/2008 - 01/05/2008 it pulls back records as there are ones within that range. (But also will not display those that dont have anything as 0, just wont display) But say i put in 01/05/2008 - 02/05/2008 then it pulls back nothing

Not quite, it doesnt seem to matter what date range you put it, it always pulls back all the results for each drug type. I tried changing it slightly so the dates in a where to SELECT CASE WHEN dbo.ReferralDrugTherapy.DTID IS NULL THEN (SELECT TOP 1 t1.DTID FROM DrugTherapy AS t1 WHERE t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, DrugTherapy.DrugTherapy, COUNT(ReferralDrugTherapy.DTID) AS numbers FROM ReferralDrugTherapy FULL OUTER JOIN DrugTherapy ON ReferralDrugTherapy.DTID = DrugTherapy.DTID LEFT OUTER JOIN CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) GROUP BY ReferralDrugTherapy.DTID, DrugTherapy.DrugTherapy, DrugTherapy.DTID but all that did was give me the same problem where if it doesnt find anything it brings back nothing instead of 0 values. Argh! :wtf:

That source didnt do the trick either :sigh: Ive been searching on this but not to much luck, theres plenty on joining the 2 tables but 3 isnt particularly good :confused:

Im slightly confused now, did you mean to add that bit to replace the top like so? select DrugTherapy.dtid,DrugTherapy.drugtherapy, (select count (dtid) from ReferralDrugTherapy where dtid = DrugTherapy.dtid) from DrugTherapy INNER JOIN ReferralDrugTherapy ON DrugTherapy.DTID = ReferralDrugTherapy.DTID INNER JOIN CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) OR (CardiacReferrals.DateSubmitted IS NULL) GROUP BY DrugTherapy.DrugTherapy Or something else? That query alone works, but how would i enter my date ranges still? Many thanks :)

Yes, so for example, i want to enter 2 range dates in say 01/01/2008 - 01/05/2008 and then to pull back for example

Drug1 1
Drug2 3
Drug3 2
Drug4 5

At the moment if i put in a large date range it brings back the records because it has some, but if no record is found for say Drug3 it doesnt get pulled back i might get

Drug1 1
Drug4 5

But what i would like is

Drug1 1
Drug2 0
Drug3 0
Drug4 5

Thanks, but that hasn't worked either im afraid

Thanks for your reply. For example In the lookup table - DrugTherapy

DTID(int) DrugTherapy(varchar)
1 Drug1
2 Drug2
3 Drug3
4 Drug4

Drug referrals made - ReferralDrugTherapy

DTRID(int) ReferralID(int) DTID(int)
1 1 1
2 1 1
3 2 4
4 2 3
5 3 2
6 4 2
7 5 1

Main Table CardiacReferrals

ReferralID(int) Ward(int) Comments(varchar) DateSubmitted(datetime)
1 1 test 01/01/2008
2 2 test1 22/01/2008
3 3 test2 01/02/2008
4 1 test543 21/02/2008
5 2 test45 01/02/2008
6 3 testtgdf 25/02/2008
7 9 testgdf 05/03/2008
8 5 testggd 11/03/2008
9 6 testfg 12/03/2008
10 4 testgd 07/04/2008

I have tried the following which works if there is values for the record, but if there isnt nothing is diplayed, what i want is for it to display 0 still if there is nothing there: SELECT DrugTherapy.DrugTherapy, COUNT(ReferralDrugTherapy.DTID) AS Expr1 FROM DrugTherapy INNER JOIN ReferralDrugTherapy ON DrugTherapy.DTID = ReferralDrugTherapy.DTID INNER JOIN CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) OR (CardiacReferrals.DateSubmitted IS NULL) GROUP BY DrugTherapy.DrugTherapy Any help much appreciated. :)

Hi Im pulling my hair out here trying to produce a count between dates. I have a form which is filled out online and the values put into a main table. One of the options to complete on the form is pulled from a look up table and you can select more than one option. These values go into another table with the reference number from the main referral to tie the 2 together. So i have: .: Look up therapy table :. DTID - int Therapy - varchar .: Therapy Table :. DTRID - int ReferralID - int DTID - int .: Main table :. ReferralID - int DateSubmitted - datetime plus various other columns. So i want to do a count on the amount of different therapys have been selected. I can get it to show ones that have been entered, but i want it to display 0 values to, which i cant seem to do. Any help would be great. Thanks :)

Hi I want to bind the pages of the sitemap to a gridview and then be able to select a page from there taking its name in the querystring to another page which I will be using as an admin page for it. Any help appreciated. I am using c# Many thanks Adam:)

The course ID is in the query string of the page. Ive managed to get it now working. Thanks Adam

Hi I want to send an email out to a selection of people who will have registered for a course. Within their record they will have a course ID for the course they are on. I want to be able to click a button and it sends an email to all those with that courseID in their record. I can retrieve the course ID from the pages querystring.:confused: Can someone please advise? Many thanks Adam:)