Clients and multiple accounts

[lanjoudun]

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.

 

[June7 via AccessMonster.com]

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 via AccessMonster.com]

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,
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.

 

[June7 via AccessMonster.com]

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.

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]

 

[lanjoudun via AccessMonster.com]

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. ^.^

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]

 

[tina]

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.

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,
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.

 

[lanjoudun via AccessMonster.com]

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.

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]

 

[Fred]

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.

 

[lanjoudun via AccessMonster.com]

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,

 

[tina]

comments inline.

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

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.