A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Choosing a Currency Rate for 1 table based on field in another tab



 
 
Thread Tools Display Modes
  #1  
Old September 9th, 2008, 06:36 PM posted to microsoft.public.access.tablesdbdesign
Abouttime
external usenet poster
 
Posts: 2
Default Choosing a Currency Rate for 1 table based on field in another tab

Currency fields do not allow lookup values.

Each piece of equipment will have a rate attached to it.

The equipment used is chosen from "Equipment" Table as a lookup column.
The equipment used is stored in "Applications" Table.
The rate is stored in a "Pricing" Table.

Can I have the rate field in the Pricing Table choose a value based on the
input in the Appliations table?

Eg. I have a query setup that combines the information from both Tables
(Applications & Pricing). A truck is used in my "Applications" table. The
rate for a Truck is $100 (this information will be stored in another table
for a lookup column). When I am inputting data for Pricing, is there a way
that I can have it automatically select the rate for a truck based on the
value chosen in the Applications Table?
  #2  
Old September 9th, 2008, 10:06 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Choosing a Currency Rate for 1 table based on field in another tab

I have a query setup that combines the information from both Tables
Applications & Pricing).
In the query use a left join from Applications to Pricing on equipment.

Why do you not have price in the Applications table or does the price change
or different for some customers?

--
KARL DEWEY
Build a little - Test a little


"Abouttime" wrote:

Currency fields do not allow lookup values.

Each piece of equipment will have a rate attached to it.

The equipment used is chosen from "Equipment" Table as a lookup column.
The equipment used is stored in "Applications" Table.
The rate is stored in a "Pricing" Table.

Can I have the rate field in the Pricing Table choose a value based on the
input in the Appliations table?

Eg. I have a query setup that combines the information from both Tables
(Applications & Pricing). A truck is used in my "Applications" table. The
rate for a Truck is $100 (this information will be stored in another table
for a lookup column). When I am inputting data for Pricing, is there a way
that I can have it automatically select the rate for a truck based on the
value chosen in the Applications Table?

  #3  
Old September 9th, 2008, 10:28 PM posted to microsoft.public.access.tablesdbdesign
Beetle
external usenet poster
 
Posts: 1,254
Default Choosing a Currency Rate for 1 table based on field in another tab

First of all, you should not be using Lookup fields (aka combo boxes) in
tables. See this link for a discussion as to why;

http://www.mvps.org/access/lookupfields.htm

Second, why is your pricing in a separate table? Is it because each piece of
equipment can have more than one price associated with it? If that is the
case, how do you determine which of the possible prices for a piece of
equipment should be used for a given record?

On the other hand, if each piece of equipment only has one "price" then
maybe it should just be an attribute of the equipment and not in a separate
table.

Third, you should be using a form for data entry. There are several ways you
could get the pricing info from the table like DLookup or the Column property
of a combo box, but it would be helpful to know a little more about the fields
in you tables and the relationships between tables.

--
_________

Sean Bailey


"Abouttime" wrote:

Currency fields do not allow lookup values.

Each piece of equipment will have a rate attached to it.

The equipment used is chosen from "Equipment" Table as a lookup column.
The equipment used is stored in "Applications" Table.
The rate is stored in a "Pricing" Table.

Can I have the rate field in the Pricing Table choose a value based on the
input in the Appliations table?

Eg. I have a query setup that combines the information from both Tables
(Applications & Pricing). A truck is used in my "Applications" table. The
rate for a Truck is $100 (this information will be stored in another table
for a lookup column). When I am inputting data for Pricing, is there a way
that I can have it automatically select the rate for a truck based on the
value chosen in the Applications Table?

  #4  
Old September 10th, 2008, 12:04 AM posted to microsoft.public.access.tablesdbdesign
Abouttime
external usenet poster
 
Posts: 2
Default Choosing a Currency Rate for 1 table based on field in another

The pricing is in a separate table because it is information that only
certain people will have permission to view.

I will be using a form for the data entry; however, all the data will be
stored in these separate tables. Are you recommending to have the form have
lookup fields but not the Table?

What I'm hoping is to have the price come up based on the equipment chosen,
not to actually look it up.

Please let me know if there is still something I should clarify.




"Beetle" wrote:

First of all, you should not be using Lookup fields (aka combo boxes) in
tables. See this link for a discussion as to why;

http://www.mvps.org/access/lookupfields.htm

Second, why is your pricing in a separate table? Is it because each piece of
equipment can have more than one price associated with it? If that is the
case, how do you determine which of the possible prices for a piece of
equipment should be used for a given record?

On the other hand, if each piece of equipment only has one "price" then
maybe it should just be an attribute of the equipment and not in a separate
table.

Third, you should be using a form for data entry. There are several ways you
could get the pricing info from the table like DLookup or the Column property
of a combo box, but it would be helpful to know a little more about the fields
in you tables and the relationships between tables.

--
_________

Sean Bailey


"Abouttime" wrote:

Currency fields do not allow lookup values.

Each piece of equipment will have a rate attached to it.

The equipment used is chosen from "Equipment" Table as a lookup column.
The equipment used is stored in "Applications" Table.
The rate is stored in a "Pricing" Table.

Can I have the rate field in the Pricing Table choose a value based on the
input in the Appliations table?

Eg. I have a query setup that combines the information from both Tables
(Applications & Pricing). A truck is used in my "Applications" table. The
rate for a Truck is $100 (this information will be stored in another table
for a lookup column). When I am inputting data for Pricing, is there a way
that I can have it automatically select the rate for a truck based on the
value chosen in the Applications Table?

  #5  
Old September 10th, 2008, 01:04 AM posted to microsoft.public.access.tablesdbdesign
Beetle
external usenet poster
 
Posts: 1,254
Default Choosing a Currency Rate for 1 table based on field in another

Are you recommending to have the form have lookup fields but not the Table?

Yes. Combo boxes are perfectly acceptable, and commonly used, in forms but
they will cause you nothing but headaches if you use them in tables.

There are a couple of ways you could accomplish what you want. Both of these
methods assume the following;

1) You have an existing relationship between the Equipment table and the
Price table based on, for example, EquipID.

2) The text box that will be used to display the price will be *unbound*.
Text box name (for this example) = txtDisplayPrice. This text box
should be locked so the users can't overwrite it.

3) You are using a combo box in your form to select the Equipment.
Combo box name (for this example) = cboEquipment

Option 1
******

Modify the Row Source of your combo box to include the price from the
Prices table like;

Select tblEquip.EquipID, tblEquip.EquipName, tblPrices.Price From
tblEquip Inner Join tblPrices On tblEquip.EquipID = tblPrices.EquipID
Order By tblEquip.EquipName

Then set the following properties in your combo box

Bound Column = 1
Column Count = 3
Column Widths = 0",2",0"

This way the first and third columns will be hidden so the users will only
see the Equipment Name in the combo box.

Next, set the Control Source of the unbound text box (txtDisplayPrice) to;

=[cboEquipment].Column(2)

the text box will then display the value (price) that is stored in the third
column of your combo box. It is a zero based index, so the first column is
Column(0), second is Column(1), etc.

Option 2
******

Use a combo box Row Source like;

Select EquipID,EquipName From tblEquip Order By tblEquip.EquipName

combo box properties like;

Bound Column = 1
Column Count = 2
Column Widths = 0",2"

Then set the control source of txtDisplayPrice to;

=DLookup("Price", "tblPrices", "EquipID=" & [cboEquipment])

This will "Lookup" the appropriate price based on what is selected in the
combo box.

--
_________

Sean Bailey


"Abouttime" wrote:

The pricing is in a separate table because it is information that only
certain people will have permission to view.

I will be using a form for the data entry; however, all the data will be
stored in these separate tables. Are you recommending to have the form have
lookup fields but not the Table?

What I'm hoping is to have the price come up based on the equipment chosen,
not to actually look it up.

Please let me know if there is still something I should clarify.




"Beetle" wrote:

First of all, you should not be using Lookup fields (aka combo boxes) in
tables. See this link for a discussion as to why;

http://www.mvps.org/access/lookupfields.htm

Second, why is your pricing in a separate table? Is it because each piece of
equipment can have more than one price associated with it? If that is the
case, how do you determine which of the possible prices for a piece of
equipment should be used for a given record?

On the other hand, if each piece of equipment only has one "price" then
maybe it should just be an attribute of the equipment and not in a separate
table.

Third, you should be using a form for data entry. There are several ways you
could get the pricing info from the table like DLookup or the Column property
of a combo box, but it would be helpful to know a little more about the fields
in you tables and the relationships between tables.

--
_________

Sean Bailey


"Abouttime" wrote:

Currency fields do not allow lookup values.

Each piece of equipment will have a rate attached to it.

The equipment used is chosen from "Equipment" Table as a lookup column.
The equipment used is stored in "Applications" Table.
The rate is stored in a "Pricing" Table.

Can I have the rate field in the Pricing Table choose a value based on the
input in the Appliations table?

Eg. I have a query setup that combines the information from both Tables
(Applications & Pricing). A truck is used in my "Applications" table. The
rate for a Truck is $100 (this information will be stored in another table
for a lookup column). When I am inputting data for Pricing, is there a way
that I can have it automatically select the rate for a truck based on the
value chosen in the Applications Table?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.