Oldest Date By WeekEnding Date

[Vergy39]

Thanks PNGBill, I am going to get in touch with the auditors that works this report via excel and find out why the descrepency. I did the same thing you did and the result seem to be correct when you sort by weekending date. I think the problem is with the report that I am trying to compare it to.

Thanks, any you have been a great help.

David V.

 

[PNGBill]

Glad it was GIGO rather then my in experience in not knowing how to resolve it.

Keep using this thread if you still need help when the data issue is resolved.

 

[Vergy39]

OK, Looks like the number pending for True Pres issues for Weekending 8/7/2010 was 30. I did this query:

SELECT TblIssues.IssueType, Count(*) AS [#Pending]
FROM TblIssues
GROUP BY TblIssues.IssueType, TblIssues.IssueDate, TblIssues.CompletedDate, TblIssues.BBB
HAVING (((TblIssues.IssueType)=1) AND ((TblIssues.IssueDate)<=#8/7/2010#) AND ((TblIssues.CompletedDate)>=#8/7/2010#) AND ((TblIssues.BBB)=False))
WITH OWNERACCESS OPTION;

And I get 30 issues. I then created another query using this query as the source to sum it up to just one nunmber:

SELECT Qry_PendingTruePresTest.IssueType, Sum(Qry_PendingTruePresTest.[#Pending]) AS [SumOf#Pending]
FROM Qry_PendingTruePresTest
GROUP BY Qry_PendingTruePresTest.IssueType
WITH OWNERACCESS OPTION;

This gives me one result with a date. Now the issue is how do I get it into the report. Someone on this thread or another stated I possibly could write an expression on the report that will bring this result instead of with queries. You have been great, so I hope you can still assist. The queries created above are on the zip file attached to this response.

Thanks
David V.

 

[PNGBill]

Have you tried just pulling the new query (2nd one with group result) as part of the main query that your report uses and just have the result as a field in that query.
just like a query often has a number of Tables and Queries that it uses, add this query.

Another way is to make the query as part of a Public Function ( new can of worms but great if you can learn)

The public function can then either be used in your query for the report or even as the Record source on the Control of your Report - like you would use DLookup which is just a Function.

 

[Vergy39]

My original plan was to include the query on the main query that pulled all information. Problem with that though is that the received and completed information is pulled by using a start date and ending date. The pending and oldest date queries are pulled by weekending date. So basically, I am trying to see how I can get the weekending date into the Pending and oldest date queries. I use a datepicker for the Received and completed. I am going to play around with making another date picker for the weekending date.

Thanks
David V

 

[vbaInet]

I tried skimming through the posts but got confused At this stage, what are you actually trying to do? Get the Weekending date for the Oldest Date field?

 

[Vergy39]

Good Afternoon, I appreciate your taking the time to look at this thread. I have a report that is to state the numbr of issues received, completed, pending, and oldest date by Weekending date. For example, using August. For week 1, I would use start date 8/1 and end date 8/7. Week 2 would have start date 8/1 and end date of 8/14. This would show results for both weeks on separate lines. Week 3 would use start date 8/1 and end date 8/21. This would show results for 3 weeks on separate lines. ETC. Since the # pending and the oldest dates will change each week depending on what is received and completed, I need to show what was pending for each issue type as of 8/7, 8/14, 8/21, etc. Any assistance is greatly appreciated.

Thanks
David V

 

[vbaInet]

Not completely got the full picture but this is how you calculate the week ending for a date:

dateadd("d", (7 - weekday([oldest date])), [oldest date])

Or

datevalue([oldest date]) + (7 - weekday([oldest date]))

This is assuming that week ending is Saturday.

 

[Vergy39]

I appreciate the example. I guess to throw out a challenge to the Access experts, I would give the following problem. Using the table called "TblIssues" in the sample database that is attached to this thread, can you write a query that will give me the number of True Pres Issues pending as of 8/7, 8/14, 8/21 and 8/28. Group the number pending by the week ending date. For example:

WeekEnding Date.........#Pending
8/7/2010.......................30
8/14/2010......................23
8/21/2010......................20
8/28/2010......................35

I would also need the oldest date of the pending issue. Basically using the verbiage that the issue was received prior to the weekending date, completed after the weekending date, or not completed at all. All examples are welcome.

Thanks
David V.

 

[vbaInet]

What is the name of the query it should be getting the data from? And how does it determine which week ending date to start from or is it solely on the week ending of Oldest Date?

 

[Vergy39]

What is the name of the query it should be getting the data from? And how does it determine which week ending date to start from or is it solely on the week ending of Oldest Date?

Good Morning, I have attached an updated version of the database. There are Queries for each issue type for pending. They are Qry_PendingTruePresTest, Qry_PendingBBBTest, Qry_PendingVerbalTest, and Qry_PendingCRLTest. It is set up to enter the weekending date to get the figures. For example 8/7/2010. The queries to sum these to a total number is Qry_PendingTruePresTestTest. It will ask for the weekending date. Then there is a qry for the items rcvd and completed by type. This query is called Qry_StandingReport. This is the query that I need to add the pending and oldest date queries to. The Qry_StandingReport is pulled by Weekending date, but you can enter more than one. For example, you can entere 8/1 to 8/7 to get weekending 8/7. Or you can enter 8/1 to 8/14 to get both 8/7 and 8/14. This is how the report is to be pulled. I will be working on this most of the day today and will check this thread frequently. I do appreciate any asisstance.

Thanks
David V