PDA

View Full Version : MS Access query question



Tony
11-03-2009, 04:58 PM
I suspect I'm missing something obvious (nothing new there), but I'm having a problem. I've posted the same question in another forum, but I'm hoping the collective PF1 wisdom will come to the rescue.

I have a query that returns the list of current membership types and a table of members that includes member type. I am trying to create a query that returns the number of members, by type, that have joined in the last 12 months. I want to have blank or zero values where there are no members for a member type. This is my SQL as created by Access:


SELECT Count(tblmembers.memjoindate)
AS CountOfmemjoindate, queryCurrentMemberTypes.memtype
FROM queryCurrentMemberTypes LEFT JOIN tblmembers ON
queryCurrentMemberTypes.memtype = tblmembers.memType
WHERE (((tblmembers.memjoindate)>DateAdd("m",-12,Date())))
GROUP BY queryCurrentMemberTypes.memtype;

What I am getting are just the non-zero member types. What I want is something like:

Full 120
Joint 0 (or blank)
Student 225
Life 0 (or blank)
Complimentary 2

What am I missing?

andrew93
11-03-2009, 07:42 PM
Hi Tony

If I am reading your SQL correctly (I haven't tested it) you need to open the query in design view, double click the line towards the top of the screen that joins queryCurrentMemberTypes and tblmembers and select the option to include all records from queryCurrentMemberTypes.

If this doesn't work can you post the SQL for queryCurrentMemberTypes? If you run just this query, do you see all of the different types of memberships?

Cheers
Andrew

Tony
12-03-2009, 09:52 AM
I've created a much simplified version of the database, without the intermediate queries, and attached 3 screenshots:

Capture1.png shows the design of the query.
Capture4.png shows the SQL of the query.
Capture2.png shows the results of that query.
Capture3.png shows the results of a query without the date selection (just to prove that there are more membership types).

Tony
12-03-2009, 01:35 PM
I've done an experiment, and removed all the member table records for one of the member types. Now if I run the query without date selection, the left join works as expected, i.e. it shows 0 as a count for the member type with no records.

So the issue is to do with the date selection. My Access and/or SQL knowledge aren't sufficient to allow me to investigate this, so any help would be appreciated.

Tony
12-03-2009, 02:03 PM
I've done another test and created a query that just selects all the members according to the date criteria. Then I've done the join query using that selection query on the right side of the join, and it works as expected - all the member types are shown, with zero as the value where appropriate. That would obviously solve my problem, but seems awkward.

So does that mean

there is a bug in Access
I need to achieve my result by working as above
I need to restructure my original query
What I was trying to do is just against the rules, even though it was syntactically correct.


Any suggestions?

nofam
12-03-2009, 03:35 PM
Just off the top of my head, I'd guess your date criteria is the problem; you're restricting the memjoindate to the last 12 months, but those membership types that have no members won't have any join dates either, so they're falling outside of the criteria.

Try adding an Or Is Null to your date criteria

Incidentally, your date restriction is a little unusual too; a more common way to get a rolling 12-months is like this:

Between DateSerial(Year(Date()),Month(Date())-12,1) And DateSerial(Year(Date()),Month(Date()),0)

Tony
12-03-2009, 03:51 PM
Just off the top of my head, I'd guess your date criteria is the problem; you're restricting the memjoindate to the last 12 months, but those membership types that have no members won't have any join dates either, so they're falling outside of the criteria.

But isn't the point of a left join that all the records in the left table (memtypes) are included, even when there are no matching records in the right table? That is certainly what happens when I go through the two-step process of filtering the data in one query, then applying the join in a second query.

I'm not sure what the advantage is of your date calculation. Mine has the merit(?) of being shorter (less chance to make mistakes:).) Not that I'm criticising - working by myself means that I don't get exposed to what may be best practice - and also that I don't have anyone except PF1 to ask when problems arise :crying:

Parry
13-03-2009, 12:12 PM
Hi Tony, yes you would think the Left Join should bring in all the records for type but this would only be the case if the date condition wasn't there. To get a full list of all types and have 0 where the type doesnt meet the condition (i.e. count would be 0 as the type isnt used in the date range) then do the following:

1) Create a Group By query that has the member type, count and a where clause of the date joined is in the last 12 months.
2) Create a 2nd query thay includes query in #1 above and the MemberType table with the following details:
* Do a left join where all records from MemberType are included and only those from query #1 where the joined fields are equal.
* In the fields for the query include the Type from the Types table and an expression which has an IIF statement that checks to see if count is null, and if so uses 0 instead.
eg CountType: IIF(ISNULL([CountOfmemjoindate]),0,[CountOfmemjoindate]) assuming CountOfmemjoindate is the name of the count column in query #1.

If your confused by the above please advise the name of the members and types tables and the relative columns in each and I will post the SQL for you.

hth
regards,
Graham

Tony
13-03-2009, 12:39 PM
Thanks for the very full reply. I think I have already done what you suggested, as I described a couple of posts earlier (starts "I've done another test..."), and yes, it worked as advertised.

From a separate source, I've been given this SQL:


SELECT T.MemType, Nz(SQ.TempCount, 0) AS CountByMemberType
FROM tblMemTypes AS T LEFT JOIN (SELECT M.MemType, Count(M.MemNum) AS TempCount
FROM tblmembers AS M
WHERE (M.memjoindate>DateAdd("m",-12,Date()))
GROUP BY M.MemType
) AS SQ ON T.MemType = SQ.MemType;

which appears to work - but I've absoultely idea why! My SQL knowledge is fairly rudimentary, so I've got a bit of reading to do to understand what it all means.

Parry
13-03-2009, 01:19 PM
Cool, didnt realise you had already resolved this. The query you have from the other forum is effectively doing 2 queries but is nesting the conditional query within the type query. The NZ function replaces the IIF/Is Null condition so is a different way to achieve the same result.

regards,
Graham

Paul.Cov
14-03-2009, 05:49 PM
Yeah, I've found Access to be a real pain at times when Null or Empty values get into queries.

What seems to work perfectly turns to crud when a field has nulls.

Another strategy I've resorted to in the past is to run action queries beforehand to replace all Null and Empty values in a table with Zero, or some other value that I use to represent 'not used' within the database.

Another change to your table design to set default values of Zero (not null), or Date() for date values, and to include a validation rule of IsNotNull may be another way to avoid issues like this.

I seem to recall some inconsistent use of syntax between gueries and macros at times too, like one using IsNotNull, and another using Not(IsNull(...))

Access is a really great piece of work, but it has some quirky little traps that can eat up days of design and debugging.