By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

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

P: 3

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

Expand|Select|Wrap|Line Numbers
  1.  SELECT tblAttendance1.DTE, tblAttendance1.ATTENDED.Value, tblAttendance1.[TYPE OF TRAINING]
  2. FROM tblAttendance1
  3. WHERE (((tblAttendance1.DTE) Between #1/1/2005# And #12/31/2005#) AND ((tblAttendance1.[TYPE OF TRAINING])="DRILL"));

This is the subsequent Crosstab Query

Expand|Select|Wrap|Line Numbers
  3. SELECT qryDateDrill2005.tblAttendance1.ATTENDED.Value, Count(qryDateDrill2005.[TYPE OF TRAINING]) AS [Total Of TYPE OF TRAINING]
  4. FROM qryDateDrill2005
  5. GROUP BY qryDateDrill2005.tblAttendance1.ATTENDED.Value
  6. PIVOT "Qtr " & Format([DTE],"q/yyyy");
Hope the above makes sense. Any help would be great. Thank you in advance.
Feb 1 '14 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 10K+
P: 12,366
Take the crosstab and outer join it to a distinct list of members.
Feb 1 '14 #2

P: 3
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 ([qryQyarterDrill2005].[Value]=[tblMemberList.[NUMBER/NAME])."

Any suggestions?


Feb 1 '14 #3

P: 21
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:

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


[ID] [Drilltype] [DateOfDrill] [UserID] [DidAttend]

Then you could use an outer join like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblUser.Lastname, tblUser.FirstName, tblDrills.DrillType, tblDrills.DateOfDrill, tblDrills.DidAttend 
  2. FROM tblUser LEFT JOIN tblDrills 
  3.   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:
Feb 1 '14 #4

Expert Mod 5K+
P: 5,397
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
Feb 1 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.