Can 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. Iwant 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 x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot!
0
muster
3/15/2007 12:49:00 PM
access 16762 articles. 3 followers. Follow

3 Replies
614 Views

Similar Articles

[PageSpeed] 36

SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in message news:1173962940.011576.225620@b75g2000hsg.googlegroups.com...> 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!> 
0
SusanV
3/15/2007 1:00:06 PM
ID don't matter. But I think SELECT DISTINCT would get all A, B & Cs,likeA xA yB xB yC zwhile I only need A, B or Cs that have two or more x, y or zattatched. For the table I made up C z is not wanted.ThanksOn Mar 15, 9:00 am, "SusanV" <svanal...@nospam-mvps.org> wrote:> SELECT DISTINCT and do not include the record ID perhaps? Or is the record> ID vital?>> "muster" <mus...@gmail.com> wrote in message>> news:1173962940.011576.225620@b75g2000hsg.googlegroups.com...>>>> > 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!- Hide quoted text ->> - Show quoted text -
0
muster
3/15/2007 1:17:17 PM
From your data description I honestly can't wrap my head around what exactly you're trying to do here..."muster" <muster@gmail.com> wrote in message news:1173964637.139242.298590@e65g2000hsc.googlegroups.com...> 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>> On Mar 15, 9:00 am, "SusanV" <svanal...@nospam-mvps.org> wrote:>> SELECT DISTINCT and do not include the record ID perhaps? Or is the >> record>> ID vital?>>>> "muster" <mus...@gmail.com> wrote in message>>>> news:1173962940.011576.225620@b75g2000hsg.googlegroups.com...>>>>>>>> > 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!- Hide quoted text ->>>> - Show quoted text ->> 
0
SusanV
3/15/2007 1:41:10 PM
Reply:

Similar Artilces:

Can't install Publisher
I'm having a tough time installing MS Publisher 2003 in VHP. I get a 1402 error which pertains to a certain registry key in myt Vista registry. I'm supposed to change the permissions in this key but in Vista Home Premium I can't seem to do that. Anybody have any suggestions? Did you try method 4 in the support document? You receive an error message when you install an Office program or open an Office program or document http://support.microsoft.com/kb/838687/en-us If this is not the document, where did you get the security information? This is a permission issue, you migh...

Chinese computers connecting to Exchange 5.5
Hi, We're trying to get some people in a new Chinese office connected to our Exchange 5.5 SP4 server running on NT 4 via a VPN. They are using Windows XP and Outlook 2003, as we are. When they try to create a MAPI profile, it just times out after forty five seconds when Check Name is clicked. Our firewall shows the RPC and TCP high port connections are occurring. The security event log does show a successful logon. When they try to connect by Outlook Express and POP3, they get a message of "There is no such mailbox on this server" when there definitely is. They are able to u...

Combining an update query with a parameter query
I wanted to know how you can update data in a table using the update query and a parameter query. How did you want to run this update query? In query design, you can just put your parameter in the query. To run it programmatically, you can then use RunSQL in a macro or code. A better programmatic approach might be to build the SQL statement in code, concatenating the parameter value into the string. Then Execute the string itself: you don't need a saved query. The advantage is that you know whether it worked or not. Here's an example: Action queries: suppressing dialogs, whi...

I can't get Outlook to connect to an IMAP4 server
I'm trying to connect Outlook 2000 SP3 (Windows XP Pro, all patches installed, 700 MHz Pentium III, 320 Mb RAM) to Mercury/32 v. 4.01 (Windows 2000 Pro, all patches installed, 350 MHz, 416 Mb RAM). The machines are on a small network running Ethernet (100Base-T full duplex) using a Linksys router/switch. The IMAP server PC has a static IP (192.168.1.222) - the Outlook machine does not. I know that the static IP is correct - can connect to Mercury's HTTP server by pointing Internet Explorer 6 at the IP. I have two problems: 1. When I create an IMAP account, I do not get an...

Tough one
MVPs I have a few questions 1. How do we add more than 3 conditional formats ? Macros ?? Any add-in tools available to make life easier 2. Also, can we write macros to trigger some action, not just color cells based on conditions being met 3. How can we get a count and statistics of the cells that have been highlighted due to conditional formats ? I have a big text file with thousands of lines of records and when I populate that into excel, based on some conditions, the cells would light up. Now I would need to get some statistics of which type of formats are most prevalent etc 4. Ca...

Can you do an e-mail based merge in Pub 2003?
I may be bonkers but I believe before I loaded the beta of Pub 2003 I could merge to an e-mail format in Publisher. The new wizard is good but only offers printing and help dosent help... Is this a capability or not? Publisher now lets you create an HTML publication for emailing through Outlook. It still won't allow you to mail-merge to email. "Thomas Loker" <thomas@loker.com> wrote in message news:#FRZ444WDHA.1620@TK2MSFTNGP12.phx.gbl... > I may be bonkers but I believe before I loaded the beta of Pub 2003 I could > merge to an e-mail format in Publisher. The n...

can i use filters to compare data instead of vlookup?
I have to compare membership lists to identify if customers belong to certain groups. Currently, I have been using Vlookup but I notice that there are too many nuisances. Could I use a filter to compare two membership lists and give me a more accurate answer? Maybe try setting up a new sheet with List1, and entering a new column indicating List1 in each row......then adding List 2 at the bottom and entering List2 in the new column for each row in that section............then do Data > Filter > AutoFilter and check the arrows at the tops of the columns to filter from there...........

can i import a database from access into visio #2
im want to import a database from access to visio in hopes it will draw my er digram for me. any syggestions? ...

"Runtime Error 429: ActiveX can not create object"
When I try to open Microsoft Retail Management Service POS, it opens correctly, and seems to have contact with the back-office computer that hosts the database. However, when I try to open either the Manager module or the Administrator module, I get an error that reads "Runtime Error 429: ActiveX can not create object". To my knowledge, the computer was operating correctly when the store closed Saturday, and when we opened Monday morning, this was the message. The only new thing I am seeing is Office 2007, which was installed several weeks ago. Also, one of the employees see...

Contacts I need I can't get to show as default contacts address b
With some great help from this site...!(Thanks) I have reinstalled a clean version of OL2007 back to my computer after recovering the C: drive. All seems well with PST but I cant get my old "Contacts" (That I guess that came with "copied" PST) to show as defalt when I want to choose an e-mail address from my contacts. If I click the "To box" I get offered under address book a balnk contacts called "Accounts" If I click the down arrow my contacts are there and if I double click they are live. How can I make them the default. I have right clicked ...

can i user exchange 2007 client acc & 2003 mailbox?
i have exchange 2003.i would like to install exchange 2007 client access and leave my mailbox on exchange 2003.would i be able to use new owa? No, clients will see the OWA 2003 (or OWA 2000 if the mailbox sits on an Exchange 2000 mailbox server). -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "sham" <shamun@mekorot.co.il> wrote in message news:envBPoGJHHA.2236@TK2MSFTNGP02.phx.gbl... >i have exchange 2003.i would like to install exchange 2007 client access >and > le...

Where condition on a query in VBA
Hey there, Is it possible to have a where condition on a query in VBA? I am opening a recordset based on my query and wanted to add a where condition on the product type as we have many products. So here is my statement: Set rs = CurrentDb.OpenRecordset("qryPromotions", dbOpenDynaset, dbSeeChanges) I want to add "where ProdType = DI" Then after I am done the processing needed for this recordset, I want to open another one, based on the same query, but for a different ProdType. I don't want to have to create 10 different queries for 10 different ProdTypes, ...

Can you sort Excel data by font color?
I heard that you can sort Excel data by font or column colors. Is this true and how do you do it? Hi see: http://www.cpearson.com/excel/SortByColor.htm and http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards Frank Kabel Frankfurt, Germany "Help Me Sort" <Help Me Sort@discussions.microsoft.com> schrieb im Newsbeitrag news:EF1E9176-5818-4185-9DD7-A74A613914E2@microsoft.com... >I heard that you can sort Excel data by font or column colors. Is this >true > and how do you do it? Only with VBA or helper columns: http://www.cpearson.com/excel/SortByC...

In Microsoft Outlook how can you delete email without opening it?
I am an Outlook Express user and you can click on email once and delete without opening it or double click to open it. Is there a way to delete without opening in Microsoft Outlook? Hello Corey, you wrote on Fri, 26 Aug 2005 06:48:02 -0700: > I am an Outlook Express user and you can click on email once and delete > without opening it or double click to open it. Is there a way to delete > without opening in Microsoft Outlook? Sorry, but I don't understand your question really. Which program is used? Outlook Express or Outlook? -- Best Regards Christian Goeller Hello C...

Query/Expression Help Required!
Hello, i would appreciate any help in regards to the problem i am having. I will try and explain it to the best of my ability (im a newbie to Access 2000) Here is a snipt of the information i have extracted via a query. I have been able to work out Duration via a calculation. "Agent Pin" "Start Time" "AUX Name" "End Time" "Duration" "7015" "13:45:21" "After Call Work" "13:54:18" "537" "7015" "14:30:25" "After Call Work" "14:31:03" &...

Excel Data Connections to Access Queries: Limits?
I have an Access 2007 database with data connections to: + Sharepoint Lists + SMS SQL tables/views I've written Access queries that use tables from those data connections. I want to use Excel to connect to those queries in the Access database to run various reports (such as charts, lists, etc...) However, I'm having 2 problems: 1) In Excel, when I go to Data / Get External Data / From Access, I cannot see all of the queries listed in the Access Database. 2) The queries I do see only return a small subset of records. (I've tested the query within Access, and I should get 17...

can't send/receive #3
Outlook 2003 I keep getting an error Operation Failed. An object couldn't be found (0x8004010F) How do I fix it? ...

can you mail merge in Excel
I have created an enrollment form in Excel (master file) and would like to merge it with data from a different worksheet and then print all of the merged files simultaneously, just like you would in Word. I did not create in Word because some fields require complex calculations. I am not up on macros, so if there is a way to do this w/o macros, even better. Excel doesn't have a merge feature. Perhaps you could do the merge in Word, using the calculations from Excel. Otherwise, you would need programming, to loop through the records you want to print. Ft. Worth user wrote: > I...

Can links between Excel 2003 Pivot Charts and their pivot table b.
Can links between Excel 2003 Pivot Charts and their pivot table be turned off or on? I am trying to show pivot charts for each page variable and if any formating is changed in one of the charts they all revert back to the default chart formatting. Loss of formatting is a known problem with pivot charts. There's information in the following MSKB article, suggests recording a macro as you apply the formatting: XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com/?id=215904 Or, you could create a normal chart from the data. On Jon Peltier's ...

How can I change the default forward account
Hi, At the moment when I try to forward an email using Outlook 2003 (with a number of pop3 accounts and a Microsoft exchange server account) it opens the email that is to be forwarded and the default account that tries to send the email is the Microsoft Exchange Server. How can I change the setting so that no emails are sent using this account at any time? I only want to use this account for sharing files and calendars, not for sending or receiving emails. (don't ask why!) Cheers Lenny ...

Can External Data(MsAccess) fill List Box?
Is this possible to create and can some one point me in the right direction to get started? Hi Matt one idea: - import your MS Access data with 'Data - Import external data - Import' on a separate sheet (can be hidden later on) - Create a defined name for this data source - use data validation (in combination with the defined name) to create a listbox HTh Frank Matt wrote: > Is this possible to create and can some one point me in the right > direction to get started? ...

Can I upgrade the version of APA referencing in Word?
I am aware that there is a new version of APA referencing which alters the way citations are to be made. Is there an update available using Word? No. Microsoft has not provided any update. Even the Word 2010 public beta still comes with the old style. If you have xslt knowledge, you could update the style yourself as it is completely open. Yves -- BibWord : Microsoft Word Citation and Bibliography styles http://bibword.codeplex.com "livliv3" <livliv3@discussions.microsoft.com> wrote in message news:30E078AA-3120-431D-8B10-CDEF8A78A4C9@microsoft.com... ...

Exchange Router Implementation Steps
Does anyone know the exact steps that must be taken in order to get incoming email routed to CRM? I've searched the internet, but have had no luck. Thanks in advance. Check out the Implementation Guide, chapter 17 "iamdavid" <iamdavid@discussions.microsoft.com> wrote in message news:CD9B1222-B7E9-4023-808C-0C686769933C@microsoft.com... > Does anyone know the exact steps that must be taken in order to get > incoming > email routed to CRM? I've searched the internet, but have had no luck. > Thanks > in advance. Daniel: Thanks for your response. ...

How can I get the version number of the installed Speech API?
Till now my MFC program uses TextToSpeech by means of the Speech SDK and SAPI 5.0. For a certain reason I must now use SAPI 5.1. Therefore I must make in my program the following decision: nothing yet installed => install SAPI 5.1 SAPI 5.0 installed => install SAPI 5.1 SAPI 5.1 installed => do nothing Can I somehow get in my MFC program the current SAPI version number with e.g. a SAPI function or similar? Thanks for any helpful comments. Eddy "Eddy" <Eddy@discussions.microsoft.com> wrote in message news:D51C8B3D-4996-43F8-8238-D8F15DC52...

update query help 07-14-10
sql 2000 i have three tables 1- create table mytab(id_no varchar(5), Counter smallint)) 2- create table Rectab(id_no varchar(5), refno smallint)) 3- create table Lib_tab(refno smallint) insert into mytab values(1,null) insert into mytab values(2,null) insert into mytab values(3,null) insert into mytab values(4,null) -- Rectab has duplicate values insert into Rectab values(1,1) insert into Rectab values(1,1) insert into Rectab values(1,2) insert into Rectab values(1,2) insert into Rectab values(1,2) insert into Rectab values(1,3) insert into Rectab values(2,1) ins...