UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Struggling With A Query, Any Version    
 
   
dashiellx2000
post May 29 2020, 11:48 AM
Post#1


UtterAccess VIP
Posts: 9,864
Joined: 11-March 05
From: Maryland


I have two sets of data with two fields that match

tblAssignedEncounters
EncouterID
AssignedUserID
AssignedBalance
etc...

tblWorkedLast30Days
EncounterID
WorkedUserID
WorkedBalance
etc....

So, I need a list of encounters from tblAssignedEncounters for each user where the EncounterID does not appear in tblWorkedLast30Days for that user.

In other words:

If Encounter A is Assigned to Tom and Tom has not worked it in the last 30 days, return the encounter number even if Harry worked it three days ago.

The only way I can think of doing this is looping through each user, but I'm hoping there is a way to do this via query.

Thanks.

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
theDBguy
post May 29 2020, 11:54 AM
Post#2


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


To find out which EncounterID Tom didn't touch in the last 30 days, I think you'll need another query that gives you the opposite result of tblLastWorked30Days (i.e. tblNotWorkedLast30Days).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post May 29 2020, 12:44 PM
Post#3


UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill


Also you could try:
CODE
SELECT
  a.*
FROM tblAssignedEncounters a
LEFT JOIN tblWorkedLast30Days w
       ON a.EncounterID = w.EncounterID
      AND a.AssignedUserID = WorkedUserID
WHERE w.EncounterID IS NULL
;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
dashiellx2000
post May 29 2020, 01:08 PM
Post#4


UtterAccess VIP
Posts: 9,864
Joined: 11-March 05
From: Maryland


Cheeky to the rescue again!

Thanks.

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
theDBguy
post May 29 2020, 02:11 PM
Post#5


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


Nice job!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post May 29 2020, 06:10 PM
Post#6


UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill


I'm glad it worked!

I'm already a bit curious when I see a table called something like 'tblWorkedLast30Days'.

Are you always having to shift records in and out?

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post May 29 2020, 06:35 PM
Post#7



Posts: 3,794
Joined: 27-February 09



(Can you use NOT EXISTS () in Access?)

CODE
CREATE TABLE AssignedEncounters(
    EncounterID INT IDENTITY,
    AssignedUserID INT NOT NULL,
    AssignedBalance INT
);
GO
CREATE TABLE WorkedLast30Days (
    EncounterID INT,
    WorkedUserID INT,
    WorkedBalance INT
);
GO

INSERT INTO AssignedEncounters(AssignedUserID,AssignedBalance)
VALUES (100,600),(200,75),(300,100);

INSERT INTO WorkedLast30Days(EncounterID, WorkedUserID, WorkedBalance)
VALUES (101,100,500),(202,85,85),(300,22,55);

SELECT EncounterID
FROM AssignedEncounters e
WHERE NOT EXISTS (SELECT 1 FROM WorkedLast30Days wld
                  WHERE wld.WorkedUserID = e.AssignedUserID);
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th July 2020 - 05:21 AM