[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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