Adding Sequential Dates to table 2

[puforee]

Good afternoon and Happy New Year to everyone.

I must have cleared my memory over the holidays...I knew how to do this at one point.....but....

I have a table of sequential dates....I want to add more sequential dates to the table starting the day after the current MAX date in the table.

So, I would like to add, let's say, 700 sequential dates to the table after the last date currently in the table (12/31/2010).

Any help out there please?

Thanks,

 

[dhookom]

Can you share your significant table and field names?

Do you already have at least 700 records in the table with sequential date values?



Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Public Sub addSeqDates(dateFldName As String, tblName As String, recordsToAdd As Integer)
  Dim maxDate As Variant
  Dim strSql As String
  Dim i As Integer
  maxDate = DMax(dateFldName, tblName)
  If Not IsNull(maxDate) Then
   For i = 1 To recordsToAdd
     strSql = "Insert into " & tblName & "(" & dateFldName & ") values (#" & Format(maxDate + i, "mm/DD/YYYY") & "#)"
     Debug.Print strSql
     CurrentDb.Execute strSql
   Next i
  End If
End Sub

how to use

Public Sub testAdd()
  addSeqDates "receiptDate", "tblData", 50
End Sub

 

[puforee]

MajP...and again you come through. Would I run the code from a control on a form somewhere or would I run it from the VBA code?

dhookom. Table name is "Dates tbl", field in table (only 1) "Date". Currently 1349 dates (records)ending at 12/31/2010.

I will let you know how this turns out....probably tomorrow.

Thanks,

 

[MajP]

You can just drop it in a standard module and run from the vba window.

Public Sub testAdd()
addSeqDates "yourField", "yourTable", 700
End Sub

you can even run in the immediate window by typing this and then hitting return

addSeqDates "yourField", "yourTable", 700

If you need an interface for users then you could run from a form and grab the number to add from a control.

 

[MichaelRed]

somewhat off the thread, but curiosity compells - WHY would you want / need a table with ONLY all the dates?

MichaelRed

 

[PWise]

MichaelRed:
Who ever said that the table is only dates? It could be a table with dates and information about the date. I use my date table like this

Select emptable.empid Calander.dayofyear,hw.Hoursworked
From emptable
inner join Calander
on Calander.dayofyear Between emptable.Start and emptable.enddate
left join (Select Hoursworked.empid,Dateofwork, Sum(Hours) As Hoursworked
           from Hoursworked
           Group by empid,Dateofwork)hw
on Hoursworked.empid=emptable.empid
and hw.Dateofwork=Calander.dayofyear

this gives me a listing of all days and hours worked even days emid did not work can this be done with out an Calander table

 

[PWise]

on Calander.dayofyear Between emptable.Start and emptable.enddate

Not sure if between workes in Jet quries

but you can use

on Calander.dayofyear>=Startdate
and Calander.dayofyear <=Enddate

 

[MichaelRed]

You are correct.

But, only a date field is addressed. The process adds a record (date) for each member of 'recordstoadd'. I do not uderstand the rationale to add records (dates) which may not have other unique (and meaningful) fields to a table.

Hence my inquiry.



MichaelRed

 

[PWise]

if puforee only uses it for the porpus as i explaned there is no need for other fields
in my date table i have othe information about the date but when i create the dates all i put in is the date afterward i add othe information like holdays date office closed that is determed and entered by mangement and on a later date

the way i enter dates to my table is i have a digits table
1 field digitid form 1 to 999999

Insert into YearlyCalendar (dayofyear)
Select Dateadd(d,digitid,max(dayofyear))
from YearlyCalendar ,digits 
group by digitid
having Dateadd(d,digitid,max(dayofyear))<=max(dayofyear)+700
order by 1

 

[MichaelRed]

I still would like to know / uderstand this. To what point / advantage is the generation / storage of 'information' which has no relevance to the data?

I'm not trying to change your (or anyone's) methods, just ynderstand. I could learn something about data structures, their relevance or even psychology.



MichaelRed

 

[dhookom]

I have used a date table in applications. For instance in you have a table of patient visits with a admit and discharge dates. You can use the table of dates with the visit table to get the number of patients at any given date or range of dates.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

Obviously you know much more than I. What is the purpose of the dates table in the situation you describe? I would think the patient visits would need to include the date of the visit, so it seems like it shoould be possible to extract the number of patients who visited on any given date or renge of dates? So, again just to understand, why is the dates table necessary or even useful?




MichaelRed

 

[MajP]

So if the hospital wants a log for a month of who was checked in for each day (this is pretty common data request in my opinion).

1/1/2011
Patient A
Patient B
Patient C
1/2/2011
Patient A
Patient C
Patient D
....
1/31/2011
Patient A
Patient G

Without doing what Duane is suggesting, I think I would have to do this in code or make 31 union queries. Could this be done in Sql easily withou doing what Duane is suggesting? I cannot think of an easy way.

So I too often do similar tricks to assist in insert queries or other queries. To compound the above if the question was the same for employees, and show only the workdays (no weekend and holidays). If the date table identifies weekends and holidays this would be very simple.

 

[dhookom]

OK, here is an actual query I created to chart how many employees are out of the office on any given date.
[tt][blue]
myTable (sorry about the name)
============
EmpID Numeric
FirstDayOff Date
ReturningOn Date
[/blue][/tt]
[tt][blue]
tblDates
============
TheDate Date
[/blue][/tt]

Query.qcarEmpDaysOff

SELECT myTable.EmpID, myTable.FirstDayOff, myTable.ReturningOn, tblDates.TheDate
FROM myTable, tblDates
WHERE (((tblDates.TheDate) Between [FirstDayOff] And [ReturningOn]))
ORDER BY myTable.EmpID, tblDates.TheDate;

The query to get a list of days and the number of employees off

SELECT qcarEmpDaysOff.TheDate, Count(qcarEmpDaysOff.EmpID) AS CountOfEmpID
FROM qcarEmpDaysOff
GROUP BY qcarEmpDaysOff.TheDate;

or if you wanted to display all dates

SELECT tblDates.TheDate, Count(qcarEmpDaysOff.EmpID) AS CountOfEmpID
FROM qcarEmpDaysOff RIGHT JOIN tblDates ON qcarEmpDaysOff.TheDate = tblDates.TheDate
GROUP BY tblDates.TheDate;

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

? If the date table identifies weekends and holidays ...

Still doesn't seem particularly difficult from down here.

I added a few fields which might be used in a medical facillity (including NAMES fields - which might be seperated for 'HIPPA' rules and just linked by Patient ID).

Since the sample data did'nt provide any 'Employees' I abreviated the process to use the sample patients data just seperating the records by the weekday.

Here are the samples.


The Table modeled after MajPs'

PatientL_Name PatientF_Name PatientId VisitDate VisitType
A Patient 123456789 1/1/2011 6852
A Patient 123456789 1/31/2011 9951
A Patient 123456789 1/2/2011 6656
G Patient 159753640 1/31/2011 4452
B Patient 234567891 1/1/2011 3811
C Patient 345678912 1/1/2011 7534
C Patient 345678912 1/2/2011 7534
D Patient 456789123 1/2/2011 3574

qryWorkDayVisits(WorkDayOnly records)

SELECT tblPatientVisits.VisitDate, tblPatientVisits.PatientId
FROM tblPatientVisits
WHERE (((tblPatientVisits.VisitDate) Between #1/1/2011# And #1/31/2011# And Weekday([VisitDate])<>7 And Weekday([VisitDate])<>1))
ORDER BY tblPatientVisits.VisitDate;

and the results

VisitDate	PatientId
1/31/2011	159753640
1/31/2011	123456789

qryVisitsForPeriod (All Recoerds)

SELECT tblPatientVisits.VisitDate, tblPatientVisits.PatientId
FROM tblPatientVisits
WHERE (((tblPatientVisits.VisitDate) Between #1/1/2011# And #1/31/2011#))
ORDER BY tblPatientVisits.VisitDate;

and the results

VisitDate	PatientId
1/1/2011	345678912
1/1/2011	234567891
1/1/2011	123456789
1/2/2011	123456789
1/2/2011	456789123
1/2/2011	345678912
1/31/2011	159753640
1/31/2011	123456789

MichaelRed

 

[dhookom]

MichaelRed,
A typical hospital report is a patient census that shows the number of patients each day.

How would you count/show the number of patients per day if you had an AdmitDate and DischargeDate rather than a VisitDate? A single record would then potentially represent multiple patientdays.

This scenario could also be used for hotel stays where you have CheckInDate and CheckOutDate. Also, as I have mentioned employee staffing. Having a table of all dates makes a pure SQL solution very efficient and easy.


Duane
Hook'D on Access
MS Access MVP

 

[MajP]

guess i was not clear, or I was assuming from some from the other posts.

I thought we were talking about periods. Patient A checks in on 1/1/2011 and checks out on 1/15/2011. Similiar to what Duane shows in his vacation start and return date
checkInDate
checkOutDate

Your example is simple because there is a record for each day.

 

[MichaelRed]

O.K. I quit.

You can obviouosly provide scenarios where I have not solved your question(s). I responed to the scenarios posted based on the sample data. The issues you now raise do not fit into the (aparent) structure of the data so you can continue till the hot places freeze over and get buried in ice thicker than my crust.

I concede that you and others participating in this thread find use in the table of dates. I my brief experience, I have provided reports based on the generic concepts of dates and date ranges without the use of tables of enumeration of single continious values.



MichaelRed

 

[dhookom]

MichaelRed,
I also have a table of numbers (tables of enumeration of single continious values) as does PWise ;-)

Duane
Hook'D on Access
MS Access MVP