Another Median Query

[aphex]

Hi,

I am trying to run queries to determine the median values for different age groups of peoples each with different illnesses. At the moment the database asigns an age group for each person whos age falls into the module determined age brackets, under 16, 16 - 40, 41-50 etc.

For each age group and diagnosis (bronchiectasis, sarcoidosis or pulmonary fibrosis), I need an age distribution chart including the min, max, mean and median.. min max mean are all simple enough and using MS VB median module I can individually calculate medians for each of these. The problem comes when trying to combine these all in one data set.

One solution I found was to combine two reports so I have queries with min, max, mean and then the medians set up beside them. The problem tho with this is that when a recordset returns 0 results (i.e there are no people under 16 with sarcoidosis), the query linked to the report does not return the value of 0 but as they dont exist it does not include it at all. The median fields however return blanks and cos of this the line spacing becomes messed up and the report becomes incoherrant and incorrect.

I think the solution will have something to do with querying, though so far I have not been able to work it out

Please help!

 

[neileg]

You need to look at your joins. The default join in Access is an inner join (type 1 in the join dialogue box). You will need to look at using an outer join, Left (type 2) or Right (type 3). This will ensure you get all the records from one side of the join even if there are no matching records on the other side.

 

[aphex]

Ok think I have now got to a stage where my age distribution functions are almost working.. I have one query (queryA) determining the min, max, average and occurances of each age group. Then I have another several queries giving me lists of ages in each age group, i.e Where PatieintInfo.Age >41 And <50... Next I have a new query (queryb) that calls the median module and brings together the several queries afore mentioned...

SELECT [Patient Info].Age AS Median
FROM [Patient Info]
GROUP BY [Patient Info].Age
HAVING ((([Patient Info].Age)=median("17 And 40","age") Or ([Patient Info].Age)=median("41 And 50","age") Or ([Patient Info].Age)=median("51 And 60","age") Or ([Patient Info].Age)=median("61 And 70","age") Or ([Patient Info].Age)=median("71 And 80","age") Or ([Patient Info].Age)=median("81 And 90","age") Or ([Patient Info].Age)=median(">90","age") Or ([Patient Info].Age)=median("0 And 16","age")));


The problem which I am having is combining/nesting queryA and queryB together. When I do this it repeats each median and each subsequent field several times..

Anyone got any ideas??

Thanks!

 

[aphex]

here is an example of how im trying to approach the problem...

SELECT AgeGroups([Patient Info]![Age]) AS [Age Group], Count([Patient Info].[Patient Info ID]) AS [CountOfPatient Info ID], Avg([Patient Info].Age) AS AvgOfAge, Min([Patient Info].Age) AS MinOfAge, Max([Patient Info].Age) AS MaxOfAge, [age dist 222].Median
FROM [Patient Info], [age dist 222]
GROUP BY AgeGroups([Patient Info]![Age]), [age dist 222].Median
ORDER BY Count([Patient Info].[Patient Info ID]) DESC;

 

[aphex]

omg this is infuriating! afore mentioned queryb was working absolutely fine up untill a few minutes ago when it decided not to recognise one of the medians im wanting to work out, even tho it was there 2 minutes ago!!! arg!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! thank god its Friday......