Query wont export calculated field to table properly

  • Thread starter Thread starter Keith Kaminetzky
  • Start date Start date
K

Keith Kaminetzky

That is my assumption any way. In a nutshell I'm doing a simple division
and when I run the query as a select query it calculates the appropriate
percent. If I switch the query to a make table or append the values do not
appea properly. Ive tried changing the table in the design view but I am
doing something wrong.

In the select query the calculated value appears as 1.3999% eg. If I
change to a make table or append query it appears as 1 nothing else.

Code

SELECT [MONTHLY WHOLESALE].DMREGN AS Region, [MONTHLY RETAIL].DMDCDE AS
District, DISTMGR.FTRNAM, [MONTHLY RETAIL].MTD_RET, Objectives.[Retail
Obective] AS Ret_Obj, ([MONTHLY RETAIL]![MTD_RET]/[Objectives]![Retail
Obective]) AS PCT, [MONTHLY WHOLESALE].MTD_WHL, [MTD COMITTED].Comitted,
([MONTHLY WHOLESALE]![MTD_WHL]+[MTD COMITTED]![Comitted]) AS TOT_Wholesale,
Objectives.[Wholesale Objective], ([TOT_Wholesale]/[Objectives]![Wholesale
Objective]) AS WHL_PCT, [MONTHLY WHOLESALE].DMREGN
FROM ((([MONTHLY WHOLESALE] INNER JOIN [MONTHLY RETAIL] ON ([MONTHLY
WHOLESALE].DMDCDE = [MONTHLY RETAIL].DMDCDE) AND ([MONTHLY WHOLESALE].DMREGN
= [MONTHLY RETAIL].DMREGN)) INNER JOIN [MTD COMITTED] ON ([MONTHLY
WHOLESALE].DMDCDE = [MTD COMITTED].District) AND ([MONTHLY WHOLESALE].DMREGN
= [MTD COMITTED].Region)) INNER JOIN Objectives ON [MONTHLY
WHOLESALE].DMDCDE = Objectives.District) INNER JOIN DISTMGR ON ([MONTHLY
WHOLESALE].DMREGN = DISTMGR.FTRRGN) AND ([MONTHLY WHOLESALE].DMDCDE =
DISTMGR.FTRDST)
WHERE ((([MONTHLY WHOLESALE].DMREGN)="EA"));
 
Keith said:
That is my assumption any way. In a nutshell I'm doing a simple division
and when I run the query as a select query it calculates the appropriate
percent. If I switch the query to a make table or append the values do not
appea properly. Ive tried changing the table in the design view but I am
doing something wrong.

In the select query the calculated value appears as 1.3999% eg. If I
change to a make table or append query it appears as 1 nothing else.

Code

SELECT [MONTHLY WHOLESALE].DMREGN AS Region, [MONTHLY RETAIL].DMDCDE AS
District, DISTMGR.FTRNAM, [MONTHLY RETAIL].MTD_RET, Objectives.[Retail
Obective] AS Ret_Obj, ([MONTHLY RETAIL]![MTD_RET]/[Objectives]![Retail
Obective]) AS PCT, [MONTHLY WHOLESALE].MTD_WHL, [MTD COMITTED].Comitted,
([MONTHLY WHOLESALE]![MTD_WHL]+[MTD COMITTED]![Comitted]) AS TOT_Wholesale,
Objectives.[Wholesale Objective], ([TOT_Wholesale]/[Objectives]![Wholesale
Objective]) AS WHL_PCT, [MONTHLY WHOLESALE].DMREGN
FROM ((([MONTHLY WHOLESALE] INNER JOIN [MONTHLY RETAIL] ON ([MONTHLY
WHOLESALE].DMDCDE = [MONTHLY RETAIL].DMDCDE) AND ([MONTHLY WHOLESALE].DMREGN
= [MONTHLY RETAIL].DMREGN)) INNER JOIN [MTD COMITTED] ON ([MONTHLY
WHOLESALE].DMDCDE = [MTD COMITTED].District) AND ([MONTHLY WHOLESALE].DMREGN
= [MTD COMITTED].Region)) INNER JOIN Objectives ON [MONTHLY
WHOLESALE].DMDCDE = Objectives.District) INNER JOIN DISTMGR ON ([MONTHLY
WHOLESALE].DMREGN = DISTMGR.FTRRGN) AND ([MONTHLY WHOLESALE].DMDCDE =
DISTMGR.FTRDST)
WHERE ((([MONTHLY WHOLESALE].DMREGN)="EA"));
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try using an explicit conversion function on the formula so the query
"knows" to output it as the selected data type. E.g.:

CDbl([MONTHLY RETAIL].[MTD_RET]/[Objectives].[Retail Obective]) AS PCT

CDbl() converts the results of the calculation to a Double data type.

BTW, the separator between table name & column name is a period not an
exclamation point. I know it works, but it is not proper SQL syntax.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi4TXoechKqOuFEgEQK41gCcDGGQbS9Wwz8BlOmO5xOfxk8E84UAoKQI
H8NS+K90yoYFGqVLd/jAsqSb
=v0Gk
-----END PGP SIGNATURE-----
 
Back
Top