By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,565 Members | 907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,565 IT Pros & Developers. It's quick & easy.

Help with recurring monthly payments and First time fee

P: 14
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....
May 31 '07 #1
Share this Question
Share on Google+
5 Replies

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jun 1 '07 #2

P: 14
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
Expand|Select|Wrap|Line Numbers
  1. MemberID - autonumber (PK)
  2. First name
  3. last name
  4. unit number
  5. unit type
  6. home addy
  7. city
  8. state
  9. zip code
  10. home phone
  11. email
  12. birth date
  13. date joined
  14. cell phone
  15. member dues
Member Types Table
Expand|Select|Wrap|Line Numbers
  1. Member Type ID (4 fields) (PK)
  2. Unit Type ( 5x10,10x10,10x15,10x20) 
  3. Member Dues ($40, $70, $90, $110)
Payment Method Table
Expand|Select|Wrap|Line Numbers
  1. Payment Method ID (PK)
  2. Payment Method
Payments Table
Expand|Select|Wrap|Line Numbers
  1. Payment ID (PK)
  2. Member ID
  3. Payment Amount
  4. Payment Date
  5. CC#
  6. Cardholders Name
  7. Card Exp. Date
  8. CC Author#
  9. 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
Jun 1 '07 #3

P: 14
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.
Jun 1 '07 #4

P: 14
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."
Jun 2 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Jun 4 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.