Table design question for exist program

[Joe Cilinceon]

I've been using a application I've developed over the last year to run a
self storage business. I'm in the process of fixing some design flaws in the
Ledger areas of the program and could use some advise here. Please don't
tell me to purchase an off the shelf accounting package. Now with that said
here is what I need. I'm splitting one of the tables into smaller tables
that will link together. The table my question pertains too is as follows:

LEDGER table
[LedgerID] ties record to a lease (tenant & unit)
[Transaction] unique number that ties to Table.Payments and Table.Charges.
[PaymentDate]
[PaymentAmount] total moneys collected for this transaction (we accept split
payments cash, check, credit card etc.)
.... Now the next 2 fields is what I need advise on
[PaidFrom]
[PaidThru]

Now both the PaidFrom and PaidThru dates would be blank (Null), unless they
are paying enough to move the rent forward at least 1 full month, which in
the case 90% of the time. However there are other charges that don't effect
these dates such as buying a lock or paying a fee of some kind. I was
looking for advise on how to handle these 2 fields, either leave like they
are or make still another table with Transaction and the 2 paid fields. What
would be the advantages to this if any. Oh and this to fields are used to
base a Balance Due query on for each open account.

 

[mnature]

You could set up a new table, such as "Charge Type Table," which would be an
itemization of what the charge applies to (such as rental, lock purchase,
damage deposit, etc.). It would have a ChargeTypeID as the key, and the
ChargeName (text field) where you have all the different types of charges.
This would serve several purposes. It would simplify making an entry, since
you could use the information in the ChargeName field as a pull-down menu,
and it would also allow you to split out the different types of charges that
you make during a month, making it easier to see what has been paid towards
rentals. You would add the ChargeTypeID to your LedgerTable, and link the
two tables in your relationship chart.

 

[Joe Cilinceon]

Thanks for responding mnature but I think you misunderstood what I was
asking. The Ledger Table is already linked to 2 other tables that handle the
Payments (we accept multiple payments such as a single payment made by check
and cash). This also uses a lookup table with payment type. I also have a
Charges Table that has what you suggest. It also has a lookup table for it
as you describe. The question I have is the 2 fields in the Ledger Table
called PaidFrom and PaidThru. These 2 fields are used in a query that does
the math to find out the balance due at any given month. Now a transaction
is created every time we take money regardless of what it pays. So not every
transaction would have the 2 dates filled in. I was just wondering if I
should move these 2 fields to some other table or just leave them there.

--

Joe Cilinceon

You could set up a new table, such as "Charge Type Table," which
would be an itemization of what the charge applies to (such as
rental, lock purchase, damage deposit, etc.). It would have a
ChargeTypeID as the key, and the ChargeName (text field) where you
have all the different types of charges. This would serve several
purposes. It would simplify making an entry, since you could use the
information in the ChargeName field as a pull-down menu, and it would
also allow you to split out the different types of charges that you
make during a month, making it easier to see what has been paid
towards rentals. You would add the ChargeTypeID to your LedgerTable,
and link the two tables in your relationship chart.

I've been using a application I've developed over the last year to
run a self storage business. I'm in the process of fixing some
design flaws in the Ledger areas of the program and could use some
advise here. Please don't tell me to purchase an off the shelf
accounting package. Now with that said here is what I need. I'm
splitting one of the tables into smaller tables that will link
together. The table my question pertains too is as follows:

LEDGER table
[LedgerID] ties record to a lease (tenant & unit)
[Transaction] unique number that ties to Table.Payments and
Table.Charges. [PaymentDate]
[PaymentAmount] total moneys collected for this transaction (we
accept split payments cash, check, credit card etc.)
.... Now the next 2 fields is what I need advise on
[PaidFrom]
[PaidThru]

Now both the PaidFrom and PaidThru dates would be blank (Null),
unless they are paying enough to move the rent forward at least 1
full month, which in the case 90% of the time. However there are
other charges that don't effect these dates such as buying a lock or
paying a fee of some kind. I was looking for advise on how to handle
these 2 fields, either leave like they are or make still another
table with Transaction and the 2 paid fields. What would be the
advantages to this if any. Oh and this to fields are used to base a
Balance Due query on for each open account.

 

[mnature]

Yes, I did misunderstand. I would suggest placing those two fields in a
separate table, linked to the LedgerID field. The reason for that, is that
those fields are not required for every ledger transaction. This way, the
fields are used only when necessary. This also simplifies creating queries,
forms and reports, since the ledger transactions associated with this
particular table would be the ones that indicate paying rent.

Thanks for responding mnature but I think you misunderstood what I was
asking. The Ledger Table is already linked to 2 other tables that handle the
Payments (we accept multiple payments such as a single payment made by check
and cash). This also uses a lookup table with payment type. I also have a
Charges Table that has what you suggest. It also has a lookup table for it
as you describe. The question I have is the 2 fields in the Ledger Table
called PaidFrom and PaidThru. These 2 fields are used in a query that does
the math to find out the balance due at any given month. Now a transaction
is created every time we take money regardless of what it pays. So not every
transaction would have the 2 dates filled in. I was just wondering if I
should move these 2 fields to some other table or just leave them there.

--

Joe Cilinceon

You could set up a new table, such as "Charge Type Table," which
would be an itemization of what the charge applies to (such as
rental, lock purchase, damage deposit, etc.). It would have a
ChargeTypeID as the key, and the ChargeName (text field) where you
have all the different types of charges. This would serve several
purposes. It would simplify making an entry, since you could use the
information in the ChargeName field as a pull-down menu, and it would
also allow you to split out the different types of charges that you
make during a month, making it easier to see what has been paid
towards rentals. You would add the ChargeTypeID to your LedgerTable,
and link the two tables in your relationship chart.

I've been using a application I've developed over the last year to
run a self storage business. I'm in the process of fixing some
design flaws in the Ledger areas of the program and could use some
advise here. Please don't tell me to purchase an off the shelf
accounting package. Now with that said here is what I need. I'm
splitting one of the tables into smaller tables that will link
together. The table my question pertains too is as follows:

LEDGER table
[LedgerID] ties record to a lease (tenant & unit)
[Transaction] unique number that ties to Table.Payments and
Table.Charges. [PaymentDate]
[PaymentAmount] total moneys collected for this transaction (we
accept split payments cash, check, credit card etc.)
.... Now the next 2 fields is what I need advise on
[PaidFrom]
[PaidThru]

Now both the PaidFrom and PaidThru dates would be blank (Null),
unless they are paying enough to move the rent forward at least 1
full month, which in the case 90% of the time. However there are
other charges that don't effect these dates such as buying a lock or
paying a fee of some kind. I was looking for advise on how to handle
these 2 fields, either leave like they are or make still another
table with Transaction and the 2 paid fields. What would be the
advantages to this if any. Oh and this to fields are used to base a
Balance Due query on for each open account.

 

[Joe Cilinceon]

Ok I would also have the transaction number in that table as well. I would
need away to tie a particular rent to a given rent payment. Hope that makes
sense to you.

--

Joe Cilinceon

Yes, I did misunderstand. I would suggest placing those two fields
in a separate table, linked to the LedgerID field. The reason for
that, is that those fields are not required for every ledger
transaction. This way, the fields are used only when necessary.
This also simplifies creating queries, forms and reports, since the
ledger transactions associated with this particular table would be
the ones that indicate paying rent.

Thanks for responding mnature but I think you misunderstood what I
was asking. The Ledger Table is already linked to 2 other tables
that handle the Payments (we accept multiple payments such as a
single payment made by check and cash). This also uses a lookup
table with payment type. I also have a Charges Table that has what
you suggest. It also has a lookup table for it as you describe. The
question I have is the 2 fields in the Ledger Table called PaidFrom
and PaidThru. These 2 fields are used in a query that does the math
to find out the balance due at any given month. Now a transaction is
created every time we take money regardless of what it pays. So not
every transaction would have the 2 dates filled in. I was just
wondering if I should move these 2 fields to some other table or
just leave them there.

--

Joe Cilinceon

You could set up a new table, such as "Charge Type Table," which
would be an itemization of what the charge applies to (such as
rental, lock purchase, damage deposit, etc.). It would have a
ChargeTypeID as the key, and the ChargeName (text field) where you
have all the different types of charges. This would serve several
purposes. It would simplify making an entry, since you could use
the information in the ChargeName field as a pull-down menu, and it
would also allow you to split out the different types of charges
that you make during a month, making it easier to see what has been
paid towards rentals. You would add the ChargeTypeID to your
LedgerTable, and link the two tables in your relationship chart.

:

I've been using a application I've developed over the last year to
run a self storage business. I'm in the process of fixing some
design flaws in the Ledger areas of the program and could use some
advise here. Please don't tell me to purchase an off the shelf
accounting package. Now with that said here is what I need. I'm
splitting one of the tables into smaller tables that will link
together. The table my question pertains too is as follows:

LEDGER table
[LedgerID] ties record to a lease (tenant & unit)
[Transaction] unique number that ties to Table.Payments and
Table.Charges. [PaymentDate]
[PaymentAmount] total moneys collected for this transaction (we
accept split payments cash, check, credit card etc.)
.... Now the next 2 fields is what I need advise on
[PaidFrom]
[PaidThru]

Now both the PaidFrom and PaidThru dates would be blank (Null),
unless they are paying enough to move the rent forward at least 1
full month, which in the case 90% of the time. However there are
other charges that don't effect these dates such as buying a lock
or paying a fee of some kind. I was looking for advise on how to
handle these 2 fields, either leave like they are or make still
another table with Transaction and the 2 paid fields. What would
be the advantages to this if any. Oh and this to fields are used
to base a Balance Due query on for each open account.

 

[mnature]

If you're tying into the ledger ID, then that also ties you to the unit being
rented. Can probably do a query that combines the ledger table, the
PaidFromThru table, and the lease table. Sort on the leases, and that should
consolidate your payments for each unit. Hope that makes sense to you.

Ok I would also have the transaction number in that table as well. I would
need away to tie a particular rent to a given rent payment. Hope that makes
sense to you.

--

Joe Cilinceon

Yes, I did misunderstand. I would suggest placing those two fields
in a separate table, linked to the LedgerID field. The reason for
that, is that those fields are not required for every ledger
transaction. This way, the fields are used only when necessary.
This also simplifies creating queries, forms and reports, since the
ledger transactions associated with this particular table would be
the ones that indicate paying rent.

Thanks for responding mnature but I think you misunderstood what I
was asking. The Ledger Table is already linked to 2 other tables
that handle the Payments (we accept multiple payments such as a
single payment made by check and cash). This also uses a lookup
table with payment type. I also have a Charges Table that has what
you suggest. It also has a lookup table for it as you describe. The
question I have is the 2 fields in the Ledger Table called PaidFrom
and PaidThru. These 2 fields are used in a query that does the math
to find out the balance due at any given month. Now a transaction is
created every time we take money regardless of what it pays. So not
every transaction would have the 2 dates filled in. I was just
wondering if I should move these 2 fields to some other table or
just leave them there.

--

Joe Cilinceon

mnature wrote:
You could set up a new table, such as "Charge Type Table," which
would be an itemization of what the charge applies to (such as
rental, lock purchase, damage deposit, etc.). It would have a
ChargeTypeID as the key, and the ChargeName (text field) where you
have all the different types of charges. This would serve several
purposes. It would simplify making an entry, since you could use
the information in the ChargeName field as a pull-down menu, and it
would also allow you to split out the different types of charges
that you make during a month, making it easier to see what has been
paid towards rentals. You would add the ChargeTypeID to your
LedgerTable, and link the two tables in your relationship chart.

:

I've been using a application I've developed over the last year to
run a self storage business. I'm in the process of fixing some
design flaws in the Ledger areas of the program and could use some
advise here. Please don't tell me to purchase an off the shelf
accounting package. Now with that said here is what I need. I'm
splitting one of the tables into smaller tables that will link
together. The table my question pertains too is as follows:

LEDGER table
[LedgerID] ties record to a lease (tenant & unit)
[Transaction] unique number that ties to Table.Payments and
Table.Charges. [PaymentDate]
[PaymentAmount] total moneys collected for this transaction (we
accept split payments cash, check, credit card etc.)
.... Now the next 2 fields is what I need advise on
[PaidFrom]
[PaidThru]

Now both the PaidFrom and PaidThru dates would be blank (Null),
unless they are paying enough to move the rent forward at least 1
full month, which in the case 90% of the time. However there are
other charges that don't effect these dates such as buying a lock
or paying a fee of some kind. I was looking for advise on how to
handle these 2 fields, either leave like they are or make still
another table with Transaction and the 2 paid fields. What would
be the advantages to this if any. Oh and this to fields are used
to base a Balance Due query on for each open account.

 

[Joe Cilinceon]

Yes but the reason I discovered my table problems to begin with was trying
to create a printed ledger on a single account. Sounds really easy to just
list every payment that has been made on an account until you start to see
dates that are 2 months after the paid thru date. This tell you they are
really late. It is also very hard to figure out which payment paid for what
month since some may be well in advance and others up to 90 days late. After
90 days we sell the space or dump it. I do keep the current paid from and
paid thru dates in the Leases table now for each account or in the case of a
vacate the last set of dates. I will play over the next couple of days and
see which method works best for my needs. Thanks again for the help mnature.

If you're tying into the ledger ID, then that also ties you to the
unit being rented. Can probably do a query that combines the ledger
table, the PaidFromThru table, and the lease table. Sort on the
leases, and that should consolidate your payments for each unit.
Hope that makes sense to you.

Ok I would also have the transaction number in that table as well. I
would need away to tie a particular rent to a given rent payment.
Hope that makes sense to you.

--

Joe Cilinceon

Yes, I did misunderstand. I would suggest placing those two fields
in a separate table, linked to the LedgerID field. The reason for
that, is that those fields are not required for every ledger
transaction. This way, the fields are used only when necessary.
This also simplifies creating queries, forms and reports, since the
ledger transactions associated with this particular table would be
the ones that indicate paying rent.

:

Thanks for responding mnature but I think you misunderstood what I
was asking. The Ledger Table is already linked to 2 other tables
that handle the Payments (we accept multiple payments such as a
single payment made by check and cash). This also uses a lookup
table with payment type. I also have a Charges Table that has what
you suggest. It also has a lookup table for it as you describe. The
question I have is the 2 fields in the Ledger Table called PaidFrom
and PaidThru. These 2 fields are used in a query that does the math
to find out the balance due at any given month. Now a transaction
is created every time we take money regardless of what it pays. So
not every transaction would have the 2 dates filled in. I was just
wondering if I should move these 2 fields to some other table or
just leave them there.

--

Joe Cilinceon

mnature wrote:
You could set up a new table, such as "Charge Type Table," which
would be an itemization of what the charge applies to (such as
rental, lock purchase, damage deposit, etc.). It would have a
ChargeTypeID as the key, and the ChargeName (text field) where you
have all the different types of charges. This would serve several
purposes. It would simplify making an entry, since you could use
the information in the ChargeName field as a pull-down menu, and
it would also allow you to split out the different types of
charges that you make during a month, making it easier to see
what has been paid towards rentals. You would add the
ChargeTypeID to your LedgerTable, and link the two tables in your
relationship chart.

:

I've been using a application I've developed over the last year
to run a self storage business. I'm in the process of fixing some
design flaws in the Ledger areas of the program and could use
some advise here. Please don't tell me to purchase an off the
shelf accounting package. Now with that said here is what I
need. I'm splitting one of the tables into smaller tables that
will link together. The table my question pertains too is as
follows:

LEDGER table
[LedgerID] ties record to a lease (tenant & unit)
[Transaction] unique number that ties to Table.Payments and
Table.Charges. [PaymentDate]
[PaymentAmount] total moneys collected for this transaction (we
accept split payments cash, check, credit card etc.)
.... Now the next 2 fields is what I need advise on
[PaidFrom]
[PaidThru]

Now both the PaidFrom and PaidThru dates would be blank (Null),
unless they are paying enough to move the rent forward at least 1
full month, which in the case 90% of the time. However there are
other charges that don't effect these dates such as buying a lock
or paying a fee of some kind. I was looking for advise on how to
handle these 2 fields, either leave like they are or make still
another table with Transaction and the 2 paid fields. What would
be the advantages to this if any. Oh and this to fields are used
to base a Balance Due query on for each open account.

 

[mnature]

There is one other way of looking at this problem. I assume that each leased
unit has only one tenant name associated with it. In the table that contains
your leasing information, have a field that is the beginning date of the
rental of that particular unit (something like BeginDate), and have another
field that is the monthly rental for that unit (perhaps MonthlyRental). Then
you can do a query that has a calculated field, say ElapsedTime:
round((Now()-[BeginDate])/30). This will give you the number of months
(rounded) that the lease has been active. You then calculate how much the
total rental is for that amount of time with another calculated field, say
TotalRentalOwed: [ElapsedTime]*[MonthlyRental]. Now you can directly compare
that amount to what has actually been paid by the tenant, and see how much
they owe you. That can be done in a report, summing the amounts paid by the
tenant, and subtracting from that the TotalRentalOwed.

The advantage of this is that the database does all of the work of keeping
track of dates and times. And if a tenant commonly pays two or three months
in advance, then this would automatically see that as an overpayment (a
positive number), in the report. When a tenant owes rent, then that would
show as a negative number in the report.

I hope you can get your database working as you want. I admire people that
work out these elegant solutions, without resorting to canned software. Good
luck to you.