Oldest Date By WeekEnding Date

[Vergy39]

I am trying to write a query that will provide the oldest date by weekending Date. I have a report that goes out weekly that lists the #received, #Completed, #Pending, and oldest date. If I only do one week, it is easy. But once I add the 2nd, 3rd, and 4th week, it becomes difficult. Attached is an example of the what the report looks like in excel. I want to do this in access. I have the query that will calculate the #Received and Completed. I attached the sample database as well. The qureries are called Qry_StandingReportTruePres, Qry_StandingReportBBB, Qry_StandingReportVerbals, and Qry_StandingReportCRLs. The Pending and oldest date queries I have are Qry_PendingTruePres by Weekending Date. Any assistance is greatly appreciated.

Thanks
David V

 

[PNGBill]

If your result is a Report then allow the report to do some of this work for you.

Your query can select all records and include a field called WeekNum

Have this populated with the week number only ie 1 to 52.

Use your report sorting and grouping to group on week number and order by date ascending (default) or desc.

The report will allow you to have weekly totals and overall totals

trust this assists

 

[PNGBill]

This link may assist in getting the week number
http://bytes.com/topic/access/answers/556060-week-number-calculations

 

[Vergy39]

Thanks PNGBill. I was wondering if you could be so kind and provide an example. I checked out the link you attached, but found it confusing. I am going to google WeekNum to see if I can find some more information about that. Also, not sure how the reporting part can help me with this issue. I am somewhat of a novice.

Thanks again.
David V.

 

[PNGBill]

I will try and do an sql example of week number and post.

Regarding how a report can help...

If you are using a Report to publish your result then you can reduce some of the tasks your query does as Report can easily handle Sorting and Grouping.

Say your query produces a recordset of all the member of your family showing Name, DOB, Sex (you have a big family) and you want a report that list all family members but has males on one page and females on another and they are grouped by the Month they were born in and sorted by First Name ascending.

You just add one more field to your query, MonthBorn and the report will do the rest.

Sorting and Grouping property on your report will allow you to have the data grouped by Sex, with new page for new section.
It will then sub group by Month Born and withing the subgroup, list the members be either ascending order.

If the data was Sales Figures, the report can provide subgroup totals, group totals and overall totals as well as a Running total (handy for Statements and Invoices)

 

[PNGBill]

This sql works

SELECT TblDates.TbleDateID, TblDates.DateField, DatePart("ww",[DateField]) AS WeekNum
FROM TblDates;

Table Name - TblDates
Field Name - DateField - this is in the table

Added an extra field to the query called WeekNum and for this we use Datepart("ww", fieldname)
In this example it is Datepart("ww",DateField)

In my sample data the result gave a 1, 2,or 3 for the records produced as I used dates ranging from 1st Jan to 16th Jan ie first 3 weeks of the year.

You would then get your Report to Group on the WeekNum field and Wala, you have your data grouped even though in the query it may be all over the place.

 

[Vergy39]

Thanks for the info PNGBill, I somewhat get what you are trying to do. I was able to create a query that returns the week number for each issue based on the Issue date. My problem though, is that the pending number changes daily, new issues are received and issues are closed. Right now, the report is pulling the live pending so the number pending is the same number for each weekending date. I basicaly need the query to list number pending on that particular weekending date. right now I have this formula, but it is not calculating the correct number.

Pending: Sum(IIf([IssueDate]<=[WeekEnding],IIf([CompletedDate]>
[WeekEnding] Or [CompletedDate] Is Null,1,0),0))

And here is the SQL view of one of the queires:

SELECT [IssueDate]-Weekday([IssueDate])+7 AS WeekEnding, Sum(IIf([IssueDate]<=[WeekEnding],IIf([CompletedDate]>[WeekEnding] Or [CompletedDate] Is Null,1,0),0)) AS Pending
FROM TblIssues
GROUP BY [IssueDate]-Weekday([IssueDate])+7, TblIssues.IssueType
HAVING (((TblIssues.IssueType)=2));

Again, thanks for you assistance.

David V.

 

[PNGBill]

Can you list some example data and explain why it is not what you expected.

Remember that reports and forms can do sorting and filtering and Reports also have the Grouping facility which can take some of the work from your sql.

 

[Vergy39]

I appreciate your patience with me. I attched a copy of the database to this thread. If you can open that, then refer to the queires. The queries are called Qry_PendingTruePres by Weekending Date, Qry_PendingBBB by Weekending Date, Qry_PendingVerbals by Weekending Date. and Qry_Pending CRLS by Weekending date. If you these you will see the count by weekending. I am using 8/7 as a reference date. If you open the True pres and BB queries, you will see that it shows 15 and 22 respectively. If you open the sample report (also attached) you will see that we had 43 True pres pending and 23 BBB's pending. I think wht is happening is that the issues closed after 8/7 are no longer being counted.

Again, thanks.

David V.

 

[PNGBill]

Sorry David, but right now I am unable to download and open.

Have you done some test queries to confirm your suspicion ?

Some times it is just a matter of testing different queries to identify what field is not responding the way you expect and then you have an exact question on how to resolve the issue.

Also, what appears easy to understand data to you doesn't make sence to some others not familiar with your business. More experienced support may not have this problem.

You have a measurable problem. Query should return 43 records but only returns 15.

Maybe you have a criteria on one field that isn't required.

The use of And / Or in queries can also be a little confusing.

Also, using queries as the data source for a 2nd query may simplify things until you have the experience to have one query doing the complete task.

Unions queries may also be an option.
This will allow you to "append" query data eg you want all the records that ended in week 6 but also the records that started in week 6 and ended in week 7.
A union query will allow this to be done.

Sorry not able to download and give you a final solution.
btw, I use 2000. what is your version?

 

[Vergy39]

Sorry David, but right now I am unable to download and open.

Have you done some test queries to confirm your suspicion ?

Some times it is just a matter of testing different queries to identify what field is not responding the way you expect and then you have an exact question on how to resolve the issue.

Also, what appears easy to understand data to you doesn't make sence to some others not familiar with your business. More experienced support may not have this problem.

You have a measurable problem. Query should return 43 records but only returns 15.

Maybe you have a criteria on one field that isn't required.

The use of And / Or in queries can also be a little confusing.

Also, using queries as the data source for a 2nd query may simplify things until you have the experience to have one query doing the complete task.

Unions queries may also be an option.
This will allow you to "append" query data eg you want all the records that ended in week 6 but also the records that started in week 6 and ended in week 7.
A union query will allow this to be done.

Sorry not able to download and give you a final solution.
btw, I use 2000. what is your version?

We just updated to 2010 a few weeks ago. We had 2000 until then. I stil have 2000 on my pc, so I will try to save a copy in that version for you.

Thanks
David V

 

[Vergy39]

Try this version. Hope you can open this one.

Thanks
David V.

 

[PNGBill]

Thanks David,

Downloaded and it opens.

9am here and I will be out for an hour or two but will have a look when back.

 

[PNGBill]

I should have looked at your Excel Report you are trying to emulate.

This would be quite difficult in Access to produce a report like this.

You have what appears four separate sets of Data. True, BBB & Verbal x 2.

The excel report looks like a Crosstab query result or a Pivot Table.
I use Crosstab occasionally so not that sure on if it can handle such.

My first thoughts are to use a number of queries to select the data and append same to a Temporary Table which can store them in the different view ie use a field name to be the column header in your report.

Your excel report shows 23/07/2010 to be the Oldest Date for WE 21st Aug and WE 28th Aug.
How is this correct?

 

[PNGBill]

Another Issue you should consider is to not have spaces in your object names.
I see Qry_PendingBBB by Weekending
This would be better written QryPendingBBBByWeekending - if you must have such a long name.

The actual Qry you refer to doesn't exist. The best match I can see Qry_PendingBBB by Weekending where as you include Date on the end of the name.
Minor issue with pencil and paper but can be a big issue in a database as the two object names do not match.

I see 43 records when I open Qry_PendingBBB by Weekending
You appear to state you see 22 records??

Rather then me spend some time trying to understand the full picture can we isolate one issue with say one query and if that can be resolved it may show the way forward.

Give me a Query name tell where it doesn't return the records you expect.

 

[Vergy39]

Thanks. I will clean it up a bit and put another sample on. Too many fires to put out today to work on it. I will try again tomorrow. It is 4:25pm here and time to go home. I am in Arizona. Where are you?

Thanks
David V

 

[PNGBill]

Port Moresby, Papua New Guinea - Guam Time.

If you can give a specific example of a record in a table that should or shouldn't be in the query result.

 

[Vergy39]

OK, PNGBill, hope you have not forgotten me. Sorry for the delay in responding. Was playing firefighter last several days. Attached is another copy of the database. If you look at the Query named Qry_PendingTruePres by Weekending. This query will pull the number pending by weekending date so there are several rows. The date I am using for testing purposes is Weekending Date 8/7. On 8/7, it should show 43 pending, but it only shows 15. The formulaending: Sum(IIf([IssueDate]<=[WeekEnding],IIf([CompletedDate]>[WeekEnding] Or [CompletedDate] Is Null,1,0),0)) should return the correct number because it is asking for issues that were received less than the weekending date and issues that were completed after the weekending date. For example, I received 8 issues on 8/5 and completed 3 on 8/6. So for weekending 8/7 I would have 5 pending. Then for week ending 8/14 I wil have received more issues and closed some as well as close issues received weekending 8/7. I need the report to still show that I have 5 pending for the weekending 8/7. Hope this helps.

 

[PNGBill]

File rec'd David and opens fine.
Yes I get 15 for the same weekended date.

I will study hopefully find a solution.

 

[PNGBill]

Sorry David but I still seem to have a problem understanding your data.

Tblissues clearly shows only two True Presidential were completed 8/6 not 3 as you indicate. Or am I missing something?

When I query TblIssues with this

SELECT [IssueDate]-Weekday([IssueDate])+7 AS WeekEnding, TblIssues.IssueDate AS Pending, TblIssues.CompletedDate, TblIssues.IssueType, TblIssues.BBB
FROM TblIssues
WHERE ((([IssueDate]-Weekday([IssueDate])+7)=#8/7/2010#) AND ((TblIssues.IssueType)=1) AND ((TblIssues.BBB)=No));

I get 16 records that have an IssueDate for weekending 7th Aug
5 2nd Aug, 3 3rd Aug, 5 5th Aug and 3 6th Aug.
Of these, only 1 was completed that week giving 15 to carry over.

This I guess is where your 15 comes from.

When I use this sql

SELECT TblIssues.IssueID, [CompletedDate]-Weekday([CompletedDate])+7 AS WeekEnding, TblIssues.IssueDate AS Pending, TblIssues.CompletedDate, TblIssues.IssueType, TblIssues.BBB
FROM TblIssues
WHERE ((([CompletedDate]-Weekday([CompletedDate])+7)=#8/7/2010#) AND ((TblIssues.IssueType)=1) AND ((TblIssues.BBB)=No))
ORDER BY TblIssues.IssueID;

I get 19 records completed WE 7th Aug of which one was issued that week leaving 18 new records to be returned.
15 plus 18 gives you 33, still not your 43 you expected.

Does this assist you resolve the issue? or can you enlighten me where I am going wrong above.

For this example I don't think Null is an issue so have not allowed for yet.