ClaireB

Hi guys,

I'm trying to modify an SQL statement to include some more information from yet another table but without adding any more records. Sorry but this is a long one.

Here is the SQL:
Select Status.ID,
Status.ClaimantID,
(Campaign.Claimant_Firstname +' '+ Campaign.Claimant_Lastname) As FullName,
Status.PolicyID
from PolicyStatus Status
INNER JOIN Campaign ON Campaign.UniqueKey = Status.ClaimantID
where Status.ID = ( select top 1 StatusSubSearch.ID
from PolicyStatus StatusSubSearch
INNER JOIN PolicyStatus_Actions Actions ON Actions.ID = StatusSubSearch.StageAction
where Status.ClaimantID = StatusSubSearch.ClaimantID
AND Status.PolicyID = StatusSubSearch.PolicyID
AND Actions.OverviewIgnoreAction <>1
order by StatusSubSearch.DateAdded desc)
Order By Status.StatusDate,Status.StatusTime

The tables include the following fields which are nvarchar unless otherwise stated, and relate to each other as follows:

Campaign Table - This table holds all primary policy information
UniqueKey - primary key , has a one to many relationship with the ClaimantIDs
Claimant_Firstname
Claimant_Lastname
IsDeleted - bit field

PolicyStatus
ID
ClaimantID - has a many to one relationship with the UniqueKey
PolicyID - each claimantID can have many policies
StatusDate
StatusTime

PolicyInformation - This table holds all additional policy information (policy 2, policy 3 etc)
ClaimantID - has a many to one relationship with the UniqueKey
PolicyID - each claimantID can have many policies
IsDeleted

SQL statement and tables currently produce something similar to this

ID ClaimantID Name PolicyID
59159 5034 Stewart Scott 4
45870 11182 SUSAN JONES 2
66303 18212 Mark Kirven 1
50521 5104 Mervyn Neal 1
2386 35786 Kevin Ryder 2

I want to amend the SQL statement so that it still produces the same current information, but also tells me if the policy has been deleted. For example the above data would be like:

ID ClaimantID Name PolicyID Deleted

59159 5034 Stewart Scott 4 1

45870 11182 SUSAN JONES 2 0
66303 18212 Mark Kirven 1 0
50521 5104 Mervyn Neal 1 1
2386 35786 Kevin Ryder 2 0

Where for IDs 66303 and 50521 the deleted info is obtained from the IsDeleted column in the Campaign Table, but the info for the other rows where the policy ID is 2 or 4 the IsDeleted column is referenced from the Policy Information Table.

I've tried various JOINs but don't seem to be able to retain the original number of records. Can anyone help? Thanks to all those who've read this far!

Trebz

To get the IsDeleted from the campaign table, you can just add (below line 4 in your example) "Campaign.IsDeleted as Deleted" to get that.

Is this in access? If it is, then I will be of limited help, as I would only know how to do it in SQL, and there is plenty of that which will work with MS SQL Server, but not with access.

You say for some rows, the 'Deleted' column needs to get it's data from different places, by what condition is this?