CODEWITHSUNDEEP
ms-access
viRg
viRg

Reputation: 115

MS Access Create Date Range based on Start Date and End Date

I have a macro which records employee absences using a dynamic calendar I created in a userform. When the employees choose their absence type, they can either enter a start date and end date (for absences longer than 1 day) or they can click on the calendar's cells to choose specific dates. This information is then sent to an Access database where the information is stored and used for generating reports.

My problem is that people find it easier to click on days which follow to apply their absences rather than manually typing in the start and end dates. I now see that the design could have been improved...

The effect this has on the database is there are many "redundant" records in the database. For those who are visual, here is an example what the database is now like and what it could be like:

Current way (bad way):

EmployeeName   AbsenceType   StartDate    EndDate
============   ===========   ==========   ==========
Employee1      Vacation      2018-09-24   2018-09-24
Employee1      Vacation      2018-09-25   2018-09-25
Employee1      Vacation      2018-09-26   2018-09-26
Employee1      Vacation      2018-09-27   2018-09-27
Employee1      Vacation      2018-09-28   2018-09-28

Hopeful way (better way):

EmployeeName   AbsenceType   StartDate    EndDate
============   ===========   ==========   ==========
Employee1      Vacation      2018-09-24   2018-09-28

Can anyone suggest how to go about doing this?

Thanks in advance.

Upvotes: 1

Views: 842

Answers (1)

Gustav
Gustav

Reputation: 55841

You can use a Cartesian query like this:

SELECT DISTINCT 
    [Tens]+[Ones] AS Factor, 
    10*Abs([Deca].[id] Mod 10) AS Tens, 
    Abs([Uno].[id] Mod 10) AS Ones
FROM 
    MSysObjects AS Uno, 
    MSysObjects AS Deca;

Save it as qdyFactor and create another query to generate the dates:

SELECT DISTINCT 
    PersonID, 
    DateAdd("d",[Factor],[Start Date]) AS [First Date],
    [End Date]
FROM 
    tblTable,
    qdyFactor
WHERE 
    qdyFactor.Factor Between 0 And DateDiff("d",[Start Date],[End Date]);

Upvotes: 2

Related Questions