Migrating from MS Access

Hi

I have this query, which I need to make work in MS SQL Server

select job.[CAM File],FIRST([material]), FIRST(job.[description]),
FIRST(job.[programmer]) from job where job.[CAM file] <> ''
 group by job.[cam file] order by 3

The "first" is my problem.

I can translate most of into

select job.[CAM File] from job
where job.[CAM file] <> '' group by job.[cam file])

Now I need to add the first row - for each cam file - how?
Next, I need to read something like this:

select top 1 job.[CAM File],
job.[material], job.[description], job.[programmer]
from job
where job.[CAM file] = job_from_above.[CAM file]  <- note this one

Just how do I do that?

WBR
Sonnich
0
jodleren
6/9/2010 3:52:57 PM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
588 Views

Similar Articles

[PageSpeed] 42

There is no FIRST equivalent in SQL Server (and tables have no order
of rows, so there is no first row concept). The following will be
close. You can change the ORDER BY clause in the ranking function to
order by a primary key or date column (if there is such and it can be
used to define some order of rows).

SELECT [CAM file], material, description, programmer
FROM (
SELECT [CAM file], material, description, programmer,
       ROW_NUMBER() OVER(PARTITION BY [CAM file] ORDER BY (SELECT
NULL)) AS rk
FROM job 
WHERE [CAM file] <> '')
WHERE rk = 1
ORDER BY description;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
6/9/2010 6:59:35 PM
Reply:

Similar Artilces:

Question Ragarding migration
Hello All: Having some issues with migration from 5.5 to 2000. Have 2000 exchange server setup in a different org than that of the 5.5. Both in DNS with mx records and priority 10 Have ADC installed and 2 way connection agreement between the 2 - constant replication. Can migrate the mailbox over to the 2000 server no problem with the migration wizard. However, first issue is that after the migration has completed, the mailbox is left in place on the 5.5 server. I thought it would delete it Second issue is that if I leave the mailbox intact, it receives mail on the 5.5 server. If I delete i...

Adding Additional Work to MS Project 2003
I would like to do some what-if analysis on my project schedule and am not sure how I can easily add say 30% additional work across the board on my project to show some best and worse case scenarios. Would I have to use a PERT chart to do this sort of filter? Hello Al, PERT analysis in project 2003 is based upon duration, not work. While work and duration are related through assignments, if you have changed the task type from the default of fixed units, adjusting duration will not necessarily change work. The PERT analysis tools are available on the PERT Analysis toolbar...

MDA to AA data migration
Has anyone successfully migrated MDA data to AA? We want to implement AA but are waiting for AA support from a 3rd party (AIT from mc2). We are considering implementing MDA for the short-term, then migrating to AA once it's supported by AIT. Mike, Currently working on a tool to do so, if you are interested in talking about it please contact me. MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "Mike Muscarella" wrote: > Has anyone successfully migrated MDA data to AA? We want to implement AA but > are waiting fo...

Migrating AD to new domain. How does Exch 5.5 fit in?
Over a weekend (hopefully) I will be migrating from one domain to another temporary domain. Then I will be taking the original domain out of it's current forest and into its own. I will then migrate back to the original domain. I'm using Exchange 5.5. What do I do with Exchange? Should I leave it with the original domain and assume that the appended SIDHistory of the users will still work correctly with it when they move back? I'm thinking this will work since it's happening over a weekend... I don't know anything about migrating Exchange 5.5 to a new domain... Any ...

German government warns against using MS Explorer
Now this must be the news of 2010 After nearly 10 years of unlimited patches, hotfixes and service Packs, IE is still a weak link in recent attacks on Google's systems. Either M$ is now threatened by Googles rise to prominence or IE is completely rubbish. Either way we should start taking instructions from the German government if this article is anything to go by. <http://news.bbc.co.uk/1/hi/technology/8463516.stm> Is google's search engine more powerful than M$ Bing? > Now this must be the news of 2010 > and 2011....and 2012.... IE problem...

Access application on clients without Access
Hi. I have an application made in Access 2003. Now one of my clients are migrating from Office 2003 to Office 2007, and they can not afford to buy Office versions with Access included to all the users. I know it's possible to publish "thin" Access application files, but I'm not sure how I do it. From Access 2003, I need to publish "thin" Access application files that will work on clients running Office 2007. Ant hints or links to resources appreciated. Kind regards Mr. Smith Try this one from Microsoft: http://www.microsoft.com/downloads/details.aspx...

How do I install Access 2003 under MS Office 2007
I am trying to install Access 2003, but because I have Office 2007 installed, the system is telling me that I need to have Office 97, 2000 or 2003. Is this correct? I loaded my old Access 97 CD, but the system tells me that it can't find Office 97, 2000 or 2003. Is there an upgrade for Access 2003? What do I do? Thanks... It is generally recommended to install versions of Office/Access in the order they were published. I have done otherwise though. It is imperative that you install each version in it's own directory, otherwise they will overwrite one another and crea...

connecting to data access page with filter criteria
I have an access database that I connect to from a GIS application (ArcView) using VBscript. I need to port my GIS project to the free viewer (ArcReader) which does not support VBscript but does support hyperlinking to a URL based on a value supplied by an underlying record. In my current project I can send filter critera to open a particular database, form and record. Would it be possible to do the same using data access pages or would I need to create a separate page for each record I want linked? ...

Extract Data from curupted Access 2000 Database
any sugestion on how to Extract Data from curupted Access 2000 Database ??? the Error i get when attempting to Open The microsoft jet database engine stoped the process becouse you and another user are attempting to change the same data. Hi Levon, See http://www.granite.ab.ca/access/corruptmdbs.htm On Tue, 1 Jun 2004 10:34:58 -0700, "Levon" <anonymous@discussions.microsoft.com> wrote: >any sugestion on how to Extract Data from curupted Access >2000 Database ??? > > >the Error i get when attempting to Open > >The microsoft jet database engine s...

MS Outlook 2003 ...
I have Outlook 2003 installed on the desktop and exchange 2003 running on the server. I am having several users complain about receiving duplicate messages. I have read several posts describe this being caused by rules incorrectly configured. However, I have checked that and it isn't the case. The messages that are duplicated are specifically external emails. I do have GroupShield running on the server with the SpamKiller add-on. When I look at message tracker in the system manager I only see one of the messages being delivered. The instance of dup messages arriving can be hours apa...

MS Outlook 2002 Running in Windows 98
I just changed to a new ISP. The ISP walked me through setting up an email account for their site. I no longer have a send button. I also can't see Send/Receive under Tools. I have looked and looked to no avail. Any suggestions would be appreciated. Thanks. Abby Abby, Try resetting your toolbars to the default settings. Note that you will lose any customizations you've made to them, but I think it will let you get your "Send/Receive" button and menu item back. -- Regards, Chris Burnham Microsoft Exchange Support This posting is provided "AS IS" with no...

Ms access data type detail
I am using Ms access 2007, can anyone explain to me all the DATA TYPES, like what are their limitations etc. Also how can i create a drop down list and allows selecting more than 1 texts. e.g ... in my db i have an option for known languages and the answers could be more than one. English, French, Spanish etc Start here http://office.microsoft.com/en-us/training/HA102744471033.aspx Aamir wrote: > I am using Ms access 2007, can anyone explain to me all the DATA TYPES, > like what are their limitations etc. > > Also how can i create a drop down list and al...

Access 2007 Special Effect Text Box Won't show Sunken with Windows XP
Hi All I am just getting to grips with Access 2007 and one of the quirks I have found is that when I design using Windows in XP Windows-Classic colors and set the text boxes on forms' special effect property to "sunken" They appear as sunken in the correct manner. However, when I change to the Windows XP color scheme the forms text boxes appear flat even though the special effect property has not changed. Any ideas or is it a new quirk we have to live with. I haven't tried it with Vista yet. That should be interesting Thanks Bob I am quite sure this is the same for acce...

Newsletters in MS Publisher
how do I set up an 8 page newletter that will print on 11x17 paper with pages 1 & 8 and 2 & 7 on one sheet and pages 3 & 6 and pages 4 & 5 on the same sheet? ...

Access to News groups disappears after first use and exiting.
Have used OE for a number of years for email and News Group access. Thought I might experiment and then switch to Outlook. Set it up, and, received reply on the test. I know it was working. Then tried to set up newsgroups. Followed directions as outlined in help. Last direction was to drag News to "Go". Finished ... went to go and selected News. All the News Groups I had earlier setup in OE came up. Played around looking at some posts. Then exited. Then when I went back to Go, News was no longer there. Completely disappeared. I am using Outlook 2003. Actually recopied a "Gh...

MS CRM 1.2 and MS SQL SERVER REPORTING SERVICES
CAN MS CRM 1.2 CO-EXIST WITH MS SQL SERVER 2000 REPORTING SERVICES? WE CAN ONLY GET EITHER CRM OR REPORT SERVICES TO WORK NOT BOTH. THANKS. Yes, they can co-exist. Also, please DON'T SHOUT. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Fri, 8 Jul 2005 16:08:02 -0700, HEC <HEC@discussions.microsoft.com> wrote: CAN MS CRM 1.2 CO-EXIST WITH MS SQL SERVER 2000 REPORTING SERVICES? WE CAN ONLY GET EITHER CRM OR REPORT SERVICES TO WORK NOT BOTH. THANKS. Could you please enlighten us Matt? If one has CRM 1.2 in...

Task start/end dates from MS project to Workspace site
We are currently running MSPS 2007 with WSS 3.0. I am trying to create a list in one of the project work spaces. I would like to auto-populate two columns in that list with the start and end date of certain tasks from the Project Plan in MS project. I am thinking we can do this by linking these columns to the the project SQL database fields. So, 1) Do I use the published SQL databse for this? 2) Which fields in the SQL database would I have to link to get the Start and End dates? EggHeadCafe - Software Developer Portal of Choice AutoList in ASP.Net http://www.eggheadcafe.com/tutorial...

MS Money 2006 and Money for PPC
I have installed MS Money2006 trial version and microsoft money for Pocket PC, Windows Mobile 5 operative system,device i-mate JASJAR. i can't sinchronize both money data pn PC and PPC and all the time ther is window with message "reading device databases". With Windows Mobile 2003 SE with same softwares there was no problems. I'm using also ZoneAlarm Firewall. If somebody know solution, I will appreciate it! Thanks in advance. Davor Opic P.S. Please, if you want, answer on my email addres: davor.opic@po.htnet.hr ...

Access 2003 VB Code Export Query Failing with Access 2007 user
I have a set of excel export queries I created in an access 2003 DB. Now, some of my users have upgraded to Access 2007 and are running in to a "Parameter" error when the execute the "canned" excel export queries I created for them. My only presumption is that it is due to the fact that I have hardcoded the export function, to go to an Office 2003 Excel file, and for whatever reason, Access 2007 cannot handle this. This make sense? And if so, any suggestions? I suppose if there was an easy way to test what version of office/excel they are running, I could change...

dropdown filter on Data Access Page
Hi, I have a data access page that is based on a query in my database. Currently the page brings up all data for today, and the page is formated into teams So it looks like this Today's date Team #1 entry #1 entry #2 Team #2 entry #1 Since this page can get long sometimes, I would like to add a dropdown menu at the top of the page, that allows them to filter their results by team. So once they make a selection they would only see the team selected. Today's date Team (only 1 listed) Entry #1 Entry #2 I've been trying to search on this, but jus...

MS Money and Disk Cloning
I currently use MS Money 2004 on WinXP SP2 with all updates on an HP laptop. For extra backup protection I do a monthly disk cloning of my entire drive C using CasperXP (similiar to Norton Ghost or Acronis True Image) to make a 100% mirror image / cloned bootable hard drive. I then swap these hard drives (newly cloned hard drive placed into laptop) to ensure all is working well. Every single program, component, update, patch, etc.. works flawlessly using this monthly swapped hard drive.....EXCEPT MS Money 2004. MS Money 2004 shows up on the cloned drive, it simply refuses to run. Th...

Outlook Web Access from reply-to address
Hi all, Is it possible to specify an alternative from/reply-to address for people using Outlook Web Access? Cheers, mad_staring_eyes@email.com (Tom) wrote in news:e4be56f.0404141158.1263ff14@posting.google.com: > Is it possible to specify an alternative from/reply-to address for > people using Outlook Web Access? Not that I am aware of. There may be a third party OWA add-in that could enable this but I don't know one off the top of my head. Regards, -- Arlo Clizer Exchange MVP FAQ: http://www.swinc.com/resource/exch_faq.htm Archives: http://groups.google.com "Arlo Cli...

Make a drawing via access using loop
I have a table with one field in it and I wont to draw one record soI created a form and I created a loop to go to the next recordrandomly the whole table and then every second minute I stop the loopand Igot the winner, means the record where the loop stop in itbut I don't have a way to stop the loop only by pressing ctrl breakI wont to code that when i will press any key it should stop runningthe code and it should came up a MSG box with the nameany help please?thankshere is the codePrivate Sub Command2_Click()gggg:DoCmd.GoToRecord , , acFirstDo Until Me.NewRecordDoCmd.GoToRecord{here I ...

Data Migration Framework problems
Hi, Sorry to post this again, but I've been looking all over trying to solve this problem but no luck. I'm trying to migrate some data (Accounts, Contacts and Activities) to MS CRM 3.0 using the Data Migration Framework. I succeeded in migrating the Accounts and Contacts and just over 600 out of about 7000 activities (the activities are simple tasks associated with the accounts). I noticed that all the non-migrated activities have status set to "Completed". Moreover, when I change the status of those records to "In Progress" (just to test) some of them succeed. Doe...

Possible to Import Access queries to Excel and maintain the formulas??
Hello: I am trying to confirm whether it is possible to import access queries intto Excel and keep the formulas so that they will still exist in Excel. Is that possible to do? Thanks! Hi Fred, not without writing code -- and, depending on your calculations, this may be very difficult Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * fred.sheriff@gmail.com wrote: > Hello: I am trying to confirm whether it is possible to import access > queries intto Excel and keep the formulas so that t...