Calculations and Expressions

[cc143most]

Hello, all!

I am building a more complex database than I have ever tried before and am
completely stumped. I have imported data that contains a field "Status".
This field has 8 different "Declined" reasons and 3 different "Obtained"
reasons. I created a crosstab query to get counts of each reason. That
worked great! Now, however, I need to total the different Declined reasons
together. I have tried everything I can in expression builder to get this to
work and nothing has so far.

Can anyone help? Thank you so much for your time!

 

[KARL DEWEY]

If your 'Declined' reasons include the word declined then you can parse it out.
Post the SQL of your crosstab query and sample data.

 

[Piet Linden]

Hello, all!

I am building a more complex database than I have ever tried before and am
completely stumped.  I have imported data that contains a field "Status".  
This field has 8 different "Declined" reasons and 3 different "Obtained"
reasons.  I created a crosstab query to get counts of each reason.  That
worked great!  Now, however, I need to total the different Declined reasons
together.  I have tried everything I can in expression builder to get this to
work and nothing has so far.

Can anyone help?  Thank you so much for your time!

go back to the source that contains the 8 different columns for
Declined reasons and create a query that looks at each of the 8
columns and returns "Declined" for any that contain a value. then
base your crosstab query on that.

You could probably do this more easily in a function, just because
doing it with 7 nested IIFs gets a bit hairy...

IIF(Not IsNull([D1]), "Declined",
IIF(Not IsNull([D2]), "Declined",
IIF(Not IsNull([D3]), "Declined",...

))) < == One close for each open... PITA


Once you have that, you can base your crosstab on this query and you
should be good to go.

 

[John W. Vinson]

Hello, all!

I am building a more complex database than I have ever tried before and am
completely stumped. I have imported data that contains a field "Status".
This field has 8 different "Declined" reasons and 3 different "Obtained"
reasons. I created a crosstab query to get counts of each reason. That
worked great! Now, however, I need to total the different Declined reasons
together. I have tried everything I can in expression builder to get this to
work and nothing has so far.

Can anyone help? Thank you so much for your time!

Please post the SQL view of your crosstab query.

 

[cc143most]

This is the first time I have posted. Is this what you needed?

My report headings have the "Declined" and "Obtained" reasons so I could
parse them out. I just haven't figured out how to do that.

Thanks again for any help!

TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)],
Count([Jan 2009 Query].[Link #]) AS [Total Of Link #]
FROM [Jan 2009 Query]
GROUP BY [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)]
PIVOT [Jan 2009 Query].[Consent Status];

 

[cc143most]

Hello, all!

I am building a more complex database than I have ever tried before and am
completely stumped. I have imported data that contains a field "Status".
This field has 8 different "Declined" reasons and 3 different "Obtained"
reasons. I created a crosstab query to get counts of each reason. That
worked great! Now, however, I need to total the different Declined reasons
together. I have tried everything I can in expression builder to get this to
work and nothing has so far.

Can anyone help? Thank you so much for your time!

go back to the source that contains the 8 different columns for
Declined reasons and create a query that looks at each of the 8
columns and returns "Declined" for any that contain a value. then
base your crosstab query on that.

You could probably do this more easily in a function, just because
doing it with 7 nested IIFs gets a bit hairy...

IIF(Not IsNull([D1]), "Declined",
IIF(Not IsNull([D2]), "Declined",
IIF(Not IsNull([D3]), "Declined",...

))) < == One close for each open... PITA


Once you have that, you can base your crosstab on this query and you
should be good to go.

Piet,

Thanks so much for the help. It was the way I was leaning, I just didn't
know how to proceed. My thoughts were to create a column called "Consent"
with only the words "Declined" or "Obtained" that I could use for this yearly
summary report.

My original data has declined and obtained reasons in ONE column under the
heading "Consent Status". After more reading, I thought that maybe the
following IIF statement would work. Of course, it didn't. Again, I have
never done anything like this and do not know SQL. All of my attempts come
from reading as I go along.

Consent: IIf([Consent Status],"Declined by Fam*", "Declined", IIF ([Consent
Status], "Declined by Us*", "R/O", IIf ([Consent
Status],"Obtained*","Obtained")))

Thanks again!