Access At My Whits End! Query that incorperated combo box issue!

[Danreed87]

Hi,

Hard to explain but I have a query that sorts the type of training attended "Drill" and by which members during a certain year. I then have crosstab query that counts the amount of "Drills" the members have attended in each quarter. The members that have attended are fed from a multi combobox from the table.

When the crosstab query runs it only shows the members that attended the "drill". I want to show all of the members on the query even if they have not attended a drill and it is blank. From this I can then conditional format the report to highlight members who don't make 4 drills in a quarter including those that have not turned up at all.

This is the original Query

 SELECT tblAttendance1.DTE, tblAttendance1.ATTENDED.Value, tblAttendance1.[TYPE OF TRAINING]
FROM tblAttendance1
WHERE (((tblAttendance1.DTE) Between #1/1/2005# And #12/31/2005#) AND ((tblAttendance1.[TYPE OF TRAINING])="DRILL"));

This is the subsequent Crosstab Query

TRANSFORM Count(qryDateDrill2005.[TYPE OF TRAINING]) AS [CountOfTYPE OF TRAINING]
SELECT qryDateDrill2005.tblAttendance1.ATTENDED.Value, Count(qryDateDrill2005.[TYPE OF TRAINING]) AS [Total Of TYPE OF TRAINING]
FROM qryDateDrill2005
GROUP BY qryDateDrill2005.tblAttendance1.ATTENDED.Value
PIVOT "Qtr " & Format([DTE],"q/yyyy");

Hope the above makes sense. Any help would be great. Thank you in advance.

[Rabbit]

Take the crosstab and outer join it to a distinct list of members.

[Danreed87]

Hi Rabbit,

Thank you for your reply. I have tried to outerjoin the crosstab with the list of members. I joined the names in the member list with the attended.value in the corsstab but it says

"Cannot join on Memo, OLE or Hyperlink Object ([qryQyarterDrill 2005].[Value]=[tblMemberList.[NUMBER/NAME])."

Any suggestions?

Cheers

Dan

[TjabbeTjibsma]

I would second the outer join approach. If possible, the easiest way to go about this would be to setup an attendance/drill table and a user table.

Something along the lines of:

[tblUser]
---------
[ID] [LastName] [FirstName] [Function] [EmployedSince] etc etc.

and

[tblDrills]
-----------
[ID] [Drilltype] [DateOfDrill] [UserID] [DidAttend]

Then you could use an outer join like this:

SELECT tblUser.Lastname, tblUser.FirstName, tblDrills.DrillType, tblDrills.DateOfDrill, tblDrills.DidAttend 
FROM tblUser LEFT JOIN tblDrills 
  ON tblUser.ID = tblDrills.UserID

Using WHERE clauses and GROUP BY statements you can then filter and group the data like you want it to.

As far as joining on a text field in general, I would advise against that, and try to use number fields if possible. If you're stuck with the memo fields, this blog post describes a way around the problem:

http://improvingsoftware.com/2011/03/15/how-to-join-on-memo-fields-in-microsoft-access/

[zmbd]

The members that have attended are fed from a multi combobox from the table.

Just to clarify, this is a "look-up" field at the table level, not in a query or form?

You sould also remove the backslashes from field names:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access