Solved

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

Posted on 2016-11-02
6
39 Views
Last Modified: 2016-11-03
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
Comment
Question by:joylene6
  • 4
6 Comments
 

Expert Comment

by:Zahier Adams
ID: 41870569
hi sorry please clarify is it the f002 encounter?
0
 
LVL 1

Author Comment

by:joylene6
ID: 41870588
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41870966
try this query

SELECT myTable.Customer, (select Max([Number]) From myTable as A where A.Customer=MyTable.Customer) AS Expr1
FROM myTable;
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:joylene6
ID: 41871184
Thank you Rey, I will have a chance to try this as soon as I get back to my computer.
0
 
LVL 1

Author Comment

by:joylene6
ID: 41872477
Thank you Rey! That worked. I did not even think about using MAX.
0
 
LVL 1

Author Comment

by:joylene6
ID: 41872479
Thank you Rey! That worked. I did not even think about using MAX.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Run SQL Server Proc from Access 11 27
IIF help, YN field 7 21
is Microsoft Access going to Die? 9 42
Clear Filter 8 29
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now