UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Count Field Subquery Affecting Query Results, Access 2016    
 
   
brastedhouse
post Oct 22 2019, 02:58 PM
Post#1



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Hello, another interesting puzzle.

I was recently given advice on how to solve a count problem that involved a report not allowing me to sort a count field. The process works but I am having a problem in that the count returned is for all years even though the unbound form that calls the report has a textbox for the year in it and the query has Forms!FormName!FieldName in the criteria line. Apparently the subquery (count field) is causing a problem with the main query filtering for the year?

I have attached a scaled down version of the DB for people to look at. It contains 4 tables, 1 query 1 form and 1 report. What I get now is the correct count of records for all years. I cannot figure out why I cannot get the correct count for each year as passed by the form field.

Thanks for the help.

Best, Scott
Attached File(s)
Attached File  UASortTemp__2_.zip ( 91.45K )Number of downloads: 1
 
Go to the top of the page
 
Doug Steele
post Oct 22 2019, 03:14 PM
Post#2


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Sorry: too many errors trying to look at the database to tell.

When I open the report in Design mode, I get this:

Attached File  UASortTemp_1.jpg ( 28.54K )Number of downloads: 3


When I open the report, provide a year, then go to open a report, I get this:

Attached File  UASortTemp_2.jpg ( 14.57K )Number of downloads: 1

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
brastedhouse
post Oct 22 2019, 03:55 PM
Post#3



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Hi, I don't know what the problem is. I just opened that file and I had no problems. So I just re-exported the relevant pieces and checked the file and zipped it and so here it is again. Sorry for any inconvenience. Scott

relevant items
tblCall
tblCallMember
tblDeptMember
tblCallType (I don't' think this is in any of this material , but just in case)

qryDeptMemberCallCountAll

frmMain (here to allow getting to forms without error.)
frmMainMemberReports (here to allow getting to forms without error.)
frmDeptMemberCallCountAll

rptDeptMemberCallCountAll

modCloseMeAndOpenMain (here because you will get an error in closing forms without it)
This post has been edited by brastedhouse: Oct 22 2019, 04:01 PM
Attached File(s)
Attached File  UASort_Checked.zip ( 134.69K )Number of downloads: 2
 
Go to the top of the page
 
Doug Steele
post Oct 22 2019, 04:37 PM
Post#4


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Still getting errors, but I was able to get the report to open.

The problem might be your SQL is

SQL
SELECT tblCall.callType, ([deptMemberFirst] & " " & [deptMemberLast]) AS memberName, (Year([callDate])) AS callYear, tblDeptMember.deptMemberID, (select count("1") from tblCallMember As T Where T.callMemberFK=tblDeptMember.DeptMemberID) AS callCount, tblDeptMember.deptMemberLast, tblDeptMember.deptMemberFirst
FROM tblLUCallType INNER JOIN (tblDeptMember INNER JOIN (tblCall INNER JOIN tblCallMember ON tblCall.callID = tblCallMember.callTableFK) ON tblDeptMember.deptMemberID = tblCallMember.callMemberFK) ON tblLUCallType.callTypeID = tblCall.callType
GROUP BY tblCall.callType, (Year([callDate])), tblDeptMember.deptMemberID, tblDeptMember.deptMemberLast, tblDeptMember.deptMemberFirst, tblLUCallType.callTypeID
HAVING ((((Year([callDate])))=[Forms]![frmDeptMemberCallCountAll]![txtCallYear]) AND ((tblLUCallType.callTypeID)=[Forms]![frmDeptMemberCallCountAll]![cboCallType])) OR ((((Year([callDate])))=[Forms]![frmDeptMemberCallCountAll]![txtCallYear]) AND (([Forms]![frmDeptMemberCallCountAll]![cboCallType]) Is Null));


Try changing the HAVING to WHERE (HAVING gets applied after the grouping, WHERE gets applied before it)

SQL
SELECT tblCall.callType, ([deptMemberFirst] & " " & [deptMemberLast]) AS memberName, (Year([callDate])) AS callYear, tblDeptMember.deptMemberID, (select count("1") from tblCallMember As T Where T.callMemberFK=tblDeptMember.DeptMemberID) AS callCount, tblDeptMember.deptMemberLast, tblDeptMember.deptMemberFirst
FROM tblLUCallType INNER JOIN (tblDeptMember INNER JOIN (tblCall INNER JOIN tblCallMember ON tblCall.callID = tblCallMember.callTableFK) ON tblDeptMember.deptMemberID = tblCallMember.callMemberFK) ON tblLUCallType.callTypeID = tblCall.callType
WHERE ((((Year([callDate])))=[Forms]![frmDeptMemberCallCountAll]![txtCallYear]) AND ((tblLUCallType.callTypeID)=[Forms]![frmDeptMemberCallCountAll]![cboCallType])) OR ((((Year([callDate])))=[Forms]![frmDeptMemberCallCountAll]![txtCallYear]) AND (([Forms]![frmDeptMemberCallCountAll]![cboCallType]) Is Null))
GROUP BY tblCall.callType, (Year([callDate])), tblDeptMember.deptMemberID, tblDeptMember.deptMemberLast, tblDeptMember.deptMemberFirst, tblLUCallType.callTypeID;


If you're happier using the Design View rather than working directly with the SQL, remove the conditions you've currently got, add three new columns to the right and set conditions as shown in the diagram.

Attached File  UASortTemp2.jpg ( 35.63K )Number of downloads: 1


No guarantees this will work, since I don't really know what your results should look like.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
brastedhouse
post Oct 22 2019, 04:58 PM
Post#5



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Sorry to report that that did not work. Is there anything to the thought that the subquery has an effect on the criteria? Specifically the year field? This all got started trying to sort a count in a report. Something I thought would be simple. But apparently not. I have 4 separate uses for this count code. It would be nice if it could be made to work. Best, Scott

ps . . . does anyone have a suggestion of a good on-line course for Access VB. I am sure there a lot of things I am doing that could be done better if I had a lot more education in this. I live n a very out-of-the-way rural part of western NY, so brick and mortar schools are a problem. So I am hoping that I could get a recommendation of somewhere to go. I have no idea if any of the courses I get from googling this are any good.
This post has been edited by brastedhouse: Oct 22 2019, 05:03 PM
Go to the top of the page
 
Doug Steele
post Oct 22 2019, 06:27 PM
Post#6


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I'll try and take a look at this tomorrow.

What counts do you want to see for each year?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
RJD
post Oct 22 2019, 06:51 PM
Post#7


UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but your call count (in a field subquery) is not limited to the year. Thus the problem.

Take a look at my revision to your db. I approached the report query a bit differently so that the limit would apply. See the report query and run the report with various year and type selections, including no type.

HTH
Joe

Attachment deleted, replaced by the one in my next post #12.

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
brastedhouse
post Oct 23 2019, 06:38 AM
Post#8



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


The count of members who attended a call in that year, both for all calls and for each type of call.
Go to the top of the page
 
Doug Steele
post Oct 23 2019, 07:09 AM
Post#9


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Thanks, Joe. That saves me having to revisit this issue. smile.gif

(BTW, Scott, what I'd hoped you would reply with was the actual counts you expected for each individual for each year, not just a generic statement)

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
brastedhouse
post Oct 23 2019, 12:56 PM
Post#10



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


my apologies. I misunderstood. I do have the actual counts as I created them to test this. Best, Scott. Thanks, Joe. I need to take time tonight or tomorrow night to go over your changes. Best, Scott
This post has been edited by brastedhouse: Oct 23 2019, 12:56 PM
Go to the top of the page
 
brastedhouse
post Oct 23 2019, 09:56 PM
Post#11



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


I'm sorry but I am very frustrated. I do not understand how it seems impossible that you cannot sort on a count of records.

The kind and generous person who loaded up a new copy of my db, I cannot get it to work. Specifically, it does not give anything but 1 call each. I know that some have 2.

In another post about this topic I remember someone mentioning that the subquery did not have any reference to date and that might have an effect.

It just seems not possible that this is so hard. I see people come up with answers to things that seem far more esoteric or impossible than this every day.

I am not being ungrateful. I appreciate every answer I get here. I have learned an amazing number if things in this forum. So I 'm surprised I have been stymied by this problem.

I would appreciate it if someone would rethink this and let me know if this is even possible.

Thanks, Scott
Go to the top of the page
 
RJD
post Oct 24 2019, 12:16 AM
Post#12


UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA


Hi Scott: Sorry, the field naming conventions confused me (my fault) and I linked two tables incorrectly in the queries. Plus I adjusted one of the WHERE fields as well. Yes, reports can certainly sort on the count, as long as the data are presented properly, which it looks like has now been fixed.

I also addressed the issue of you using table lookup fields in two places. This can confuse things, thinking you are using text when you are actually using a hidden number. So I got rid of these and changed the fields to text. Normalization usually calls for what you did, but in this case it is unnecessary, and complicates things a bit. I followed things through to change everywhere this impacts, including the cboType on the form.

Take a look and see if this works better for you using the revised query. Check years 2018 and 2019.

Yes, it looks like it sorts properly on the count.

Sorry for the stumble ...

HTH
Joe
Attached File(s)
Attached File  UASortTemp2_Rev2.zip ( 91.45K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
brastedhouse
post Oct 24 2019, 09:51 AM
Post#13



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


So, no problem

I have so many questions.

1. Is having the callID field with the total row set to count and then having an expression of callCount:callID set to count in the total row how you got the report to sort on count?

2. Under the callType field in the query, what does where in the total line do? Doe sit replace all the forms!.... OR forms!...Is null in the criteria? I need to fond a reference for the items in the total line.

3. I think I see a place I was having a misconception. I was trying to count the members in a call when in reality I should be counting the calls. Is that correct?

That's enough for now. My brain hurts. pullhair.gif

I am going to put this to the test tonight. Put this into a copy of th ereal DB and see ho wit goers and then try to construct 3 new ones for the other types of activities we count. I have been tryong to figure this out for well over a year. That is where my frustration past came from.

I apologize to everyone for my rant last night. And Thank you all again for the amazing help. Someday I hope to be proficient enough to do the same for someone else.

Thank, Scott
Go to the top of the page
 
RJD
post Oct 24 2019, 10:53 AM
Post#14


UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA


Hi Scott: Glad we made some progress on this ...

QUOTE
1. Is having the callID field with the total row set to count and then having an expression of callCount:callID set to count in the total row how you got the report to sort on count?

Actually, once the count is accurate in the query, having been limited by the year and type or any type, the sorting is done in the report itself. Look to the bottom of the report in Design View and you will see two sorts: call count and then department member name (on the name you might want to sort on last and first - I had just sorted on last). The key is to get the correct counts in the query. Previously you used a method that did not take into account the year or type. That method is independent and is not limited by the rest of the query, thus the count problems before.

QUOTE
2. Under the callType field in the query, what does where in the total line do? Doe sit replace all the forms!.... OR forms!...Is null in the criteria? I need to fond a reference for the items in the total line.

The WHERE totals, without Show, for callType simply applies the WHERE condition without grouping on the callType. And the second Design View column with the form reference and Is Null, is an awkward (IMO) way that Access SQL displays the logic of taking the callType as a limit or all callTypes if the combobox is Null. Here it is in a more reasonable syntax in SQL (again, IMO) ...

WHERE (((tblCall.callType)=[forms]![frmDeptMemberCallCountAll]![cboCallType])) OR ((([forms]![frmDeptMemberCallCountAll]![cboCallType]) Is Null))

It just says use the callType in the combobox, but if that is Null, take all callTypes.

It does not replace any forms, but reads the controls on the form to do its work.

QUOTE
3. I think I see a place I was having a misconception. I was trying to count the members in a call when in reality I should be counting the calls. Is that correct?

Yes. Since you are grouping on the member, then you should count the associated calls.

Be careful in applying this to your full db. Note that I not only changed the report query, but also the form frmDeptMemberallCountAll and the report. And I changed the display format to Overlapping Windows, with no Modals or Popups. And maybe some other things as well as I tinkered with the db. Try putting the new query, form (replacement) and report (replacement) in your main database (a copy to start) and see how that goes. Plus change the table lookup fields to text, with the appropriate names (EMS, etc.) in the records. And the change in the combobox to a single text field might affect other things in your db.

Good luck, and let us know if we can be of further assistance.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
brastedhouse
post Oct 25 2019, 10:11 AM
Post#15



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Ok, so I am getting an error. Error Number = 3270, Property not found. I see the change of the cboCallType to the text field in the query and the change to the form combo box. I don't see anything else that would affect the rest of the db. So I am confused. Sorry.

I did make a copy and I'm working on the copy, so I can make all the mistakes I want. Right now I'm not sure where to look for the problem.

Best, Scott
Go to the top of the page
 
RJD
post Oct 25 2019, 10:33 AM
Post#16


UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA


Hi Scott: You will get that when comparing the call type, where one side is numeric and the other text, in the query criteria. Check callType in the table. Make sure it is NOT a lookup field, but is text. Then make sure you changed the table values to EMS, etc., instead of 1, 3, etc. Make sure the cbocallType has 1 field, the text field only, with 1 column.

I ran into that issue developing my revision to your db. And that is what it was.

Look at the revision. Look at how I did the combo box and how I changed the table and values.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
brastedhouse
post Oct 27 2019, 09:57 PM
Post#17



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Thank you very much. An update to let you know that your idea worked like a charm. I went through the form and query and report and looked at the table. I figured out what you did and successfully incorporated the changed into my db.

I would like to know why you made the callType text. It does not allow me to create a relationship between the table that hold the list of callTypes and tblCall. Is this a problem?

And as a bonus, while digging through all this, I came upon yet another lookup table that I changed and is now a number field with a relationship to the table that needs the data. There seems to be no end to multivalue and multivalue lookup fields.

Thanks to everyone who contributed to this.

Best, Scott
Go to the top of the page
 
RJD
post Oct 28 2019, 10:46 AM
Post#18


UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA


Hi Scott: You are very welcome, from both of us (I am sure Doug joins me in this). Glad you got that working.

QUOTE
I would like to know why you made the callType text. It does not allow me to create a relationship between the table that hold the list of callTypes and tblCall. Is this a problem?

Shouldn't be a problem. Since there is only one field in tblLUcallType except for the callTypeID (an Autonumber), the only reason for the table anyway is to give sequence to the text values (beyond alphabetical). You can still do that in the combobox while still capturing the text value. To me (and there will be other opinions no doubt) if there is only one field to be concerned with (the text), I usually just put the text in the main table instead of having to always link the tables. This is easy to do from the combobox as well. If you DO want to use the sequence, you can link through the text when that is necessary. As I said, others may differ with this, but for me this is pretty much standard. If the utility table contains multiple fields to which you must refer, then yes, you may need to use the ID as the linkage, although the text abbreviation can also serve as the key.

You can use the ID in the combobox if you want, un-shown, to sequence the text value.

Just be sure you accommodate this in all places in your db if you use this approach.

QUOTE
I came upon yet another lookup table that I changed...

Yes, there are a couple of these in tblDeptMember. Good catch. You might also see HERE for a discussion on table lookup fields.

QUOTE
There seems to be no end to multivalue and multivalue lookup fields.

I saw the lookup fields, but no multivalue fields. But I would not use those either, as a matter of practice in my case. There are better ways, IMO, to handle cases where this may seem appropriate.

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 12:21 AM