Do Not select records if duplicatation in specific field

[Cullihall]

Do Not select records if duplication in specific field

Hi. I have a table with several fields. Some records will have the same information in certain fields but different info in other fields. Three fields in question are: [CaseID], [Batch], and [Trip].
Suppose there are 3 records in the table. Two of which have the same [CaseID], one of these two will have [Batch] Null and [Trip] Not Null, the other will have [Batch] Not Null and [Trip] Null.
The third record has a unique [CaseID], [Batch] Not Null and [Trip] Null.

I need to select all records but if the [CaseID] is the same, then only take the record where the [Trip] Is Not Null.

I'd appreciate any help at all!

 

[Brianwarnock]

If that is the only situation then 2 queries
SELECT Table1.casid, Count(Table1.casid) AS CountOfcasid
FROM Table1
GROUP BY Table1.casid;

SELECT Table1.casid, Table1.batch, Table1.trip
FROM Table1 INNER JOIN Query1 ON Table1.casid = Query1.casid
WHERE (((Table1.trip) Is Not Null) AND (Query1.CountOfcasid)=2) OR (((Query1.CountOfcasid)=1));

but be aware that this only covers the situation you quoted

Brian

 

[Cullihall]

Re: Do Not select records if duplication in specific field

Thanks Brianwarnock!.

It's not quite where I'd like it but definitely in right direction I think.
I've attached a sample database including only the table I'm talking about.

I would like to have a query to return all records in the table and all the same fields of the table but as I said before, if the CaseID is the same in more than one record, take only the record where the Trip field is Not blank for those records where the CaseID is the same.

In the sample table there are 8 records, the query should return only 7 records because there are two records where the CaseID is the same.

 

[Cullihall]

Re: Do Not select records if duplication in specific field

I apologize Brianwarnock.

I believe it does do what I asked for. I was just messed up because my CaseID was pasted from scientific notation in Excel into text in Access so the CaseID were almost all the same.

Thank you very much!!

 

[Cullihall]

Re: Do Not select records if duplication in specific field

Thanks to Brianwarnock for getting me as far as I currnetly am.

There's one more thing I would like to accomplish here.
I forgot to mention if the CaseID is the same in more than one record, take only the record where the Trip field is Not blank for those records where the CaseID is the same unless the Trip field is blank in those records where the CaseID is the same, then take all the records.

Example:
In the attached sample table there are 8 records. the query should return 7 of them.

There are two records where the CaseID is the same and one has a value in the Trip field so only the one with the trip field should be returned.

There are also another two records where the CaseID is the same and neither has a value in the Trip field so both of these records should be returned.

Also all records that have unique CaseID should be returned.

A CaseID can be in more than just two records.

Again, I'd appreciate any help at all!

 

[Brianwarnock]

Didn't notice that you had posted again.
I have amended your 2 queries to take account of the requirement.

Brian

 

[Cullihall]

That's awesome Brianwarnock. Everything as I requested. Thank you again! Very much appreciated.

 

[Brianwarnock]

Happy to help

Brian