If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Clients and multiple accounts
I'm building a database for my personal training business, and currently I
ran into a huge pickle and don't know how to bite it. My problem is this, a client purchases a certain number of sessions that expire after a certain amount of time. I figure I need an account that records the number of sessions paid for and the date that they expire. After the sessions expires the remaining sessions are then moved to a savings account kind of, that is refunded only after accumulating to a certain number. The price of each session is variable, depending on the package the client signs up with, so I need a method to be able to turn session numbers back into monetary value. So far I decided to create an invoice table that records every paid order; along with payment date and start date, and then with a query I calculate the end date. For example: Payment starts on the 3rd of the month, client pays for 2 sessions a week and they paid for 4 weeks of training; so they receive 8 training sessions that expire on 31st. My main issue is setting the database up so every client has a session account (including reserved and expired sessions), that increases every time an invoice is paid, and expires so that their paid unattended sessions are moved to another field (expired sessions). I also want to be able to still keep the market value of each session (reserved or expired), because some clients might pay for sessions but never use them. I'd like to be able to refund the money. The query does all the calculations but I'm stuck as far as moving the information into an account table or such. |
#2
|
|||
|
|||
Clients and multiple accounts
If the query works then build a report to output the results, not save to a
table. Basic principle of relational database design, Save Raw Data, Calculate in Report. lanjoudun wrote: I'm building a database for my personal training business, and currently I ran into a huge pickle and don't know how to bite it. My problem is this, a client purchases a certain number of sessions that expire after a certain amount of time. I figure I need an account that records the number of sessions paid for and the date that they expire. After the sessions expires the remaining sessions are then moved to a savings account kind of, that is refunded only after accumulating to a certain number. The price of each session is variable, depending on the package the client signs up with, so I need a method to be able to turn session numbers back into monetary value. So far I decided to create an invoice table that records every paid order; along with payment date and start date, and then with a query I calculate the end date. For example: Payment starts on the 3rd of the month, client pays for 2 sessions a week and they paid for 4 weeks of training; so they receive 8 training sessions that expire on 31st. My main issue is setting the database up so every client has a session account (including reserved and expired sessions), that increases every time an invoice is paid, and expires so that their paid unattended sessions are moved to another field (expired sessions). I also want to be able to still keep the market value of each session (reserved or expired), because some clients might pay for sessions but never use them. I'd like to be able to refund the money. The query does all the calculations but I'm stuck as far as moving the information into an account table or such. -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Clients and multiple accounts
I think the major issue is the set up of the relationship.
One client can have many invoices (1:M) One client can have many payment plans (1:M) One invoice can have only one payment plan (1:1) One client can have many classes (1:M) One class can have many clients (1:M) One trainer can have many classes (1:M) One class can have many trainers (1:M) June7 wrote: If the query works then build a report to output the results, not save to a table. Basic principle of relational database design, Save Raw Data, Calculate in Report. I'm building a database for my personal training business, and currently I ran into a huge pickle and don't know how to bite it. My problem is this, a [quoted text clipped - 20 lines] The query does all the calculations but I'm stuck as far as moving the information into an account table or such. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200907/1 |
#4
|
|||
|
|||
Clients and multiple accounts
How does this obstruct building report? If the query works report(s) can be
built. lanjoudun wrote: I think the major issue is the set up of the relationship. One client can have many invoices (1:M) One client can have many payment plans (1:M) One invoice can have only one payment plan (1:1) One client can have many classes (1:M) One class can have many clients (1:M) One trainer can have many classes (1:M) One class can have many trainers (1:M) If the query works then build a report to output the results, not save to a table. Basic principle of relational database design, Save Raw Data, [quoted text clipped - 5 lines] The query does all the calculations but I'm stuck as far as moving the information into an account table or such. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200907/1 |
#5
|
|||
|
|||
Clients and multiple accounts
Well I think in my database design, I linked all payment plans to clients,
but now I see that clients should get invoices and those invoices are linked to payment plans. Since from invoice to invoice a plan might change. So now my report is able to calculate the total amount of sessions provided for each invoice for each client, but how should I subtract a training session from an invoice (this way if training sessions aren't completed for said invoice, the amount left could roll over into another account, so refunds or penalties can be served depending on each invoice). Invoices are time sensitive. Btw thank you for all your help I know you could be wasting time on something or one more important lol. ^.^ June7 wrote: How does this obstruct building report? If the query works report(s) can be built. I think the major issue is the set up of the relationship. [quoted text clipped - 11 lines] The query does all the calculations but I'm stuck as far as moving the information into an account table or such. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200907/1 |
#6
|
|||
|
|||
Clients and multiple accounts
relationships have two sides; you must define both to determine what type of
relationship you're working with: One client can have many invoices AND one invoice may belong to only one client. 1:n (n represents an unknown value, it may be one, many, or none) One client can have many payment plans AND one payment plan may be assigned to many clients. n:n One invoice can have only one payment plan AND one payment plan may be assigned to many invoices. n:1 (though normally one-to-many relationships are expressed *from* the one side *to* the many side, since that's how the relationship is actually set up) One client can have many classes AND One class can have many clients n:n One trainer can have many classes AND One class can have many trainers n:n the many-to-many (n:n) relationships are modeled in Access by linking both of the tables to a third, instead of directly to each other, in two one-to-many relationships, as client 1:n classclients class 1:n classclients i know that appears to match your posted declarations: One client can have many classes (1:M) One class can have many clients (1:M) my point is that it's important to define the relationships themselves correctly: One client can have many classes AND One class can have many clients n:n to make sure you then build the appropriate tables (clients, classes, AND classclients) to support them properly. hth "lanjoudun via AccessMonster.com" u53027@uwe wrote in message news:9895bf9daaa3d@uwe... I think the major issue is the set up of the relationship. One client can have many invoices (1:M) One client can have many payment plans (1:M) One invoice can have only one payment plan (1:1) One client can have many classes (1:M) One class can have many clients (1:M) One trainer can have many classes (1:M) One class can have many trainers (1:M) June7 wrote: If the query works then build a report to output the results, not save to a table. Basic principle of relational database design, Save Raw Data, Calculate in Report. I'm building a database for my personal training business, and currently I ran into a huge pickle and don't know how to bite it. My problem is this, a [quoted text clipped - 20 lines] The query does all the calculations but I'm stuck as far as moving the information into an account table or such. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200907/1 |
#7
|
|||
|
|||
Clients and multiple accounts
Alright so far change the relationships, so now every invoice is linked to
one payment method a 1 to 1 method, my next problem is now every invoice is alotted a number of sessions, I want to limit the number of records entered per invoice by the session number, ex: client buys 6 sessions, so the invoice can only be linked to 6 sessions, I already limited the session criteria so it can only allow sessions between invoice start and end period. I'm wondering how I should place these validations in. tina wrote: relationships have two sides; you must define both to determine what type of relationship you're working with: One client can have many invoices AND one invoice may belong to only one client. 1:n (n represents an unknown value, it may be one, many, or none) One client can have many payment plans AND one payment plan may be assigned to many clients. n:n One invoice can have only one payment plan AND one payment plan may be assigned to many invoices. n:1 (though normally one-to-many relationships are expressed *from* the one side *to* the many side, since that's how the relationship is actually set up) One client can have many classes AND One class can have many clients n:n One trainer can have many classes AND One class can have many trainers n:n the many-to-many (n:n) relationships are modeled in Access by linking both of the tables to a third, instead of directly to each other, in two one-to-many relationships, as client 1:n classclients class 1:n classclients i know that appears to match your posted declarations: One client can have many classes (1:M) One class can have many clients (1:M) my point is that it's important to define the relationships themselves correctly: One client can have many classes AND One class can have many clients n:n to make sure you then build the appropriate tables (clients, classes, AND classclients) to support them properly. hth I think the major issue is the set up of the relationship. [quoted text clipped - 15 lines] The query does all the calculations but I'm stuck as far as moving the information into an account table or such. http://www.accessmonster.com/Uwe/For...esign/200907/1 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200907/1 |
#8
|
|||
|
|||
Clients and multiple accounts
Hello lanjoudun,
If you don't mind a blunt 30,000' view in an attempt to be helpful, your overall posts looks like you are putting the cart before the horse. I think that have recognized the importance of and have done some good work on step 1, which is to shut the computer off and get organized on the nature of the entities and information that you want to database. I say "start" because your list of relationships raises some issues which I don't think that you have resolved. Your first three lines describe a triangle of relationships. I suspect that one of these (probably clients to payment plans) may be indirect and thus not to be recorded as a relationship in your database. Another is that you have described 2 pairs of relationships (Clients- classes, Trainers- classes) that are "one to many" in both directions, which adds up to one many-to-many relations with related requirements (IF you are documenting these relationships) for junction tables. Another is that I think that you missing one or two types of entities/tables. To decide that you will need to decide/clarfiy what a paid invoices creates and entitlement for a certain total value of sessions vs. creates an entitlement to a particular set of sessions. Step two is a solid table structure to accomplish that. Yours needs a solid (somewhat complex) table structure, and from your post, I think that this (most important) stage seems to be barely on your radar screen and you are jumping to later steps and wondering why they aren't working. Without that you are building on swanpland and nothing will go well. If you did indeed do these, then those structural details should/would be a part of your questions. Regarding sessions, if the answer to that last question is the former, then you probably need an InstancesOfAClientReceivingASession table (of course, shorten my long names). If it's the latter, then you probably need a "SessionTransactions" table which has "credit" records added when they buy sessions and debit records for instances of a client using a session. |
#9
|
|||
|
|||
Clients and multiple accounts
Thanks that was a really good post, although some of if went overhead, I'd be
reading it over and over a couple of times. I figure this will be a complex group of relationship in order to achieve desired affect. Instead of creating a balance I decided to link every invoice to a number of sessions that are also linked to session details (trainers). This way the monetary value of the sessions are remained and I could just count up all the missed of defaulted sessions with session attendance sheet or something. The most important part is retaining the financial information (invoice and payment method), So Client :: Invoice :: Payment Plan :: Sessions :: Trainer. A bunch of validations would be placed on the Sessions part so that sessions don't exceed certain parameters (dates, times). I figure I'd need some visual basic knowledge to run these complex validation criteria and calculations. So that if a trainer already has a session it can only be added under the same time frame if that trainer is in the same location in regards to another session. The most important thing is that a client pays for the session because if it is not paid for then the sessions wouldn't exist, Fred wrote: Hello lanjoudun, If you don't mind a blunt 30,000' view in an attempt to be helpful, your overall posts looks like you are putting the cart before the horse. I think that have recognized the importance of and have done some good work on step 1, which is to shut the computer off and get organized on the nature of the entities and information that you want to database. I say "start" because your list of relationships raises some issues which I don't think that you have resolved. Your first three lines describe a triangle of relationships. I suspect that one of these (probably clients to payment plans) may be indirect and thus not to be recorded as a relationship in your database. Another is that you have described 2 pairs of relationships (Clients- classes, Trainers- classes) that are "one to many" in both directions, which adds up to one many-to-many relations with related requirements (IF you are documenting these relationships) for junction tables. Another is that I think that you missing one or two types of entities/tables. To decide that you will need to decide/clarfiy what a paid invoices creates and entitlement for a certain total value of sessions vs. creates an entitlement to a particular set of sessions. Step two is a solid table structure to accomplish that. Yours needs a solid (somewhat complex) table structure, and from your post, I think that this (most important) stage seems to be barely on your radar screen and you are jumping to later steps and wondering why they aren't working. Without that you are building on swanpland and nothing will go well. If you did indeed do these, then those structural details should/would be a part of your questions. Regarding sessions, if the answer to that last question is the former, then you probably need an InstancesOfAClientReceivingASession table (of course, shorten my long names). If it's the latter, then you probably need a "SessionTransactions" table which has "credit" records added when they buy sessions and debit records for instances of a client using a session. -- Message posted via http://www.accessmonster.com |
#10
|
|||
|
|||
Clients and multiple accounts
comments inline.
"lanjoudun via AccessMonster.com" u53027@uwe wrote in message news:98a3f758632a3@uwe... Alright so far change the relationships, so now every invoice is linked to one payment method a 1 to 1 method, if you truly have a one-to-one relationship between an invoices table and a payment methods table, i'd say you have a problem. remember BOTH sides of any relationship must be defined. each invoice may have only one payment method, but can each payment method *really* be assigned to only one invoice? so if one invoice is assigned "cash" payment method, no other invoice can be paid by cash? i doubt that's what you want. rather, i'd guess that in reality you have a one-to-many relationship between payment methods and invoices: one payment method may be assigned to many invoices, but each invoice may have only one payment method. my next problem is now every invoice is alotted a number of sessions, I want to limit the number of records entered per invoice by the session number, ex: client buys 6 sessions, so the invoice can only be linked to 6 sessions, I already limited the session criteria so it can only allow sessions between invoice start and end period. I'm wondering how I should place these validations in. yes, that's do-able, at the form level. but i really recommend that you STOP, turn off your PC, and study up on relational design principles before you go any further. for more information, see http://home.att.net/~california.db/tips.html#aTip1 hth tina wrote: relationships have two sides; you must define both to determine what type of relationship you're working with: One client can have many invoices AND one invoice may belong to only one client. 1:n (n represents an unknown value, it may be one, many, or none) One client can have many payment plans AND one payment plan may be assigned to many clients. n:n One invoice can have only one payment plan AND one payment plan may be assigned to many invoices. n:1 (though normally one-to-many relationships are expressed *from* the one side *to* the many side, since that's how the relationship is actually set up) One client can have many classes AND One class can have many clients n:n One trainer can have many classes AND One class can have many trainers n:n the many-to-many (n:n) relationships are modeled in Access by linking both of the tables to a third, instead of directly to each other, in two one-to-many relationships, as client 1:n classclients class 1:n classclients i know that appears to match your posted declarations: One client can have many classes (1:M) One class can have many clients (1:M) my point is that it's important to define the relationships themselves correctly: One client can have many classes AND One class can have many clients n:n to make sure you then build the appropriate tables (clients, classes, AND classclients) to support them properly. hth I think the major issue is the set up of the relationship. [quoted text clipped - 15 lines] The query does all the calculations but I'm stuck as far as moving the information into an account table or such. http://www.accessmonster.com/Uwe/For...esign/200907/1 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200907/1 |
Thread Tools | |
Display Modes | |
|
|