DELETE query criteria question - MS Access 2010


,

New at this DELETE thing.

I wish to write a DELETE query that will remove records from the 'REPAIRS" table. The conditions that must be met is that the "active" field = false in the "REPAIRS" table and that the "tradeID" field in the "REPAIRTRADE" table = cet. I'm confused about the statement needed and the syntax.
Posted On: Monday 31st of December 2012 01:18:07 AM Total Views:  224
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




SQL query brainfade

Hi . New guy here. Ok, here's a project that I have. I'm wondering if anyone can give me insight if it's possible. Ok, I have two different tables. One contains a size code for an item we sell such as (along with measurements specific to that code, that are not important at this point) For information purposes, the table name is "size_data" and the value below is "size_code", which is the primary key in the table. THS-101 THS-102 THS-103 .... ... . I have another table that contains that material data that they're using. This has a code as well of : 700 701 702 727 728 ... .. . That table is called "material_data" and the value is "material_code", which is the primary code in that table. Now, what I want to do is concatenate the values, but in every combination possible so that we can document every possible combination of the numbers. For an output example: THS-101-700 THS-101-701 .. .. . THS-102-700 THS-102-701 THS-102-702 .. .. . Is a query like that even possible with SQL I can't figure it out and I'm spinning my wheels. I just want to take the first entry of one table, concatenate it to all the combinations of the second table.........go the next entry of the first table and concatenate it to all the combinations of the second table, and so on. Anybody able to help me out on this one\t
VIEWS ON THIS POST

97

Posted on:

Friday 26th October 2012
View Replies!

sql query in an asp page

hi..i am trying to read in record from a databse located in the web server... i am trying to do this sql query in an asp page..so this is an asp code...i have written the code as this just asking that is there any thing wrong with this sql query....because my page isn;t showing...and i donno if it is the right way to connect different tables...please help
VIEWS ON THIS POST

102

Posted on:

Friday 26th October 2012
View Replies!

joining a view to match main data in search query

I am currently building a telephone directory, i'm at the stage where I have the search functions runing smoothly. Except the way I have the database set up is that the main table holds the business type as a numeral which was entered from another table with coresponding names. I have built views to tie these two tables together. Now once a person searchs the listings they currently recieve a page of results, it is here that the problem occurs. I need to to in my sql statment tie the main table of data to the view to pull the business type name out instead of the business type numeral which is contained within the main data table. Here is my current Sql Statement: SELECT * FROM dbo.MAIN, dbo.VIEW_BUSINESS_NAME WHERE ENTRY_TYPE LIKE 'varSearchType' AND LAST_NAME LIKE '%varName%' AND INITIAL LIKE '%varInitial%' AND STREET_NAME LIKE 'varStreet' AND STATE LIKE 'varState' AND REGION LIKE 'varRegion' AND BUSINESS_TYPE LIKE 'varBusinessType' AND dbo.VIEW_BUSINESS_NAME.Expr1 = dbo.MAIN.BUSINESS_TYPE ORDER BY LAST_NAME It is being put together in dreamweaver mx. This is returning a multiple list of the one business repeated for as many listings as there are for that business type! Hope this makes sense it's very early in the morning.
VIEWS ON THIS POST

156

Posted on:

Friday 26th October 2012
View Replies!

sql query question...

Here is a snippet of my query..... select top 500 MAX (distinct userip), count(*) as hits, username, sum( convert( bigint, bytecount))/1024 as bytecount_MB, 0 as blocks from webproxy.dbo.transactionsunday group by userip, username union select distinct userip, 0 as hits, username, 0 as bytecount_MB, count(*) as blocks from webproxy.dbo.transactionsunday where status = '403' group by userip, username How can I get the same 500 userips from the first part, queried in the second part
VIEWS ON THIS POST

114

Posted on:

Friday 26th October 2012
View Replies!

Simple sql join - banging my head!

Ok have two tables with columns of: News Tag Lists - newstagID - newstagTopic Tags - tagID - newstagID Now I simply want to return a query with a list of ALL news articles and a column with a value to specify whether there is a tag for this, ie: newstagID | newstagTopic | Tagged 1 | Sport | 1 3 | Politics | null 4 | Business | 1 etc SELECT * FROM newsTagList returns my list, but I'm struggling to work out how to join the extra table.
VIEWS ON THIS POST

62

Posted on:

Wednesday 7th November 2012
View Replies!

ASP writing datetime to MS SQL (used to write to Access)

We have just begun the process of converting the datbase from Access 2000 to MS SQL Server 2000. I want to be able to write a null value to a datetime field(column); but I always get 1/1/1900 for a value. I want to be able to write a time value to a datetime field(column); but it is always prefixed with 1/1/1900. Am I asking/attempting the impossible! TIA, Chuck , You should NEVER allow NULL values in SQL Server, it can seriously hinder query performance. You should always set a default value on all of your columns.
VIEWS ON THIS POST

149

Posted on:

Wednesday 7th November 2012
View Replies!

Search Algorithm/Full Text Search

Hi , I want to search 27 millions of records within 2-3 seconds. when I run the T-SQL query it will take 45 seconds to give me result. Please give me some proper search algorithm to sort this out. Can I use Full Text Search Can I take this data in xml file and do search on that I used .net to accomplished this search task. waiting for positive reply.
VIEWS ON THIS POST

45

Posted on:

Tuesday 20th November 2012
View Replies!

Mapped Credentials

Hiya, I need to use Mapped Credentials in SQL Server (2008), so i'd just like clarification on what i'm doing. I am mapping to Active Directory user accounts. 1) Create the Credential in SQL Server, choosing the AD user account to be mapped 2) Create a SQL auth. User Account (Security > Logins), using the 'Map to Credential', choosing the Credential I created in step 1 However, during the creation of the credential (step 1), it asks me for a dedicated password. I would like for this to use the AD user password as we forcefully change user passwords every 6 weeks, and the fact I have to put something in makes me think it is a seperate password to the AD one. Is that the case
VIEWS ON THIS POST

52

Posted on:

Tuesday 20th November 2012
View Replies!

Business intelligence using olap

hello m currently working on a project of business intelligence.Its my final year project. i m having a company database n i have 2 analyze it n generate queries by finding patterns and create a report.how should i start the project\t\t i have database in the form of excel sheets.i have integrated d database in sql server 2008. i want 2 use business intelligence studio(visual studio 2008) as a front end and sql server 2008 as backend. is it possible\t where shud i write queries\t how do i connect sql 2008 wid visual studio\t should i use d analysis project in visual studio\t plzz help...!!!1 thanx.... sql2008
VIEWS ON THIS POST

68

Posted on:

Tuesday 20th November 2012
View Replies!

Cannot paste my text into column

Hi , I'm have a strange situation with SQL Server 2000. I am trying to paste this into a VARCHAR (8000): Admittedly this is a long piece of text but running a little letter count application I know that it is only 1211 characters long. However when I paste the above text into SQL Server I get this warning: "The value you entered not consistent with the data type or length of the column." I read on the web a little and discovered that somebody had this problem and they had used NVARCHAR instead which fixed it for them, this has not worked for myself though. Does anyone have any idea what SQL is objecting to
VIEWS ON THIS POST

68

Posted on:

Tuesday 20th November 2012
View Replies!

MSDE And remote connections

We have done a programme that connects to a SQL 2000 SERVER with ADO librarys, and it works ok both using ip address and machine names. But the same programme fails if it trys to connect to a MSDE, if the MSDE and the client programme are in differents machines. On the other hand it works propperly if MSDE and the client programme are in the same machine (DATA SOURCE=(local) in the connection string). Is that normal Should MSDE answer to remote connections Thank you very much for your help.
VIEWS ON THIS POST

88

Posted on:

Tuesday 20th November 2012
View Replies!

How to store/append images to DB

Hi I have worked in SQL/DBs but never had an experience of adding/appending images. Simply i want to display images on my website but these images are that of the ads that I'll receive from vendors so they can't be hard coded and will be changes on daily if not weekly basis. The following link explained the ways of accomplishing this but forbade from the first two. 1. Store images right into the DB (Doesn't recommends) 2. Store images (image data) inside a binary field (Doesn't recommends) 3. Store (upload) images in the folder and just upload the address to it. The third one is recommended (is this really the best one) but the article also says that The only problem to this approach is that you must synchronize the data in the link field with your file system. What does that mean Please elaborate. What and how do we synchronize CB
VIEWS ON THIS POST

61

Posted on:

Tuesday 20th November 2012
View Replies!

Database design?

I have inherited a fairly large database that is in a bit of a mess with null fields etc. The database is made up of a number of records in a single table with links to a variety of different types of content such as pdfs, images, video clips etc. These resources exist in one folder on the web server. I was thinking about ways to improve this database. Do you think it would be helpful to divide the different types of content into new tables in the database It would however mean that i would be searching several tables instead of one. Maybe even separate like content into different folders. So basically have one folder containing all pdfs, one folder containing video clips etc What do you think
VIEWS ON THIS POST

51

Posted on:

Tuesday 20th November 2012
View Replies!

Counting number of columns that contain data

I got a table with 10 column. I would like to count the total of column from A1 - A8 which contain with data for a particular record. Example: Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10 ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ID No. A1 A2 A3 A4 A5 A6 A7 A8 with the data as such: 01. 10 x1 x2 x3 x4 The above example should return value "4". Cause from column A1 to A8... only 4 column is filled with data. Is there such query to perform the count
VIEWS ON THIS POST

90

Posted on:

Tuesday 20th November 2012
View Replies!

gah more errors

grr i don;t understand this error. here it is: ADODB.Recordset error '800a0bb9' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. send.asp, line 23 here are my lines of code 21-23: strSql = "SELECT Email FROM newsletters2" Set rs = Server.CreateObject("ADODB.Recordset") rs.Open strSql, con, 1, 2 any ideas what's wrong
VIEWS ON THIS POST

87

Posted on:

Tuesday 20th November 2012
View Replies!

Looping info into an SQL function

Hi I need to get a list of numbers from an sql satement i.e SELECT name_ctr FROM actNamePropertyAddressView then I need to do a loop inserting the name_ctr value in the procedure below select dbo.funnucNamePostAddress(name_ctr), 'pr', 0, GETDATE()) then I need to output the results I would appreciate any feedback on this
VIEWS ON THIS POST

73

Posted on:

Tuesday 20th November 2012
View Replies!

Confused...?

all, I've been using access for most of my applications but have decided to pickup a copy of MS SQL7 w 10 Cal to learn. I purchased an open box of the software and it contained one cd, license agreements, and manual. But when I attempt to install I get an error stating 'incorrect machine type'. The cd states Alpha Processor. Am I missing a cd for x86 Server Specs: 1.9 GHz Pent 4 4 - 100 Gig IDE drives 1 gig ram Windows 2000 Server ADV
VIEWS ON THIS POST

94

Posted on:

Tuesday 20th November 2012
View Replies!

All I want for the Holidays...

The feeling of the holiday season is in the air: people are whistling, the birds are migrating, and Old Jack Frost has decided to pay a visit once more. Of course, when youre in South Florida, that means that the air outside has dropped to a nippy 22 degrees Celsius, that is. Yes, the sweater inducing room temperature is almost too much for us Floridians to handle, but we get through. When it gets this nice and cool, one cant help but be happy, think about music, love, and look nostalgically back at sitting down in front of a warm glow, taking in some of those wonderful holiday treats: Cookies, Cocoa, and HotJava. In fact, this time of year makes me think of that wonderful holiday classic, The Night Santa Went Crazy, by the masterful Al Yankovick; its what I like listening to on the long drives from Ft. Lauderdale to the frozen tundra of Tampa, FL, where my family takes residence. Its always interesting to pull up next to the house and see them there, enjoying the weather next to their Igloo. Holiday traveling: theres nothing in the world like it. Of course, the feeling of the holiday season isnt just about temperatures, travels, and things remembered. To a great many of us, this is the season for giving kind of like celebrating a month long birthday party. Of course, we here at Developer Shed are always in the giving spirit towards our readers, which is why this month were giving away a FREE Sound Blaster MP3+ USB External Sound Card. Thats right, folks, you no longer have to sit through those long flights and drives with nothing more than the crappy sound card your laptop or PDA came with. This nice little item allows you to have high quality sound in even the most aurally challenged portable devices. This is truly a highly deed item, by man and beast alike! But how, pray tell, do you enter this months giveaway The rules are simple: 1) Write and submit an article to norbert@developershed.com, with the subject "Developer Shed MP3+ Contest" for publication in any of our websites and win this awesome device, featured in an upcoming review. (Don't worry. The review was written before our marketing monks came up with the contest, so the review's content has not been affected.) The article, a tutorial, hardware review, tech-oriented personal experience tale, or news-worthy report on a tech related field (no corporate business coverage of X, Inc. is suing Y.com, please), must be about one of the topics covered on one of the following websites: Dev Shed, ASP Free, Dev Articles, Dev Hardware, and SEO Chat. This pretty much covers all aspects of development and todays technology. The article must be at least 1500 words in length, subdivided into at least 5 pages of information. (Check out http://www.devhardware.com/hardware/how-to/809,1/ for an example of what were looking for.) 2) The Contest runs until December 31, 2003, so your article must be in by that time. Articles may be submitted in the following formats: DOC, SWX, RTF, TXT, and HTML (please do not use the Save as HTML function in MS Word or Star Office). If there are images in the story, please send them in a .ZIP file containing both the pictures and the article. Articles (and inquiries) should be sent to norbert@developershed.com, with the subject "Developer Shed Contest Entry." 3) Judging will look at subject matter, quality of content (so make it interesting), and grammar. In fact, chances are that if we have to edit it, you ain't winning. It might still be published, so not all is lost! You may yet still have the chance at the fame and geek chic that comes with having your writings read by millions worldwide. I don't want to have to spend three days correcting grammatical mistakes, though - I'm not your English teacher, daing it! By submitting an article you agree to give Developer Shed exclusive publication rights to the story. the credit for genius, however, will stay with you, so no worries about my placing our grubby little name on the story and claiming that its our brainchild. The winner will be announced on the website and via phone call, or e-mail, or IM which ever is easiest to reach him/her at. Start your typing!!! Gnorb P.S. Check this posting once in a while in case there's something which needs to be changed in the contest rules.
VIEWS ON THIS POST

101

Posted on:

Tuesday 20th November 2012
View Replies!

Odd date/locale issue

We are having a problem updating a datetime field via an ASP script. The ASP script is executing a stored procedure and passing it a datetime in the format '2008-08-01 00:00:00'. However, the database is storing this as the 8th of January 2008. If I run the exact SQL query that the ASP script does through the SQL Server Management Studio then the date is stored correctly as the 1st of August 2008. Any clues I'm at a total loss.
VIEWS ON THIS POST

75

Posted on:

Tuesday 20th November 2012
View Replies!

Comma Separated Values

I need deptno and against/beside it comma separated ename's for join dept.deptno=emp.deptno in a single query.
VIEWS ON THIS POST

26

Posted on:

Tuesday 20th November 2012
View Replies!

Spid auto clearing time

everybody, We tries to connect to the database, a new process (SPID) is created. Even when we close all connections properly, the SPID remains and it is automatically cleared after some time locally. On our hosting server we see that the SPIDs are not cleared for quite long time. any SPs/functions idea on finding out the clearing time
VIEWS ON THIS POST

104

Posted on:

Tuesday 20th November 2012
View Replies!

SQL connection question

Hi , so far i'm only using MS Access database, and i'm also using the following connection: Code: my question is, if i decide to move to MySQL, can i still use the same connection as above, and if not what should be changed also does the Access database need to be converted to MySQL database and how can i do that Finally i'm using 7host.com to host the asp files and the database, how can i upload the MySQL database and access it from the asp files also would the code in the asp files change if the database is changed, like( SQL commands) thx in adv..
VIEWS ON THIS POST

140

Posted on:

Tuesday 20th November 2012
View Replies!

Query: datetime dataType issues

Hi. I have a table with a datetime DataType. The datetime is stored like such: 11/23/2004 11:23:45 AM If I try to query my data by a specific date '11/23/2004' no records are returned. If I try to do a date range BETWEEN '11/23/2004 12:00:01 AM' AND '11/23/2004 11:59:59 PM' my webpage throws back and error.
VIEWS ON THIS POST

157

Posted on:

Tuesday 20th November 2012
View Replies!

DTS - using multiple data sources

How do I use a view or query that references two different data sources ie 1 Oracle (APPPCT_PROJECT_TYPE ) and 1 table within my Sql. (ProjectTypes) I want to use a query that looks and the data in the Oracle table compares it against my current table to determine if there are differenes and if so update current table with the differences in the data. This is the query I would have used in Access: Help me to convert it use using DTS. INSERT INTO ProjectTypes ( ProjectTypeCode, ProjectTypeDescrip, ProjectCatagory, ProjectClass, CapitalProjectNum, DateCreated, UserCreated, DateModified, UserModified ) SELECT APPPCT_PROJECT_TYPE.ID, APPPCT_PROJECT_TYPE.DESCR, APPPCT_PROJECT_TYPE.PROJ_DESCR_CATEGORY, APPPCT_PROJECT_TYPE.PROJ_CLASS, APPPCT_PROJECT_TYPE.CPN, Now() AS Expr1, "import" AS Expr2, Now() AS Expr3, "import" AS Expr4 FROM APPPCT_PROJECT_TYPE LEFT JOIN ProjectTypesON APPPCT_PROJECT_TYPE.ID = ProjectTypes.ProjectTypeCode WHERE (((ProjectTypes.ProjectTypeCode) Is Null)); Any assistance in this mater is greatly appreciated. Sincerely, Karen
VIEWS ON THIS POST

198

Posted on:

Tuesday 20th November 2012
View Replies!

Link request

, Does anyone have any website links related to SQL Server 2000 table relationships I can't get my 1-n relationship to work properly plz help,
VIEWS ON THIS POST

71

Posted on:

Tuesday 20th November 2012
View Replies!

Urgent! Please help. Can't insert!

I am running SQL2000 on W2k server. Everything was working fine untill sometime yesterday. in a sudden, I was not able to insert into my database table. I can do select, and I can even run sp_columns on the table, but when I try to INSERT, I get the following error ... Server: Msg 208, Level 16, State 3, Line 4 Invalid object name [tablename]. The insert statment should be working fine as they were run before the problem occurs! Has anyone ran into this before Any input is appreciated!
VIEWS ON THIS POST

103

Posted on:

Wednesday 21st November 2012
View Replies!

Policy Management

I need to create policy to check and see which users have WITH GRANT Permissions on the database.
VIEWS ON THIS POST

215

Posted on:

Monday 26th November 2012
View Replies!

Query problem with 2 FK in table

, I have a problem with a query... and 2 tables I want to associate each travel with a departure and an arrival port. I keep all the ports in another table TravelsTable - Travel_ID(pk) ; DeparturePort(fk) ; ArrivalPort(fk) PortTable - Port_ID(pk) ; PortLocation my two foreign keys in the TravelsTable are linked to the Port_ID in the PortTable... so I have records like this in the Tables Travel_ID=1 DeparturePort = 1 ArrivalPort = 4 Travel_ID=2 DeparturePort = 3 ArrivalPort = 2 Travel_ID=3 DeparturePort = 4 ArrivalPort = 5 Port Table Port_ID=1 PortLocation= London Port_ID=2 PortLocation= Le Havre Port_ID=3 PortLocation= New York Port_ID=4 PortLocation= La Rochelle Port_ID=5 PortLocation= Zeebrugge ---------------- What I need is to have a list with the Name of the departure/arrival ports... I need something like this Travel_ID Departure Arrival 1 London La Rochelle 2 New York Le Havre .... Can you help me writing this query, in SQL I read about joins but I can't seem to put things together... I'm not even sure is the way to do it
VIEWS ON THIS POST

98

Posted on:

Monday 26th November 2012
View Replies!

Concatenate records from a sub-query together.

Hi , I'm building a website for a floor-tiles company and Im currently working on the online sample request section. I have a table called tblRequest and another table called tblSample. tblSample stores a list of information on all of the tiles available and tblRequest contains info on each request made by a customer. I have a third table calledtblSampleRequest which basically acts as the middle man for the many-to-many relationship between tblRequest and tblSample... tblRequest -> tblSampleRequest
VIEWS ON THIS POST

133

Posted on:

Monday 26th November 2012
View Replies!

passing parameter to stored procedure in Access

This is my first time using sql server and there's lots I'm still figuring out. I know how to pass a parameter to a stored procedure when my stored procedure is the data source for a report. But this is different - I created a stored procedure that gives data needed for a mail merge. The caseID is variable. I have a button on my case form that, when clicked, outputs the stored procedure to an excel document so that it can be used as a mail merge source. Right now it (as expected) prompts the user to enter the Case ID. But that is a hidden field anyway. I want to take the Case ID from the current record and make it the value of @CaseID. Can anyone tell me how to do this
VIEWS ON THIS POST

113

Posted on:

Monday 26th November 2012
View Replies!

what's the error in my mssql statement? :(

hi i always used this type of command PHP Code: \t\tresponse.write("") \t\t\t\tresponse.write("") \t\t\t\tresponse.write("") \t\t\t\tresponse.write("") \t\t\tresponse.write("") to send out my data to mssql.. but problems will occur when the users fill in the CommonName fields with values with whitespaces and/or ' how should i fix this in java there's some method called preparedstatement.. how about in asp any help is greatly appreciated.
VIEWS ON THIS POST

125

Posted on:

Monday 26th November 2012
View Replies!

dsn problem?????urgent

hi... at one server my asp files & at other server my sql database. i had created dsn for connection.....and now i m using this dsn to my asp files which is at other server......it gives me invalid connection or connection is closed........ can any one tell me proper way to uset it......... its urgent..........thanx
VIEWS ON THIS POST

116

Posted on:

Monday 26th November 2012
View Replies!

Extraneous spaces in job text output

Hi Rather than providing several web-based reports for the company directors, I've opted to produce these with the SQL Server Agent by running the queries as overnight jobs and sending the output to .txt files in a secure folder Everything is absolutely fine apart from the fact that the reports always have masses of white space between columns: Code: See attachment Is anybody aware of what causes there to be so much space between columns and if it's possible to reformat the report or the query to stop it
VIEWS ON THIS POST

108

Posted on:

Monday 26th November 2012
View Replies!