Search results for query: *

From your initial post, you indicate that both husband and wife records exist in the same table, just on different rows. If that is the case, you problem exists in the table name of the second table accessed. You wrote: select a.name , b.name, a.phone from table1 as a left join table2 as b on...

When importing from text files (I have a number of such jobs running nightly) I tend to populate a basic varchar table (one having ALL columns of varchar data type), and then simply call a SProc to format the data and transfer it into the correct table(s). This has two bonuses (for me). One...

regilobo, Interesting set-up. My initial reaction was to mention Rowlocking, but, as I am nwo aware, 2000 determines it's own level of locking, so that one's out. What if you were to use hash tables to emulate the real tables that require updating. For example, a request is made a select...

JoshWithrow, Try this one then: SELECT o.Name AS Office, COUNT(e.ID) AS OEmpCount, SUM(p.mListQty) AS OPosts, (select count(Agent) from aPostcardTracking where Office = o.Name GROUP BY Office ) AS PEmpCount FROM aPostcardTracking p RIGHT OUTER JOIN aEmployees e ON p.Agent = e.ID RIGHT...

icebo3, You can use the first part: DECLARE @myDATE datetime DECLARE @myDATE2 datetime DECLARE @iDAYS int SET @myDATE = GETDATE() SET @iDAYS = datepart(dw,@myDATE)-2 SET @myDATE2 = GETDATE()-@iDAYS --This is Mondays Date to introduce a variable into your own code: select a,b from...

JamesLean, The use of: SELECT * FROM (EXEC myproc1) would serve no practical purpose. My reference to 'You can use one SProc to call another SProc and receive it's result record set' was meant to refer to the ability to directly access the result set from a SProc as you would a standard...

organicglenn, Hmm, I've spent the last 10 minutes looking at the results of the SProc (I recreated it locally and got the printout in QA), and I've now got a brain-ache!! OK. Right. First off, you say you are returning multiple rows based on tripusers rather than simply on Trips, and you...

sr727, OK. I re-created the tables (including the one missing from the first posting - OilCautions), and all appears quite normal. To answer your question as to how to change or delete the Oil.OilName and Cautions.Description columns, this would depend on under what circumstances are the...

Jason, OK, no great problem there. In that case you do need a SProc (as per your first suggestion) to return the results, as you will need to create a string for the TSQL statement, and then execute it. Here goes, copy and paste it as a new SProc: <CODE> -- Written by Logicalman for...

regilobo, From re-reading your initial question, it comes to mind that if the remote DBs are causing updates to the data, and this appears to be a Subscription PULL type set up, then you may be better off simply storing the data pulled from the remotes, and then run all the transactions once...

icebo3, No, the 2 is the SQL WeekDay for Monday. The WeekDay function in SQL returns 1 for Sunday through 7 for Saturday. Therefore, the above algorithm takes the current WeekDay number, subtracts 2, and the result is the number of days elpased since the last Monday. It will fail if it...

pulsar6, You state that the tables 'work together but do not relate to each other', how do they achieve this? What type of application drives this database? is there any coding in the Application that makes these apparent joins? As twifosp correctly points out, what denotes 'old data' in the...

regilobo, Are you certain you are not reaching a Deadlock situation here? If so, and in any case, you may want to use the SET LOCK_TIMEOUT function to cause the secong thread to timeout quickly. I wasn't aware of the stated Client/Server deadlock. Where is the reference to this? Thanks...

Jason, Here you go: select text, sum(case text2 when 'b1' then num else 0 end) AS B1, sum(case text2 when 'b2' then num else 0 end) AS B2, sum(case text2 when 'b3' then num else 0 end) AS B3 from tPIVOT group by text (I named my table tPIVOT but the column headers were the same as per your...

Hexonx, I'm unsure what you mean when you state that the result set is already in the database. When running a Sproc, the result set is alive only as long as the SProc is there, unless it is used as an output from the SProc, and in that case it is only alive as long as the process that called...

icebo3, Here's the function to find the Monday of the current week (up to and including Saturday) and the number of days lapsed since the Monday. Copy and paste it in QA and run it from there: DECLARE @myDATE datetime DECLARE @iDAYS int SET @myDATE = GETDATE() SET @iDAYS =...

JoshWithrow, The below code does 75% of your needs. Unfortunately I'm unclear on exactly what you need in respect of '% of Employees that show up in the PostcardTracking table per office'. Can you give a further explanation as to exactly what you need. Is it the % of times an Office appears...

sr727, I think I need a little more information as to what you intend to do with the tables, e.g. what records need updating, deleting etc, and, if this involves both the above tables, then where is the key field link? One thing you should also consider is that Cursers are not always the...

Marco, You can use the recordsetclone object based on the recordsource object of the form. The basic advantage is that it makes a copy of the records rather than the actual source: Dim rst As Recordset Set rst = Me.RecordsetClone At this point you can use the new recordset object (rst)...

SQLBill, I think ter79 needs clarification of terms here. SQL Server is (predominantly) a server-side application. Access is (predominantly) a Client-side Application. Stored Procedures (SProcs) and Views reside solely in SQL Server. Queries reside solely in Access MDB files. An...