 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / March 2007Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All "A"s or "B", "C"s should have only x or y or z property attached. I want to find A, B or C which has more than one properties. 1 A x ... 2 A x ... 3 A x ... 4 A y ... 5 B x 6 B y 7 C z ... resutls would look like: A x A y ... I think I can do this in two steps or with a subquery. Then I thought of self-join but didn't figure out how to use it in this case. Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital? > Suppose I have a talble like this, each record has a unique ID. All > "A"s or "B", "C"s should have only x or y or z property attached. I [quoted text clipped - 18 lines] > > Thanks a lot! ID don't matter. But I think SELECT DISTINCT would get all A, B & Cs, like A x A y B x B y C z while I only need A, B or Cs that have two or more x, y or z attatched. For the table I made up C z is not wanted. Thanks > SELECT DISTINCT and do not include the record ID perhaps? Or is the record > ID vital? [quoted text clipped - 23 lines] > > - Show quoted text - In your example, how would you distinguish rows 1 and 2 from 3 and 4? > ID don't matter. But I think SELECT DISTINCT would get all A, B & Cs, > like [quoted text clipped - 37 lines] > > > > - Show quoted text - ID is unique and fields after the third are different. Actually I should have made it like this: 1 A x ... (other fields) 2 A x 3 A x 4 A y 5 B x 6 B x 7 B x 8 C z 9 C z 9 C z and the result I want is: A x A y Thanks, On Mar 15, 9:27 am, scubadiver <scubadi...@discussions.microsoft.com> wrote: > In your example, how would you distinguish rows 1 and 2 from 3 and 4? > [quoted text clipped - 45 lines] > > - Show quoted text - Does "A x" in your result represent rows 1 - 3 in your example because I think either "unique values" in the query properties is springing to mind. What I suggest is if you give us a brief description of what you are trying to achieve. > ID is unique and fields after the third are different. Actually I > should have made it like this: [quoted text clipped - 68 lines] > > > > - Show quoted text - From your data description I honestly can't wrap my head around what exactly you're trying to do here... > ID don't matter. But I think SELECT DISTINCT would get all A, B & Cs, > like [quoted text clipped - 38 lines] >> >> - Show quoted text - Sorry I didn't make it clearer. Try this: ID Husband Wife Event 1 Dave Bush dinner 2 Dave Bush sex 3 Dave Hilary quarrel 4 Sue Nicole shopping 5 Sue Nicloe trip 6 Mike Mary nothing 7 Mike Mary divorce 8 Mike Mary movie Each couple does something and get a record. But sometimes they do this with their lovers. I need to find husbands who have more than one wives. like: Dave, Bush Dave, hilary Thanks, > From your data description I honestly can't wrap my head around what exactly > you're trying to do here... [quoted text clipped - 47 lines] > > - Show quoted text - LOL interesting database you have there! What kind of output are you looking for? SELECT DISTINCT YourTable.Husband, YourTable.Wife FROM YourTable; will give you: Husband Wife Dave Bush Dave Hilary Sue Nicole Mike Mary You could then run a find duplicate query against the distinct query focusing on the Husband field to get a list of just the husband's names... > Sorry I didn't make it clearer. Try this: > [quoted text clipped - 71 lines] >> >> - Show quoted text - Each husband can have many wives! :) So create two tables and establish a 1-to-many relationship. By counting records, you can create a query that can tell you all those husbands who have more than one wife. Otherwise you would have made it difficult for yourself with the design you already had. > Sorry I didn't make it clearer. Try this: > [quoted text clipped - 69 lines] > > > > - Show quoted text - I cann't change the table which is somebody esle's work. I can selcet only A x or the whole record that don't matter to me. I just wonder if I can do this in one step by some trick. If that's not possible, then I'll do it in 2 steps. Thanks. On Mar 15, 10:19 am, scubadiver <scubadi...@discussions.microsoft.com> wrote: > Each husband can have many wives! :) > [quoted text clipped - 84 lines] > > - Show quoted text - I will also point out 1) that Dave can be married to Hillary but Hillary can be married to Mike ... LoL. 2) Each relationship can have many events so it would Husband -> Wife -> Event So you would need three tables (with two 1-to-many relationships) > Sorry I didn't make it clearer. Try this: > [quoted text clipped - 69 lines] > > > > - Show quoted text - In fact it would be the following: Husband -> Event <- Wife Each husband has many wives, Each wife has many husbands the combination of both can have many events. tble_husband H_ID (PK) Husband Tble_event H_ID (FK) W_ID (FK) Event Tble_Wife W_ID (FK) Wife I think the above three tables will give you the example data you gave. > Sorry I didn't make it clearer. Try this: > [quoted text clipped - 69 lines] > > > > - Show quoted text - SELECT DISTINCT Husband, Wife FROM MatrimonialEvents AS ME1 WERE EXISTS (SELECT * FROM MatrimonialEvents AS ME2 WHERE ME2.Husband = ME1.Husband AND ME2.Wife <> ME1.Wife); Ken Sheridan Stafford, England > Sorry I didn't make it clearer. Try this: > [quoted text clipped - 17 lines] > > Thanks, |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.