Formula in Table

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
0
Utf
1/3/2010 7:12:02 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

14 Replies
1215 Views

Similar Articles

[PageSpeed] 51

On Jan 3, 2:12=A0pm, ant1983 <ant1...@discussions.microsoft.com> wrote:
> Not sure if im posting this in the right category; if not apologies...
>
> Ive created a pretty straight-forward table to capture training. =A0So on=
e 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: =A0Seats Left to calculate automatically :) so i thou=
ght i
> could do that when designing the table but i cant see any wayn of doing i=
t...
> =A0I know it can be done if i pull it into a query

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!
0
pietlinden
1/3/2010 7:23:52 PM
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.

"ant1983" <ant1983@discussions.microsoft.com> wrote in message 
news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
> 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 


0
Jeff
1/3/2010 8:11:12 PM
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?

"Jeff Boyce" wrote:

> 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.
> 
> "ant1983" <ant1983@discussions.microsoft.com> wrote in message 
> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
> > 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 
> 
> 
> .
> 
0
Utf
1/3/2010 9:21:01 PM
On Sun, 3 Jan 2010 11:12:02 -0800, ant1983 <ant1983@discussions.microsoft.com>
wrote:

>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

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

             John W. Vinson [MVP]
0
John
1/3/2010 9:34:04 PM
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" <ant1983@discussions.microsoft.com> wrote in message 
news:1217964E-2FBC-4CF2-A086-E3CF454D1798@microsoft.com...
> 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?
>
> "Jeff Boyce" wrote:
>
>> 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.
>>
>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message
>> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
>> > 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
>>
>>
>> .
>> 


0
David
1/3/2010 9:51:43 PM
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

"ant1983" <ant1983@discussions.microsoft.com> wrote in message 
news:1217964E-2FBC-4CF2-A086-E3CF454D1798@microsoft.com...
> 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?
>
> "Jeff Boyce" wrote:
>
>> 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.
>>
>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message
>> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
>> > 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
>>
>>
>> .
>> 


0
Gina
1/3/2010 9:55:54 PM
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.

"ant1983" <ant1983@discussions.microsoft.com> wrote in message 
news:1217964E-2FBC-4CF2-A086-E3CF454D1798@microsoft.com...
> 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?
>
> "Jeff Boyce" wrote:
>
>> 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.
>>
>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message
>> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
>> > 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
>>
>>
>> .
>> 


0
Jeff
1/3/2010 11:38:21 PM
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.

"Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in message 
news:ObPjW3MjKHA.1824@TK2MSFTNGP04.phx.gbl...
> 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.
>
> "ant1983" <ant1983@discussions.microsoft.com> wrote in message 
> news:1217964E-2FBC-4CF2-A086-E3CF454D1798@microsoft.com...
>> 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?
>>
>> "Jeff Boyce" wrote:
>>
>>> 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.
>>>
>>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message
>>> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
>>> > 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
>>>
>>>
>>> .
>>>
>
> 


0
David
1/4/2010 3:30:44 AM
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...

"John W. Vinson" wrote:

> On Sun, 3 Jan 2010 11:12:02 -0800, ant1983 <ant1983@discussions.microsoft.com>
> wrote:
> 
> >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
> 
> 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.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/4/2010 8:16:01 AM
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 
news:uZDlM5OjKHA.6096@TK2MSFTNGP02.phx.gbl...
> 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.
>
> "Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in message 
> news:ObPjW3MjKHA.1824@TK2MSFTNGP04.phx.gbl...
>> 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.
>>
>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message 
>> news:1217964E-2FBC-4CF2-A086-E3CF454D1798@microsoft.com...
>>> 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?
>>>
>>> "Jeff Boyce" wrote:
>>>
>>>> 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.
>>>>
>>>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message
>>>> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
>>>> > 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
>>>>
>>>>
>>>> .
>>>>
>>
>>
>
> 


0
Jeff
1/4/2010 1:39:28 PM
Specific information is almost always helpful.  For what exactly does the
parameter prompt ask?  BTW, I have to assume dteBookingDate is not a date
field, despite what the name suggests, as subtracting a date from a number is
unlikely to give you the correct number of remaining seats, if that is what
you are trying to do with that expression.

ant1983 wrote:
>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...
>
>> >Not sure if im posting this in the right category; if not apologies...
>> >
>[quoted text clipped - 11 lines]
>> Tables are for data storage. They are NOT intended for data display or
>> editing; normally users should never even *see* table datasheets, only forms.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201001/1

0
BruceM
1/4/2010 1:40:43 PM
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" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in message 
news:%23pzBXNUjKHA.2780@TK2MSFTNGP05.phx.gbl...
> 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 
> news:uZDlM5OjKHA.6096@TK2MSFTNGP02.phx.gbl...
>> 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.
>>
>> "Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in 
>> message news:ObPjW3MjKHA.1824@TK2MSFTNGP04.phx.gbl...
>>> 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.
>>>
>>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message 
>>> news:1217964E-2FBC-4CF2-A086-E3CF454D1798@microsoft.com...
>>>> 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?
>>>>
>>>> "Jeff Boyce" wrote:
>>>>
>>>>> 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.
>>>>>
>>>>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message
>>>>> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
>>>>> > 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
>>>>>
>>>>>
>>>>> .
>>>>>
>>>
>>>
>>
>>
>
> 


0
David
1/4/2010 2:07:54 PM
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" <David.C.Holley> wrote in message 
news:%23BTePdUjKHA.1420@TK2MSFTNGP05.phx.gbl...
> 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" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in message 
> news:%23pzBXNUjKHA.2780@TK2MSFTNGP05.phx.gbl...
>> 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 
>> news:uZDlM5OjKHA.6096@TK2MSFTNGP02.phx.gbl...
>>> 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.
>>>
>>> "Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in 
>>> message news:ObPjW3MjKHA.1824@TK2MSFTNGP04.phx.gbl...
>>>> 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.
>>>>
>>>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message 
>>>> news:1217964E-2FBC-4CF2-A086-E3CF454D1798@microsoft.com...
>>>>> 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?
>>>>>
>>>>> "Jeff Boyce" wrote:
>>>>>
>>>>>> 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.
>>>>>>
>>>>>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message
>>>>>> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
>>>>>> > 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
>>>>>>
>>>>>>
>>>>>> .
>>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Jeff
1/4/2010 3:02:46 PM
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" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in message 
news:OjAH67UjKHA.1652@TK2MSFTNGP05.phx.gbl...
> 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" <David.C.Holley> wrote in message 
> news:%23BTePdUjKHA.1420@TK2MSFTNGP05.phx.gbl...
>> 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" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in 
>> message news:%23pzBXNUjKHA.2780@TK2MSFTNGP05.phx.gbl...
>>> 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 
>>> news:uZDlM5OjKHA.6096@TK2MSFTNGP02.phx.gbl...
>>>> 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.
>>>>
>>>> "Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD-HYPHEN-TO-END> wrote in 
>>>> message news:ObPjW3MjKHA.1824@TK2MSFTNGP04.phx.gbl...
>>>>> 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.
>>>>>
>>>>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message 
>>>>> news:1217964E-2FBC-4CF2-A086-E3CF454D1798@microsoft.com...
>>>>>> 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?
>>>>>>
>>>>>> "Jeff Boyce" wrote:
>>>>>>
>>>>>>> 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.
>>>>>>>
>>>>>>> "ant1983" <ant1983@discussions.microsoft.com> wrote in message
>>>>>>> news:7D638834-641D-4D93-885A-E061EAD562AF@microsoft.com...
>>>>>>> > 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
>>>>>>>
>>>>>>>
>>>>>>> .
>>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
David
1/4/2010 11:42:01 PM
Reply:

Similar Artilces:

Formula calculating fulltime/parttime vs employees.
I have a spreadsheet listing employees jobs in one column. Another column lists if they are full time or part time. There are several employees with the same job but work different times. I need a formula to calculate how many people with that title work full time and how many people with the same job work part time. A pivot table will do a very nice job for you. They are very powerful once you get to know them. Take a look at Chip Pearson's site for a tutorial on them: http://www.cpearson.com/excel/pivots.htm -- Regards, Fred "VP" <VP@discussions.microsoft.com&...

Account balance table?
Does anyone know if there is a table in Great Plains that contains the life-to-date balance for balance sheet accounts? I can obtain the debits and credits for balance sheet accounts which gives me the activity for a period, but what I really need is a simple way to pull the life-to-date balances. For example, if I'm in period 5 of 2004, I need to pull all my balance sheet account balances at that point in time. Thanks ...

Excel Formula for determining azimuth relative to true north between two sets of geographic coordinates?
Using the geographic coordinates for each, I've "translated" the FCC procedure for determining the distance between two transmitters ( per FCC 73.208) into Excel formulas. Excel gives identical distance results to the "fill-in" form provided in www.fcc.gov/mb/audio/bickel/distance.html . But there is no comparable formula in the FCC R&R for determining the bearing (azimuth) between the stations relative to true north, although the FCC's fill-in form does provide azimuth. My problem with using the fill-in form is that I have hundreds of computations to make and...

Table headings in Excel
Hi, How do I get table headings to repeat at top of each page? Thanks, Allie Hello, try File - page setup - sheet then enter your range in 'rows to repeat at top' Suddes "Allie" wrote: > Hi, > > How do I get table headings to repeat at top of each page? > > Thanks, > Allie Suddes' response is correct for printing. If you're wanting them to display as you scroll down, you can split/freeze the panes. >-----Original Message----- >Hi, > >How do I get table headings to repeat at top of each page? > >Thanks, >Allie >. &...

Table / Column descriptions
Is there ANYWHERE that you can find what the column names in each table represent ? Such as there is a column in the SOP10200 table that is PURCHSTAT. Some of them are a 1 and some are a 2. What does this represent ? Where can we find out a detailed list of what these columns are in the tables ? Why does MS not provide this ? Is it a secret or is it just because they dont even know ? This is VERY frustrating in trying to troubleshoot Great Plains .... or shoud I say Great PAINS !! Accolade has been the best so far, just not as detailed as one would need for accurate tro...

HOW DO I ENTER DATA WHEN I SEEM TO BE STUCK IN A SUMMATION FORMULA
WHEN I OPEN MY SIMPLE SPREAD SHEET AND ATTEMPT TO FILL ONE CELL I AM STUCK THERE AND AS I TRY TO MOVE AROUND I GET THE BLUE SHADING AS IF I WERE DOING A SUMMATION. I CAN'T GET RID OF THE SHADING OR GET IT TO ENTER DATA? -- K J PETERSON hi typing with all caps is considered to be shouting and therefore impolite. press the F8 key once and see what happens. Regards FSt1 "kenneth j peterson" wrote: > WHEN I OPEN MY SIMPLE SPREAD SHEET AND ATTEMPT TO FILL ONE CELL I AM STUCK > THERE AND AS I TRY TO MOVE AROUND I GET THE BLUE SHADING AS IF I WERE DOING A > SUMMATION....

Pivot Table Drop Down Boxes
I want to be able to limit the drop down box content to information relevant to what I have seleced on the page. Example: I select Region A at the page selection, then open the District drop down box, and all districts countrywide are shown. I only want to see Districts to choose from in Region A. Excel 2003 - Windows XP That feature isn't available in the pivot table dropdown lists. Dave S wrote: > I want to be able to limit the drop down box content to information relevant > to what I have seleced on the page. Example: I select Region A at the page > selection, then ...

Macro to delete specific data in tables
Hi: I am an analyst working in service quality for a market research firm. We use SPSS 12 to output the data and later copy paste the tables into Excel for formatting and printing. The problem I currently have is that I got a large group of output tables which come in two different formats. The first table is like this: Satisfacción Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 Más de 100.001 UF ...

Formulas within Cell References
Hello All, I need to use a number that was calculated from a formula in a cel reference. Here is the situation: I have a large spreadsheet tha changes periodically. I need to count the number of items in tha spreadsheet(i will use COUNTA - already works) and then take tha number and use it as a row number in a cell reference like this: In cell H1: =COUNTA(A2:A9999) Then take that number in cell H1 and insert that into say $J$XXXX wher XXXX represents the number in cell H1. I'm looking to increase the calculation speed of the workbook by onl calculating cells that I need to. Thanks f...

Combining Tables
Hello, I have 5 files in excel. These files will have the same fields, but the information will change in them every week. I am then going to import the the excel files into access into my five tables that I have created. What I need is from all those 5 five tables to be combined into one big table. I want to keep all the same fields, just a combination of the five. I have to do some modifications on that on that huge file. But my main concern is how to combine all that data into one huge file. Thanks It sounds as though you are expecting Access to be Excel ("same...

Access 2003 linked tables causes read-only
Hi A weird one - I have an Access 2k mdb set with linked tables. Ive opened the set under Access 2003. If the link is to the c: drive (where the functions are) all is OK, but if I put the mdb with the tables in on a net drive (eg X:\.....) the tables become read only and I can find how to stop this.. Any help anyone, please???!!?? thanks Jim McDonald What permissions do you have on the folder where the mdb has been placed? You must have Change permissions (Read, Write, eXecute and Delete) on the folder, not just the file. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele ...

Custom Fields/Formulas Receiving an error "NA" in Project Center
Hello All, Was wondering if I could get some help, we are running project server 2007 latest edition with all patches. And are running a custom formula. "IIf([Milestone]=Yes,"Milestone",IIf([PPC_Result]>=1,"100.00%",IIf([Baseline Start]=ProjDateValue("NA") Or [Baseline Finish]=ProjDateValue("NA") Or [Baseline Duration]=ProjDateValue("NA"),"No Baseline",IIf([Baseline Start]>Now(),"Baseline start in future",Format(([PPC_NUM]/[Baseline Duration]),"percent")))))" The formula runs off a ...

New To Pivot Table
I have the following sales data: dept, category, description, Qty sold, sold price, total sale, date sold, cost,profit,prfit margin. the following is an example: beer 12 pks,miller lite, 2, 7.99, 15.98, 7/1/05, 13.98, 2, 14% The 12 pks represents the ctegory, miller lite represents the description, and soforth. I have about 15 different departments, I would like to setup a pivot table to track and compare the data per week, month, quarters and year, for example: total sales, profit, cost, profit margin for the first week on july for each department compared with the same da...

Formula to calculate interest only on a short term loan?
I am not familiar with Excel 2007 and need to calculate a bridge loan MONTHLY INTEREST ONLY scenario, with a fixed %rate, for a range of 1 -6 months. Not sure if if interest accrues daily or monthly. Assuming you have an annual interest rate, a month's interest is: =Principal*IntRate/12 This may be good enough for your purposes. Most financial institutions would calculate the interest based on the number of days in the month, so something like: =Principal*IntRate/365*day(eomonth(a1,0)) Regards, Fred. "Excel2007Help" <Excel2007Help@discussions.microsoft.c...

Filtered Records as source for appending records to a table
I have an inventory database with a form to create multiple items with the same product information at once, it goes to a second form where the individual serial numbers can be entered. The second form shows the filtered recordset of items just created. I want to be able to press a button on the second form and have a transaction created in my transactiontbl using each of the itemids in the filtered recordset. The new transaction records should be as follows... TransactionTbl TransactionID -> Autonumber Transdate -> Date () ItemID -> Number (foreign Key itemid fr...

Can You Embed An Equation in a PP Table 2007
I've got a table of financial data in PowerPoint (instead of an Excel worksheet - I know!). Is it possible to put in a simple equation to add the column similar to Word 2007 tables? Thanks! (B^>)-]=[ No, sorry. PPT tables don't have any kind of functions like this at all. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "WSR" <wsr-203@hotmail.com> wrote in...

Scroll Through Pivot Table Entries
XL 2007 XP I have a pivot table chart. The chart is setup to show only one entry at at time. I have with many different entries, 197 total, in the Report Filter, however, as mentioned above I only want it to display one entry at at time. Is there a way to create a scroll bar from the Forms toolbar and use that so that it causes the chart to scroll through each entry one at a time? Thanks ...

Word randomly changing my figures and tables captions
I'm trying to format a large document, with over 150 tables and figures in it. I've used captions to label them all, but I'm now finding that Word is randomly changing some that I have specified as 'table' to 'figure' and vice versa. This particularly seems to be happening with tables and figures that appear on the same page, but is not restricted to these instances. As soon as I change one caption back to the correct type, the one below will change itself to the wrong type again! I need the captions to remain as I've entered them for my table of f...

How can I delete rows from Pivot Tables in Excel 2000 as in 97
I was able to delete multiple unwanted rows from Excel 97 Pivot Tables. Now I find I can only delete rows individually by untagging them in Excel 2000 Pivot Table. This is far too time consuming... Does anyone have a faster Solution ...

Table Help
Hello Everyone, I know I have seen this problem before and that I have corrected it in the past, but for some reason I can't figure it out this time or find something similar online. The problem is simply, when I view a certain table one of its columns is completely filled in with "##########". The data is still there because when I click on it the data appears, and the data is visible in a justified form. It can't stay like this but I can't figure how I did it in the first place or how to change it back. Any help would be greatly appreciated. Thank You Ko...

Excel displays formulae, not results
I have inherited a spread sheet that has a number of formulae in it. However when I type in a new formula, it displays it as the formula, not as the result. This is not applicable to all cells, just some of them. I am now reduced to copying a formula that displays the result, then editing it to give the formula that I actually want. Obviously there is some setting in the individual cells that governs how it displays as well as the general setting. Hi Roger, This happens when a cell is formatted as text the moment -- Kind Regards, Niek Otten Microsoft MVP - Excel you enter the formula. C...

span a table to multiple pages
Hi, I have a very long table with 300+ rows. Instead of adding a new table to every pages, is there a command to "span" the one table to multiple pages? Thanks! Not in Publisher. Publisher only allows 128 rows per page. Word has this capability. -- Mary Sauer http://msauer.mvps.org/ "Tom" <nospam@yahoo.com> wrote in message news:u1WmV9bHJHA.1940@TK2MSFTNGP03.phx.gbl... > Hi, > > I have a very long table with 300+ rows. Instead of adding a new table to > every pages, is there a command to "span" the one table to multiple pages? >...

Update query result to table
I can't find a good example on the web showing how to update table with the result from query. Table1 name "Agent1" column have: phone#(primary key), birthdate, and agent (total record 100). table2 name "Agent 2" have the same field (total record 150) except the data on agent is different. I run a query for this two tables and want to update the table1 with the table2 data where the phone#(primary Key). I want the result form the query will replace the agent in Agent1 table. How can i do that? UPDATE Table1 INNER JOIN Table2 ON Table1.[Phone#] = Table2.[Phone#]...

Custom addition formula
Using Office2000 Sp3 I use Excel a lot for historical things including eg; areas of land expressed in ACRES ROODS PERCHES which is not added in tens (similar to �/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a formula which when applied to entries across 3 columns gives a decimal answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in D1, Roods E1, perches F1. Once this is decimalised I can work out ratios etc. However, I occasionally get tabbed text files or excel files from people who have entered lots of raw data as eg ARP where it would be ...

Shipping amount field in SQL table.
Good Morning To All, I have a customer who is having one of their internal programmers write an interface that will update GL accounts according to their finance department's needs. They are very specific. One of the things they need is the shipping amounts at a transaction level. I cannot find these amounts anywhere. I read somewhere else that in order for this amounts to be populated the ship to and the shipping carrier had to be filled in on the transaction. I tested it, and I still can't see the totals for the shipping charges. Does anyone know anything about this? THANKS! ...