Complex Queries - Why Won't They Include Everything??

[andy_dyer]

Hi,

I have created the attached sample as a guide to what i am trying to do...

There are 23 Surgeries and I am attempting to obtain their total referrals and put them in a league table.

When i do the number displyed drops to 18

I then want a second column on my league table for the referrals in the last 3 months...

When I do this the number displyed drops to 16

Why are my numbers dwindling??

 

[st3ve]

Now 23

Not quite sure what you're after but ...

Removing the lower 'true' from under the criteria for OnBoard field in qryPracticeReports-WestLeagueTable gives me 23.

 

[andy_dyer]

Hi St3ve,

Sorry that was my goof up!!

The number onboard should be 18

The number in the league is 18

BUT the number in the second query showing the number of referrals over the last 3 months is only 16...

I cannot work out why two practices are disapeering when all the other ones that have "0" are still there!

 

[Mile-O]

Not looked at your database but is the JOINS your problem?

If you have an INNER JOIN then it will only return those records where the key they are joined on atches in both tables.

 

[andy_dyer]

Nope! Not this time!

Code that gives all 18 practices:

SELECT tblSurgery.Surgery, Count(Query1.Surgery) AS [Number of Referrals]
FROM tblSurgery LEFT JOIN [SELECT tblInput.* FROM tblInput WHERE tblInput.[Referral Received Date] Between #4/1/2003# And #3/31/2004#]. AS Query1 ON tblSurgery.Surgery = Query1.Surgery
WHERE (((tblSurgery.PCTID)=3) AND ((tblSurgery.OnBoard)=True)) OR (((tblSurgery.PCTID)=3) AND ((tblSurgery.OnBoard)=True) AND ((Query1.Surgery) Is Null))
GROUP BY tblSurgery.Surgery
ORDER BY Count(Query1.Surgery) DESC;

Code that gives only 16 practices:

SELECT tblSurgery.Surgery, Count(Query1.Surgery) AS [Number of Referrals]
FROM tblSurgery LEFT JOIN [SELECT tblInput.* FROM tblInput WHERE tblInput.[Referral Received Date] Between #4/1/2003# And #3/31/2004#]. AS Query1 ON tblSurgery.Surgery = Query1.Surgery
WHERE (((tblSurgery.PCTID)=3) AND ((tblSurgery.OnBoard)=True) AND (([tblInput].[Referral Received Date])>=DateAdd("m",-3,Date()))) OR (((tblSurgery.PCTID)=3) AND ((tblSurgery.OnBoard)=True) AND ((Query1.Surgery) Is Null))
GROUP BY tblSurgery.Surgery
ORDER BY Count(Query1.Surgery) DESC;

I need both to give me the 18 so that on export to Excel they can line up correctly when listed by Alphabetical order....

 

[andy_dyer]

I figure that I need to get the 3 months crietria into Query1 which is the Query within the Query...

That was Namliam's suggestion when I had the problem getting the first query to work which worked fine but I am trying to apply the same thing to this new problem with no success!!

The code I am working on is:

SELECT tblSurgery.SurgeryID, tblSurgery.Surgery, Count(Query1.Surgery) AS [Number of Referrals]
FROM tblSurgery LEFT JOIN [SELECT tblInput.* FROM tblInput WHERE ((tblInput.Referral Received Date)>=DateAdd("m",-3,Date()))]. AS Query1 ON tblSurgery.Surgery = Query1.Surgery
WHERE (((tblSurgery.OnBoard)=True) AND ((tblSurgery.PCTID)=3) OR (((tblSurgery.OnBoard)=True) AND ((tblSurgery.PCTID)=3) AND ((Query1.Surgery) Is Null)))
GROUP BY tblSurgery.SurgeryID, tblSurgery.Surgery
ORDER BY tblSurgery.Surgery;

But I keep getting syntax errors on the DateAdd part...

 

[MattS]

The two surgeries being dropped, "Petersgate Medical Centre - Scawthorpe" & "The Nelson Practice - Scawthorpe", only have referral dates of 1st April 2003 - therefore they are not null, and not referred in the last 3 months.

 

[andy_dyer]

Yes but I need them listed but displaying "0" in order for my excel report to work...

I basically have these two queries export to Excel and then on a different worksheet pull the two figures together for the surgeries in alphabetical order and then sort them on the numbers...

Without all the surgeries being listed I have figures going against the wrong practices...

 

[st3ve]

I think i've sussed it

See attached db1plus.mdb

The trick seems to be that the date range over which you are going to count the referrals, needs to be filtered out first see q0 in the db1plus.mdb Then do the grouping in q1, and finally use q2 to arrange your data as required.

You may be able to reduce/simplify these queries, but this should get you re-started, i hope.

HTH

 

[andy_dyer]

U've done it!!!!!!!!!!!

I have no idea how you dreamt up that working of Queries but I am sooooo glad you did!!

Thank you so much for your help!!