MS Access, SQL/VB CODE 1

[brainmetz]

Hello,

Here is what I am trying to do…Not sure if it is possible:



SELECT [Activity File].[Computer Name], Sum([Activity File].[Total Time]) AS [SumOfTotal Time] INTO [ATT/IBM URL's By Computer]

FROM [Activity File]

WHERE ((([Activity File].[URL Address]) Like "*att*" Or ([Activity File].[URL Address]) Like "*ibm*&quot)

GROUP BY [Activity File].[Computer Name];



Those lines will find att and ibm as a url address….That works fine, but what I also need is (logic code

If URL Address is not like att or ibm

Then URL Address is NULL




I was thinking the code should be something like this:



(if ((([Activity File].[URL Address]) Is Not = "*att*" Or ([Activity File].[URL Address])Is Not Like "*ibm*&quot) Then ([Activity File].[URL Address] = NULL))



but that doesn’t work.



I need that so each computer name will have something associated with it…What is being displayed now is only those computer names which att/ibm shows up for and the total time spent in those URL’s. I need it to say something like:



Computer1 10

Computer2 0

Computer3 11



It currently only displays:

Computer1 10

Computer3 11



And it leaves out computer2…is there anyway to make it display computer2 with a value of 0?



That query saves the output to a table called ATT/IBM URL’s by Computer.



Thanks for your help.

 

[mp9]

Why not just use the Query Builder to create an update query that would make the changes you require, then switch to SQL view to get syntactically correct SQL? It will be somehting like:

UPDATE [ActivityFile]
SET [URL Address] = Null
WHERE [URL Address] Not Like "*att" And [URL Address] Not Like "*ibm*";

As an aside, fieldnames with spaces in... more trouble than they're worth. Why not

UrlAddress

in future?

Hope this helps.

http://www22.brinkster.com/accessory/

 

[brainmetz]

That gives me the URL's of all computers...I already knew how to do that...What i need to do is:

If I added the isnull part it would display everything...I dont want it to add up the total sum of the other URLs...I just need the rest of the computer names to have a total sum of 0. Does that make sense?

 

[mp9]

I misunderstood, sorry. Try this:

SELECT [Computer Name], (-1 * ([URL Address] LIKE '*att*' OR [URL Address] LIKE '*ibm*') * Sum([Total Time])) AS [SumOfTotal Time]
FROM [Activity File]
GROUP BY [Computer Name];

This works by evaluating a condition ([URL Address] LIKE '*att*' OR [URL Address] LIKE '*ibm*') which will return true (-1) or false (0). Multiply this by -1 to give either 1 or 0, then multiply it by the sum of [Total Time]. This then gives the sum for computer wil URLs like att or ibm and returns zero for everything else... which I think (!) is what you want.

http://www22.brinkster.com/accessory/

 

[brainmetz]

Thanks alot!