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
> Getting A Single Result From A Relate Table, Access 2013    
 
   
SomekindaVB
post Nov 26 2017, 06:01 PM
Post#1



Posts: 157
Joined: 15-December 16



Hi All,

So I have two tables. One is a parent table that contains a list of outstanding work with the active field being a work type. The other is a child table that contains a list of work types completed for the parent and the date the selection was made in the parent table.

I need to run a comparison between the parent table where the work type is closed and where the child table does not record a closed worktype item for the parent.

Vice versa, I need a query that finds where a close item exists in the child table, but does not exist as a current worktype in the parent table

The SQL I tried was

CODE
SELECT Tbl_Parent.WorkType, TBl_Child. WorkType
FROM TBl_Child INNER JOIN Tbl_Parent ON TBl_Child.ParentID = Tbl_Parent.ID
WHERE (((Tbl_Parent. WorkType)="Closed") AND ((TBl_Child. WorkType)<>[Tbl_Parent].[ WorkType] And (TBl_Child. WorkType)<>'Closed'));


I hope this makes sense.

Thanks in advance

Cheers
Go to the top of the page
 
GroverParkGeorge
post Nov 26 2017, 06:14 PM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Okay, what you need, I believe, is a LEFT JOIN that returns all records from the Parent table and only matching records from the Child table, with the criteria applied to return only those records that are not closed.

That said, though, I'm puzzled. You have "Work Type" in both tables? How does that work?


This will return parent table records which are not marked as closed in the child table.
SQL
SELECT tblParent.WorkID
FROM tblParent LEFT JOIN tblChild On tblParent.ParentID = tblChild.ParentID WHERE tblChild.WorkID NOT IN (SELECT tblChild.ParentID FROM tblChild WHERE tblChild.WorkType ="Closed")



I can't quite figure out why you have the WorkType duplicated in both tables, though.

Maybe if you can elaborate on how this works, we can drill in better on the needed approach.


)

--------------------
Go to the top of the page
 
projecttoday
post Nov 26 2017, 07:14 PM
Post#3


UtterAccess VIP
Posts: 8,674
Joined: 10-February 04
From: South Charleston, WV


If the work type isn't closed, is it null? I suspect that's why your code doesn't work.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
HairyBob
post Nov 27 2017, 03:21 AM
Post#4



Posts: 875
Joined: 26-March 08
From: London


If the WorkType field allows nulls and you intend a null to represent 'Closed' as well, the following may give you what you want:

CODE
SELECT P.WorkID FROM tblParent AS P WHERE NOT EXISTS (SELECT * FROM tblChild as C WHERE P.ParentID = C.ParentID AND (C.WorkType = "Closed" OR C.WorkType Is Null))


A parent record will be in the result set if there is no corresponding child record (i.e. with the same ParentID value) or if there is a corresponding child record where the WorkType is null or where the WorkType is not 'Closed'.

--------------------
If it was easy, It wouldn't be fun!
Go to the top of the page
 
projecttoday
post Nov 27 2017, 04:54 AM
Post#5


UtterAccess VIP
Posts: 8,674
Joined: 10-February 04
From: South Charleston, WV


Going back to the original code:

CODE
SELECT DISTINCT Tbl_Parent.WorkType, TBl_Child. WorkType
FROM TBl_Child INNER JOIN Tbl_Parent ON TBl_Child.ParentID = Tbl_Parent.ID
WHERE (Tbl_Parent.WorkType="Closed" AND TBl_Child.WorkType IS NULL) OR (Tbl_Parent.WorkType IS NULL AND TBl_Child.WorkType = "Closed");

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
SomekindaVB
post Nov 27 2017, 05:12 PM
Post#6



Posts: 157
Joined: 15-December 16



Thanks all. some really great advice here, which I will apply to my project.

Cheers
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 11:35 PM