UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Payment Due Dates, Access 2016    
 
   
Knuckles
post Feb 22 2018, 03:00 PM
Post#1



Posts: 829
Joined: 1-February 10
From: New Jersey


Good afternoon UA,

I have a table with 6 fields:

tblPolicyDates
pkPolicyDateID
fkCustomerID
fkPolicyID
PolicyEffectiveDate
PolicyExpirationDate
DayOfMonthDueDate


I have been asked to create a table that would display the customerID, policyID and every monthly due date
between the effective date and expiration date based on the day of the month the payment is due.

I have not been able to locate a solution. Any help would be greatly appreciated.

Thanks in advance.

Knuckles

Actually, since every policy # will be unique the table would look more like this:

tblPolicies
fkPolicyID
PolicyNumber
PolicyEffectiveDate
PolicyExpirationDate
DayOfMonthDueDate

This post has been edited by Knuckles: Feb 22 2018, 03:09 PM
Attached File(s)
Attached File  PolicyDates.zip ( 25.53K )Number of downloads: 11
 
Go to the top of the page
 
theDBguy
post Feb 22 2018, 03:10 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,276
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Have you tried using a Cartesian Query approach?
Go to the top of the page
 
Knuckles
post Feb 22 2018, 03:30 PM
Post#3



Posts: 829
Joined: 1-February 10
From: New Jersey



Hi DBGuy,

I totally forgot about that. I will take a look and see if I can figure it out.

Thanks, I will let you know.

K
Go to the top of the page
 
MadPiet
post Feb 22 2018, 05:30 PM
Post#4



Posts: 2,494
Joined: 27-February 09



Like DBGuy said... I would use a Calendar table and use that to do my Cartesian Product. (Well, either a Calendar table or a numbers table...) Jeff Moden has a great article on what "Numbers" or "Tally tables" are. He's talking about SQL Server, but you could do it with any database.
Go to the top of the page
 
Larry Larsen
post Feb 26 2018, 01:48 PM
Post#5


UA Editor + Utterly Certified
Posts: 24,171
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi..
Intresting.. any luck..??
thumbup.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 04:17 AM