Re: thread701-1524777- Query to get earliest date 1

[Dophia]

Hi again: I am trying to use the query to sort a report based on the week beginning date. I want a user to open the report and see which assessments are due to be done during the current week.

I referenced my query to a new one and used the field "TheLeast" to show me the week, but I don't want to see the week number, but the actual date of the week beginning.

Week: Format([TheLeast],"ww")

Any suggestions?

Sophia

 

[dhookom]

If you have a week number, you can generally use
TheDate: DateAdd("ww",[Some Number],[Some Date])

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Duane: I'm not sure what you mean by [Some Number].

Sophia

 

[Dophia]

Duane: When I use "1" in the SomeNumber field, I get a date that is 7 days in advance, when I use -1, I get a date that is 7 days earlier.

Sophia

 

[dhookom]

I believe in your query, this would be [TheLeast]. I assume this is a week number.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

It would really help if you typed in a few records with significant fields and then added the value/date you wanted to display.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

The previous thread looks like it is returning a string value that resembles a date. This thread makes me think TheLeast is a week number

the field "TheLeast" to show me the week, but I don't want to see the week number

I also cautioned against creating an expression that returned a string when I thought you might really want to return a date or null.

I think you now need to convert TheLeast back to a number or date or whatever. Formatting should be done in your report or form controls, not usually in your query.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

I have a table of health assessments for an animal shelter.

tblAssessment:
PS_No - links to specific animal in table -primary key
AssessID - autonumber - primary key
AssessDate - Date of assessment
AssessType - Eg. 1st, 2nd, 3rd assessment, etc.

Qry1: (Used to
AssessID - Date of assessment
WeekOneDate: Format(IIf([MaxDate]-[Arrival_Date]<7,([Arrival_Date]+7),"-"),"Mmm-dd-yy")
WeekSecondDate -etc

Qry2:Based on Query 1
TheLeast: Format(GetLeastDate(IIf([WeekOneDate]="-",0,[WeekOneDate]),IIf([WeekSecondDate]="-",0,[WeekSecondDate]),IIf([MthOneDate]="-",0,[MthOneDate]),IIf([MthlyDate]="-",0,[MthlyDate])),"Mmm-dd-yy")

So, Qry 2 shows when the next assessment is due. I want the report to show which assessments for which animals are due, sorted by week begining dates.
Thanks for any suggestions.
Sophia

 

[Dophia]

Duane: I used a string, since I wanted to show that the assessment was not required, since according to the formula, for WeekOneDate, for example, that assessment may have already been done.

Sophia

 

[dhookom]

I'm sorry I'm so dense but I don't understand how your assessment records get added to the table (at earliest or as they occur), what your function GetLeastDate() is for, what your objective is, what your actual data looks like, what are you storing in MaxDate and Arrival_Date, ...

Hopefully someone else isn't as confused.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Sorry for the confusion. I tried to condense the information so it wouldn't be information overload.

The animal assessments are entered into a table on the day that they are done, which is supposed to be as follows:
AssessType:
Admission-on arrival
First-7 days after arrival
Second-21 days after arrival etc.
MthOne- 30 days after the Second
Mthly -30 days thereafter

The tables would look like:
tblAnimal
AutoNo PS_No ArrivalDate Type etc
1 1000-x Jan-05-09 Cat
2 1001-x Jan-26-09 Dog

tblAssessment
AssessID PS_No AssessDate AssessType
1 1000-x Jan-05-09 Admission
2 1000-x Jan-12-09 First
3 1000-x Jan-26-09 Second
4 1001-x Jan 26-09 Admission


The above shows that 3 assessments were done for 1000-x and one assessment was done for 1001-x.

I want the query to tell me when the next assessment is due. So, I have the Expression:

WeekOneDate: Format(IIf([MaxDate]-[Arrival_Date]<7,([Arrival_Date]+7),"-"),"Mmm-dd-yy")

The MaxDate in the expression is the AssessDate grouped to find the Max date in the tblAssessment....which in 1000-x would be Jan -26-09 and 1001-x the same.

So, my query shows:

PS_No MaxDate WeekOneDate WeekSecondDate
1000-x Jan-26-09 - -
1001-x Jan-26-09 Feb-03-09 Feb-17-09

My next query is based on the above query:
PS_No WeekOneDate WeekSecondDate TheLeast
1000-x - - Dec-30-99 (=0 date)
1001-x Feb-03-09 Feb-17-09 Feb-03-09

TheLeast which contains "GetLeastDate" is from a public function:

Public Function GetLeastDate(WeekOneDate As Date, WeekSecondDate As Date, _
MthOneDate As Date, MthlyDate As Date) As Date

Dim dteMaxDate As Date
dteMaxDate = MthlyDate
If WeekOneDate = "0" Then dteMaxDate = WeekSecondDate
If WeekSecondDate = "0" Then dteMaxDate = MthOneDate
If MthOneDate = "0" Then dteMaxDate = MthlyDate

If MthOneDate <> "0" And MthOneDate < dteMaxDate Then dteMaxDate = MthOneDate
If WeekSecondDate <> "0" And WeekSecondDate < dteMaxDate Then dteMaxDate = WeekSecondDate
If WeekOneDate <> "0" And WeekOneDate < dteMaxDate Then dteMaxDate = WeekOneDate


GetLeastDate = dteMaxDate
End Function


Now I want to create a report that will show me TheLeast field, but sorted by week. So, we can see which assessments are due for which animal in the upcoming week.
The example shows two animals, but there may be over 100 animals in the shelter at any one time. So, I don't want someone to have to scan all the dates to see which ones are due in the upcoming week.

I hope you can still help. If not, that's okay. I might have to think of something else to do.

Sophia

 

[Dophia]

Oops! In the above, "Dec-30-99" should be under TheLeast column and not the WeekSecondDate column. Dec-30-99 is the result of a "0" date. I still have this to fix in my query calculations.

Sophia

 

[dhookom]

I would just append all of the assessment records to tblAssessment when the animal record is created or the first assessment is created. I would probably change the dates in tblAssessment to be:

[tt][blue]
ScheduledDate (populated with first append)
ActualDate (populated at time of assessment)
Status (use for whatever like "active or cancelled"
[/blue][/tt]

You could then have all of the potential dates in your assessment table so you would know the level of business in the future.

When the assessment actually occurs, you could find the difference between the ScheduledDate and ActualDate to run an update query to adjust future dates for the animal.

You can then filter for whatever date range you want. A crosstab query would display all dates in a grid and finding the max would be easy.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Duane: That sounds great! I wondered what I could do to take into account the date of an assessment that was late.

But, what do you mean by:
I would just append all of the assessment records to tblAssessment when the animal record is created or the first assessment is created

The assessment records are in the Assessment table already.

Sophia

 

[dhookom]

You stated

The animal assessments are entered into a table on the day that they are done

That suggests the assessment records are entered individually at the time of the assessment. My suggestion would be to append 3-4 records at the initial assessment.

Do you have a numeric "DaysFromInitial" field in your AssessType table? If not, I would add one.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Duane: Sorry to bother you again, but would it make sense to append all assessment types to the animal on admission when some only have one done, and some may be there for a year and have many done? I'm not sure I understand why you would add them all up front?

Also, how would you do that?

Sophia

 

[dhookom]

I would consider allowing users of the system to append the future dates unless you can tell me why this wouldn't be a good idea.

How I would do this depends on answers to 1 or more questions I have asked previously.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Duane: Thanks for your suggestions and help. It's greatly appreciated.

Sophis