Formula in Table

  • Thread starter Thread starter ant1983
  • Start date Start date
A

ant1983

Not sure if im posting this in the right category; if not apologies...

Ive created a pretty straight-forward table to capture training. So one of
those fields is "Seats Available" with a number such as 25.

Another field is Seats Booked - lets say that value is 20...

I want a 3rd field: Seats Left to calculate automatically :) so i thought i
could do that when designing the table but i cant see any wayn of doing it...
I know it can be done if i pull it into a query
 
Not sure if im posting this in the right category; if not apologies...

Ive created a pretty straight-forward table to capture training.  So one of
those fields is "Seats Available" with a number such as 25.

Another field is Seats Booked - lets say that value is 20...

I want a 3rd field:  Seats Left to calculate automatically :) so i thought i
could do that when designing the table but i cant see any wayn of doing it...
 I know it can be done if i pull it into a query
Click to expand...

Doesn't belong in the table anyway... it's a calculated amount.
(SeatsAvailable - SeatsBooked). Just do it in a query or in a
calculated control on your form. You are not trying to view all your
data from tables are you? Definitely the wrong way to go!
 
As suggested elsethread, it is rarely necessary to store a value you can
calculate.

Use a query to return the calculated value 'on-demand'.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Yups, thats what i thought but i thought it best to make sure as didnt want
to make changes down the line...

One question though: you say it is "rarely necessary" - when would it be
necessary? I mean when would you NOT put it in a query?
 
Not sure if im posting this in the right category; if not apologies...

Ive created a pretty straight-forward table to capture training. So one of
those fields is "Seats Available" with a number such as 25.

Another field is Seats Booked - lets say that value is 20...

I want a 3rd field: Seats Left to calculate automatically :) so i thought i
could do that when designing the table but i cant see any wayn of doing it...
I know it can be done if i pull it into a query
Click to expand...

Just do it in a Query, or as a calculated field on a form or report.

Tables are for data storage. They are NOT intended for data display or
editing; normally users should never even *see* table datasheets, only forms.
 
Usually in scenarios where the calculation is such that it would impact
performance. For example, calculating the current balance on a bank account.
To calculate the balance on the fly, the database would have to add up all
of the transactions from now until as far back as there is history. In the
case of my credit union, it would 15 years of history representing around
18,000 transactions which of course would have to be pulled from the other
hundreds of thousands of transactions from other the account holders.
Instead of calculating the balance on the fly, the bank would create a
summary table to hold the balance and then update the balance as apart of
processing a new transaction. The bank would then rebuild the summary table
periodically to ensure that it reflects the correct calculated balance.

Another example would be a data warehouse where various calculated values
are stored in order to speed up data analysis. For example, Walmart (damn
them) would have a database that provides sales information for products in
the store that provides quantities sold on a day, quantites sold in a week,
in a month, in a year complete with total sales in dollars, average prices,
average discounts etc.
 
ant1983,

To answer your question... Invoicing and/or Order Details and even then
certain conditions apply. And it is not that you would not put it in a
query, it's that you would store the calculated value, it still ALWAYS
should be done in the query.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
As Gina points out, in invoicing, you might want to store the
"point-in-time" unit price as part of the transaction for a product/item,
mainly because that item's price can change over time.

However, it is NOT necessary to do so, as you could design a (much more
complex) table structure that held ItemID, UnitPrice, FromDate, and ToDate,
and use this structure to track the price changes for items over time. You
would also use this to "know" what the price was when the transaction
happened (i.e., using a TransactionDate).

As pointed out elsethread, there may also be performance issues that would
make it more likely to see a stored calculated value... but if that's the
case, you might also be looking for a more robust, server-based back-end for
your data!

Best of luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Ah...but how would you handle negotiated rates on a per account basis? Or
applying a discount? There could be some scenarios where the nature of the
transaction is such that the invoice can't show that a standard rate was
discounted. For example, the client is paying $75 for an item that normall
sells for $100, but the billing has to show $75 as opposed to $100 with a
25% discount.
 
OK Its settled: Will do it in a query... Can someone help me with my query
please? :)

Ive got the following:

Two tables are in the query; tblTrainingSession and tblBooking

From tblTrainingSession ive inserted the numSeatsAvailable field (amongst
others) and from the tblBooking ive inserted the dteBookingDate field. I
clicked the totals button and grouped the numSeatsAvailable and Counted the
dteBookingDate. When i run the query the amounts are right.

Now i want my "Available Space Left" so i wrote this in another field:

"Seats Left: [numSeatsAvailable]-[dteBookingDate]"

It returns the correct result but it brings up that "Enter PArameter Value"
prompt thingy...

Thanks...
 
David

I assume yours was not rhetorical ...

I would have a [Discount] field, since that, too, is data I may want to know
about later...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
No it wasn't. Although I haven't seen that scenario per se in my
organization, there is a very common scenario where we bill at the standard
rates and then after the invoice is paid create a second invoice to issue
discounts to the client. Then there's the client who requires that their PO
Number appear as the first line item on their invoice (independent of the PO
number field in the upper right of the invoices) which requires use of an ad
hoc part number to accomplish.

....asprin anyone...

Jeff Boyce said:
David

I assume yours was not rhetorical ...

I would have a [Discount] field, since that, too, is data I may want to
know about later...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

David C. Holley said:
Ah...but how would you handle negotiated rates on a per account basis? Or
applying a discount? There could be some scenarios where the nature of
the transaction is such that the invoice can't show that a standard rate
was discounted. For example, the client is paying $75 for an item that
normall sells for $100, but the billing has to show $75 as opposed to
$100 with a 25% discount.
Click to expand...
Click to expand...
 
Why bother introducing a spurious part number to "mean" a discount?

If you add TWO fields (DiscountType - percent or amount; Discount), you
should be able to accommodate any discount situation, record the discount
applied, and still keep a well-normalized data design...

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

David C. Holley said:
No it wasn't. Although I haven't seen that scenario per se in my
organization, there is a very common scenario where we bill at the
standard rates and then after the invoice is paid create a second invoice
to issue discounts to the client. Then there's the client who requires
that their PO Number appear as the first line item on their invoice
(independent of the PO number field in the upper right of the invoices)
which requires use of an ad hoc part number to accomplish.

...asprin anyone...

Jeff Boyce said:
David

I assume yours was not rhetorical ...

I would have a [Discount] field, since that, too, is data I may want to
know about later...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

David C. Holley said:
Ah...but how would you handle negotiated rates on a per account basis?
Or applying a discount? There could be some scenarios where the nature
of the transaction is such that the invoice can't show that a standard
rate was discounted. For example, the client is paying $75 for an item
that normall sells for $100, but the billing has to show $75 as opposed
to $100 with a 25% discount.

message As Gina points out, in invoicing, you might want to store the
"point-in-time" unit price as part of the transaction for a
product/item, mainly because that item's price can change over time.

However, it is NOT necessary to do so, as you could design a (much more
complex) table structure that held ItemID, UnitPrice, FromDate, and
ToDate, and use this structure to track the price changes for items
over time. You would also use this to "know" what the price was when
the transaction happened (i.e., using a TransactionDate).

As pointed out elsethread, there may also be performance issues that
would make it more likely to see a stored calculated value... but if
that's the case, you might also be looking for a more robust,
server-based back-end for your data!

Best of luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services
mentioned in
this post. Mention and/or description of a product or service herein
does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Yups, thats what i thought but i thought it best to make sure as didnt
want
to make changes down the line...

One question though: you say it is "rarely necessary" - when would it
be
necessary? I mean when would you NOT put it in a query?

:

As suggested elsethread, it is rarely necessary to store a value you
can
calculate.

Use a query to return the calculated value 'on-demand'.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services
mentioned in
this post. Mention and/or description of a product or service herein
does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Not sure if im posting this in the right category; if not
apologies...

Ive created a pretty straight-forward table to capture training.
So one
of
those fields is "Seats Available" with a number such as 25.

Another field is Seats Booked - lets say that value is 20...

I want a 3rd field: Seats Left to calculate automatically :) so i
thought
i
could do that when designing the table but i cant see any wayn of
doing
it...
I know it can be done if i pull it into a query


.
Click to expand...
Click to expand...
Click to expand...
 
Two separate issues. The spurious part number is actually a part number that
can be added to any item when you need to add a part on the fly that does
not warrant adding a part to the master table. We happen to use it to add
the client's PO number to their invoice as the Description is entirely
arbritary.

The other issue is that of discounts. We do have discounts at the line item
level. My original statement was pertaining to multiple prices for the same
product for the same time period caused by the various contracts invovled
requiring the price to be shown as-is ($75) as opposed to a discounted price
($100 w/a 25% discount).

Jeff Boyce said:
Why bother introducing a spurious part number to "mean" a discount?

If you add TWO fields (DiscountType - percent or amount; Discount), you
should be able to accommodate any discount situation, record the discount
applied, and still keep a well-normalized data design...

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

David C. Holley said:
No it wasn't. Although I haven't seen that scenario per se in my
organization, there is a very common scenario where we bill at the
standard rates and then after the invoice is paid create a second invoice
to issue discounts to the client. Then there's the client who requires
that their PO Number appear as the first line item on their invoice
(independent of the PO number field in the upper right of the invoices)
which requires use of an ad hoc part number to accomplish.

...asprin anyone...

Jeff Boyce said:
David

I assume yours was not rhetorical ...

I would have a [Discount] field, since that, too, is data I may want to
know about later...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services
mentioned in
this post. Mention and/or description of a product or service herein
does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"David C. Holley" <David.C.Holley> wrote in message
Ah...but how would you handle negotiated rates on a per account basis?
Or applying a discount? There could be some scenarios where the nature
of the transaction is such that the invoice can't show that a standard
rate was discounted. For example, the client is paying $75 for an item
that normall sells for $100, but the billing has to show $75 as opposed
to $100 with a 25% discount.

message As Gina points out, in invoicing, you might want to store the
"point-in-time" unit price as part of the transaction for a
product/item, mainly because that item's price can change over time.

However, it is NOT necessary to do so, as you could design a (much
more complex) table structure that held ItemID, UnitPrice, FromDate,
and ToDate, and use this structure to track the price changes for
items over time. You would also use this to "know" what the price was
when the transaction happened (i.e., using a TransactionDate).

As pointed out elsethread, there may also be performance issues that
would make it more likely to see a stored calculated value... but if
that's the case, you might also be looking for a more robust,
server-based back-end for your data!

Best of luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services
mentioned in
this post. Mention and/or description of a product or service herein
does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Yups, thats what i thought but i thought it best to make sure as
didnt want
to make changes down the line...

One question though: you say it is "rarely necessary" - when would
it be
necessary? I mean when would you NOT put it in a query?

:

As suggested elsethread, it is rarely necessary to store a value you
can
calculate.

Use a query to return the calculated value 'on-demand'.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services
mentioned in
this post. Mention and/or description of a product or service herein
does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is",
with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Not sure if im posting this in the right category; if not
apologies...

Ive created a pretty straight-forward table to capture training.
So one
of
those fields is "Seats Available" with a number such as 25.

Another field is Seats Booked - lets say that value is 20...

I want a 3rd field: Seats Left to calculate automatically :) so i
thought
i
could do that when designing the table but i cant see any wayn of
doing
it...
I know it can be done if i pull it into a query


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top