Trying to make a query to pick out lowercase codes (1 Viewer)

[plog]

I don't want to use SQL code I want it to be picked up from a access query

SQL code is an Access query.

Try DBguys solution in post #9. It will only identify records meeting your criteria, it will not change anything.

 

[The_Doc_Man]

I don't want them automatically changed as not sure what table they are from so want the query to tell me what table they are from also

I don't want this to sound harsh, but I need to be honest with you. Some people confuse honesty with brutality, but I am just trying to be clear.

Access queries don't work the way you describe. I believe you have an incorrect understanding of queries. But worse than that, I suspect you have bad design issues if you have the same problem in multiple tables such that a hypothetical query would be able to tell you about the table in which this problem was discovered. If you have the same kind of data in several tables, your design is not normalized and that will make you work VERY much harder than you want or need to.

You said earlier you didn't want to use SQL, but that is exactly what Access uses. When you do a query-grid design, Access will convert that to SQL and run that because the Access data engine IS an SQL engine. All modern database engines use SQL. You can't escape it.

In design terms, if you aren't sure from which table you are taking data, you can't even BUILD a query since the FROM clause is going to require you to name a table. It is a requirement of the syntax.

It IS possible to make a query case-sensitive and you are correct that in such cases, an ORDER BY clause would cause all upper case letters to appear before all lower case letters. However, if you have mixed case situations, you might find an even more confusing sort order.

Whatever you are trying to do, you are going to have trouble because this design has been described inconsistently. Your inconsistent discussion betrays a lack of understanding of what you are really trying to do, and this lack will absolutely kill any chances of success.

You have already been informed of our attitudes on cross-posting in your other post about LCase and UCase, so please be aware that too many repetitions of that action will cause you to be shunned. We DO have patience with new users - but not with new users who don't get the message about wasting our time. You should also know that other forums, when they discover cross-posting, can be more emphatic about it than we are.

 

[cayham86]

I don't want this to sound harsh, but I need to be honest with you. Some people confuse honesty with brutality, but I am just trying to be clear.

Access queries don't work the way you describe. I believe you have an incorrect understanding of queries. But worse than that, I suspect you have bad design issues if you have the same problem in multiple tables such that a hypothetical query would be able to tell you about the table in which this problem was discovered. If you have the same kind of data in several tables, your design is not normalized and that will make you work VERY much harder than you want or need to.

You said earlier you didn't want to use SQL, but that is exactly what Access uses. When you do a query-grid design, Access will convert that to SQL and run that because the Access data engine IS an SQL engine. All modern database engines use SQL. You can't escape it.

In design terms, if you aren't sure from which table you are taking data, you can't even BUILD a query since the FROM clause is going to require you to name a table. It is a requirement of the syntax.

It IS possible to make a query case-sensitive and you are correct that in such cases, an ORDER BY clause would cause all upper case letters to appear before all lower case letters. However, if you have mixed case situations, you might find an even more confusing sort order.

Whatever you are trying to do, you are going to have trouble because this design has been described inconsistently. Your inconsistent discussion betrays a lack of understanding of what you are really trying to do, and this lack will absolutely kill any chances of success.

You have already been informed of our attitudes on cross-posting in your other post about LCase and UCase, so please be aware that too many repetitions of that action will cause you to be shunned. We DO have patience with new users - but not with new users who don't get the message about wasting our time. You should also know that other forums, when they discover cross-posting, can be more emphatic about it than we are.

Thanks for the reply I should of described my problem better before posting

 

[Gasman]

Now also crossposted at https://www.mrexcel.com/board/threads/lowercase-code-query-problems.1209462/

 

[jdraw]

@cayham86,
The other repliers have offered advice. Your issue, as Doc suggested, is based on a poor design and a misunderstanding of Access and how it works. You are asking a question regarding HOW can I do this in Access. Perhaps the more appropriate post is a plain English description of the business process involved to help readers understand WHAT you want to do. There may be several ways to accomplish it.

It appears that you re new to database, new to Access and new to forums.

"I should of described my problem better before posting". Yes, you should post clearly described issues with examples when possible. Changing the issue/criteria as the thread gets responses is not helpful.

 

[oldaf294]

caymam86,
The_Doc_Man is correct. However, I don't see his post any more?
When you reference Code in your question, I believe your 'Code' field is being confused with actual coding.

I hate to be the bearer of bad news, but your DB is going to need some redesign. You should take a screen shot of the "Relationships" and post.

IF YOU HAVE multiple tables with the 'Code' field in each table, your queries are going to be extremely complex and, perhaps, impossible to create. I did a similar thing many years ago when Access was first released in 92.

You need a separate table, perhaps named, Diagnosis with only; 'PK', 'Code' and 'Description' as fields.
Your Table(s) should have a corresponding field to hold the PK of the Diagnosis table.

You need the PK AutoNumber in your table(s) instead of the 'Code.'
AutoNumber Code Description
1 DN Ductal (NST)
2 LP Lobular
3 ME Medullary
4 MP ...
5 OM ...
...
You can use "Find and Replace" to change the 'Code,' or whatever, field values in your other table(s).

Display a table with the "Home" tab.
Click in the first entry in the 'Code' field or whatever.
Select "Replace."
In the "Find What" box type DN and in the "Replace With" box type 1.
Do that for each entry of the DN,LP,ME,MP,OM,... using the corresponding number for each 'Code.'
Doesn't matter lower or upper case.

LInk the Relationship PK of the newly created, 'Diagnosis" table to the 'Code,' or whatever, field in each table.
Doing this will allow you to only need change the 'Code' field in the 'Diagnosis' table.

You might wait for a reply to my post from The_Doc_Man, theDBguy, Gasman, CJ London or others to be sure my post is correct and is a workable solution.
They are among the ultimate experts of this forum.

Hope this is correct and helps.

oldaf294,
Jim