Querying SQL Server data - changing data format 3

[KathyRingwood]

I have a database with tables linked via SQL Server. The way the data is stored on the server is not useful for reports we must submit to our funder. I'm wondering if there's a way to have the data display differently. For example, one of the columns in the table is "Gender". Rather than having either "Female" or "Male" displayed, the funder requires an "F" or an "M". In the case of race, rather than spelling out the individual's race (i.e. "White"), it needs to be "yes" or "no" ("no" if the value in the field is null). I don't know much about writing code so I am hoping this won't be too difficult--but if it is, I'll try to learn! Thanks for your help.

Kathy

 

[dhookom]

You can change the displayed value in your query

SELECT Left([Gender],1) as Gndr, IIf(IsNull([Race]), "No", "Yes"), ...other fields...
FROM LinkedTableFromSQLServer

Duane
Hook'D on Access
MS Access MVP

 

[KathyRingwood]

Hi Duane,
Thank you for replying! Sounds like this is doable. I apologize in advance for my lack of knowledge--but where do I write this code? Is it in the query itself or elsewhere? Thanks again.

--Kathy

 

[lespaul]

yes this would be the query....you just need to change the table and field names to be what YOUR table and fields are named....

Leslie

Come join me at New Mexico Linux Fest!

 

[KathyRingwood]

Hi Leslie,

Can you give me an example? I'm just not getting this. I want to change the displayed values so I am not sure where that code is written. Thanks very much.

Kathy

 

[lespaul]

create a new query....it will probably open in the Query Design Grid....you need to switch to the SQL View (and I can't remember exactly where that menu option is - there may be a drop down in the top left corner that allows you to change to the SQL View)....once there you can write your SQL:

SELECT Left([b]YourGenderField[/b], 1) As OneCharGender,  IIf(IsNull([b]YourRaceField[/b]), "No", "Yes") As NewRaceInfo FROM [b]YourTableName[/b]

will return from your table a list of results that look like:

[tt]
OneCharGender NewRaceInfo
F No
M Yes
M Yes
F Yes[/tt]

if you would like more specific help if you will post your table structure, field names and your results someone would most likely be able to write you a query that you would just have to paste into the SQL view and it would work....

HTH

Leslie

Come join me at New Mexico Linux Fest!

 

[KathyRingwood]

I found the place to write the SQL, but I think I'm in over my head so I'm pasting part of the table structure, field names and results:

Table: registration_

Fields: Type When selected, displays:
user_id number
gender Text (Male or Female)
race1 Text (AmericanIndian)
race2 Text African American or Black)
race3 Text (Asian)
race4 Text (Pacific Islander)
race5 Text (White)

There are several other fields that need to be changed but if I can see how these are done, I think I can do the rest on my own.

Here's how the data displays currently:

user_id Gender Race1 Race2 Race3 Race4 Race 5
10001 Female Asian
10002 Male White
10003 Male Native Amer
10004 Male White

Here's how our funder requires the display:

user_id Gender Race1 Race2 Race3 Race4 Race 5
10001 F N N Y N N
10002 M N N N N Y
10003 M Y N N N N
10004 M N N N N Y

Any blanks must display as "N" (for no); F=Female, M=Male. If someone selects race5, for example, rather than displaying "White", it must display as "Y" for yes.

I hope this makes sense. Thanks again for all your help. I'm learning a lot on this one!

--Kathy

 

[dhookom]

Try SQL like:

SELECT User_ID Left(Gender,1) As Gndr, 
IIf(IsNull(Race1),"N","Y") As Rc1,
IIf(IsNull(Race2),"N","Y") As Rc2,
IIf(IsNull(Race3),"N","Y") As Rc3,
IIf(IsNull(Race4),"N","Y") As Rc4,
IIf(IsNull(Race5),"N","Y") As Rc5
FROM registration_;

Duane
Hook'D on Access
MS Access MVP

 

[KathyRingwood]

Hi Duane,

Thank you for your help with this. I copied and pasted the query you suggested into SQL and ran it. I get the following error: Syntax error (missing operator) in query expression 'User_ID Left(Gender,1)'.

The word left is highlighted in the query when I get this error message.

Thanks again for your help.

--Kathy

 

[PHV]

Replace this:
User_ID Left(Gender,1)
with this:
User_ID[!],[/!]Left(Gender,1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KathyRingwood]

Thanks very much. It works! You all are terrific. Thanks for the support and assistance.

--Kathy