I am currently using enterprise manager to run multiple queries on a
single table in a DB. I refresh these queries every few minutes. Due
to the huge number of them I was looking for a better way (or should I
just say "a way") to manage/save these queries so I can recall them
easier/faster for monitoring purposes. Suggestions?
TIA.
Hi,
I have to manage many SQL Server databases on several servers. How can I manage the jobs, backups and the space on the disc without going to each and every server and database and job? Is there any script to run this? It will be very helpful if you can provide me the sample script or point me to any web site where I can get the info/script for this. Thanks in advance...
Well the boss suddenly decided to slap me with one mean!!! (when you have never worked with SQL/MSDE) project, any help or guidance will be gretly appreciated :)
Q: Also understand how MSDE works. Find out if an MSDE database can be used independently of SQL Server. i.e. figure out how an MSDE database gets used as an "off-line" version of SQL Server. I.e. so you can carry a portion of a SQL Server database away with you on a notebook and synchronise back when you re-connect
..... the question is a little broken but i'm sure one of you wiz's will understand,
BTW. I have a little knowledge of Enterprise manager, SQL DB's, Backing up/Restoring, basics in other words, so I might seem a little slow ;) if you bombard me with complicated replies.
Thanks once again.
If I am using Ado.Net from a .Net Class library hosted outside SqlServer to do Sends and Receives do I have to use anything else other than queues? The reason I am asking is because I already have an abstraction layer for a couple of our queueing systems and I was hoping to put SSB in there as well but the abstraction layers' primitive is the Queue not Conversation/Dialog/Service, etc.
Thanks
Costas
im trying to gain some experience using mssql. in my system tray there is a sql server service manager. my question is this...what do i use to interface with sql server? ive seen messages referencing 'enterprise manager', but i cant find it or this thing called 'query analyzer'.
im running windows 2000 professional. i downloaded and installed a program called 'database fishing tool' which seems to do what i need but, im asked for a username and password. since ive never connected to the sql server i dont know this info.
ive scoured the web for anything to help me with this, but cant find any info for what seems would be the most fundamental...
please help. thank you.
Hi,very new to SQL queries, and strugling with join conceptI had to do a join based on a single field:select*fromtableA, tableBwheretableA.value = tableB.value(+);this works finebut how can i do the same thing while comparing multiple columns ... iwas thinking something like this: (obviously doesn't work)select*fromtableA, tableBwhere[tableA.value1, tableA.value2] = [tableB.value1,tableB.value2](+);Is there some sort of "tuple" comparison I can do?Thank you.
View 2 Replies View Related.......I need to know how to have that displayed before the reports are ran. I'd like to have in the Report Parameters area so users can see this information when making choices for the reports.
View 1 Replies View RelatedHi,After a power outage (and UPS failure), my boss's SQL Server 2000 won'tstart (or crashes quickly or something). We don't have any backups, as it'sa test server. Is there a way to import the database files into a new SQLinstallation? By "installation," I mean either a re-installed SS, a secondinstallation on the same PC, or maybe even on another PC. In another words,given only the files sitting on the harddrive, is there a way to "import"the DBs in those files into SS?Sp_attach_db would probably be the answer, except it seems to requirerunning sp_detach_db...which I cannot do, as SS is not working.Thanks in advance,Jay
View 2 Replies View RelatedHi
I have two queries, one which takes 1 second to execute and the other which takes less that a second to execute.
I need to join them up similar to the following but when I do it take forever for them to execute.
select *
from table
where column not in (select column from table2)
It is quite complex and I have a couple of views included in the queries but it executes quickly on my dev database and when I run it on the live one it takes forever. Granted there is alot more data on the live database but the individual queries only take a second to run.
Does anyone know why there would be such a time increase whenever I join the queries and run them on my live database?
Any help would be appreciated.
Thanks,
Frankie
*************************************************************
<code> tags added by moderator. Please use <code> and
</code> tags to bracket comments to make code readable.
*************************************************************
I am a beginner to this whole .net thing so my code is probablly scary, but anyway, I am looking for a way to insert records into a database and then get the last ID. I am sure there is a much better way to do this, but here is the code that I have that ins't quite working:
'SQL Insert data'
Dim Conn As SqlConnection
Dim Rdr As SqlDataReader
Dim strConn As String = AppSettings("doConnect")
Dim strSQL As String = "INSERT INTO USERS (username, password, accountNum) VALUES ('" + Email.Text.Trim() + "', '" + password.Text.Trim() + "', '" + Session("accountNum").Trim() + "')"
Conn = New SqlConnection(strConn)
Dim Cmd As New SqlCommand(strSQL, Conn)
Conn.Open()
Cmd.ExecuteNonQuery()
'SQL Get Last ID
Dim ConnGet As SqlConnection
Dim RdrGet As SqlDataReader
Dim strConnGet As String = AppSettings("doConnect")
Dim strSQLGet As String = "select MAX(id) as maxid from USERS"
ConnGet = New SqlConnection(strConn)
Dim CmdGet As New SqlCommand(strSQL, Conn)
ConnGet.Open()
If CmdGet.ExecuteScalar() <> 0 Then
RdrGet = CmdGet.ExecuteReader()
While RdrGet.Read()
Session("theID") = RdrGet("name")
End While
Else
Session("theID") = 0
End If
Does anyone know how to count multiple columns in a SQL Query from One table and return numbers I can use the following but don't know how to join the two statements to one.
SELECT COUNT(*) AS [NUMBER OF SERVERS]
FROM DBO.OS
WHERE (OSTYPE LIKE '%SER%')
The other statement is
SELECT COUNT(*) AS [NUMBER OF WORKSTATIONS]
FROM DBO.OS
WHERE (CAPTION LIKE '%9%') OR (CAPTION LIKE '%PRO%') OR (CAPTION LIKE '%ME%')
If any one has any idea's please reply or email me directly. I would greatly appreciate it.
I'm writing an ASP page for a project and it requires multiple queries.However, I'm trying to combine multiple SELECT statements, but can'tfigure out a way that actually works.Basically, here are the SELECT statements I want to combine:SELECT * FROM schoolrequest WHERE SLid=10SELECT SLlastName FROM academicofficeWHERE schoolrequest.SLsendToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLbillToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLrequestorId=academicoffice.AOidSELECT * FROM diaryentry WHERE diaryentry.DEkeyValue=10AND diaryentry.DEdeletedDate IS NULLThe academicoffice table just contains basic contact info, but needs tobe used 3 times in the query to get specific contact info for 3different contacts(SLsendToId, SLbillToId, SLrequestorId)The diaryentry table contains info entered in by students. TheDEkeyValue is actually the primary id of the schoolrequest table(SLid).Any ideas?Thanks,M
View 4 Replies View RelatedHello everyone...
I have created a report that pulls data from 5 different tables. I have created a tableadapter for this. I have a sql stored procedure with left joins that I am trying to use but keep getting key violations and not null value violations when I try to use it. I have tried setting the NULLValue to NOT throw exception but that has not helped. I have also tried to writing a query for each table. I can add each query to my tableadapter but I can use them all at the same time for some reason. Can this be done? When I go to my report and try to add fields to it from the tableadapter, I only see the results of one query. I am only trying to SELECT. I am not doing any updates or deletes to the tables on the SQL Server.
Any advice would be greatly appreciated.
I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data. I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.
View 4 Replies View RelatedI hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid. currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
View 12 Replies View RelatedIf you have a dozen or so queries related to the same thing such as an exception report do you wrangle with the query to make it a one stop shop or do you do something different? Is it possible to drop them into a SP and have them all execute at the same time?
I am running queries for against business rules and wanted to do it the right way. I don't have a solid mentor at work, two guys who are experienced and off a lot but not in the world of reports.
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below.
/* will remove for aspx page use */
USE Database
/* these params are on the page in drop down boxes*/
DECLARE @ProductID int;
DECLARE @BuildID int;
DECLARE @StatusID int;
/* static params for this sample */
SET @ProductID = -1;
SET @BuildID = -2
SET @StatusID = -3
/*
the query that will build the datagrid. currently this runs and produces three different result sets.
How do I combine these statements so they produce a single set of results?
*/
IF (@ProductID = -1) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))
IF (@BuildID = -2) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))
IF (@StatusID = -3) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
Hi,
I have the below query that has multiple select statements from the same query. Is it possible to run this more efficiently with a SUM/ CASE statement so that its just one SELECT ?
I just used a SUM(CASE WHEN ....... statement on another query, but I am having trouble figuring out how to integrate it on this one, and if its even possible or worth it.
Thanks very much for any help!!
mike123
CREATE PROCEDURE [dbo].[select_mailbox_Count]
(
@userID int
)
AS SET NOCOUNT ON
SELECT count(*) as totalInbox,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 0 and deletedbyRecipient =0 ) as totalInbox_UnRead,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 2 and deletedbyRecipient =0 ) as totalInbox_UnReplied,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID AND deletedByRecipient = 0 AND deletedBySender = 0 ) as totalOutbox,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID or messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0 ) as totalTrash
FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND deletedByRecipient = 0
I'm cleaning data which involves updating ~12 million rows with threedifferent models, progressively. First clean values using the modelwith finest granularity, then the remainder with the next model,finally what's left using the last model. The first model sets ~1/2 ofthe rows, the second ~1/4, the third ~1/5, and the remaining 5% don'tget updated.It's something like this:UPDATE t SET value=value*m.AdjustmentFactor, updateFlag='updated'FROM Table t JOIN Models m ON ....WHERE m.ModelID='first model' AND t.updateFlag IS NULLStart with 'first model' then 'second model' etc.I'm wondering what happens if I submit all three queries together, oras three separate submissions, waiting for the one before to complete.If I do them all as one group, the query planner might plan for thesecond and third updates based on the initial distribution of values.However, the first update removes half of the rows from consideration,so it seems to me a new plan should be prepared for the second query,based on the distribution at that time. If I highlight the queries inQuery Analyzer and execute, are all three plans created at thebeginning? Does putting GO between them (which I do) make anydifference?This is SQL Server 2000.Thanks,Jim
View 2 Replies View Related
I've written a SQL script to go in and pull student grades for honor roll. The first subquery in the script eliminates any students that have any D's, and the second sub query is supposed to give me a only students that have no more than 1 C of any type as the Honor Roll criteria is that a student has to have a GPA between 3.0 and 3.49 and no more than 1 C.
If I run each individual sub query by itself they work and pull the students that they are supposed to. If I run the main query along with the first sub query to eliminate students with D's everything works. If I run the 2nd sub query by itself it will pull the students that meet the criteria. If I run the main query along with both sub queries or the main along with the 2nd sub query it doesn't work.
Here is what the query looks like that I'm trying to run.
Thanks in advance for any help.
select distinct i.personid, i.lastname + ',' + ' ' + i.firstname as student, p.studentnumber, e.grade,
t.name as Term, gs.score, c.name as course, sec.teacherdisplay
from gradingscore gs
JOIN [identity] i on i.personid = gs.personid
JOIN person p ON p.personid = i.personid
JOIN enrollment e on e.personid = p.personid
JOIN term t on t.termid = gs.termid
JOIN v_TermGPA tg on tg.personid = gs.personid and tg.calendarid = gs.calendarid
JOIN v_GpaTermCalc tc ON tc.personid = gs.personid and tc.calendarid = gs.calendarid
JOIN section sec on sec.sectionid = gs.sectionid
JOIN course c on c.courseid = sec.courseid
where gs.calendarid = 20 and t.name = '2nd 6wk' and tc.gpa between 3.0 and 3.49 and
e.enddate is null
--Sub Query to eleminate students with any D's in scores
and p.personid not in
(select distinct gs1.personid from gradingscore gs1
join Term t1 ON t1.termid = gs1.termid
join v_TermGPA tg1 ON tg1.personid = gs1.personid
where gs1.calendarid = 20 AND t1.name ='2nd 6wk'and
tc.gpa >=3.0 AND tc.gpa <= 3.49
and gs1.score IN('D+','D', 'D-')
--Sub Query to eliminate students with more than 1 C of any type in scores.
and gs.personid not in
(select distinct gs2.personid from gradingscore gs2
join Term t2 ON t2.termid = gs2.termid
join v_TermGPA tg2 ON tg2.personid = gs2.personid
where gs2.calendarid = 20 AND t2.name ='2nd 6wk'and tc.gpa >=3.0 AND tc.gpa <= 3.49
and gs2.score IN('C+','C', 'C-')
group by gs2.personid
HAVING count(gs2.score)<=1))
order by i.[student]
Hi,
I have some questions about making muliple T-SQL queries againt an SQL-server. I retrieve all rows from an SQL-server table called ActivityGroup and add the result to a Radio Button List. Suppose, I also wish to add the total sum of every "ActivityGroup" value to the Radio Button List. I guess this ought to be done using a SELECT COUNT statement and then retrieved using the Executescalar method?
The question is do I need to copy the code for the SELECT ('*) statement and make the neccessary changes for the SELECT COUNT statement and Executescalar method or is there a more simple way? Is it possible to use two command statements in the same code block?
The total sum is going to be calculated using the values from both an SQL-server and an Access database. The OleDb sample below is what I refer to as a code block. I appreciate any help!
string connectionString = ConfigurationManager.ConnectionStrings["ServetteConnectionString"].ConnectionString;
string sQuery = "SELECT * FROM Aktivitetsgrupper where aktivitetsid = " + Request["AktivitetsId"];
OleDbConnection oOleDbConnection = new OleDbConnection(connectionString);
oOleDbConnection.Open();
OleDbCommand command = new OleDbCommand(sQuery, oOleDbConnection);
OleDbDataReader reader = command.ExecuteReader();
int test = 12;
while (reader.Read())
{
rblAktivitetsgrupper.Items.Add(new ListItem(String.Format("{0}, platser", reader["aktivitetsGruppNamn"]), reader["aktivitetsGruppID"].ToString()));
}
In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.
INSERT INTO STU_SUMMARY
(SUMMARY_PERIOD,
CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU)
VALUES (GETDATE(),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
)
Hi,
I'm having problems manipulating the results from multiple count(*) queries.
I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.
I currently have the following three queries:
1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)
2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
3) SELECT COUNT(*) FROM RECOMMENDATIONS
I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3
My initial idea was:
SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working:
SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... and again the result was "0"
Please could someone help me!!
Ian
I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?
I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.
Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.
Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.
Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;
OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y
Hi everyone,
I am reading about the Rollback transaction but I'm not sure if it's the feature I need.
My application is going to update a few tables, but my programmer said that it will be done using more than 1 transaction.
For example, if I want to create a new employer with all the detailed informations, tables will be filled when the user complete the insertion of a part of data. If he decides to abort the operation, i'd like to delete all the values inserted before with the other queries. Is it possible to create a "savepoint" and roll back all transactions processed from this savepoint?
thank you!
HiI am trying to write two Select * statements to the same text fileusing bcp (from a stored procedure).But cannot find a way of appending to a file using bcp.Does anyone know if this is possible or is there another way of writingmultiple queries to a file from a stored procedure?ThanksCaro
View 2 Replies View RelatedGreetings,I have what seems a simple problem....I want to save the output from the following queries in 1 file in'SQL Query Analyser', but I can only save each 'grid' separately.I have about 30 queries in total. 'Messages' tells me the number ofrows affected but I need the data.So can I save all the grids together at the same time in 1 file ?ThanksMikePRINT 'HEAT 3510'SELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,ISNULL(d.tpitpayrollcode,'6900') AS Pay_Code,d.tpitdaydate AS Payroll_Date,c.tishactualenddate AS TS_end_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidLEFT OUTER join timesheetpayrollitems d ON d.tpittishguid =c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050614'and (d.tpitdaydate > '20050614'or d.tpitdaydate IS null)order by d.tpitdaydateSELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,c.tishactualenddate AS TS_end_date,g.thisdaydate AS TSH_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidINNER join timesheethistory g ON g.thistishguid = c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050619'order by g.thisdaydatePRINT 'HEAT 3213'
View 3 Replies View RelatedI have the following fieldname in an Access_Table:Field 1 = Cust_ID (Primary key)Field 2 = DateField 3 = DescriptionField 4 = Inv_NoField 5 = AmountMy SQL Input syntax are:"Select distinct Cust_ID,Date,Description,Inv_No,Amount fromAccess_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date =Main.Date"It failed to generate the result I want,anywayMy "Required" Output is:Cust_ID Date Description Inv_No >30DYS >60DYS8000 21/05/2004 PC RAM 2008000 26/06/2004 Modem 180---------------------------------------------------------------Total: 180 200---------------------------------------------------------------8001 22/04/2004 Cable 508001 23/05/2004 HD 210---------------------------------------------------------------Total: 260---------------------------------------------------------------Grand Total: 180 460can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi there,
I am new to ssis.I need to execute multiple queries to update number of tables in sqlserver.Pleasesuggest me the right way to do this.
Thanks in Advance.
Regards
Vinod
Hey guys,
In my DB i have hundreds on queries setup for all different reporting purposes..
We have just changed they way our system handles costing and are moving from a average cost function to a standard cost function.. This in turns, means that i need to replace any reports where i use the average cost field and replace it with the standard cost field.
Is it possible, that i can do a full search and replace over all my queries, looking for a specific field name and replacing it with something else.
I am dreading the idea of opening each individual queries and checking if it exists..
If anyone knows of software or someway to write a SP to accomadate this, please let me know. I would be most grateful.
Thanks
Scotty
I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server.
I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number.
The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.:
1: Database A -DSNA_tblMaiin - Serialnumber
2: Database B -DSNB_tblMaiin - Serialnumber
3: Database C -DSNC_tblMaiin - Serialnumber
I believe that this is a dot notation question.
Thanks in advance,
Gene
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!