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

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

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

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"));
  4.  


This is the subsequent Crosstab Query

Expand|Select|Wrap|Line Numbers
  1.  
  2. TRANSFORM Count(qryDateDrill2005.[TYPE OF TRAINING]) AS [CountOfTYPE OF TRAINING]
  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");
  7.  
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


Rabbit
Expert Mod 10K+
P: 12,315
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?

Cheers

Dan
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:

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

and

[tblDrills]
-----------
[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:

http://improvingsoftware.com/2011/03...rosoft-access/
Feb 1 '14 #4

zmbd
Expert Mod 5K+
P: 5,287
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.