Solved Create list of forward payments (1 Viewer)

[Kayleigh]

I am planning a DB to manage payouts to various companies on specific day of the month. I have figured out the following structure - a staff table; a junction table recording staff/company/payment info (including day of month); a table recording transactions info.
The process is that each staff is assigned companies to payout every month and the payments should be scheduled ie. created in the transaction table with status 'scheduled' and once processed it moves status and finally it is invoiced.
I would like to write a query to show upcoming payments from the junction table. It should take day of month, combined with this month and year to create date and then can be filtered according to which are in transactions table (meaning it has been created).
Can anyone help me with writing this query please.

 

[Pat Hartman]

1. Do not create future payments. Create only the NEXT payment at the time it is scheduled to be paid if you want to do this as two steps rather than one.
2. There must be some payment stop date which will be the date of the last payment. If there is no fixed stop date, then this date can be null or an arbitrary fixed date tin the future if you prefer.
3. On the form, create four unbound controls. Populate year and month with today's year and month. The user will enter the day or select from a combo. The fourth control should be defined as a date format. It will be populated by combining the other three columns into a date in the click event of your run button.

Me.txtScheduledDate = CDate(Me.txtMonth & "/" & Me.txtDay & "/" & Me.txtYear)


The query would then be:

Select ...
From ...
Where ScheduledDate = Forms!YourForm!txtScheduledDate;

 

[Kayleigh]

I do not wish to create payments until they are approved for processing. So I would like a query to show the upcoming possible payments. Then it should be possible to select which payments to actually create. Once created the payment can go through the processing stages.
The only field I have to show upcoming payments is day of month payment to be taken. Do I then put in Month and year based on current date? And how would I filter out the ones which have been created already?

 

[Auntiejack56]

So, correct me if I am wrong, but the junction table is for holding the information that says "Staff member Suzie is looking after Acme Co, and their payout date is the 15th of the month."
Here are the made-up names I've given to your tables:

As you can see, I've put 2 transactions into the transaction table, to test whether the query picks them up or not (it doesn't).
You'll need 2 queries:

SELECT tblJunction.StaffMember, tblJunction.CompanyName, DateSerial(Year(Date()),Month(Date()),[DayOfMonth]) AS PayoutDate, tblJunction.DayOfMonth
FROM tblJunction;

call that one qryJunctionDates. Then use it in the next one:

SELECT qryJunctionDates.StaffMember, qryJunctionDates.PayoutDate, qryJunctionDates.DayOfMonth, tblTransactions.ID, qryJunctionDates.DayOfMonth
FROM qryJunctionDates LEFT JOIN tblTransactions ON (qryJunctionDates.PayoutDate = tblTransactions.PayoutDate) AND (qryJunctionDates.CompanyName = tblTransactions.CompanyName) AND (qryJunctionDates.StaffMember = tblTransactions.StaffMember)
WHERE (((tblTransactions.ID) Is Null) AND ((qryJunctionDates.DayOfMonth)=[Enter Day of Month]));

call it say qryPayoutDates. When you run qryPayoutDates it will prompt you for the day of the month that you are interested in, and show you the records in the junction table that have no scheduled payment in the Transaction table that matches that day of the current month.
Jack

 

[Kayleigh]

This is EXACTLY what I was asking for!
Thank you

 

[arnelgp]

or you can just use a form to check which month/year.