query issue

[ashley25]

I have created the following statement to count unique records from a table in my database :

SELECT "A Review" AS [Work Stream], COUNT(1) AS PERSON_COUNT
FROM (SELECT DISTINCT
tblAReview.[Person ID]
FROM
[tblAReview]
)

I then repeat the statement, adjust (and union) to bring in all other work streams into the table.

What I need to do is add an extra column that brings in a figure from another query, for each work-streams. Is there an easy way to do this?

 

[CJ_London]

To be more definitive, need to know more about your other query and how it relates to the the data already being selected but in principle you would do this

SELECT "A Review" AS [Work Stream], COUNT(1) AS PERSON_COUNT
FROM (SELECT DISTINCT 
tblAReview.[Person ID]
FROM 
[tblAReview] 
), [COLOR=red](Select sum(figure) from otherquery as tmp)
[/COLOR]

 

[ashley25]

The second query is written exactly the same but it counts from the previous weeks table.

Essentially, I want to say whether the count from the current week has gone up or done, when compared to the previous week.

 

[CJ_London]

OK, so you have a separate table for each week and each review - not very efficient!

but your code would be

SELECT "A Review" AS [Work Stream], COUNT(1) AS PERSON_COUNT
FROM (SELECT DISTINCT 
tblAReview.[Person ID]
FROM 
[tblAReview]) As Wk1, COUNT(1) AS PERSON_COUNT
FROM (SELECT DISTINCT 
tblAReview.[Person ID]
FROM 
[tblAReviewpreviousweek]) as Wk0

This gives you to count from each week. I would then have a query which uses your union query and you can subtract wk0 from wk1

 

[ashley25]

Thanks for this.

So I would need an extra query if i wanted to do some calculations on the totals?

Regarding efficency, my tables (6 linked Business Objects reports) are refreshed every Monday. I then run some comparison queries to check for customers who have been added to the tables or lost since the previous run.

 

[CJ_London]

Re efficiency, It would be better to have a single table in Access so have a routine to insert the BO tables to a single access table ensuring you have columns for the ID for TblReview and if necessary a week ID. However outside the scope of this thread.

So I would need an extra query if i wanted to do some calculations on the totals?

Depends on what you want.

Each of the subqueries bring back a single value per row - at the moment that is a count. It could be a sum, first, last or avg or a Top 1 (based on some criteria).

You can also do a calculation within the query posted instead of after the union query as previous suggested

SELECT "A Review" AS [Work Stream], COUNT(1) AS PERSON_COUNT
FROM (SELECT DISTINCT 
tblAReview.[Person ID]
FROM 
[tblAReview]) As Wk1, COUNT(1) AS PERSON_COUNT
FROM (SELECT DISTINCT 
tblAReview.[Person ID]
FROM 
[tblAReviewpreviousweek]) as Wk0[COLOR=red], wk1-wk0 as Difference, wk1/wk0 as PercentageChange[/COLOR]

However if you wanted to work on the totals overall (i.e. after your union query) you would need to start of with

 Workstream, Wk1, Wk2 FROM UnionQuery

And then you can group by, sum etc

 

[ashley25]

I've tried your suggestion and a syntax error in FROM clause message keeps showing.

 

[CJ_London]

I can't see anything wrong with the syntax (although I'm having a bad day with quotation marks)

Can you post what you have - I presume you have replaced tblAReviewpreviousweek with whatever your previous week table is called

 

[ashley25]

No problem at all.

I've literally copied your code but changed the table name to the previous week.

 

[ashley25]

It seems to fall down after this:

As Wk1, COUNT(1) AS PERSON_COUNT
FROM (SELECT DISTINCT
tblAReview.[Person ID]
FROM
[tblAReviewpreviousweek]) as Wk0, wk1-wk0 as Difference, wk1/wk0 as PercentageChange

 

[CJ_London]

Sorry, didn't get your code but revisiting and rewriting slightly

SELECT 'A Review' AS [Work Stream], Wk1.Person_Count, Wk0.Person_Count FROM (SELECT Count(1) AS Person_Count, [Wk1].[Person_Count]-[Wk0].[Person_Count] AS Diff
FROM (SELECT DISTINCT [Person ID] FROM [tblAReview]))  AS Wk1, (SELECT Count(1) AS Person_Count
FROM (SELECT DISTINCT [Person ID] FROM [tblAReviewPreviousWeek]))  AS Wk0

This works with some similar data I have myself

 

[ashley25]

The parameter box comes up for Wk1.Person_Count and for Wk0.Person_Count.

Should this part be here too: (SELECT Count(1) AS Person_Count, [Wk1].[Person_Count]-[Wk0].[Person_Count] AS Diff

 

[CJ_London]

You need to copy the whole code the bit you are talking about is off to the left.

The only difference between this and what I did with my data is the name of the field Person ID and the names of the two tables