Calculations

[Zack90305]

I had a couple of relatively simple questions this time- assuming tables can even be used to serve this purpose ><.

1. Under the table 'tblMailboxes' in the attached database, I wanted to make a field for a price that increments (preferably monthly, but daily at a smaller increment is fine too).

Some customers increment for a different value (some pay $25 monthly- some pay $15).

I was thinking that something like '(systemdate - starting date) * .15' But I don't have all of those tools available to me in a table from the looks of it.

2. I want to create a column in 'tblAccounts' that totals up all of the 'TotalDue' values from 'tblInvoices' with the same 'Account_ID'. (I want all of the values for a specific account number for an "unpaid balance" field basically).


Anyone know which direction I should head in for that?

 

[jdraw]

Before we can provide any suggestions, we need to know WHAT you are talking about.

Pretend we're in a McDonalds line up and I said to you "WHAT is it you're trying to do".
In very simple, plain English, What would you say?

Tell me, again in simple, plain English, why do you have 6 copies of tblProductsServices in your relationships window?

 

[Zack90305]

Before we can provide any suggestions, we need to know WHAT you are talking about.

Pretend we're in a McDonalds line up and I said to you "WHAT is it you're trying to do".
In very simple, plain English, What would you say?

1. How do I make a price increment by date in a table field?

2. How can I total up all of the "TotalDue" invoice values for a given Account_ID into a field in tblAccounts?

 

[plog]

I don't fully understand what you are trying to do with #1, but your methodology for how to get there seems sound. Now, you wouldn't store a calculated value in a field--you would calculate it. So if a customer was to have a certain amount add/subtracted based on the time, you would use the calculation you mentioned. Again, I don't fully understand what you are trying to do, so you would have to explain better (with further data) for me to fully help.


For #2, that would be an easy one, if you had a proper table structure. As it is now, you're tblInvoices is flawed. A general rule is that when you start numerating fields (e.g. ItemCost3, Quantity4, Tax2), its time for a new table.

In this instance I suggest you make a new table for all those numerated fieldtypes (ProductService, LineTotal, Discount, etc.). Call it something like InvoiceItems. You use the InvoiceID in that new table as a foreign key back to tblInvoices (like how you have Customer_ID in tblInvoices to get to tblCustomers).

With that structure, totaling up your invoices by customer is simple.

 

[Zack90305]

I don't fully understand what you are trying to do with #1, but your methodology for how to get there seems sound. Now, you wouldn't store a calculated value in a field--you would calculate it. So if a customer was to have a certain amount add/subtracted based on the time, you would use the calculation you mentioned. Again, I don't fully understand what you are trying to do, so you would have to explain better (with further data) for me to fully help.


For #2, that would be an easy one, if you had a proper table structure. As it is now, you're tblInvoices is flawed. A general rule is that when you start numerating fields (e.g. ItemCost3, Quantity4, Tax2), its time for a new table.

In this instance I suggest you make a new table for all those numerated fieldtypes (ProductService, LineTotal, Discount, etc.). Call it something like InvoiceItems. You use the InvoiceID in that new table as a foreign key back to tblInvoices (like how you have Customer_ID in tblInvoices to get to tblCustomers).

With that structure, totaling up your invoices by customer is simple.

With the first one- there are P.O. boxes for the business. Customers pay "x" amount monthly on them. I was hoping that tblMailboxes would keep track of that value with users only having to interface with it to input payments.

Example: Person "A" owes "X" on their mailbox. The amount that they owe goes up by $15 monthly. If they come in and pay less than X, I was hoping the field would allow me to input that they paid whatever they did- but I still need it to go up by $15 one time every month in real time.

I hope that's a bit clearer? ><


With number 2, that helps a lot actually- I noticed that I had a problem with my invoice table- but didn't really know how I was going to go about fixing it. But- won't I still be numbering the invoice item foreign keys in the Invoice table?

 

[plog]

tblMailboxes wouldn't really keep track of it. A query would. You would need a table somewhere to determine the monthly (or whatever interval) rate and the start date. Then in a query you would use the DateDiff function (http://www.techonthenet.com/access/functions/date/datediff.php) to determine the number of months they need to have paid, multply that by the monthly rate. Then you subtract from that all the payments they made.

But- won't I still be numbering the invoice item foreign keys in the Invoice table

I don't know what you are trying to say with that. With each invoice item you will have the Invoice_ID that links back to tblInvoice. If you want to number which invoice item is which invoice item you can accomodate that with a field to denote what invoice item number it is.

Generally, its not important to know that some particular item is a specific number of an invoice. Think of a grocery bill, does it matter in which order your kiwis rang up?

 

[Zack90305]

tblMailboxes wouldn't really keep track of it. A query would. You would need a table somewhere to determine the monthly (or whatever interval) rate and the start date. Then in a query you would use the DateDiff function (http://www.techonthenet.com/access/functions/date/datediff.php) to determine the number of months they need to have paid, multply that by the monthly rate. Then you subtract from that all the payments they made.

Ah- I'll go and get started then. Sorta need to start working on it to get the picture there.

I don't know what you are trying to say with that. With each invoice item you will have the Invoice_ID that links back to tblInvoice. If you want to number which invoice item is which invoice item you can accomodate that with a field to denote what invoice item number it is.

Generally, its not important to know that some particular item is a specific number of an invoice. Think of a grocery bill, does it matter in which order your kiwis rang up?

I just had it backwards mentally ><.

Thanks a million. You were extremely helpful

 

[jdraw]

Zack,
Further to plog's comments, the tables won't "do" anything but store data that you give it/them. The fact is that not Access nor any dbms is going to do something that you haven't provided info for it to return to you after some calculation/manipulation. I think there are some basic concepts that that should be aware of or re-familiarize with.
Here's a tutorial you have to work through, but you will learn about tables and relationships. I think you would benefit from working through it.

Good luck.

 

[RainLover]

The design of your tables could do with some touches.

There is an article on "The Evils of Lookups in Tables" It is important that to read, understand and adopt the suggestion contained in that article. Do a Google.

Also every table should have a primary key. I always make them Auto number end the name with PK. Just helps down the track a little. Foreign keys should be a Long Integer and its name should be the same as the PK except it would end with FK not PK.

Hope this helps a little.

Finally please fix your relationships before reposting.