Need help to structure simple (I think) query...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I think this is a simple query but I am not well-versed in SQL so I cannot
figure this out.

I have the following tables (I show only the relevant stuff):

Documents
PK_DocumentID

Reviews
PK_ReviewID
FK_DocumentID
FK_ReviewStatusID

The relationship is one-to-many: One Document can have many Reviews.
A Review has a Status:
1 = Not Started
2 = Open
3 = Closed

For ALL the Reviews associated with a Document, there can be only ONE Review
that has a non-closed status. Sometimes this condition does exist, which
results in a data integrity problem, so I want to identify these Documents so
they can be fixed.

Example 1: This is the correct data for the Review Status...

Document A
Review 1 -- Status = 3 (Closed)
Review 2 -- Status = 3 (Closed)
Review 3 -- Status = 2 (Open)

Example 2: An incorrect condition would be...

Document A
Review 1 -- Status = 3 (Closed)
Review 2 -- Status = 1 (Not Started)
Review 3 -- Status = 2 (Open)

So I want to structure a query that will identify the Document IDs that fit
Example 2. I can't figure it out.

I tried a Crosstab query and I can get the data values in the right rows and
columns, but I want only the Document records where there is more than one
Review record with a non-closed status. I tried a crosstab query like this:

TRANSFORM Count(Reviews.FK_ReviewStatusID) AS CountOfFK_ReviewStatusID
SELECT Documents.PK_DocumentID
FROM Documents INNER JOIN Reviews ON Documents.PK_DocumentID =
Reviews.FK_DocumentID
WHERE (((Reviews.FK_ReviewStatusID)=1 Or (Reviews.FK_ReviewStatusID)=2))
GROUP BY Documents.PK_DocumentID
PIVOT Reviews.FK_ReviewStatusID;

This gives me the following kind of results:

DocumentPK 1 2
===============
2519 1
2522 1
2531 1 1
2545 1

I want to have the results only show Document record 2531.

Maybe I need a subquery or something other than what I am doing here.

Does this make sense to anyone? It seems like a trivial thing to do but I
have been at it all day and I cannot figure it out. Any help?

I am using Access 2000. I ultimately want to have the results of this query
populate into a ListBox on a form so that a user can double-click on a listed
item to open the Document in order to fix the problem.
 
This will find DocumentID's that have more than one review in Not Started or
Open status at the same time.

SELECT Reviews.DocumentID
FROM Reviews
GROUP BY Reviews.DocumentID
HAVING (((Sum(IIf([ReviewStatusID]=1 Or [ReviewStatusID]=2,1,0)))>1));
 
Thank you very much. This worked perfectly.

KARL DEWEY said:
This will find DocumentID's that have more than one review in Not Started or
Open status at the same time.

SELECT Reviews.DocumentID
FROM Reviews
GROUP BY Reviews.DocumentID
HAVING (((Sum(IIf([ReviewStatusID]=1 Or [ReviewStatusID]=2,1,0)))>1));

--
KARL DEWEY
Build a little - Test a little


StuckInTheMiddle said:
I think this is a simple query but I am not well-versed in SQL so I cannot
figure this out.

I have the following tables (I show only the relevant stuff):

Documents
PK_DocumentID

Reviews
PK_ReviewID
FK_DocumentID
FK_ReviewStatusID

The relationship is one-to-many: One Document can have many Reviews.
A Review has a Status:
1 = Not Started
2 = Open
3 = Closed

For ALL the Reviews associated with a Document, there can be only ONE Review
that has a non-closed status. Sometimes this condition does exist, which
results in a data integrity problem, so I want to identify these Documents so
they can be fixed.

Example 1: This is the correct data for the Review Status...

Document A
Review 1 -- Status = 3 (Closed)
Review 2 -- Status = 3 (Closed)
Review 3 -- Status = 2 (Open)

Example 2: An incorrect condition would be...

Document A
Review 1 -- Status = 3 (Closed)
Review 2 -- Status = 1 (Not Started)
Review 3 -- Status = 2 (Open)

So I want to structure a query that will identify the Document IDs that fit
Example 2. I can't figure it out.

I tried a Crosstab query and I can get the data values in the right rows and
columns, but I want only the Document records where there is more than one
Review record with a non-closed status. I tried a crosstab query like this:

TRANSFORM Count(Reviews.FK_ReviewStatusID) AS CountOfFK_ReviewStatusID
SELECT Documents.PK_DocumentID
FROM Documents INNER JOIN Reviews ON Documents.PK_DocumentID =
Reviews.FK_DocumentID
WHERE (((Reviews.FK_ReviewStatusID)=1 Or (Reviews.FK_ReviewStatusID)=2))
GROUP BY Documents.PK_DocumentID
PIVOT Reviews.FK_ReviewStatusID;

This gives me the following kind of results:

DocumentPK 1 2
===============
2519 1
2522 1
2531 1 1
2545 1

I want to have the results only show Document record 2531.

Maybe I need a subquery or something other than what I am doing here.

Does this make sense to anyone? It seems like a trivial thing to do but I
have been at it all day and I cannot figure it out. Any help?

I am using Access 2000. I ultimately want to have the results of this query
populate into a ListBox on a form so that a user can double-click on a listed
item to open the Document in order to fix the problem.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need help on building a query 2
Query Formula need HELP 3
Crosstab Qry, column heading, by week number, show "Mar 9-16" 3
Date Format in a Query 5
formula need a fix. HELP! 1
Need Help with Formula Expansion 5
Really need query help!! 2
Query Help 5

Back
Top