MS Access query need help with statement in query to remove data for certain criteria

Hello - I have a question for the experts.

I have a field that I need to weed out some information in the query... either adding in VBA code or right in the query.



The field has 002, 003 and blanks for each encounter.

If the customer has a 002 for an encounter, then I need 002 to show
If the customer has 002 and a 003 for the encounter, I just want the 003 to show
If the customer is blank for the encounter then the blank field to show

I wrote this out in the example attached to clarify.  Thank you.
Sample_1.xlsx
LVL 1
joylene6Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Deacon AdamsI.T SpecialistCommented:
hi sorry please clarify is it the f002 encounter?
0
 
joylene6Author Commented:
Hello-
    to clarify the encounter number is the encounter number field.   The numbers 002,003 and the blanks are in the number field.

I probably should have named them something completely different. I hope this helps. Thank you!
0
 
Rey Obrero (Capricorn1)Commented:
try this query

SELECT myTable.Customer, (select Max([Number]) From myTable as A where A.Customer=MyTable.Customer) AS Expr1
FROM myTable;
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
joylene6Author Commented:
Thank you Rey, I will have a chance to try this as soon as I get back to my computer.
0
 
joylene6Author Commented:
Thank you Rey! That worked. I did not even think about using MAX.
0
 
joylene6Author Commented:
Thank you Rey! That worked. I did not even think about using MAX.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.