Solved

Find missing datesw

Posted on 2013-02-03
8
171 Views
Last Modified: 2013-03-20
My Rental tbl contains Rental_ID (Number) and RentPeriod (Date)
Each Rental_ID should have 12 entries per year... one for each month RentPeriod of the year

Each RentPeriod is entered in this format: 1/1/2012

All the RentPeriod start on the same date: 1/1/2008
All the RentPeriod end on the same date: Date()

I need to loop through the table for each Rental_ID and insert the missing RentPeriod

Example:

Rental_ID    RentPeriod
...
      65                       1/1/2012
      65                       2/1/2012
      65                       4/1/2012

      67                       1/1/2012
      67                       2/1/2012
      67                       3/1/2012
      67                       5/1/2012

The loop would add  65      3/1/2012
                                 67      4/1/2012
0
Comment
Question by:Worcse
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38849002
Worcse,

You wrote:

All the RentPeriod start on the same date: 1/1/2008

However, you also indicate that for each of the two example Rental ID values, only a single record must be added.

So, which is it?  Do you need only to insert values between the min and max date for each Rental ID, or do you need to make sure that there are entries for each month going back to 1/1/2008?

Patrick
0
 

Author Comment

by:Worcse
ID: 38849033
matpat - thank you for your response.  Sorry I didn't explain myself throughly enough in my question.

For each Rental_ID I need to begin the loop at the start date of 1/1/2008 and stop at the end date of Date()

This means that as of today each Rental_ID should have 62 entries (one per month)

Any missing record should be inserted into the Rental tbl

~Worcse
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 300 total points
ID: 38849056
Create a table (tbl_Numbers), with one field (intNumber) and 12 values (the numbers 1-12)

You will use this table to generate your list of rental periods.

I'm assuming that you also have a table of Rentals, with the periods (startDate - EndDate) of their rental.  You don't indicate what year this is for, so lets just assume you want to generate a list of Rentals and RentPeriods from 1 Jan, 2008 through the current date.  That query might look like:

SELECT tblRentals.RentalID
           , DateSerial(2007 + RentYear.intNumber, RentMonth.intNumber, 1) as RentalPeriod
FROM tblRentals, tbl_Numbers as RentYear, tbl_Numbers_as RentMonth
WHERE DateSerial(2007 + RentYear.intNumber, RentMonth.intNumber, 1) BETWEEN
tblRentals.StartDate AND cdate(NZ(tblRentals.EndDate, Date()))

This query would give you a set of 12 rental periods per year for each rental, and would limit the list for a particular Rental to those dates the rental was active.  If you save this as qry_Rental_Periods, you could use a left join between this query and your RentalPayments (since you didn't tell us the names of your tables, this is an assumption) table to identify the missing Rental/Periods.

SELECT RentalID, RentalPeriod
FROM qry_Rental_Periods
LEFT JOIN RentalPayments
ON qry_Rental_Periods.RentalID = RentalPayments.RentalID
AND qry_Rental_Period.RentalPeriod = RentalPayments.RentalPeriod
WHERE RentalPayments.RentalPeriod IS NULL

This should give you a list of the missing values.  Then just modify this query to an append query and append the appropriate values to your RentalPayments table
0
 

Author Comment

by:Worcse
ID: 38849142
I will work on this and let you know if I am successful.
Hopefully I can get it done before Kick-off...!
~Worcse
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 38849191
You could also try running this code in a module:
    Dim rst As DAO.Recordset
    Dim CurrID As Integer
    Dim CurrPeriod As Date
    
    Set rst = CurrentDb.OpenRecordset("Select * From TableName Order By Rental_ID, RentPeriod")
    
    Do While Not rst.EOF
        If rst!Rental_ID < CurrID Then Exit Do
        
        CurrID = rst!Rental_ID
        CurrPeriod = #1/1/2008#
        
        Do While rst!Rental_ID = CurrID
            Do Until CurrPeriod = rst!RentPeriod
                rst.AddNew
                rst!Rental_ID = CurrID
                rst!RentPeriod = CurrPeriod
                rst.Update
                CurrPeriod = DateSerial(Year(CurrPeriod), Month(CurrPeriod) + 1, 1)
            Loop
            rst.MoveNext
            CurrPeriod = DateSerial(Year(CurrPeriod), Month(CurrPeriod) + 1, 1)
        Loop
        
        Do While CurrPeriod < Date
            rst.AddNew
            rst!Rental_ID = CurrID
            rst!RentPeriod = CurrPeriod
            rst.Update
            CurrPeriod = DateSerial(Year(CurrPeriod), Month(CurrPeriod) + 1, 1)
        Loop
    Loop

Open in new window

0
 

Author Closing Comment

by:Worcse
ID: 39005154
Thank you for your answer...!
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39005196
Just curious if you tried the code I gave you...
0
 

Author Comment

by:Worcse
ID: 39005208
IrogSinta - thank you also for your code... but I did not try running your code.
I worked with Fyed's first and was able to resolve the issue using that.
Regards,
Worcse
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now