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
> Crosstab Query Report With Variable Date Range, Access 2016    
 
   
USMCdBA
post Nov 27 2017, 09:44 AM
Post#1



Posts: 158
Joined: 11-December 02
From: Charleston, SC


Hello fellow UA members,
Stumped all weekend on this. Saw a similar post Friday morning( http://www.UtterAccess.com/forum/index.php...tab+Query\ ) but have not been able to work it out. I think I only confused myself further.

I have a database that collects hours worked on various projects. The goal is a monthly report of hours worked (Level of Effort { LOE } ) summed in a crosstab as follows:
ECP or task as ROW headers, WEFdate as COLUMN headers, and SumOfEHW as field values.

The crosstab works well until I try to limit the dates. This will be a locked application operated by users with no knowledge of Access who absolutely know in their hearts that Excel is the greatest database ever devised.
I am posting a copy of the database. From the main menu click the Reports Module button. There you can see the start & end date combo boxes by which to limit the report.




Attached File  DO_ECP_Tracker_ZIP.zip ( 85.47K )Number of downloads: 7

--------------------
KDP
Charleston, SC -- USA
Bless those who don't need everything immediately.
Go to the top of the page
 
River59
post Nov 27 2017, 10:09 AM
Post#2



Posts: 1,345
Joined: 7-April 10
From: Detroit, MI


The first problem that I ran into was that it couldn't find the form field [End Date]. This is referred to in the query "EHWsum_byECPnum_withinWEFdate".
You need to change the field name and remove the space [EndDate] is the correct name of the control.

The queries all run after I made this change. Is there an issue with what they return?

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
projecttoday
post Nov 27 2017, 10:28 AM
Post#3


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


You mention Excel. Are you aware that you can create an Excel pivot table from Access. A major advantage of doing it this way is the user can double-click on any number and see the details that went into that number. So the user gets more than just a bunch of aggregates.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
RJD
post Nov 27 2017, 10:56 AM
Post#4


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


Hi: I made some changes to your db. As was mentioned above by River, I took the spaces out of StartDate and EndDate in the criteria in the query EHWSum_byECPnum_withinWEFdate. But I also changed the way the Parameters were written, along with making the combobox date selections into actual sort-able dates.

See if this does what you want with the queries. Both crosstabs have been altered.

The question remains how you will deal with the variable field names from the crosstab when they get to the report... will you do this manually?

HTH
Joe
Attached File(s)
Attached File  DO_ECP_Tracker_Rev1.zip ( 71.44K )Number of downloads: 1
 

--------------------
"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
 
RJD
post Nov 27 2017, 11:12 AM
Post#5


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


Oh, and another question... Did you mean to display repeating same dates if there are different times in the date field? Or did you want to consolidate based on dates regardless of differentiating times?

See Rev2 attached that consolidates across times into like dates.

The question still remains how you will deal with variable columns in the report header...

Additional issue: You are using First in the crosstab query. This does not always give you what you expect. What is your intent here?

HTH
Joe
Attached File(s)
Attached File  DO_ECP_Tracker_Rev2.zip ( 68.09K )Number of downloads: 2
 

--------------------
"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
 
USMCdBA
post Nov 27 2017, 11:52 AM
Post#6



Posts: 158
Joined: 11-December 02
From: Charleston, SC


Thanks RJD (Joe) & River59. The sortable date was next on my list and I see that the query works as expected now. When I click the button for the report though, I get an error saying that the Access database engine does not recognize " as a valid field name or expression. It must be something in the button code or on the report itself since the query is functioning correctly independent of the report formatting.


Also of concern is the "Field List" for the report . If I open the report in Design view and select Add Existing Fields from the Design menu, the field list is showing me the dates for my column headers. The field names on the report are the actual dates from the data entry as opposed to the field names for the date fields of the data.


If this report is to be dynamic in that each month or every time it is run it will conceivably have different start and end dates, how can it be saved with existing data dates as field names?

Each time the start & end dates on the report board are changed, the column headers (dates) change when the crosstab query is run. Does this mean I will have to re-create the report every time it is run?
I suppose as an alternative method, I could run the query and export the results to Excel. I know I've coded that to a button before, but it has been years & Access Versions ago (2003 I think), so things may have changed some.


Thanks for the time & efforts.
Regards etc...
KDP

--------------------
KDP
Charleston, SC -- USA
Bless those who don't need everything immediately.
Go to the top of the page
 
USMCdBA
post Nov 27 2017, 12:01 PM
Post#7



Posts: 158
Joined: 11-December 02
From: Charleston, SC


Hi RJD,
The hours are summed for the Week Ending Date which will be Friday (hence WEFdate for week ending Friday Date). I am without solution for the variable field (column) names.
More & more, I am liking the click button to export to Excel (or even Word) solution and letting the end user deal with their data formatting as needed. smile.gif pullhair.gif

R/etc...
KDP

--------------------
KDP
Charleston, SC -- USA
Bless those who don't need everything immediately.
Go to the top of the page
 
River59
post Nov 27 2017, 12:08 PM
Post#8



Posts: 1,345
Joined: 7-April 10
From: Detroit, MI


To fix the ' ... does not recognize " as a valid field name ...' error, you need to change the sort order for the report and remove the table name.
Just sort it by [fkECPid].

There is a solution to the variable field column names with a bit more work. I should have an example but not sure if I have it with me today. Let me check, if I find it, I will let you know.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
RJD
post Nov 27 2017, 01:28 PM
Post#9


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


Hi again: I went ahead and dealt with the variable fields in the report. To do that I sequence numbered the dates in the queries and added records (with UNION queries) to pad out the range in case no records were available for a particular date. I dealt with this in the report by calling the sequence numbers in the body and calling the date headers from the form fields. I am assuming since you call this a monthly report, that you will have either 4 or 5 weeks. I dealt with that by logic in the 5th week. I also indicated the range of dates in the header of the report. This to show you where the totals came from, in case the range was greater than 5 weeks. You could change the total values by adding the shown columns (fields) and avoid any totals beyond the desired 4 or 5 weeks even if you entered an expanded range.

This process deals with variable date ranges in reports. This method avoids both VBA and manual intervention. ADDED COMMENT: Crosstab can also be avoided as well in this method of Dynamic Matrix Reporting.

See how this looks to you. I am still not understanding what you did with the form manipulation, but I will let you deal with that.

(The report had not been dealt with earlier - just the queries.)

SEE correction attached in the next post ...

HTH
Joe
Attached File(s)
Attached File  DO_ECP_Tracker_Rev3.zip ( 73.87K )Number of downloads: 2
 

--------------------
"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
 
RJD
post Nov 27 2017, 02:06 PM
Post#10


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


My error. I forgot to add a 1 to the week calculation in the first section of the UNION queries. And I overlooked the 0 column produced. Attached is a revision that corrects this oversight...

Sorry about that ... pullhair.gif

HTH
Joe
Attached File(s)
Attached File  DO_ECP_Tracker_Rev4.zip ( 69.48K )Number of downloads: 4
 

--------------------
"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
 
River59
post Nov 27 2017, 02:09 PM
Post#11



Posts: 1,345
Joined: 7-April 10
From: Detroit, MI


Thanks for picking this up, RJD. I'm at work and never know when duty will call (usually when it is least opportune). Nice solution to OP's need.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
RJD
post Nov 27 2017, 02:48 PM
Post#12


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


QUOTE
Nice solution to OP's need.

Thanks River. Hope this (with my last correction) meets the OP's needs. This is a technique I developed several years ago to avoid VBA and manual intervention (plus crosstab as well, if one wants to avoid that) difficulties. There are VBA solutions out there, changing the report controls, but I just thought that could be avoided. And manual intervention is, well, just not right in an automated environment!

Let's see how the OP reacts, and we can go from there if needed.

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
 
USMCdBA
post Nov 27 2017, 02:50 PM
Post#13



Posts: 158
Joined: 11-December 02
From: Charleston, SC


PERFECT: I was walking through rev3 line-by-line trying to determine why the last column was returning zero's even though there is data there when rev 4 came accross. River 59 is correct: This is a nice solution and does exactly what I need. Y'all do nice work-- wish I could say the same for myself -- but I'll cut myself a little slack since this is the first I have used Access 2016 and the first database project I've attempted since August of LAST year. They've had me busy doing AGILE Software administration -- Atlassian products-- I am starting to think upside-down & backward like they do in Australia

Again, thank you guys! Now I all I have to do is touch up the paint and break-test it.
Best Regards all!
K D P

--------------------
KDP
Charleston, SC -- USA
Bless those who don't need everything immediately.
Go to the top of the page
 
RJD
post Nov 27 2017, 03:03 PM
Post#14


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


You are very welcome, from both of us. Glad that's working for you. And it should be a good model for future endeavors of this type.

Good luck with your project.

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
 
RJD
post Nov 27 2017, 09:06 PM
Post#15


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


Hi again: It occurred to me that since the dates are always 7 days apart and you will always be looking for 5 dates to report (although the 5th may not be wanted), there should be an easier way to do this. So I got rid of the crosstab, substituting a Totals query instead, and also got rid of the UNION query as well, since no padding is now required. Here's what the query looks like...

CODE
SELECT ECP_Number, ECP_Title,
Sum(IIf([WEFdateDay]=[forms]![ECP_LOE_ReportBoard]![StartDate],[SumOfEHW],0)) AS 1,
Sum(IIf([WEFdateDay]=[forms]![ECP_LOE_ReportBoard]![StartDate]+7,[SumOfEHW],0)) AS 2,
Sum(IIf([WEFdateDay]=[forms]![ECP_LOE_ReportBoard]![StartDate]+14,[SumOfEHW],0)) AS 3,
Sum(IIf([WEFdateDay]=[forms]![ECP_LOE_ReportBoard]![StartDate]+21,[SumOfEHW],0)) AS 4,
Sum(IIf([WEFdateDay]=[forms]![ECP_LOE_ReportBoard]![StartDate]+28 And forms!ECP_LOE_ReportBoard!StartDate+28 <= forms!ECP_LOE_ReportBoard!EndDate ,[SumOfEHW],0)) AS 5,
[1] + [2] + [3] + [4] + [5] AS EHWSum
FROM EHWsum_byECPnum_withinWEFdate
GROUP BY ECP_Number, ECP_Title;

This becomes the record source for the report, and actually should run a bit faster.

See my latest (and hopefully last) version. The report is the Alt one that runs from a command button directly beneath the other report button.

In case you are still looking at this ... and now I will let this go! thumbup.gif

HTH
Joe
Attached File(s)
Attached File  DO_ECP_Tracker_Rev5.zip ( 77.15K )Number of downloads: 4
 

--------------------
"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
 
USMCdBA
post Dec 1 2017, 01:06 PM
Post#16



Posts: 158
Joined: 11-December 02
From: Charleston, SC


Thanks again Joe: I went with the REV 5 solution to keep it simple. The project was well received and as is normal, ADDITIONAL REQUIREMENTS are being considered. Boss even revisited a previously rejected requirement for separation of users' and admins' functions via password. He was also thinking of posting it to a shared drive or SharePoint -- it just never ends(which is a good thing).
Best Regards
KDP

--------------------
KDP
Charleston, SC -- USA
Bless those who don't need everything immediately.
Go to the top of the page
 
RJD
post Dec 1 2017, 01:31 PM
Post#17


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


You are very welcome. Yes, the more the boss ponders, the more work there is - usually a good thing!

Good luck with your project.

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    12th December 2017 - 08:14 PM