[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

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
0
joylene6
Asked:
joylene6
  • 4
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now