Data/Values change when Query Analyzed in Excel

[Kristibaer]

I have a query with 3 tables that represent master item list, count qty and
as qty. When I look at the results in Query, all is well and accurate yet
when I select Analyze with Excel, values change. For example, if a record
shows zero qty in the as of field and zero quantity in the count field, I get
a value in one or both of the fields that origianlly were zero. Other than
linking the tables and grouping by master item list (to show all parts
regardless of qty's), there are no formulas or expressions in this query.

One of the tables is linked to a FoxPro table via ODBC driver.

Why do the values change when exported to Excel? I am desparate to fix this
as I have been working on this for over a month and cannot trust my results
in the Excel format that my accountant requires. There are 20,717 records in
this query with 5 columns of data. Item, description, cost, as of qty and
count qty.

Is there a way to keep the data/values as they appear in the query format
when analyzed/exported to Excel????

 

[Jerry Whittle]

What does the data look like? Something real small or with scientific notation?

Please post the SQL statement for the query. It might tell us something.

 

[Kristibaer]

The specific values that are changing are numeric. For expample, in Query if
I have a quantity in a reocrd of zero, when I analyze the data with Excel, it
came up with a quantity of -83, another zero to 501. I am trying to analyze
variances for physical inventory in an Excel format for management, but
summarizing and manipulating the data is much easier for me in Access,
especially with 20k+ records. Here is the SQL statement:

SELECT [ITEMS MASTER].item, [ITEMS MASTER].descrip, [FREEZE QTY].[=Freeze
Qty], ([FREEZE QTY].[=Freeze Qty]*phymast.avgcost) AS [freeze Value], [COUNT
FINAL].[SumOfQty on Hand], Sum(phymaint.phycnt) AS SumOfphycnt, ([COUNT
FINAL].[SumOfQty on Hand]*phymast.avgcost) AS [Count Value], phymast.avgcost,
immaster.avgcost, immaster.lastcost
FROM (phymaint RIGHT JOIN (immaster RIGHT JOIN (phymast RIGHT JOIN ([FREEZE
QTY] RIGHT JOIN [ITEMS MASTER] ON [FREEZE QTY].item = [ITEMS MASTER].item) ON
phymast.item = [ITEMS MASTER].item) ON immaster.item = [ITEMS MASTER].item)
ON phymaint.item = [ITEMS MASTER].item) LEFT JOIN [COUNT FINAL] ON [ITEMS
MASTER].item = [COUNT FINAL].[Item#]
GROUP BY [ITEMS MASTER].item, [ITEMS MASTER].descrip, [FREEZE QTY].[=Freeze
Qty], ([FREEZE QTY].[=Freeze Qty]*phymast.avgcost), [COUNT FINAL].[SumOfQty
on Hand], ([COUNT FINAL].[SumOfQty on Hand]*phymast.avgcost),
phymast.avgcost, immaster.avgcost, immaster.lastcost
ORDER BY [ITEMS MASTER].item;

 

[Jerry Whittle]

I can't see anything obvious in the SQL statement, but then it seems to be
based on other queries.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The specific values that are changing are numeric. For expample, in Query if
I have a quantity in a reocrd of zero, when I analyze the data with Excel, it
came up with a quantity of -83, another zero to 501. I am trying to analyze
variances for physical inventory in an Excel format for management, but
summarizing and manipulating the data is much easier for me in Access,
especially with 20k+ records. Here is the SQL statement:

SELECT [ITEMS MASTER].item, [ITEMS MASTER].descrip, [FREEZE QTY].[=Freeze
Qty], ([FREEZE QTY].[=Freeze Qty]*phymast.avgcost) AS [freeze Value], [COUNT
FINAL].[SumOfQty on Hand], Sum(phymaint.phycnt) AS SumOfphycnt, ([COUNT
FINAL].[SumOfQty on Hand]*phymast.avgcost) AS [Count Value], phymast.avgcost,
immaster.avgcost, immaster.lastcost
FROM (phymaint RIGHT JOIN (immaster RIGHT JOIN (phymast RIGHT JOIN ([FREEZE
QTY] RIGHT JOIN [ITEMS MASTER] ON [FREEZE QTY].item = [ITEMS MASTER].item) ON
phymast.item = [ITEMS MASTER].item) ON immaster.item = [ITEMS MASTER].item)
ON phymaint.item = [ITEMS MASTER].item) LEFT JOIN [COUNT FINAL] ON [ITEMS
MASTER].item = [COUNT FINAL].[Item#]
GROUP BY [ITEMS MASTER].item, [ITEMS MASTER].descrip, [FREEZE QTY].[=Freeze
Qty], ([FREEZE QTY].[=Freeze Qty]*phymast.avgcost), [COUNT FINAL].[SumOfQty
on Hand], ([COUNT FINAL].[SumOfQty on Hand]*phymast.avgcost),
phymast.avgcost, immaster.avgcost, immaster.lastcost
ORDER BY [ITEMS MASTER].item;

What does the data look like? Something real small or with scientific notation?

Please post the SQL statement for the query. It might tell us something.