check a table to see whether it has nothing in it (was "Help with An SQL stateme

i am currently designing a study resource site for a college, as part of this site i have to design a personal calendar for the students to be able to insert diary entries and view them when the time is right.
Here comes my problem.

I need a statement that will check a database table to see whether it has nothing in it. I have a table with 3 fields that contain no data( username, day_id, message. I need my code to check that the teo fields username and day_id have no data in them. I need this so that i can insert data into that record when these two fields are empty and update the fields when there is data in them.

Sorry for the longwinded message but i have been fussing over this all day and it has got more complicated by the hour.Im not sure if this is what you want:

select count(*) from tablename where
username is not null and day_id is not null;|||anyone who writes "An SQL statement!" instead of "A SQL statement!" surely deserves a reply

under which circumstances will you find a row where both username and day_id are empty but message isn't? whose message would it be, and for which day?

perhaps if you would kindly show us the CREATE TABLE statement that you used to create the table?|||anyone who writes "An SQL statement!" instead of "A SQL statement!" surely deserves a reply

under which circumstances will you find a row where both username and day_id are empty but message isn't? whose message would it be, and for which day?

perhaps if you would kindly show us the CREATE TABLE statement that you used to create the table?

Basically, i wasnt searching the message field because the contents of it are irrelivant. I am running a check as to whether a record is in the database for that username and that day. Example username :gr203 example day_id: 1(monday)
The check is necessary as it determines whether i need to run an insert or update statement. I cannot have two entries in the table for the same username and day hence if both are null then the entry does not already exist so insert the data, else there is already data for that entry so update the specified record.
Thankyou both for your replies so far.|||my point is: if the given user has no entry for the given day, there will not be a row with NULL in those two columns, there won't be a row at all!!

see the difference?

Chart in Report Builder

hi there

I am just designing a simple chart in Report Builder - but when trying to drag any field into the value field of the chart (it doesn't change colour and won't accept any field).

If I drag a field into the category or series the curser changes and on approaching the chart properties they seem to change colour.

Can someone help to what can be wrong?

thanks

Dianne

still needing this information

Is someone using the Report Builder and have the same? Or is someone using it and no problems - so I can check?

thanks

|||Can u say it more clear.|||Items added to the value fields area in a chart must be aggregates. Dropping non-aggregates to the value area is not allowed and would give you the behavior you are seeing. You can identify aggregates in your report explorer by looking for the aggregate icon which has three stacked yellow boxes and a curly brace. In the report model aggregates have the IsAggregate property set to true. If you used the auto generate option to create your base model aggregates would have been created for you unless you turned that option off in the model generation wizard.|||

hey there Carolyn

thank you for your answer - I didn't know that.

so if I explain what I wanted to do can you tell me if it is possible some way then.

CallID field

Application field

in my graph I just wanted to show - no of calls per application

and as you pointed out my CallID field is not an aggregate field.

I have done this the same as my report in Reporting Services and

CallID is in the Value field

Application is in the category field

I am demonstrating the ease of the report builder to the Manager - rather than depending on static reports.

thanks

Dianne

|||

Dianne,

You can create a new expression to count the number of calls. Go to the entity that contains CallId. On top of the fields area of the Explorer, you'll find a New Field icon. Click on it. Alternately, go to the Report menu and select New Field. You're defining a new field for your report.

Give your new field a name
Click on the Functions tab in the Define Formula dialog.
Expand the Aggregate node.
Double click on Count
Click on the Fields tab
Double click on the CallId field
Your expression should read Count(CallId)
Click OK

You should now be able to drag this new field to the Values area.

Hope this helps,
-Carolyn

|||

thank you Carolyn that did the trick.

just to clarify - this is a temporary arrangement? no way to save the aggregate field?

cheers for your help

Dianne

|||

Meant to add that to my last post. If you want to use this field in more than one report you should add it to the report model. Edit your report model in Model Designer and use basically the same steps to add the new field. To get a new field in Model Designer go to the Report Model menu and select new field.

-Carolyn

|||

cool - so I am using BI to edit my model project = yes?

add new source field = yes

how do I make it an aggregate field?

thanks

Dianne

|||

When the field is selected change the IsAggregate property in the Property Grid for the field to True.

-Carolyn

|||

awesome - thank you for your help

Dianne