Query: Employees with no Transaction

[jdlc]

hi guys,

i'm trying to create a query of all employees doesn't have any transaction for a certain range of date and will also shows the last transaction date they have.

i have two databases one is the transaction file and the other is the user file. i'm trying to play around and yet i'm not successful. i do appreciate if someone here can help me out.

thanks as always.

 

[jdlc]

here's what i did.

first i created a query to get all the employees that have transactions:

SELECT DISTINCTROW DataFile.UserID, UserFile.UserFname, Max(Format$([DataFile].[TranDate],'dd-mm-yyyy')) AS [TranDate By Month], Sum(DataFile.NoEmail) AS [Sum Of NoEmail], Sum(DataFile.NoPhone) AS [Sum Of NoPhone], (sum(DataFile.NoEmail)+sum(DataFile.NoPhone)) AS TOTAL
FROM DataFile INNER JOIN UserFile ON DataFile.UserID=UserFile.UserID
GROUP BY DataFile.UserID, UserFile.UserFname, Year([DataFile].[TranDate])*12+DatePart('m',[DataFile].[TranDate])-1;

next, based on that query i created another query to find those employees that is not included in the first:

SELECT UserFile.UserID, UserFile.UserFname, UserFile.UserLname
FROM UserFile LEFT JOIN DataFile ON UserFile.UserID=DataFile.UserID
WHERE (((DataFile.UserID) Is Null));

what i wanted is to include the Max(TranDate) which in the DataFile in the second query.

thanks

 

[CJ_London]

What you are asking for won't work - you are using WHERE (((DataFile.UserID) Is Null)) which means no record has been found - if no record has been found then there cannot be a trandate.

I suspect you need to rephrase your question

 

[jdlc]

thanks for the reply cj, anyways i found a way to resolve my problem, here's the statement:

SELECT DISTINCTROW DataFile.UserID, UserFile.UserFname, UserFile.UserLname, Max(Format$([DataFile].[TranDate],'dd-mm-yyyy')) AS [TranDate By Month]
FROM DataFile INNER JOIN UserFile ON DataFile.UserID = UserFile.UserID
WHERE (((DataFile.TranDate)<=#3/1/2013#))
GROUP BY DataFile.UserID, UserFile.UserFname, UserFile.UserLname;

again many thanks boss!

 

[jdlc]

Sorry boss, here is the correct statement that gives me what i needed:

SELECT UserFile.UserID, UserFile.UserFname, UserFile.UserLname, Max(DataFile.TranDate) AS MaxOfTranDate
FROM DataFile INNER JOIN (UserFile LEFT JOIN [DataFile Query] ON UserFile.UserID=[DataFile Query].UserID) ON DataFile.UserID=UserFile.UserID
GROUP BY UserFile.UserID, UserFile.UserFname, UserFile.UserLname, [DataFile Query].UserID
HAVING ((([DataFile Query].UserID) Is Null));

 

[jdraw]

jdlc,

Your statement

i have two databases one is the transaction file and the other is the user file. i'm trying to play around and yet i'm not successful. i do appreciate if someone here can help me out.

seems like have acquired the Access software, and now you expect things to "just happen" - databases to be created.....

Here's a link to a tutorial that will help you with a process to design and build meaningful and well structured databases.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

The sooner you work through and understand the process, the sooner your frustration with tables and relationships will be reduced.

Good luck

 

[Solo712]

here's what i did.

first i created a query to get all the employees that have transactions:

SELECT DISTINCTROW DataFile.UserID, UserFile.UserFname, Max(Format$([DataFile].[TranDate],'dd-mm-yyyy')) AS [TranDate By Month], Sum(DataFile.NoEmail) AS [Sum Of NoEmail], Sum(DataFile.NoPhone) AS [Sum Of NoPhone], (sum(DataFile.NoEmail)+sum(DataFile.NoPhone)) AS TOTAL
FROM DataFile INNER JOIN UserFile ON DataFile.UserID=UserFile.UserID
GROUP BY DataFile.UserID, UserFile.UserFname, Year([DataFile].[TranDate])*12+DatePart('m',[DataFile].[TranDate])-1;

next, based on that query i created another query to find those employees that is not included in the first:

SELECT UserFile.UserID, UserFile.UserFname, UserFile.UserLname
FROM UserFile LEFT JOIN DataFile ON UserFile.UserID=DataFile.UserID
WHERE (((DataFile.UserID) Is Null));

what i wanted is to include the Max(TranDate) which in the DataFile in the second query.

thanks

You have the right idea but it lacks execution. Take a look at this example which shows how to create a complementing query of "no-data" rows.

Best,
Jiri

 

[jdlc]

hi cj, jdraw and solo712,

i do appreciate your replies, it give me great idea. but in my 7th post i already figure it out and i got the result which what i'm expecting of.

i'll still consider the link's that you guys provided to me. and study the sample's. pardon me for being a newbie in access and trying my best to understand.