calculating data from multiple fields

[Guest]

ok here is my problem

i have the following tables

bottle size ( which has its own keys)
item which has the field price in it and a drop down menu for the bottle size

i want to create a calculating query on price per liter
but for some reason after making the expressoin to update in a new field on
same item table it gives me incorrect results

i think its because the bottle size data is from its own table and messes
up somewhere
is there any way around this without having to delete the table bottle size
and make it agin in the item table without its own key
thanks for any replys on this matter

David

 

[Allen Browne]

You should be able to have a PricePerLitre field in your Item table, and a
BottleSize field in the Bottle table.

The query should then be able to calculate the price as:
Item.PricePerLitre * Bottle.BottleSize

Does your query generate an error?
What result do you get?
Is the BottleSize field in litres?

 

[Guest]

i dont get any other errors apart from the data being really wrong

ok here is a complete list of tables with its filelds including the sytax i
used

tables

Bottle size

ID
Bottle size

products table

many fields in it including a drop down menu comming from the bottle size
table
(note not the key filed or id from bottle table)
fields are as listed
so item id(key)
product
description
bottle size ( drop down menu from other table)
base price
price per liter

the query used is a update query

the output is going ionto the right field
here is the systax im using

Pruducts![base price]*Pruducts![Bottle size (cl)]/"100"

first line in the reply of this in liter price is well out

note first item is a 70 (cl) item
price is 17.95

so the calculation whould be 17.95*70/100 = 12.565
but it gives me 0.718

i have tried different sytax for the line of command and none work

please help its driving me mad

i thank you in advance
and willing to give out my msn messanger if u feel it will help solve this
problem

best regards

David

 

[Allen Browne]

Lose the quotes:
Pruducts![base price] * Pruducts![Bottle size (cl)] / 100

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i dont get any other errors apart from the data being really wrong

ok here is a complete list of tables with its filelds including the sytax
i
used

tables

Bottle size

ID
Bottle size

products table

many fields in it including a drop down menu comming from the bottle size
table
(note not the key filed or id from bottle table)
fields are as listed
so item id(key)
product
description
bottle size ( drop down menu from other table)
base price
price per liter

the query used is a update query

the output is going ionto the right field
here is the systax im using

Pruducts![base price]*Pruducts![Bottle size (cl)]/"100"

first line in the reply of this in liter price is well out

note first item is a 70 (cl) item
price is 17.95

so the calculation whould be 17.95*70/100 = 12.565
but it gives me 0.718

i have tried different sytax for the line of command and none work

please help its driving me mad

i thank you in advance
and willing to give out my msn messanger if u feel it will help solve this
problem

best regards

David

You should be able to have a PricePerLitre field in your Item table, and
a
BottleSize field in the Bottle table.

The query should then be able to calculate the price as:
Item.PricePerLitre * Bottle.BottleSize

Does your query generate an error?
What result do you get?
Is the BottleSize field in litres?

 

[Guest]

ok i found my problem

but still dont know the solution

problem is its calculating the auto number made for the field and not the
number of the input field

sorry for being such a noob at this and thank you for your help

just so i can define it clearly

if i take another example of the fields

the bottle in question was a 70 (cl) one
but the auto number for the key was 4

ie id 1 was 20 cl
2 was 37.50
3 was 50cl
and id 4 was 70

it does all its calculations based on the ID (key) number and not the other
field

would i have to delet the id field on the main table
or is thier a way around this without having to redo all of the DB on this
table
( over 1000 items )

best regards

David

Lose the quotes:
Pruducts![base price] * Pruducts![Bottle size (cl)] / 100

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i dont get any other errors apart from the data being really wrong

ok here is a complete list of tables with its filelds including the sytax
i
used

tables

Bottle size

ID
Bottle size

products table

many fields in it including a drop down menu comming from the bottle size
table
(note not the key filed or id from bottle table)
fields are as listed
so item id(key)
product
description
bottle size ( drop down menu from other table)
base price
price per liter

the query used is a update query

the output is going ionto the right field
here is the systax im using

Pruducts![base price]*Pruducts![Bottle size (cl)]/"100"

first line in the reply of this in liter price is well out

note first item is a 70 (cl) item
price is 17.95

so the calculation whould be 17.95*70/100 = 12.565
but it gives me 0.718

i have tried different sytax for the line of command and none work

please help its driving me mad

i thank you in advance
and willing to give out my msn messanger if u feel it will help solve this
problem

best regards

David

You should be able to have a PricePerLitre field in your Item table, and
a
BottleSize field in the Bottle table.

The query should then be able to calculate the price as:
Item.PricePerLitre * Bottle.BottleSize

Does your query generate an error?
What result do you get?
Is the BottleSize field in litres?

ok here is my problem

i have the following tables

bottle size ( which has its own keys)
item which has the field price in it and a drop down menu for the
bottle
size

i want to create a calculating query on price per liter
but for some reason after making the expressoin to update in a new
field
on
same item table it gives me incorrect results

i think its because the bottle size data is from its own table and
messes
up somewhere
is there any way around this without having to delete the table bottle
size
and make it agin in the item table without its own key
thanks for any replys on this matter

David

 

[Allen Browne]

Try creating a query that contains both tables.

If the Field row of the query, create the calculation from the 2 fields form
the 2 tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ok i found my problem

but still dont know the solution

problem is its calculating the auto number made for the field and not the
number of the input field

sorry for being such a noob at this and thank you for your help

just so i can define it clearly

if i take another example of the fields

the bottle in question was a 70 (cl) one
but the auto number for the key was 4

ie id 1 was 20 cl
2 was 37.50
3 was 50cl
and id 4 was 70

it does all its calculations based on the ID (key) number and not the
other
field

would i have to delet the id field on the main table
or is thier a way around this without having to redo all of the DB on this
table
( over 1000 items )

best regards

David

Lose the quotes:
Pruducts![base price] * Pruducts![Bottle size (cl)] / 100

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i dont get any other errors apart from the data being really wrong

ok here is a complete list of tables with its filelds including the
sytax
i
used

tables

Bottle size

ID
Bottle size

products table

many fields in it including a drop down menu comming from the bottle
size
table
(note not the key filed or id from bottle table)
fields are as listed
so item id(key)
product
description
bottle size ( drop down menu from other table)
base price
price per liter

the query used is a update query

the output is going ionto the right field
here is the systax im using

Pruducts![base price]*Pruducts![Bottle size (cl)]/"100"

first line in the reply of this in liter price is well out

note first item is a 70 (cl) item
price is 17.95

so the calculation whould be 17.95*70/100 = 12.565
but it gives me 0.718

i have tried different sytax for the line of command and none work

please help its driving me mad

i thank you in advance
and willing to give out my msn messanger if u feel it will help solve
this
problem

best regards

David






:

You should be able to have a PricePerLitre field in your Item table,
and
a
BottleSize field in the Bottle table.

The query should then be able to calculate the price as:
Item.PricePerLitre * Bottle.BottleSize

Does your query generate an error?
What result do you get?
Is the BottleSize field in litres?

ok here is my problem

i have the following tables

bottle size ( which has its own keys)
item which has the field price in it and a drop down menu for the
bottle
size

i want to create a calculating query on price per liter
but for some reason after making the expressoin to update in a new
field
on
same item table it gives me incorrect results

i think its because the bottle size data is from its own table and
messes
up somewhere
is there any way around this without having to delete the table
bottle
size
and make it agin in the item table without its own key
thanks for any replys on this matter

 

[Guest]

ok that answer completly stumpped me

maybe been baning my head on this too long

but can u explain that a little easyer as i have no idea what you ment
thanks

David

Try creating a query that contains both tables.

If the Field row of the query, create the calculation from the 2 fields form
the 2 tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ok i found my problem

but still dont know the solution

problem is its calculating the auto number made for the field and not the
number of the input field

sorry for being such a noob at this and thank you for your help

just so i can define it clearly

if i take another example of the fields

the bottle in question was a 70 (cl) one
but the auto number for the key was 4

ie id 1 was 20 cl
2 was 37.50
3 was 50cl
and id 4 was 70

it does all its calculations based on the ID (key) number and not the
other
field

would i have to delet the id field on the main table
or is thier a way around this without having to redo all of the DB on this
table
( over 1000 items )

best regards

David

Lose the quotes:
Pruducts![base price] * Pruducts![Bottle size (cl)] / 100

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i dont get any other errors apart from the data being really wrong

ok here is a complete list of tables with its filelds including the
sytax
i
used

tables

Bottle size

ID
Bottle size

products table

many fields in it including a drop down menu comming from the bottle
size
table
(note not the key filed or id from bottle table)
fields are as listed
so item id(key)
product
description
bottle size ( drop down menu from other table)
base price
price per liter

the query used is a update query

the output is going ionto the right field
here is the systax im using

Pruducts![base price]*Pruducts![Bottle size (cl)]/"100"

first line in the reply of this in liter price is well out

note first item is a 70 (cl) item
price is 17.95

so the calculation whould be 17.95*70/100 = 12.565
but it gives me 0.718

i have tried different sytax for the line of command and none work

please help its driving me mad

i thank you in advance
and willing to give out my msn messanger if u feel it will help solve
this
problem

best regards

David






:

You should be able to have a PricePerLitre field in your Item table,
and
a
BottleSize field in the Bottle table.

The query should then be able to calculate the price as:
Item.PricePerLitre * Bottle.BottleSize

Does your query generate an error?
What result do you get?
Is the BottleSize field in litres?

ok here is my problem

i have the following tables

bottle size ( which has its own keys)
item which has the field price in it and a drop down menu for the
bottle
size

i want to create a calculating query on price per liter
but for some reason after making the expressoin to update in a new
field
on
same item table it gives me incorrect results

i think its because the bottle size data is from its own table and
messes
up somewhere
is there any way around this without having to delete the table
bottle
size
and make it agin in the item table without its own key
thanks for any replys on this matter

 

[John Spencer (MVP)]

Pardon me for jumping in.


SELECT Products.Product, [Base Price]*[Bottle Size].[Bottle Size]/100 as cost
FROM Products INNER JOIN [Bottle Size]
ON Products.[bottle Size] = [Bottle Size].ID

To get the above, add both your tables to the query grid.
Click on Products.Bottle Size and drag to Bottle Size ID

In a new column, set up the following calculation:

Cost: Products.[Base Price]*[Bottle Size].[Bottle Size]/100

ok that answer completly stumpped me

maybe been baning my head on this too long

but can u explain that a little easyer as i have no idea what you ment
thanks

David

Try creating a query that contains both tables.

If the Field row of the query, create the calculation from the 2 fields form
the 2 tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ok i found my problem

but still dont know the solution

problem is its calculating the auto number made for the field and not the
number of the input field

sorry for being such a noob at this and thank you for your help

just so i can define it clearly

if i take another example of the fields

the bottle in question was a 70 (cl) one
but the auto number for the key was 4

ie id 1 was 20 cl
2 was 37.50
3 was 50cl
and id 4 was 70

it does all its calculations based on the ID (key) number and not the
other
field

would i have to delet the id field on the main table
or is thier a way around this without having to redo all of the DB on this
table
( over 1000 items )

best regards

David

:

Lose the quotes:
Pruducts![base price] * Pruducts![Bottle size (cl)] / 100

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i dont get any other errors apart from the data being really wrong

ok here is a complete list of tables with its filelds including the
sytax
i
used

tables

Bottle size

ID
Bottle size

products table

many fields in it including a drop down menu comming from the bottle
size
table
(note not the key filed or id from bottle table)
fields are as listed
so item id(key)
product
description
bottle size ( drop down menu from other table)
base price
price per liter

the query used is a update query

the output is going ionto the right field
here is the systax im using

Pruducts![base price]*Pruducts![Bottle size (cl)]/"100"

first line in the reply of this in liter price is well out

note first item is a 70 (cl) item
price is 17.95

so the calculation whould be 17.95*70/100 = 12.565
but it gives me 0.718

i have tried different sytax for the line of command and none work

please help its driving me mad

i thank you in advance
and willing to give out my msn messanger if u feel it will help solve
this
problem

best regards

David






:

You should be able to have a PricePerLitre field in your Item table,
and
a
BottleSize field in the Bottle table.

The query should then be able to calculate the price as:
Item.PricePerLitre * Bottle.BottleSize

Does your query generate an error?
What result do you get?
Is the BottleSize field in litres?

ok here is my problem

i have the following tables

bottle size ( which has its own keys)
item which has the field price in it and a drop down menu for the
bottle
size

i want to create a calculating query on price per liter
but for some reason after making the expressoin to update in a new
field
on
same item table it gives me incorrect results

i think its because the bottle size data is from its own table and
messes
up somewhere
is there any way around this without having to delete the table
bottle
size
and make it agin in the item table without its own key
thanks for any replys on this matter

 

[Guest]

thanks for jumping in john

i havnt tried it yet

i did find out what the problem was and been looking at other ways around it

problem being it took the ID or first field which was or is the Key so its
auto numbered 1 to whatever instead of taking the 2nd field info

if it dosnt work i have had 3 ideas on how to solve this problem
and 2 of them involve redoing the tables ( which i dont really want to do if
i can help it

thanks to both of you for being understanding in trying to help a complet
newbie at access

best regards

David

Pardon me for jumping in.


SELECT Products.Product, [Base Price]*[Bottle Size].[Bottle Size]/100 as cost
FROM Products INNER JOIN [Bottle Size]
ON Products.[bottle Size] = [Bottle Size].ID

To get the above, add both your tables to the query grid.
Click on Products.Bottle Size and drag to Bottle Size ID

In a new column, set up the following calculation:

Cost: Products.[Base Price]*[Bottle Size].[Bottle Size]/100

ok that answer completly stumpped me

maybe been baning my head on this too long

but can u explain that a little easyer as i have no idea what you ment
thanks

David

Try creating a query that contains both tables.

If the Field row of the query, create the calculation from the 2 fields form
the 2 tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ok i found my problem

but still dont know the solution

problem is its calculating the auto number made for the field and not the
number of the input field

sorry for being such a noob at this and thank you for your help

just so i can define it clearly

if i take another example of the fields

the bottle in question was a 70 (cl) one
but the auto number for the key was 4

ie id 1 was 20 cl
2 was 37.50
3 was 50cl
and id 4 was 70

it does all its calculations based on the ID (key) number and not the
other
field

would i have to delet the id field on the main table
or is thier a way around this without having to redo all of the DB on this
table
( over 1000 items )

best regards

David

:

Lose the quotes:
Pruducts![base price] * Pruducts![Bottle size (cl)] / 100

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i dont get any other errors apart from the data being really wrong

ok here is a complete list of tables with its filelds including the
sytax
i
used

tables

Bottle size

ID
Bottle size

products table

many fields in it including a drop down menu comming from the bottle
size
table
(note not the key filed or id from bottle table)
fields are as listed
so item id(key)
product
description
bottle size ( drop down menu from other table)
base price
price per liter

the query used is a update query

the output is going ionto the right field
here is the systax im using

Pruducts![base price]*Pruducts![Bottle size (cl)]/"100"

first line in the reply of this in liter price is well out

note first item is a 70 (cl) item
price is 17.95

so the calculation whould be 17.95*70/100 = 12.565
but it gives me 0.718

i have tried different sytax for the line of command and none work

please help its driving me mad

i thank you in advance
and willing to give out my msn messanger if u feel it will help solve
this
problem

best regards

David






:

You should be able to have a PricePerLitre field in your Item table,
and
a
BottleSize field in the Bottle table.

The query should then be able to calculate the price as:
Item.PricePerLitre * Bottle.BottleSize

Does your query generate an error?
What result do you get?
Is the BottleSize field in litres?

ok here is my problem

i have the following tables

bottle size ( which has its own keys)
item which has the field price in it and a drop down menu for the
bottle
size

i want to create a calculating query on price per liter
but for some reason after making the expressoin to update in a new
field
on
same item table it gives me incorrect results

i think its because the bottle size data is from its own table and
messes
up somewhere
is there any way around this without having to delete the table
bottle
size
and make it agin in the item table without its own key
thanks for any replys on this matter