SQL query using ms access db.Urgent Help please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL query using ms access db.Urgent Help please

Author

Topic

Shailen
Starting Member

17 Posts

Posted - 2005-11-22 : 07:57:00

Hi Guys

I am having a problem extracting the correct data from my tables
I have 2 tables
Table 1 - contains the project names and descriptions

Table 2 contains the status of each project. There can be multiple updates to a record. Here is my code below

SELECT FinAdmin.[No],FinAdmin.[File No],FinAdmin.Company,FinAdmin.[Name] as [Employee Name],FinAdmin.[Description], UpdateFinAdmin.[Date], UpdateFinAdmin.[Comment]As Status
FROM FinAdmin LEFT JOIN UpdateFinAdmin on FinAdmin.[No] = UpdateFinAdmin.[No] order by FinAdmin.[No], UpdateFinAdmin.[Date] desc

How to i get the query to excute only data from the tables where the Comment = 'Completed'. If the record has a history, it must bring up the history as well for that record only

No File No Company Employee Name Description Date Status
16 test Nick Testing1 29/10/05 Completed
16 test Nick Testing1 29/10/05 testing 2
17 test Nick Testing2 29/10/05 testing 3
17 test Nick Testing3 29/10/05 testing 4

What i am tryin to achieve is, where the comment is = 'completed, it must bring the entire history of the record only. The query must only pull out the records below. Hope u understand what i am tryin to say

No File No Company Employee Name Description Date Status
16 test Nick Testing1 29/10/05 Completed
16 test Nick Testing1 29/10/05 testing 2

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-11-23 : 20:49:33

I have a few questions,
1. Do you mean that you want to retrieve records if any of the records for that [No] have the comment "completed"?
2. There would appear to be some data missing from your example results (7 column names but only 6 columns?)

anyhoo, maybe this is what you want?

SELECT a.[No],a.[File No],a.Company,a.[Name] as [Employee Name],a.[Description], 
	b.[Date], b.[Comment] As Status 
FROM FinAdmin a LEFT JOIN UpdateFinAdmin b 
	on a.[No] = b.[No] 
where b.No in (select No from UpdateFinAdmin where Comment = 'Completed')
order by a.[No], b.[Date] desc

(I used aliases a and b for simplicity.)

If this is not what you're after, maybe you could post a few rows of example data from each table and then the result set you want for that data.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources