Select Distinct & Where

[ashley25]

I have created the following query:

SELECT
'A Review' AS [Work Stream],
Current_Wk.Person_Count,
Previous_Wk.Person_Count,
IIf([Current_Wk].[Person_Count]=[Previous_Wk].[Person_Count], "No Change", IIF([Previous_Wk].[Person_Count]>[Current_Wk].[Person_Count], "-" & ([Previous_Wk].[Person_Count]-[Current_Wk].[Person_Count]), "+" & ([Current_Wk].[Person_Count]-[Previous_Wk].[Person_Count]))) AS Difference
FROM
(SELECT Count(1) AS Person_Count
FROM (SELECT DISTINCT [Person ID] FROM [tblAReview])) AS Current_Wk,
(SELECT Count(1) AS Person_Count
FROM (SELECT DISTINCT [Person ID] FROM [tblAAReview])) AS Previous_Wk;

I wanted to add in two other columns to say whether the person has expired or not (in the current week). I have a column in the tblAAReview that gives this information.

How can I fit this in? Would a Where clause suffice?

 

[plog]

To do what you asked, you would probably bring tblAAReview into your query and LEFT JOIN from the existing data to it. However, I think you have bigger problems and that may not work.

I smell an improperly structured database, for 2 reasons:

1. tblAReview and tblAAReview sound like they have the same structure. If that's true, then you shouldn't have seperate tables for the data in them.

2. You've established that tblAAReview has multiple records per [Person ID], but then you say that there is a column in it that determines if the person has expired. That piece of data sounds like it should be contained in the table from which [Person ID] originates.

Can you post the structure of tblAReview and tblAAReview and explan what they are each for?

 

[ashley25]

In relation to point (1):

the structures are the same for both tables.

tblAReview is refreshed once a week via Business Objects (tblAAReview is tblAReview but for the previous weeks data).

all queries etc run from tblAReview, I only use tblAAReview when I want to compare/sumise the current weeks figures with the previous weeks.

In relation to point (2):

as stated above, tblAReview is derived from Business Objects so I can't control the way the data is presented. Customer lines [Person ID] are "duplicted" for example, if a customer has more than one order number.