Access 2010 No productivity in 30 days

I have a query with the following fields to tell me the # of days an account has gone without being worked:

Account
Assigned to
Assigned to date
ReconciledOn
# of Days: Sum(DateDiff("d",[Assigned to date],Date()))

 I need to see the accounts that are within the # of days column broken down by 30, 60, and 90 days.

If possible, I would also like to count or sum how many accounts are without work for 30, 60, and 90 days.

Can anybody help?

Thanks,
Statements-Pending-Reconciliatio.docx
NNiicckkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Select *, IIf(DateDiff("d", NumOfDays, Date()) > 30 AND < 60, NumOfDays, 0) As 30Days, IIf(DateDiff("d", NumOfDays, Date()) >= 60 AND < 90, NumOfDays, 0) As 60Days, IIf(DateDiff("d", NumOfDays, Date()) >= 90 , NumOfDays, 0) As  90Days
From YourQuery;

Notice that I changed your column name from [# of Days] to NumOfDays.  Good practice precludes the use of special characters or spaces in column/table/object names.  You also need to avoid reserved words such as Date, Time, Name, Filter, Select, etc.  So, you can either memorize the hundreds of reserved words for VBA, Access, and SQL server or you can make compound names like SaleDate, ReceivedTime, CustName, FormFilter, SelectedCustomer.  Notice that I use CamelCase.  That style capitalizes the first character of each word to make the string more readable.   Some people prefer the_underscore.
0
NNiicckkAuthor Commented:
Thanks, the system tells me "The syntax of the subquery in this expression is incorrect."
Then it highlights the first word, Select.
0
Rey Obrero (Capricorn1)Commented:
try this expression

Select *,
 IIf(DateDiff("d", [# of Days], Date()) >=30 AND DateDiff("d", [# of Days], Date())< 60, [# of Days], 0) As 30Days,
IIf(DateDiff("d", [# of Days], Date()) >=60 AND DateDiff("d", [# of Days], Date())< 90, [# of Days], 0) As 60Days,
IIf(DateDiff("d", [# of Days], Date()) >=90 , [# of Days], 0) As 90Days
From your query
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PatHartmanCommented:
There is no subquery in the query I posted.  I intended for you to save your existing query - give it a name.  Then substutitute that name for "YourQuery" and also substitute your column name for NumOfDays if you didn't take my advice about naming conventions.  If you still have a problem, Please post the query you are using.  There is no way to sort out syntax errors without actually seeing the statement.
0
Rey Obrero (Capricorn1)Commented:
here is another way of doing this

Select *,
 IIf(DateDiff("d", [# of Days], Date()) Between 30 AND 59, [# of Days], 0) As 30Days,
IIf(DateDiff("d", [# of Days], Date()) between 60 And 89, [# of Days], 0) As 60Days,
IIf(DateDiff("d", [# of Days], Date()) >=90 , [# of Days], 0) As 90Days
From your query
0
NNiicckkAuthor Commented:
I am not able to get past Syntax error message.  My original query sql is:

SELECT Statements.Account, Statements.StatementDate, Statements.Assignedto, Statements.Assignedtodate, Statements.ReconciledOn, Sum(DateDiff("d",[Assignedtodate],Date())) AS NumOfDays
FROM Statements
GROUP BY Statements.Account, Statements.StatementDate, Statements.Assignedto, Statements.Assignedtodate, Statements.ReconciledOn
HAVING (((Statements.ReconciledOn) Is Null))
ORDER BY Sum(DateDiff("d",[Assignedtodate],Date())) DESC;

The sub query is:

Select *, IIf(DateDiff("d", NumOfDays, Date()) > 30 AND < 60, NumOfDays, 0) As 30Days, IIf(DateDiff("d", NumOfDays, Date()) >= 60 AND < 90, NumOfDays, 0) As 60Days, IIf(DateDiff("d", NumOfDays, Date()) >= 90 , NumOfDays, 0) As  90Days
From SPR;
SPR.docx
0
Rey Obrero (Capricorn1)Commented:
did you try any of this query


Select *,
 IIf(DateDiff("d", [NumOfDays], Date()) Between 30 AND 59, [NumOfDays], 0) As 30Days,
IIf(DateDiff("d", [NumOfDays], Date()) between 60 And 89, [NumOfDays], 0) As 60Days,
IIf(DateDiff("d", [NumOfDays], Date()) >=90 , [NumOfDays], 0) As 90Days
From SPR

or this one

Select *,
 IIf(DateDiff("d", [NumOfDays], Date()) >=30 AND DateDiff("d", [NumOfDays], Date())< 60, [NumOfDays], 0) As 30Days,
IIf(DateDiff("d", [NumOfDays], Date()) >=60 AND DateDiff("d", [NumOfDays], Date())< 90, [NumOfDays], 0) As 60Days,
IIf(DateDiff("d", [NumOfDays], Date()) >=90 , [NumOfDays], 0) As 90Days
From SPR
0
NNiicckkAuthor Commented:
Both formulas created 30Days, 60Days and 90Days columns but put all of the NumOfDays in the 90Days column.  

I should get some data in each field as the first query, the NumOfDays field ends up with:
68
48
33
18
12
7
2
0
Rey Obrero (Capricorn1)Commented:
post the sql statement of the query that you used.
0
NNiicckkAuthor Commented:
SELECT Statements.Account, Statements.StatementDate, Statements.Assignedto, Statements.Assignedtodate, Statements.ReconciledOn, Sum(DateDiff("d",[Assignedtodate],Date())) AS NumOfDays
FROM Statements
GROUP BY Statements.Account, Statements.StatementDate, Statements.Assignedto, Statements.Assignedtodate, Statements.ReconciledOn
HAVING (((Statements.ReconciledOn) Is Null))
ORDER BY Sum(DateDiff("d",[Assignedtodate],Date())) DESC;
0
Rey Obrero (Capricorn1)Commented:
well, off course you will see all the values in one column with that query.

is the name of the query you posted above is "SPR" ?

create a NEW query and  switch to SQL view, then copy and paste

this

Select *,
 IIf(DateDiff("d", [NumOfDays], Date()) Between 30 AND 59, [NumOfDays], 0) As 30Days,
IIf(DateDiff("d", [NumOfDays], Date()) between 60 And 89, [NumOfDays], 0) As 60Days,
IIf(DateDiff("d", [NumOfDays], Date()) >=90 , [NumOfDays], 0) As 90Days
From SPR

or this one

Select *,
 IIf(DateDiff("d", [NumOfDays], Date()) >=30 AND DateDiff("d", [NumOfDays], Date())< 60, [NumOfDays], 0) As 30Days,
IIf(DateDiff("d", [NumOfDays], Date()) >=60 AND DateDiff("d", [NumOfDays], Date())< 90, [NumOfDays], 0) As 60Days,
IIf(DateDiff("d", [NumOfDays], Date()) >=90 , [NumOfDays], 0) As 90Days
From SPR
0
NNiicckkAuthor Commented:
Sorry for the confusion, I thought you were asking for the SQL from my first query.

In the first query SPR, I have this:

SELECT Statements.Account, Statements.StatementDate, Statements.Assignedto, Statements.Assignedtodate, Statements.ReconciledOn, Sum(DateDiff("d",[Assignedtodate],Date())) AS NumOfDays
FROM Statements
GROUP BY Statements.Account, Statements.StatementDate, Statements.Assignedto, Statements.Assignedtodate, Statements.ReconciledOn
HAVING (((Statements.ReconciledOn) Is Null))
ORDER BY Sum(DateDiff("d",[Assignedtodate],Date())) DESC;


In my second query, I have this:

Select *,
 IIf(DateDiff("d", [NumOfDays], Date()) >=30 AND DateDiff("d", [NumOfDays], Date())< 60, [NumOfDays], 0) As 30Days,
IIf(DateDiff("d", [NumOfDays], Date()) >=60 AND DateDiff("d", [NumOfDays], Date())< 90, [NumOfDays], 0) As 60Days,
IIf(DateDiff("d", [NumOfDays], Date()) >=90 , [NumOfDays], 0) As 90Days
From SPR


On my second query, I get the 30, 60, 90 columns but all results go to the 90 days in error.

Thanks for looking
0
Rey Obrero (Capricorn1)Commented:
can you upload a copy of your db..
0
NNiicckkAuthor Commented:
I am trying to determine the number of days an account has not been worked, then break them down into 30, 60 and 90 days.  Then ultimately, total those numbers:  example 4 accounts not worked in the last 30 days, 1 account not worked in the last 60 days and 3 accounts not worked in the last 90 days.
Nick092113.accdb
0
Rey Obrero (Capricorn1)Commented:
try this query

SELECT Statements.Assignedtodate, DateDiff("d",[Assignedtodate],Date()) AS NumOfDays, Sum(IIf(DateDiff("d",[Assignedtodate],Date())>=30 And DateDiff("d",[Assignedtodate],Date())<60,1,0)) AS 30Days, Sum(IIf(DateDiff("d",[Assignedtodate],Date())>=60 And DateDiff("d",[Assignedtodate],Date())<90,1,0)) AS 60Days, Sum(IIf(DateDiff("d",[Assignedtodate],Date())>=90,1,0)) AS 90Days
FROM Statements
GROUP BY Statements.Assignedtodate, DateDiff("d",[Assignedtodate],Date()), Statements.ReconciledOn
HAVING (((Statements.ReconciledOn) Is Null))
ORDER BY DateDiff("d",[Assignedtodate],Date()) DESC;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NNiicckkAuthor Commented:
That worked, thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.