Crosstab Unique value count

[spartansFC]

Hi

I'm trying to get a unique count total from a crosstab, the data i have is

ParentID TotalChildID New Old
1 4 0 4
176 22 0 22
690 22 0 22
1192 96 92 4

so my sql statement is

TRANSFORM Count(qrySummaryReportYearlyExistingNew.ChildId) AS CountOfChildId
SELECT qrySummaryReportYearlyExistingNew.ParentId, Count(qrySummaryReportYearlyExistingNew.ChildId) AS [Total Of ChildId]
FROM qrySummaryReportYearlyExistingNew
GROUP BY qrySummaryReportYearlyExistingNew.ParentId
PIVOT qrySummaryReportYearlyExistingNew.strRateTypeDesc;

What i would like to happen is have a count to say:

NewCategory: If New < 1, "New", If New <1 and Old < 1,"Old","old"

I know what i want to happen and i know how to do it in excel but it's not the same the access, can anyone work out my if statement please.

thanks

Mikie

 

[dhookom]

Can you post the actual desired display?
I would try a value expression of:

  IIf(New < 1 AND Old >= 1,"Old","New")

Set the totals to Min or Max.


Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I've tried to use the value expression method but got a bit confused, but i've seen you're example so i'll try that when i'm back at work.

I've attached a link to a mock up results picture created using dummy data in excel to show the desired display. Basically ParentID represents the id number for each family, and if there is:

a value in old column, means there is either only one child in the family, or more than 1 child but they're both on the old rate.
a value in new column,, means there is either only one child in the family, or more than 1 child but they're both on the new rate.
a value in both new and old, means there is more than one child in the family, they child(ren) have different rates, but they have to have the old rate.

Hope that makes sense.

thanks again for your help

 

[dhookom]

I can't view file sharing sites from work so I won't be able to answer until later.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Your image seems to have little relationship to

ParentID TotalChildID New Old
1 4 0 4
176 22 0 22
690 22 0 22
1192 96 92 4

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

You're right, the picture and the original dataset don't match but the principle is the same:

If there is a value greater than 1 in both New and Old column, i would like the NewCatergory = Old

 

[dhookom]

What is "the data i have"? Is it the result of a crosstab or your raw data?
Can you please post your actual data from qrySummaryReportYearlyExistingNew with the final results you would like?

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

sorry about the confusion, i have a query:

qrySummaryReportYearlyExistingNew which pulls together all the data i need

i then use the above query in a crosstab query to summarise it:

qrySummaryReportYearlyExistingNew_Crosstab1

I've attached a pic which shows the data that is pulled through in qrySummaryReportYearlyExistingNew, the output i get at the minute from qrySummaryReportYearlyExistingNew_Crosstab1 and hopefully my eventually output which will be used in reports.

I know i don't explain myself properly, i have been googling around and i've read about sub queries but i'm not sure if they'd work in this problem or if crosstab queries are the way to go.

Hope this helps

Michael

 

[dhookom]

Your ...existingNew has only Nursery2 yet your eventual output has Nursery1 and Nursery 2. It's impossible to connect the two displays.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

ok so i've amended it to show data for both Nursery 1 and Nursery 2. Sorry for not explaining myself properly