complex table query.. help needed

[Zaeed]

Ok this is harder than i thought, unless im missing something

I have a table; that tabe contains records relating to Changes. Each change contains an Initiator (change table) Tasks (taks table) and Impacts (Impact table)

Im currently building a search form that will filter out only the changes in the Change table that have the current user as either the Initiator, or have a Task or Impact assigned to them (each of these records has a Who field)

Can somebody please prod me in the right direction..

I can see how to build a list of all hits, but that would give me a list of changes, tasks and impacts.. I only want the Changes that have say a Task with the current user assigned to it.

An exampe:
Current User
-Tim Tam

Change 32 - record in Change Table
-Initiator Joe Blogs
-Tasks 1, 4, 12
-Impacts 44, 3, 78

Change 205- record in Change Table
-Initiator Sam Jones
-Tasks 7, 2, 222
-Impacts 1, 65, 55

Task 1 - record in Task Table
-Who Tim Tam

Impacts 65 - record in Impacts Table
-Who Tim Tam

So in the above basic example, my search form would display Change 32 and 205 since it picked up that the Curret User had a Task and and Impact assigned to them.

hope i made it clear..

Thanks

 

[MagicMan]

Question, are Tasks and Impacts Access 2007 multivalue fields? How are they defined in table Changes?

 

[Zaeed]

Its Access 2003.
Each task is an individual record with a unique id. One of the fields in the record is the Change Number, which is the unique id for the Change Table.. There is no reference in the Change Table to the Tasks assigned to each Change.

i.e.
Task table

Unique ID --- ChangeNumber --- Who
--- 1 ----------- 32 --------- Tim Tam
--- 2 ----------- 9 ---------- Tom Jones

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

I've got a UNION selection going that extracts all of the ChangeNumbers that I need. What I cant get atm is how to use those numbers to get the records out, since it seems that I cant use the UNION as a nested statement in a bigger SQL statement. Any ideas?

 

[Zaeed]

nvm, figured it out

 

[ajetrumpet]

nvm, figured it out

Hey Zaeed,

You have a wonderful reputation around here of posting a question and then figuring it out on your own.

 

[Zaeed]

yeah well it took me about 3x the time that it would have taken if somebody had thrown a bone.. My only choice was to battle on and tear my hair out until i made it work.. The only problem is that I don't know if my solution is the best option. Thats why I posted a message. I was seeking help. However I got no response, at least not a helpful one anyway.

 

[MSAccessRookie]

Zaeed,

Kudos on your ability to resolve your own issue this time, and Sorry to hear that you had to spend the day figuring out what was wrong without much assistance. That has happened to me a number of times.

I have learned that just because I have an issue, it does not mean that there will be someone with an instant answer for me (Oh how I wish this were true). I don't go sour on the Forum when this happens, as I have had many great assists, provided a few myself, and expect many more positive interactions in the future.

BTW, can you tell us what the end resolution was for future reference?

 

[Zaeed]

Yeah sorry to be snappy, bad week.

this is my resulting sql query.

SELECT tbl_Change.Title, tbl_Change.Initiator, tbl_Change.Change_Nature, tbl_Change.Change_Type, tbl_Change.Status, tbl_Change.Primary_Location FROM tbl_Change WHERE ChangeNumber IN (SELECT ChangeNumber FROM tbl_Tasks WHERE tbl_Tasks.Who = UserName)
UNION
SELECT tbl_Change.Title, tbl_Change.Initiator, tbl_Change.Change_Nature, tbl_Change.Change_Type, tbl_Change.Status, tbl_Change.Primary_Location  FROM tbl_Change WHERE ChangeNumber IN (SELECT ChangeNumber FROM tbl_Change WHERE tbl_Change.Initiator = UserName)
UNION 
SELECT tbl_Change.Title, tbl_Change.Initiator, tbl_Change.Change_Nature, tbl_Change.Change_Type, tbl_Change.Status, tbl_Change.Primary_Location  FROM tbl_Change WHERE ChangeNumber IN (SELECT ChangeNumber FROM tbl_Secondary_Impacts WHERE tbl_Secondary_Impacts.Who = UserName);