try four statments 1. Select * into temp from bom where partno ='1'; 2.
delete FROM bom where ( Select Count(*) as mycount from bom group by partno having bom.partno= partno and partno ='1' ) >1 ; 3. insert into bom Select top 1 * from temp; 4. Drop table temp Delete from bom WHERE PartNo = 'YC21775' and Count (*) >1 --- In [EMAIL PROTECTED], "Julian Holt" <[EMAIL PROTECTED]> wrote: > Chad > > Looks like its a syntax thing > > The query > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) > > Doesn't work BUT > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM WHERE PartNo = 'YC21775') > > Does > > Similary your query > > DELETE BOM FROM > (SELECT TOP 1 * FROM BOM) AS x1 > WHERE BOM.PartNo = x1.PartNo > AND x1.PartNo = 'YC21775' > > seems to need to be > > DELETE BOM FROM > (SELECT TOP 1 * FROM BOM AS x1 > WHERE BOM.PartNo = x1.PartNo > AND x1.PartNo = 'YC21775') > > Anyway it seems to be working now > > Thanks again. > > Julian > > > > > > -----Original Message----- > From: Julian Holt [mailto:[EMAIL PROTECTED] > Sent: 14 May 2004 13:06 > To: [EMAIL PROTECTED] > Subject: RE: [vbhelp] Re: Simple database question (I guess) > > > Hi Chad > > Sorry, I didn't explain correctly > > The query - DELETE BOM FROM (SELECT TOP 1 * FROM BOM) WHERE PartNo = > 'YC21775' > > is a manual test that I have been performing directly in Access to see if > it > works. YC21775 is a random part number in the DB. The above query doesn't > work. > > Strange thing is that it seems to pick the correct part number 'YC21775' > if > you just try the SELECT bit, i.e > > SELECT TOP 1 * FROM BOM WHERE PartNo = 'YC21775' > > Works OK and produces a table with the single part YC21775 but when I run > the full query in Access > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) WHERE PartNo = 'YC21775' > > it says "Deleting 0 rows OK to proceed > > Same thing happens with your query > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > x1.PartNo > AND x1.PartNo = 'YC21775' > > I have noticed that I can put anything in the part number string with the > same result > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > x1.PartNo > AND x1.PartNo = 'GarbageString' > > it says "Deleting 0 rows OK to proceed > > So basically when the DELETE is added it no longer finds the partnumber > (unless it happens to be the very first item in the full table) > > I am confused! > > Julian > > > -----Original Message----- > From: Chad [mailto:[EMAIL PROTECTED] > Sent: 14 May 2004 12:36 > To: [EMAIL PROTECTED] > Subject: RE: [vbhelp] Re: Simple database question (I guess) > > > Julian, > > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > > x1.YC21775 > > The above select will fail if YC21775 is not a valid fieldname like > PartNo. > My guess is this will work: > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > x1.PartNo > > But that will delete the very (any) first record it encounters. You will > have to filter it down further by extending the WHERE clause: > > DELETE BOM FROM > (SELECT TOP 1 * FROM BOM) AS x1 > WHERE BOM.PartNo = x1.PartNo > AND x1.PartNo = 'YC21775' > > I'm assuming (maybe incorrectly) that youre trying to delete PartNo > YC21775 > (?) > > hth > > Chad > > > > > -----Original Message----- > > From: Julian Holt [mailto:[EMAIL PROTECTED] > > Sent: 14 May 2004 13:00 > > To: [EMAIL PROTECTED] > > Subject: RE: [vbhelp] Re: Simple database question (I guess) > > > > Hmmm I am still having problems > > > > I am working on something like, > > > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > > x1.YC21775 > > > > This is almost there but not quite. > > > > The query produces an error in VB. > > > > When I run this query directly in Access I am prompted to > > "Enter Parameter Value" into a dialog box. > > > > I can seem to type in anything to complete the query (1, 0 > > etc) but I can't figure out what to add to the SQL to make > > this work as a single string. > > > > I.E > > > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > > x1.YC21775 1 > > > > Doesn't work. > > > > Any idea what the "Parameter" is and how I apply it in my query? > > > > > > Thanks again > > > > Julian > > > > > > -----Original Message----- > > From: Julian Holt [mailto:[EMAIL PROTECTED] > > Sent: 12 May 2004 10:39 > > To: [EMAIL PROTECTED] > > Subject: RE: [vbhelp] Re: Simple database question (I guess) > > > > > > "Well I never" > > > > Worked first time....................... > > > > Thanks Chad, I have spent hours on this - YOU ARE THE MAN > > > > Cheers > > > > Julian > > -----Original Message----- > > From: Chad [mailto:[EMAIL PROTECTED] > > Sent: 12 May 2004 10:05 > > To: [EMAIL PROTECTED] > > Subject: RE: [vbhelp] Re: Simple database question (I guess) > > > > > > > PartNo Description > > > 111 Hello > > > 123 Hello > > > 456 World > > > 123 Hello > > > 123 Hello > > > 123 Hello > > > 123 Hello > > > > > I want to delete the first occurrence of "123 > > Hello" to leave > > > > You may want to use the TOP clause. I have not tested > > this in Access, but > > I > > know it works in SQL. > > > > DELETE yourtable > > FROM (SELECT TOP 1 * FROM yourtable) AS x1 > > WHERE yourtable.partno = x1.partno > > AND x1.partno = 123 > > AND x1.description LIKE 'Hello' > > > > More info here: > > http://msdn.microsoft.com/library/en-us/tsqlref/ts_de- dz_9lut.asp > > > > -Chad > > > > > > > > > > '// ======================================================= > > Rules : http://ReliableAnswers.com/List/Rules.asp > > Home : http://groups.yahoo.com/group/vbHelp/ > > ======================================================= > > Post : [EMAIL PROTECTED] > > Join : [EMAIL PROTECTED] > > Leave : [EMAIL PROTECTED] > > '// ======================================================= > > > > > > > > Yahoo! Groups Sponsor > > ADVERTISEMENT > > > > > > > > > > > > > > ------------------------------------------------------------- - > > ------------ > > -- > > -- > > Yahoo! Groups Links > > > > a.. To visit your group on the web, go to: > > http://groups.yahoo.com/group/vbhelp/ > > > > b.. To unsubscribe from this group, send an email to: > > [EMAIL PROTECTED] > > > > c.. Your use of Yahoo! Groups is subject to the Yahoo! > > Terms of Service. > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > '// ======================================================= > > Rules : http://ReliableAnswers.com/List/Rules.asp > > Home : http://groups.yahoo.com/group/vbHelp/ > > ======================================================= > > Post : [EMAIL PROTECTED] > > Join : [EMAIL PROTECTED] > > Leave : [EMAIL PROTECTED] > > '// ======================================================= > > > > > > > > Yahoo! Groups Sponsor > > ADVERTISEMENT > > > > > > > > > > > > ------------------------------------------------------------- - > > -------------- > > -- > > Yahoo! Groups Links > > > > a.. To visit your group on the web, go to: > > http://groups.yahoo.com/group/vbhelp/ > > > > b.. To unsubscribe from this group, send an email to: > > [EMAIL PROTECTED] > > > > c.. Your use of Yahoo! Groups is subject to the Yahoo! > > Terms of Service. > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > ------------------------ Yahoo! Groups Sponsor > > ---------------------~--> Make a clean sweep of pop-up ads. > > Yahoo! Companion Toolbar. > > Now with Pop-Up Blocker. Get it for free! > > http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/k7folB/TM > > ------------------------------------------------------------- - > > -------~-> > > > > > > '// ======================================================= > > Rules : http://ReliableAnswers.com/List/Rules.asp > > Home : http://groups.yahoo.com/group/vbHelp/ > > ======================================================= > > Post : [EMAIL PROTECTED] > > Join : [EMAIL PROTECTED] > > Leave : [EMAIL PROTECTED] > > '// ======================================================= > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > '// ======================================================= > Rules : http://ReliableAnswers.com/List/Rules.asp > Home : http://groups.yahoo.com/group/vbHelp/ > ======================================================= > Post : [EMAIL PROTECTED] > Join : [EMAIL PROTECTED] > Leave : [EMAIL PROTECTED] > '// ======================================================= > > > > Yahoo! Groups Sponsor > ADVERTISEMENT > > > > > > ----------------------------------------------------------------- --------- > -- > -- > Yahoo! Groups Links > > a.. To visit your group on the web, go to: > http://groups.yahoo.com/group/vbhelp/ > > b.. To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of > Service. > > > > [Non-text portions of this message have been removed] > > > > > '// ======================================================= > Rules : http://ReliableAnswers.com/List/Rules.asp > Home : http://groups.yahoo.com/group/vbHelp/ > ======================================================= > Post : [EMAIL PROTECTED] > Join : [EMAIL PROTECTED] > Leave : [EMAIL PROTECTED] > '// ======================================================= > > > > Yahoo! Groups Sponsor > ADVERTISEMENT > > > > > > ------------------------------------------------------------------- --------- > -- > Yahoo! Groups Links > > a.. To visit your group on the web, go to: > http://groups.yahoo.com/group/vbhelp/ > > b.. To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. > > > > [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor ---------------------~--> Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar. Now with Pop-Up Blocker. Get it for free! http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/k7folB/TM ---------------------------------------------------------------------~-> '// ======================================================= Rules : http://ReliableAnswers.com/List/Rules.asp Home : http://groups.yahoo.com/group/vbHelp/ ======================================================= Post : [EMAIL PROTECTED] Join : [EMAIL PROTECTED] Leave : [EMAIL PROTECTED] '// ======================================================= Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/vbhelp/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
