0

Help - I am fairly new at MS SQL, but understand the concepts and have looked at the code in MS Access many times. But here's my problem:
I need to create a complex query across multiple tables within the same database, but I'm just not sure how to do it within MS SQL. I've done it in Access, creating separate queries and then created a query on those queries, but it takes a long time to run (and I don't trust the results from Access). Here's the code that does work, but it specifies criteria that I really want to query (in the WHERE clause, what is in bold):

SELECT Tracking.TransactionNumber, Min(Tracking.DateTime) AS DateTime, WorldCatInformation.OCLCNumber, WorldCatInformation.Type, WorldCatInformation.Data, Transactions.RequestType, Transactions.DocumentType, Transactions.CallNumber, Transactions.Location, UsersALL.NVTGC, UsersALL.Organization, UsersALL.Department, UsersALL.Status, UsersALL.LastName, Transactions.CitedIn, Transactions.ESPNumber, Transactions.ISSN, Transactions.SystemID, Transactions.LoanAuthor, Transactions.LoanTitle, Transactions.CitedVolume, Transactions.LoanEdition, Transactions.LoanPlace, Transactions.LoanPublisher, Transactions.LoanDate, Transactions.PhotoArticleAuthor, Transactions.PhotoArticleTitle, Transactions.PhotoItemAuthor, Transactions.PhotoItemEdition, Transactions.PhotoItemPlace, Transactions.PhotoItemPublisher, Transactions.PhotoJournalTitle, Transactions.PhotoJournalYear, Transactions.PhotoJournalMonth, Transactions.PhotoJournalVolume, Transactions.PhotoJournalIssue, Transactions.PhotoJournalInclusivePages, Transactions.LendingLibrary, Transactions.LendingString, Transactions.TransactionDate, Transactions.TransactionStatus, Transactions.ReasonForCancellation
FROM         WorldCatInformation INNER JOIN Transactions ON WorldCatInformation.TransactionNumber = Transactions.TransactionNumber INNER JOIN
UsersALL ON Transactions.Username = UsersALL.UserName  INNER JOIN
Tracking ON Transactions.TransactionNumber = Tracking.TransactionNumber
WHERE Tracking.DateTime Between ('1/1/2008') And ('1/31/2008') And UsersALL.NVTGC <> 'BIOMED' AND UsersALL.NVTGC <> 'LAWILS' AND [B]WorldCatInformation.Type = 'Call Number (LC)'[/B]
GROUP BY Tracking.TransactionNumber, WorldCatInformation.OCLCNumber, WorldCatInformation.Type, WorldCatInformation.Data, Transactions.RequestType, Transactions.DocumentType, Transactions.CallNumber, Transactions.Location, 
UsersALL.NVTGC, UsersALL.Organization, UsersALL.Department, UsersALL.Status, UsersALL.LastName, Transactions.CitedIn, Transactions.ESPNumber, Transactions.ISSN, Transactions.SystemID, Transactions.LoanAuthor, Transactions.LoanTitle, Transactions.CitedVolume, Transactions.LoanEdition, Transactions.LoanPlace, Transactions.LoanPublisher, Transactions.LoanDate, Transactions.PhotoArticleAuthor, 
Transactions.PhotoArticleTitle, Transactions.PhotoItemAuthor, Transactions.PhotoItemEdition, Transactions.PhotoItemPlace, 
Transactions.PhotoItemPublisher, Transactions.PhotoJournalTitle, Transactions.PhotoJournalYear, Transactions.PhotoJournalMonth, 
Transactions.PhotoJournalVolume, Transactions.PhotoJournalIssue, Transactions.PhotoJournalInclusivePages, Transactions.LendingLibrary, 
Transactions.LendingString, Transactions.TransactionDate, Transactions.TransactionStatus, Transactions.ReasonForCancellation;

I've tried using UNION, but that won't work since the columns & rows are not identical in type. I thought about a JOIN and have tried this, removing the bold portion out and putting this code at the end:

(SELECT MIN(WorldCatInformation.TransactionNumber) AS FirstOfTransactionNumber, WorldCatInformation.OCLCNumber, WorldCatInformation.Data
FROM WorldCatInformation INNER JOIN Transactions ON WorldCatInformation.TransactionNumber=Transactions.TransactionNumber
WHERE WorldCatInformation.Type = 'Call Number (LC)'
GROUP BY WorldCatInformation.OCLCNumber, WorldCatInformation.Data, WorldCatInformation.Type)

But this creates a two tables.

A secondary problem is the SELECT MIN. In Access, there is the option to select FIRST, but I have tried that over and over in MS SQL and it says it does not recognize FIRST. I really need to just find the first occurrence of each instance so I'm not sure how to do that within MS SQL. Any advice on either one of these issues would be greatly appreciated. Thank you!

Jamen McGranahan
jmcgranahan at tds dot net

3
Contributors
3
Replies
4
Views
9 Years
Discussion Span
Last Post by Kandakis
0

Well, not exactly. The command you suggest returns only the top entry in the table. That's not what I am looking for. In this table, there are multiple transactions per item, and what the user is looking for is the first transaction date per item. Is there a way I can do this?

Jamen McGranahan

0

Well, not exactly. The command you suggest returns only the top entry in the table. That's not what I am looking for. In this table, there are multiple transactions per item, and what the user is looking for is the first transaction date per item. Is there a way I can do this?

Jamen McGranahan

I don't think you're going to get away with trying to get all the data with just one query. Access SQL is quite different from Standard SQL. You may want to run several stored procs with order bys on the data to get just what you want. You may have to use temp tables as an intermediary step. Sorry, I've been there with Access. EX VB Programmer no longer working for Fortune 100 companies.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.