How to evaluate only one place in a string. (1 Viewer)

sfoster1 · Jan 6, 2005

I know I've seen this problem discussed but can't find it anywhere.

I'm trying to query a table and evaluate only one place at a time in a five character coded string, then output descriptive text in a report based on what is found in that place. Each place in the string represents a different area of study (1st place English, 2nd place Math, 3rd Place Social Studies, 4th Place Science, 5th place Foreign Language). One of seven different characters occupies each place in the string, representing where a student will take their final exam for that class. I set up a small table to give a descriptor for each letter. For example, "R" means classroom, "L" means resource room, "C" means computer lab, and so on. However, I can't figure out how to evaluate say only the 2nd character when I want to report where that student will take the Math exam. A typical code might look like "RRLCR". If I am reporting for the science final, then this student takes the test in the Computer lab since the 4th place represents the science test and the "C" represents the computer lab.

I originally tried setting up code tables for each of the finals with every possible combination paired with the appropriate description of that 5 character code for that particular final. Not only is it cumbersome, but for some reason it is causing errant filtering of data in my query.

sfreeman@co.mer · Jan 6, 2005

Fairly simple:

Assuming a table called tblCodes, with a StudentID field and a Code field.

Here is the query:

SELECT
tblCodes.StudentID
, tblCodes.Code
, Location(Left(

Code:

,1)) AS One
, Location(Mid([Code],2,1)) AS Two
, Location(Mid([Code],3,1)) AS Three
, Location(Mid([Code],4,1)) AS Four
, Location(Right([Code],1)) AS Five

FROM 
  tblCodes;

Here is the function 'Location' [substitute values as required]:

Function Location(strInput) As String
Select Case strInput
    Case "E"
        Location = "Gym"
    Case "F"
        Location = "Classroom"
    Case "R"
        Location = "Library"
    Case "C"
        Location = "Computer Lab"
    Case "L"
        Location = "Science Lab"
End Select

End Function

Output looks something like [cut and paste into Notepad or Word to see correctly]:

StudentID	Code	One	Two	Three	Four	Five
1	FCCLR	Classroom	Computer Lab	Computer Lab	Science Lab	Library
2	RECLR	Library	Gym	Computer Lab	Science Lab	Library
3	RCCLF	Library	Computer Lab	Computer Lab	Science Lab	Classroom
4	RCLCR	Library	Computer Lab	Science Lab	Computer Lab	Library
5	EFCLR	Gym	Classroom	Computer Lab	Science Lab	Library
6	RCCFE	Library	Computer Lab	Computer Lab	Classroom	Gym
7	RLREF	Library	Science Lab	Library	Gym	Classroom
8	FEFER	Classroom	Gym	Classroom	Gym	Library
9	EEFFR	Gym	Gym	Classroom	Classroom	Library
10	RCCLR	Library	Computer Lab	Computer Lab	Science Lab	Library

HTH :cool:

sfoster1 · Jan 7, 2005

Need more help with output please.

Thanks for your help.

My access skills are weak, so please forgive me these next questions.

I'm not sure where to place the Function Location. I assume it is still within the query and not in the report.

Also, my output is a report for each teacher. It lists their students and what accomodations they receive for their particular exam. English teachers' reports will focus on the first character in each string in each category (Location is only one of five categories).

Attached is a sample of what the output would roughly look like except that I want to replace the 5 character strings with the appropriate text for that student in that category. I hope I'm not being too ambiguous. I'd submit my database but even with the data pared down, it is 4 Mb.

john471 · Jan 7, 2005

Create a new (code) module, and copy/paste (+amend as appropriate) the Location function into there.

Might be slightly more user friendly for you if you modify sfreeman's query to be a little more meaningful to your purpose:-

sfoster1 said:
(1st place English, 2nd place Math, 3rd Place Social Studies, 4th Place Science, 5th place Foreign Language).

SELECT
tblCodes.StudentID
, tblCodes.Code
, Location(Left(

Code:

,1)) AS EnglishLocation
, Location(Mid([Code],2,1)) AS MathLocation
, Location(Mid([Code],3,1)) AS SocStudLocation
, Location(Mid([Code],4,1)) AS ScienceLocation
, Location(Right([Code],1)) AS ForLangLocation

FROM 
tblCodes;

[B]However[/B], it sounds like your data is not adhering to first normal form (your Location field is non-atomic:- you are storing more than one piece of information in the field), and this is going to cause you problems  :( 

If you have control over this aspect of the database; you should strongly consider changing it.  Read up on Database Normalization. :) 

HTH.

sfoster1 · Jan 7, 2005

John... thanks. I shall have to promptly meet with our database administrator and properly tell him off (It was his idea to use this goofy "code" setup in the first place. The problem is that my data comes from the school database (Maintained on proprietary software) over which I have almost no control. My department is limited as to how many fields we can store on the student record, and thus we are forced to compress multiple bits of data in the fields alotted. What I CAN do is break the data out once it is exported to me. I need to set that up so that it is a fairly simple process that can be applied each time I receive a static upload of data (about once a month). Any suggestions on how I should approach this? I'll give it a shot with what I've learned thus far and see how it goes. I have a feeling I'll be back with a few more (annoying) questions.

sfoster1 · Jan 7, 2005

Figured out how to break out individual numbers

Using Left, Mid, and Right statements as given in the examples by both of you, I was able to break the 5 digit/character strings into individual bits. So instead of having a 5 character string for location, I have 5 different location fields (EngLoc, MathLoc, FLLoc, SciLoc, and FLLoc) each with only one character. I still need to figure out the "as" statement that will allow me to put in the text in the report to replace the 1 character code.

john471 · Jan 8, 2005

sfoster1 said:
What I CAN do is break the data out once it is exported to me. I need to set that up so that it is a fairly simple process that can be applied each time I receive a static upload of data (about once a month).

I would suggest you should do that first. - Get the design right first, before you have too many dependancies on the current design.

Any suggestions on how I should approach this?

That would largely depend on the format(s) that the proprietary system can output.

I'll give it a shot with what I've learned thus far and see how it goes.

That's the spirit

I have a feeling I'll be back with a few more (annoying) questions.

Isn't that what the forum is for??? (with one caveat :- Search first and ask second

.)

I still need to figure out the "as" statement that will allow me to put in the text in the report to replace the 1 character code.

The AS statement gives a query field named "as" whatever follows the AS statement. You can then use that field (by name) in your report (assuming your report is based on the query, or a query based on the query (etc).) In the example kindly supplied by sfreeman, the result of the Location function (also kindly supplied by sfreeman) is what wil be in the query's field; not the one letter code, so there should be very little left to work out. The problem you are going to have is having the SciLoc (and only the SciLoc) showing up on a Science teacher's report (and the converse of that) etc. It can be gotten around; but would be much easier if the data were properly normalized; hence my prime suggestion - get the design right first.

Good luck.

John.

How to evaluate only one place in a string. (1 Viewer)

sfoster1

Registered User.

sfreeman@co.mer

Sam_F

sfoster1

Registered User.

Attachments

john471

Registered User.

sfoster1

Registered User.

sfoster1

Registered User.

john471

Registered User.

Similar threads

Users who are viewing this thread