show one or the other

[SteveJtoo]

I have a query that keys off on a status field. I just use Nz(status) and filter out all other types but 'W'. This allows me to show any record for each ID with that status (there is only one or none per each ID) but it also shows the records of ID's that have no status (one record per each ID). I would like to show a list that has one record for each ID with the chosen 'W' status BUT if that ID doesn't have a 'W' status then show the record that has no status. I need one or the other but default on the 'W'.

WHERE (((Nz([StatFlag]))<>"R" And (Nz([StatFlag]))<>"M" And (Nz([StatFlag]))<>"H" And (Nz([StatFlag]))<>"P")

Can someone point me in the right direction. I am not an Ace programmer. Thanks

 

[plog]

Post some sample data from your table, include field and table names. And then, based on that sample data, also show what should be returned by this query. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name...
14, Dave, W
71, Sally, H
81, Tim,

 

[MSAccessRookie]

While you are at it, post the entire Query, not just the Where Clause, since there may be issues in the rest of the Query as well

 

[SteveJtoo]

By giving you the whole query then I guess I'm giving you the table names and field names. I hope it's enough.

SELECT Authors.AuthorID, Authors.LastName, Authors.FirstName, Books.Title, Books.Category, Books.CopyrightYear, Books.Series, Books.EditionNumber, Books.Notes, Books.field2, Books.amazon, Books.field222, Books.HowToGet, Nz([StatFlag]) AS Expr1, Authors.NewCheck, Reviews.Review, Books.Kindle, Books.Own, Authors.Status, Books.Author
FROM Reviews RIGHT JOIN (Authors LEFT JOIN Books ON Authors.AuthorID = Books.Author) ON Reviews.BookId = Books.BookID
WHERE (((Nz([StatFlag]))<>"R" And (Nz([StatFlag]))<>"M" And (Nz([StatFlag]))<>"H" And (Nz([StatFlag]))<>"P") AND ((Authors.Status)="A"))
ORDER BY Authors.LastName, Authors.FirstName;

This gives me both 'N' StatFlag and NULL StatFlag. That's 2 records for ID that has an 'N' and 1 record for those with no 'N'.

 

[plog]

Its not enough for me. I need sample data as well. Going in and coming out.

 

[SteveJtoo]

Not sure if it uploaded. It's Sample.xls

 

[plog]

I don't see it anywhere. Post it on screen per the instructions of my first post.

 

[SteveJtoo]

Try Again. http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=50184&stc=1&d=1379017486

 

[plog]

Last chance. Give me examples of what the starting data is and then what the data the query should produce based on that sample data. Preferable less than 10 records in the starting data.

 

[SteveJtoo]

I gave you data in xls file. A couple of doz records. It should produce a list containing 1 record of each author with either a StatFlag of 'W' or if there is none then a record with StatFlag of IsNull. as it stands it gives both so a max of 2 records for each author. In the data I provided, the first 2 authors will have 1 record each (null StatFlag) and the others will show 2 records each (StatFlag 'W' and StatFlag null value).

I am trying to give you what you want. If you cant read the xls file tell me. No need to threaten me with last chance.

 

[namliam]

Select Distinct [Author ID] from yourtable
where statflag = 'W' or StatFlag is null

That will give you a unique list fo Authors with either a W or Null statFlag

 

[SteveJtoo]

Thanks namliam. I did as you said but still getting same results. 2 records of each authoe that has both statflags and 1 record for ones with 1 kind of ststflag.

Here is the SQL of the query

SELECT DISTINCT Authors.AuthorID, Authors.LastName, Authors.FirstName, Books.Title, Books.Category, Books.CopyrightYear, Books.Series, Books.EditionNumber, Books.Notes, Books.field2, Books.amazon, Books.field222, Books.HowToGet, Books.StatFlag, Authors.NewCheck, Reviews.Review, Books.Kindle, Books.Own, Authors.Status, Books.Author
FROM Reviews RIGHT JOIN (Authors LEFT JOIN Books ON Authors.AuthorID = Books.Author) ON Reviews.BookId = Books.BookID
WHERE (((Books.StatFlag)='W' Or (Books.StatFlag) Is Null) AND ((Authors.Status)="A"))
ORDER BY Authors.LastName, Authors.FirstName;

Did I do something wrong?

 

[namliam]

It should produce a list containing 1 record of each author with either a StatFlag of 'W' or if there is none then a record with StatFlag of IsNull.

Your query doesnt comply with your question asked...
Your question: I want unique author
Your query: I want unique books

Since your average author will have multiple books.... its a different question and a different solution if at all possible.

 

[SteveJtoo]

Yes you are right, but I want both unique Author and unique book to go with it. That's been my problem all along. I have tried many many ideas but no success!

 

[namliam]

You want an author + book that has a W
If that dont exist, Author + Book that has a Null

??

 

[SteveJtoo]

YES!!!! That is exactly what I want. Can it be done?

 

[SteveJtoo]

YES!!!! That is exactly what I want. Is it possible?

 

[namliam]

Then get your question right first before you start asking it

First off see what book statflags are available:

SELECT author
     , Sum(IIf([StatFlag]="W",1,0)) AS StatW
     , Sum(IIf([StatFlag] Is Null,1,0)) AS StatNull
FROM YourTable
GROUP BY author;

Save this as a query and join this query back into your original query as an additional table.

And filter on that data:

WHERE ((Books.StatFlag='W' and StatW = 1  )
   or  (Books.StatFlag Is Null and StatW = 0 and StatNull = 1))
  AND Authors.Status="A"

Note though if there happen to be two books with "W" neither will be selected due to the StatW value being 2 not 1

 

[spikepl]

SteveJtoo

FYI:

plog asked you 3 times very specifically to provide a small set of input data and the corresponding resulting data. The "last chance" was not a threat, but an acknowledgment that some people simply cannot be helped. After 3 times you still did not provide what was asked for. The input and corresponding output data is normally asked for to remove linguistic ambiguity normally present in a narrative and not to have invent or dig after representative data.

You will get much more out of the help by carefully reading and responding to what is asked for. BTW I commend plog for his patience - I myself ask only once

 

[SteveJtoo]

namliam. I'm sorry that I'm inexperienced and please don't get frustrated with me. I am trying my best to do what you want and I appreciate your help and patience.

The example you gave me - where do i put Sum(IIf([StatFlag]="W",1,0)) AS StatW and Sum(IIf([StatFlag] Is Null,1,0)) AS StatNull. If I put them as Fields or criteria I get syntax error.