MS Access, SQL/VB CODE

[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*"))

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*")) 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.

[M Owen]

What about an additional clause in your WHERE ... OR ISNULL([Activity File].[URL Address])=True

[brainmetz]

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?

[antares686]

Remove your Where clause and do an if for the SUM like so.

SUM(iif([Activity File].[URL Address] Like "*att*" Or [Activity File].[URL Address] Like "*ibm*",[Activity File].[Total Time], 0)) as AS [SumOfTotalTime]

This will output all values and where the URL isn't like att or ibm the value will be 0.

[brainmetz]

That seems to be exactly what i want to do, but I crash access everytime...Any ideas?

here is what i have:

SELECT [Activity File].[Computer Name], SUM(iif([Activity File].[URL Address] Like "*att*" Or [Activity File].[URL Address] Like "*ibm*",[Activity File].[Total Time], 0)) as [SumOfTotalTime] INTO [ATT/IBM URL's By Computer]

[brainmetz]

Also,

When i put it where the WHERE clause is, I get a Syntax error in FROM clause.

Here is what i have...


SELECT [Activity File].[Computer Name], Sum([Activity File].[Total Time]) AS [SumOfTotal Time] INTO [ATT/IBM URL's By Computer]
FROM [Activity File]
SUM(iif([Activity File].[URL Address] Like "*att*" Or [Activity File].[URL Address] Like "*ibm*",[Activity File].[Total Time], 0)) as AS [SumOfTotalTime]
GROUP BY [Activity File].[Computer Name];


Thanks for everything

[antares686]

Sorry I didn't mean to confuse you, I meant do this

SELECT [Activity File].[Computer Name], SUM(iif([Activity File].[URL Address] Like "*att*" Or [Activity File].[URL Address] Like "*ibm*",[Activity File].[Total Time], 0)) as AS [SumOfTotal Time] INTO [ATT/IBM URL's By Computer]
FROM [Activity File]
GROUP BY [Activity File].[Computer Name];

[brainmetz]

Thanks alot...that is it...I owe you one.

Shane