Help with recurring monthly payments and First time fee

[slavisa]

Hello,

I have a database for rental units and i have two problems that i cannot solve. I have a table with 4 different options of units with different prices. what i would like to do is for the first time customers there is 1 time fee, how would i be able to charge them only 1 time and no charge after that.

My second question would be i have a payment form also and lets say a person joined today and has to pay 40 for monthly rental and 20$(the 1 time payments) which comes to 60$, and the person pays it in full so the balance is 0$, how would i set it up that a month from now, the 0$ will turn again to 40$ so the person has to pay again?

And also would i be able to set up a late charge if they dont pay on the day that they are supposed to?

Thanks for all the help....

[MMcCarthy]

Hello,

I have a database for rental units and i have two problems that i cannot solve. I have a table with 4 different options of units with different prices. what i would like to do is for the first time customers there is 1 time fee, how would i be able to charge them only 1 time and no charge after that.

My second question would be i have a payment form also and lets say a person joined today and has to pay 40 for monthly rental and 20$(the 1 time payments) which comes to 60$, and the person pays it in full so the balance is 0$, how would i set it up that a month from now, the 0$ will turn again to 40$ so the person has to pay again?

And also would i be able to set up a late charge if they dont pay on the day that they are supposed to?

Thanks for all the help....

Hi slavisa

I would need a lot more information on how this database is structured and how your tables are set up. Have a look at this tutorial and see how you should structure your tables. Ask any questions if there is anything you don't understand.

Database Normalisation and Table structures

Mary

[slavisa]

i have couple of questions where i got stuck during the creation of the appended query. I created the query but i dont know which fields should i add to it since there are 4 different accounts in the database which charge different fee.

As for the one time fee, i would like it to be charged as soon as the account is opened. I tried doing it where after the Date joined is entered there is an event to add 25 one time fee to the amount due using the vb code but i could not get it to work.

I included what tables and fields i have and their relationships below for you so you can check and see if you can give me other great inputs. Thanks a lot

Members Table

MemberID - autonumber (PK)
First name
last name
unit number
unit type
home addy
city
state
zip code
home phone
email
birth date
date joined
cell phone
member dues

Member Types Table

Member Type ID (4 fields) (PK)
Unit Type ( 5x10,10x10,10x15,10x20) 
Member Dues ($40, $70, $90, $110)

Payment Method Table

Payment Method ID (PK)
Payment Method

Payments Table

Payment ID (PK)
Member ID
Payment Amount
Payment Date
CC#
Cardholders Name
Card Exp. Date
CC Author#
Payment Method

[Member] MemberID - [Payments] MemberID ------> ONE TO MANY

[MemberTypes]MemberTypeID - [Member]MemberTypeID

[Payments]PaymentTypeID - [PaymentType]PaymentMethodID



Sum of Payments Query

Member Types Form
Members Form
Payment Methods Form
Payments Form

5 Reports also

[slavisa]

Ok i figured out the one time charge fee since i was familiar with VB. What i did was i created an even on the Date Joined filed where when the date is first inputed (And it will stay the same always) add 25$ one time fee to the member dues.

Now whats left is the Montly recurring payment and the late charge fee if thats possible to create.

[slavisa]

I have finished the late payment fee.

Would anyone be able to help me out with recurring monthly fee.

I know that i can do it in two ways as i was told but i dont know how and where to start. I create some of it but i dont know what to include in the appended query etc.

i was told that In Access Help, look for COMMAND LINE OPTIONS and you will find -X macro, which is a way you can run a given macro from a command line.

"Create an append query that adds a $40 charge to every open account. Put this query to run in a macro. Create a separate icon that is private to you so that only you know where it is. Edit the icon properties to launch access on that database file with the -x macroname appended. Now use Windows Task Scheduler to launch the icon at a specific time of day on the 10th of each month. Don't forget that the macro has to end with a QUIT action."

[MMcCarthy]

The problem here is with the Member types table.

Your primary key I understand is a composite of 4 fields. You should never do this.
What are the fields?
How does this relate to the Members table?
Why is Member Dues in both the Members and Members Types table?