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

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.
 
G

Guest

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));
 
G

Guest

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
Really need query help!! 2
Query Help 5
formula need a fix. HELP! 1
Crosstab query and Lookup field problem 3
Need Query Help 0
Query Filter Help 4
Need Help with Formula Expansion 5

Top