Changing data types

[benesch]

Hi,

I am trying to change the data type of multiple calculated fields from text to logical? Is it possible to do this in a query? Alternatively, how could I run a make-table query and have the values of these fields be set to logical each time the query is run.

The reason that I wish to do this is that I have a table containing multiple text fields with "X" values if the condition is true. I wish to count the number of occurences for each of these fields simultaneously. Once the type is changed to logical I can sum the occurences of -1 (Yes), and multiply by -1.

Thank you for your help.

Andy Benesch

 

[AlexMidd]

You could just do a query that counts the number of rows with 'X' in them, which would save you having to convert the fields to Boolean (which is what I presume you mean by 'logical').



Have fun! )

Alex Middleton

 

[Wolfen02]

If you must have a table with logical info, the easiest way using a make table query would by with an IIF statement.
IIF([fieldname] like "X",-1,0)

Then you could use the absolute value rather then multiple by -1.
ABS(SUM([fieldname]))

Alex's advice to count the current data would work as well.



Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[benesch]

Hi Alex,

How would I run a query to count the number of rows with 'X' n them for multiple columns? The 'X' values may be in different rows in different columns. Below is a sample of the data:


FACID ADMHOME ADMNURHOME ADMSUBACUTE
NJ80A003 X
NJ40A000 X
NJ82462 X
NJ80A110 X
YMOSFX X
NJ80A006 X

Thank you for your prompt response, and for any further suggestions that you can offer.

Andy Benesch

 

[PHV]

[ADMHOME] & [ADMNURHOME] & [ADMSUBACUTE] Like '*X*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[benesch]

Hi all,

Thank you for your help. Unfortunately I tried all of your suggestions, and still didn't get the result I am looking for. Perhaps I didn't make it clear. The result, which I am looking for is to count the total number of occurences of "x" for several different fields. I wish to find a separate total for each field.

When I tried PHV's suggestion above [ADMHOME] & [ADMNURHOME] & [ADMSUBACUTE] Like '*X*', I get a value of -1 for each time an "X" occurs in any of these fields. If I add these up I get a combined count for the three fields, as oppopsed to three separate counts.

I also tried WOLFEN02's suggestion:

If you must have a table with logical info, the easiest way using a make table query would by with an IIF statement.
IIF([fieldname] like "X",-1,0)

The fields created in the new table are text rather than logical values, so I can't count them using the method above. I also tried deleting all of the values in the new table, setting the apppropriate fields to logical, and then running an Append query. When I do this, I get a data conversion error.

Please let me know if any of the suggested techniques should work, and I am missing something, or if you have any other suggestions.


Andy

 

[AlexMidd]

You would have to add logical fields to the new table then do an update query which sets the value of the new fields to true when there is an 'X' in the corresponding old fields. Alternatively, if you need counts for each field separately, that should be easy enough using Count([FieldName]).


Have fun! )

Alex Middleton

 

[PHV]

And what about this in the make table query ?
CBool(IIf(ADMHOME='X',-1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[benesch]

Hi Alex and PHV,

I tried both of your suggestions:

Alex when I ran the update query, no records were updated.
Can I update the new field and set the values to true for each "X" in the same query?

Using Count([FieldName]) doesn't work for me because it gives a count of all records in the field, not just those containing an "X"

PHV when I used the expression CBool(IIf(ADMHOME = "X",-1,0)) in a make-table query, Access converted the values to Number format, as opposed to Boolean in the new query.

Once again, I am grateful to all of you who have tried to help me, and would appreciate further sugggestions. In the meantime, I will continue to research this issue.

Andy

 

[AlexMidd]

Are you using Count([Field]) WHERE [Field] = 'X';?

In the update query, are you using a where clause there as well to ensure that only the appropriate fields get updated?

Have fun! )

Alex Middleton

 

[benesch]

Hi Alex,

When I use the WHERE clause, I get an error message, "The expression you entered contains invalid syntax. You may have entered an operand without an operator."

Andy

 

[lespaul]

could you please post the SQL of the query you are trying? That will make this A LOT easier!!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[benesch]

Hi Leslie,

The SQL is as follows

ADMLHOME: Count([ADMHOME]) WHERE [ADMHOME] = "X"

Andy

 

[PHV]

ADMLHOME: Sum(IIf([ADMHOME]='X',1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[benesch]

Hi PHV,

When I try your suggestion,ADMLHOME: Sum(IIf([ADMHOME]='X',1,0)) the query returns one record with a value of "YES."

Andy

 

[PHV]

Any chance you could post the SQL code, some input samples, actual result and expected result ?
Your last post makes no sense for me, sorry.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

that's not the SQL of the query, that's the criteria you are putting in the query grid. Switch to the SQL view and post the ENTIRE query please.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[benesch]

Hi PH and Leslie,

Below are sample input, the complete SQL code, the actual result, and the desired result. Thank you for your patience with me!

SAMPLE INPUT

RESID ADMHOME ADMNURHOME ADMSUBACUTE
19 X
20 X
21 X
22 X
23 X
24 X
25 X
26 X
27 X
28 X
29 X
30 X
31 X
32 X
33 X

SQL Code

SELECT Sum(IIf([ADMHOME]='X',1,0)) AS ADMHOMEL, Sum(IIf([NURHOME]='X',1,0)) AS ADMNURHOMEL, Sum(IIf([ADMNURHOME]='X',1,0)) AS ADMSUBACUTEL
FROM [In-house Resident Profile];

Actual Result of Query

ADMHOMEL ADMNURHOMEL ADMSUBACUTEL
YES 4 4

Desirec Result

ADMHOMEL ADMNURHOMEL ADMSUBACUTEL
6 4 5

 

[PHV]

Create a brand new query with this SQL code:
SELECT Sum(IIf([ADMHOME]='X',1,0)) AS ADMHOMEL
, Sum(IIf([ADMNURHOME]='X',1,0)) AS ADMNURHOMEL
, Sum(IIf([ADMSUBACUTE]='X',1,0)) AS ADMSUBACUTEL
FROM [In-house Resident Profile];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886