multiply a certain field in all query by same value

P

Pedro

Hello everyone
I must tell you i'm not an expert
But i would like to put in all my querys a certain field that multiplies by
0,06 but i would like to change someday and i would prefer to do it, just in
one place.
What's the best advise you can give. But don't forget i'm not an expert.
Explain me like if i were 10.
Tks in advance
Pedro
 
K

Ken Snell \(MVP\)

Create a new table, name it tblMultiplierValue. Put one field in the table,
name it fldMultiplierValue; make it a Double Numeric data type. Save the
table, and then open it in datasheet view. Add one record to the table,
using the value that you want as the multiplier -- in this case, 0.06 (or
0,06 for your settings).

Now open in design view a query that should use this mutliplier value. Add
this new table to the query; do not create any joins between this table and
the other table(s) in the query. Add a calculated field to the query:
MyMultipliedValue: tblMulplierValue.fldMultiplierValue * YourFieldName

Save the query. It now will show the field that you wish.
 
P

Pedro

Ken, tks lot for reply my answer. I've done all you ask me and then, when i
add that table to that qry it shows me an error message. SQL :

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio], [Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome], [Tabela de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor], [Tabela de
dados].Comissionavel, IIf([Comissionavel]<0,[Margem Adjudicado]*TCV!CV,0) AS
[Comis VD]
FROM TCV, ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de dados].[Tipo de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_1] ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_2] ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_3] ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));

The table in question is called TCV and the field is CV.

Any ideas?
Tks again
Pedro
 
K

Ken Snell \(MVP\)

Change
TCV!CV

to
TCV.CV
--

Ken Snell
<MS ACCESS MVP>


Pedro said:
Ken, tks lot for reply my answer. I've done all you ask me and then, when
i
add that table to that qry it shows me an error message. SQL :

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio],
[Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome], [Tabela de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor], [Tabela
de
dados].Comissionavel, IIf([Comissionavel]<0,[Margem Adjudicado]*TCV!CV,0)
AS
[Comis VD]
FROM TCV, ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de dados].[Tipo de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_1] ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_2] ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de
Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_3] ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));

The table in question is called TCV and the field is CV.

Any ideas?
Tks again
Pedro


Ken Snell (MVP) said:
Create a new table, name it tblMultiplierValue. Put one field in the
table,
name it fldMultiplierValue; make it a Double Numeric data type. Save the
table, and then open it in datasheet view. Add one record to the table,
using the value that you want as the multiplier -- in this case, 0.06 (or
0,06 for your settings).

Now open in design view a query that should use this mutliplier value.
Add
this new table to the query; do not create any joins between this table
and
the other table(s) in the query. Add a calculated field to the query:
MyMultipliedValue: tblMulplierValue.fldMultiplierValue *
YourFieldName

Save the query. It now will show the field that you wish.
Click to expand...
Click to expand...
 
P

Pedro

Still not working ...


Ken Snell (MVP) said:
Change
TCV!CV

to
TCV.CV
--

Ken Snell
<MS ACCESS MVP>


Pedro said:
Ken, tks lot for reply my answer. I've done all you ask me and then, when
i
add that table to that qry it shows me an error message. SQL :

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio],
[Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome], [Tabela de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor], [Tabela
de
dados].Comissionavel, IIf([Comissionavel]<0,[Margem Adjudicado]*TCV!CV,0)
AS
[Comis VD]
FROM TCV, ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de dados].[Tipo de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_1] ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_2] ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de
Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_3] ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));

The table in question is called TCV and the field is CV.

Any ideas?
Tks again
Pedro


Ken Snell (MVP) said:
Create a new table, name it tblMultiplierValue. Put one field in the
table,
name it fldMultiplierValue; make it a Double Numeric data type. Save the
table, and then open it in datasheet view. Add one record to the table,
using the value that you want as the multiplier -- in this case, 0.06 (or
0,06 for your settings).

Now open in design view a query that should use this mutliplier value.
Add
this new table to the query; do not create any joins between this table
and
the other table(s) in the query. Add a calculated field to the query:
MyMultipliedValue: tblMulplierValue.fldMultiplierValue *
YourFieldName

Save the query. It now will show the field that you wish.
--

Ken Snell
<MS ACCESS MVP>


Hello everyone
I must tell you i'm not an expert
But i would like to put in all my querys a certain field that
multiplies
by
0,06 but i would like to change someday and i would prefer to do it,
just
in
one place.
What's the best advise you can give. But don't forget i'm not an
expert.
Explain me like if i were 10.
Tks in advance
Pedro
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

What error message do you see?

--

Ken Snell
<MS ACCESS MVP>


Pedro said:
Still not working ...


Ken Snell (MVP) said:
Change
TCV!CV

to
TCV.CV
--

Ken Snell
<MS ACCESS MVP>


Pedro said:
Ken, tks lot for reply my answer. I've done all you ask me and then,
when
i
add that table to that qry it shows me an error message. SQL :

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio],
[Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome], [Tabela
de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor],
[Tabela
de
dados].Comissionavel, IIf([Comissionavel]<0,[Margem
Adjudicado]*TCV!CV,0)
AS
[Comis VD]
FROM TCV, ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT
JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de dados].[Tipo
de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_1]
ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de
Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_2]
ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de
Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_3]
ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));

The table in question is called TCV and the field is CV.

Any ideas?
Tks again
Pedro


:

Create a new table, name it tblMultiplierValue. Put one field in the
table,
name it fldMultiplierValue; make it a Double Numeric data type. Save
the
table, and then open it in datasheet view. Add one record to the
table,
using the value that you want as the multiplier -- in this case, 0.06
(or
0,06 for your settings).

Now open in design view a query that should use this mutliplier value.
Add
this new table to the query; do not create any joins between this
table
and
the other table(s) in the query. Add a calculated field to the query:
MyMultipliedValue: tblMulplierValue.fldMultiplierValue *
YourFieldName

Save the query. It now will show the field that you wish.
--

Ken Snell
<MS ACCESS MVP>


Hello everyone
I must tell you i'm not an expert
But i would like to put in all my querys a certain field that
multiplies
by
0,06 but i would like to change someday and i would prefer to do it,
just
in
one place.
What's the best advise you can give. But don't forget i'm not an
expert.
Explain me like if i were 10.
Tks in advance
Pedro
Click to expand...
Click to expand...
Click to expand...
 
P

Pedro

something about "have to relation this table with the others ..."

Ken Snell (MVP) said:
What error message do you see?

--

Ken Snell
<MS ACCESS MVP>


Pedro said:
Still not working ...


Ken Snell (MVP) said:
Change
TCV!CV

to
TCV.CV
--

Ken Snell
<MS ACCESS MVP>


Ken, tks lot for reply my answer. I've done all you ask me and then,
when
i
add that table to that qry it shows me an error message. SQL :

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio],
[Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome], [Tabela
de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor],
[Tabela
de
dados].Comissionavel, IIf([Comissionavel]<0,[Margem
Adjudicado]*TCV!CV,0)
AS
[Comis VD]
FROM TCV, ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT
JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de dados].[Tipo
de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_1]
ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de
Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_2]
ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de
Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_3]
ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));

The table in question is called TCV and the field is CV.

Any ideas?
Tks again
Pedro


:

Create a new table, name it tblMultiplierValue. Put one field in the
table,
name it fldMultiplierValue; make it a Double Numeric data type. Save
the
table, and then open it in datasheet view. Add one record to the
table,
using the value that you want as the multiplier -- in this case, 0.06
(or
0,06 for your settings).

Now open in design view a query that should use this mutliplier value.
Add
this new table to the query; do not create any joins between this
table
and
the other table(s) in the query. Add a calculated field to the query:
MyMultipliedValue: tblMulplierValue.fldMultiplierValue *
YourFieldName

Save the query. It now will show the field that you wish.
--

Ken Snell
<MS ACCESS MVP>


Hello everyone
I must tell you i'm not an expert
But i would like to put in all my querys a certain field that
multiplies
by
0,06 but i would like to change someday and i would prefer to do it,
just
in
one place.
What's the best advise you can give. But don't forget i'm not an
expert.
Explain me like if i were 10.
Tks in advance
Pedro
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

My apology. We'll need to use two separate queries to do this.

The first query needs to be your original query, with a slight change. Use
this SQL statement, and name the query qryOriginal (note: you need to add
the correct table name into the query for the [Margem Adjudicado] field):

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio], [Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome], [Tabela de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor], [Tabela de
dados].Comissionavel, [PUT_CORRECT_TABLENAME_HERE].[Margem Adjudicado]
FROM ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de dados].[Tipo de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_1] ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_2] ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_3] ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));



Then create a second query, name it qryResult, and use this SQL statement
for it:

SELECT qryResult.Data, qryResult.Tipo de Negócio], qryResult.Vendedor,
qryResult.[Prémio de Gestão Nome], qryResult.[Prémio de Gestão Valor],
qryResult.[Prémio dePreparação Nome], qryResult.[Prémio de Preparação
Valor],
qryResult.Gestor, qryResult.[Premio Gestor], qryResult.Comissionavel,
IIf(qryResult.[Comissionavel]<0,qryResult.[Margem Adjudicado]*TCV.CV,0) AS
[Comis VD]
FROM TCV, qryResult;



--

Ken Snell
<MS ACCESS MVP>




Pedro said:
something about "have to relation this table with the others ..."

Ken Snell (MVP) said:
What error message do you see?

--

Ken Snell
<MS ACCESS MVP>


Pedro said:
Still not working ...


:

Change
TCV!CV

to
TCV.CV
--

Ken Snell
<MS ACCESS MVP>


Ken, tks lot for reply my answer. I've done all you ask me and then,
when
i
add that table to that qry it shows me an error message. SQL :

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio],
[Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome],
[Tabela
de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio
dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor],
[Tabela
de
dados].Comissionavel, IIf([Comissionavel]<0,[Margem
Adjudicado]*TCV!CV,0)
AS
[Comis VD]
FROM TCV, ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT
JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela
de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de
dados].[Tipo
de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de
Vendedores_1]
ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de
Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de
Vendedores_2]
ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de
Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de
Vendedores_3]
ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));

The table in question is called TCV and the field is CV.

Any ideas?
Tks again
Pedro


:

Create a new table, name it tblMultiplierValue. Put one field in
the
table,
name it fldMultiplierValue; make it a Double Numeric data type.
Save
the
table, and then open it in datasheet view. Add one record to the
table,
using the value that you want as the multiplier -- in this case,
0.06
(or
0,06 for your settings).

Now open in design view a query that should use this mutliplier
value.
Add
this new table to the query; do not create any joins between this
table
and
the other table(s) in the query. Add a calculated field to the
query:
MyMultipliedValue: tblMulplierValue.fldMultiplierValue *
YourFieldName

Save the query. It now will show the field that you wish.
--

Ken Snell
<MS ACCESS MVP>


Hello everyone
I must tell you i'm not an expert
But i would like to put in all my querys a certain field that
multiplies
by
0,06 but i would like to change someday and i would prefer to do
it,
just
in
one place.
What's the best advise you can give. But don't forget i'm not an
expert.
Explain me like if i were 10.
Tks in advance
Pedro
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Another approach would be to use the DLookup function to go get the
multiplier value (but note that this will slow down the query a bit):

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio], [Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome], [Tabela de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor], [Tabela de
dados].Comissionavel, IIf([Comissionavel]<0,[Margem Adjudicado]*
DLookup("CV","TCV"),0) AS [Comis VD]
FROM ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de dados].[Tipo de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_1] ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_2] ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_3] ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));

--

Ken Snell
<MS ACCESS MVP>




Ken Snell (MVP) said:
My apology. We'll need to use two separate queries to do this.

The first query needs to be your original query, with a slight change. Use
this SQL statement, and name the query qryOriginal (note: you need to add
the correct table name into the query for the [Margem Adjudicado] field):

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio],
[Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome], [Tabela de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor], [Tabela
de
dados].Comissionavel, [PUT_CORRECT_TABLENAME_HERE].[Margem Adjudicado]
FROM ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores] RIGHT JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] = [Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de dados].[Tipo de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_1] ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_2] ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de
Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de Vendedores_3] ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));



Then create a second query, name it qryResult, and use this SQL statement
for it:

SELECT qryResult.Data, qryResult.Tipo de Negócio], qryResult.Vendedor,
qryResult.[Prémio de Gestão Nome], qryResult.[Prémio de Gestão Valor],
qryResult.[Prémio dePreparação Nome], qryResult.[Prémio de Preparação
Valor],
qryResult.Gestor, qryResult.[Premio Gestor], qryResult.Comissionavel,
IIf(qryResult.[Comissionavel]<0,qryResult.[Margem Adjudicado]*TCV.CV,0) AS
[Comis VD]
FROM TCV, qryResult;



--

Ken Snell
<MS ACCESS MVP>




Pedro said:
something about "have to relation this table with the others ..."

Ken Snell (MVP) said:
What error message do you see?

--

Ken Snell
<MS ACCESS MVP>


Still not working ...


:

Change
TCV!CV

to
TCV.CV
--

Ken Snell
<MS ACCESS MVP>


Ken, tks lot for reply my answer. I've done all you ask me and
then,
when
i
add that table to that qry it shows me an error message. SQL :

SELECT [Tabela de dados].Data, [Tabela de dados].[Tipo de Negócio],
[Tabela
de dados].Vendedor, [Tabela de dados].[Prémio de Gestão Nome],
[Tabela
de
dados].[Prémio de Gestão Valor], [Tabela de dados].[Prémio
dePreparação
Nome], [Tabela de dados].[Prémio de Preparação Valor], [Tabela de
dados].Gestor, [Comis VD]*[Val Premio Gestor] AS [Premio Gestor],
[Tabela
de
dados].Comissionavel, IIf([Comissionavel]<0,[Margem
Adjudicado]*TCV!CV,0)
AS
[Comis VD]
FROM TCV, ((([Tab Negócios] RIGHT JOIN ([Tabela de Vendedores]
RIGHT
JOIN
[Tabela de dados] ON [Tabela de Vendedores].[Cod Vendedor] =
[Tabela de
dados].Vendedor) ON [Tab Negócios].Designação = [Tabela de
dados].[Tipo
de
Negócio]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de
Vendedores_1]
ON
[Tabela de dados].[Prémio de Gestão Nome] = [Tabela de
Vendedores_1].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de
Vendedores_2]
ON
[Tabela de dados].[Prémio dePreparação Nome] = [Tabela de
Vendedores_2].[Cod
Vendedor]) LEFT JOIN [Tabela de Vendedores] AS [Tabela de
Vendedores_3]
ON
[Tabela de dados].Gestor = [Tabela de Vendedores_3].[Cod Vendedor]
WHERE ((([Tabela de dados].Comissionavel)=True));

The table in question is called TCV and the field is CV.

Any ideas?
Tks again
Pedro


:

Create a new table, name it tblMultiplierValue. Put one field in
the
table,
name it fldMultiplierValue; make it a Double Numeric data type.
Save
the
table, and then open it in datasheet view. Add one record to the
table,
using the value that you want as the multiplier -- in this case,
0.06
(or
0,06 for your settings).

Now open in design view a query that should use this mutliplier
value.
Add
this new table to the query; do not create any joins between this
table
and
the other table(s) in the query. Add a calculated field to the
query:
MyMultipliedValue: tblMulplierValue.fldMultiplierValue *
YourFieldName

Save the query. It now will show the field that you wish.
--

Ken Snell
<MS ACCESS MVP>


Hello everyone
I must tell you i'm not an expert
But i would like to put in all my querys a certain field that
multiplies
by
0,06 but i would like to change someday and i would prefer to do
it,
just
in
one place.
What's the best advise you can give. But don't forget i'm not an
expert.
Explain me like if i were 10.
Tks in advance
Pedro
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sum a field value in all querys 1
List all records but ... 3
Multiply a range by Input Box Value 2
Combining CrossTab Qry 1
IIF true, multiply a field by 2 3
multiply tables in a query 2
Multiply field value of one table with cell value of another table 8
Count text values in a Access Field 0

Top