Problem in calculation in query

[Coco111 via AccessMonster.com]

Hi, I got problem in calculation field in query,

Total Amount: ([Extended Price]*[Exchange Rate])

There is 36 records, 35 records calculated were presented correct figure..
only 1 record was wrong..so funny and big problem.. US$2210X1.385 (exchange
rate) should be $3060.85 but it's result shown $1530.43. How to check and
solve this problem. Please help.

Tq...coco

 

[Gina Whipp]

Coco111,

Oodly enough, your result would make the calculation appear as if it's being
divided in half. So I have to ask the obvious, are you sure there is no
..../2... anywhere in your calculation? Is your [Exchange Rate] per record?
If it is I would check that it doesn't include anything else.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Coco111 via AccessMonster.com]

Yes, it's very odd..puzzled me as well...i double check, no other number and
no divided...Exchange rate is per record. I found out where the figure come
from when I try delete and key in Exhange rate again... i change exchange to
1.... the amount US$2210 is the total sales of 2 items (us$1105X2)..It show
only US$1105...if i change again the exchange rate to 1.385 so figure show
$1530.425..one of the amount is not calculated... so strange... How?

Coco111,

Oodly enough, your result would make the calculation appear as if it's being
divided in half. So I have to ask the obvious, are you sure there is no
.../2... anywhere in your calculation? Is your [Exchange Rate] per record?
If it is I would check that it doesn't include anything else.

Hi, I got problem in calculation field in query,

[quoted text clipped - 7 lines]

Tq...coco

 

[Coco111 via AccessMonster.com]

i try and found out another funny thing..i change 2 amount to (us$1104.50+us
$1105.50 = total US$2210) and now exchange rate x1.385... it works.. but it's
just testing.. i cant any how change the price actual price US$1105 for
different item...

Yes, it's very odd..puzzled me as well...i double check, no other number and
no divided...Exchange rate is per record. I found out where the figure come
from when I try delete and key in Exhange rate again... i change exchange to
1.... the amount US$2210 is the total sales of 2 items (us$1105X2)..It show
only US$1105...if i change again the exchange rate to 1.385 so figure show
$1530.425..one of the amount is not calculated... so strange... How?

Coco111,

[quoted text clipped - 8 lines]

 

[Gina Whipp]

In your initial post you said...

Total Amount: ([Extended Price]*[Exchange Rate])

.... however, are you now saying there is a Quantity field OR is [Extended
Price] a calculated field?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

i try and found out another funny thing..i change 2 amount to
(us$1104.50+us
$1105.50 = total US$2210) and now exchange rate x1.385... it works.. but
it's
just testing.. i cant any how change the price actual price US$1105 for
different item...

Yes, it's very odd..puzzled me as well...i double check, no other number
and
no divided...Exchange rate is per record. I found out where the figure
come
from when I try delete and key in Exhange rate again... i change exchange
to
1.... the amount US$2210 is the total sales of 2 items (us$1105X2)..It
show
only US$1105...if i change again the exchange rate to 1.385 so figure show
$1530.425..one of the amount is not calculated... so strange... How?

Coco111,

[quoted text clipped - 8 lines]

Tq...coco

 

[Coco111 via AccessMonster.com]

Extended Price is also the calculated field of sum total amount of invoice
item, some got 1 item, some 2, some 3, some 4 and etc... all of them..all the
figure is correct... except this invoice records that i point out in initial
post...

I check the extended price is the amount is correct (us$1105x2=2210)..but *
exchange rate (1.385) was given me wrong figure as informed earlier.

In your initial post you said...

Total Amount: ([Extended Price]*[Exchange Rate])

... however, are you now saying there is a Quantity field OR is [Extended
Price] a calculated field?

i try and found out another funny thing..i change 2 amount to
(us$1104.50+us

[quoted text clipped - 19 lines]

 

[Gina Whipp]

Coco111,

Then let's try this... what is the calculation that gives you Extended
Price, not the numbers but the fields that you are calculating.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Extended Price is also the calculated field of sum total amount of invoice
item, some got 1 item, some 2, some 3, some 4 and etc... all of them..all
the
figure is correct... except this invoice records that i point out in
initial
post...

I check the extended price is the amount is correct (us$1105x2=2210)..but
*
exchange rate (1.385) was given me wrong figure as informed earlier.

In your initial post you said...

Total Amount: ([Extended Price]*[Exchange Rate])

... however, are you now saying there is a Quantity field OR is [Extended
Price] a calculated field?

i try and found out another funny thing..i change 2 amount to
(us$1104.50+us

[quoted text clipped - 19 lines]

Tq...coco

 

[Coco111 via AccessMonster.com]

field as below:-

Extended Price: CCur([Qty Order]*[Unit Price]*(1-[Discount]))
Price Total: Extended Price
Total Amount: ([Extended Price]*[Exchange Rate])

Coco111,

Then let's try this... what is the calculation that gives you Extended
Price, not the numbers but the fields that you are calculating.

Extended Price is also the calculated field of sum total amount of invoice
item, some got 1 item, some 2, some 3, some 4 and etc... all of them..all

[quoted text clipped - 19 lines]

 

[Gina Whipp]

Coco111,

What is the [Discount] amount?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

field as below:-

Extended Price: CCur([Qty Order]*[Unit Price]*(1-[Discount]))
Price Total: Extended Price
Total Amount: ([Extended Price]*[Exchange Rate])

Coco111,

Then let's try this... what is the calculation that gives you Extended
Price, not the numbers but the fields that you are calculating.

Extended Price is also the calculated field of sum total amount of
invoice
item, some got 1 item, some 2, some 3, some 4 and etc... all of
them..all

[quoted text clipped - 19 lines]

Tq...coco

 

[Coco111 via AccessMonster.com]

0, there is no discount given to anybody, but i cant take it out cos i modify
from northwind template.

Coco111,

What is the [Discount] amount?

field as below:-

[quoted text clipped - 14 lines]

 

[KenSheridan via AccessMonster.com]

That doesn't mean you need to include it in the expression. Use:

Extended Price: CCur([Qty Order]*[Unit Price])

and for the total amount repeat the expression rather than referencing the
computed column:

Total Amount: CCur([Qty Order]*[Unit Price]*[Exchange Rate])

As regards the original inconsistencies do you by any chance have a discount
rate of 0.5 in those rows?

Ken Sheridan
Stafford, England

0, there is no discount given to anybody, but i cant take it out cos i modify
from northwind template.

Coco111,

[quoted text clipped - 5 lines]

 

[Gina Whipp]

Ken,

Great minds think alike! That was my thought since it appears from prior
statements that it's per line item.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

That doesn't mean you need to include it in the expression. Use:

Extended Price: CCur([Qty Order]*[Unit Price])

and for the total amount repeat the expression rather than referencing the
computed column:

Total Amount: CCur([Qty Order]*[Unit Price]*[Exchange Rate])

As regards the original inconsistencies do you by any chance have a
discount
rate of 0.5 in those rows?

Ken Sheridan
Stafford, England

0, there is no discount given to anybody, but i cant take it out cos i
modify
from northwind template.

Coco111,

[quoted text clipped - 5 lines]

Tq...coco

 

[Coco111 via AccessMonster.com]

I did take out the discount in the expression. But problem still not yet be
solved. Btw, I double confirm there is no discount..all default 0. How to
explain this.. item 1 extended price $1105 and item 2 also $1105...if i
change item 1 $1104 and item 2 $1106, then I will get the correct figure..

That doesn't mean you need to include it in the expression. Use:

Extended Price: CCur([Qty Order]*[Unit Price])

and for the total amount repeat the expression rather than referencing the
computed column:

Total Amount: CCur([Qty Order]*[Unit Price]*[Exchange Rate])

As regards the original inconsistencies do you by any chance have a discount
rate of 0.5 in those rows?

Ken Sheridan
Stafford, England

0, there is no discount given to anybody, but i cant take it out cos i modify
from northwind template.

[quoted text clipped - 4 lines]

 

[Gina Whipp]

Coco111,

Ummm, now I am a little confused and am now having a hard time visualizing
what you are doing. Initially, you said...

Now you are saying...

Perhaps you should type here a few lines of data and maybe even the SQL of
your query.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I did take out the discount in the expression. But problem still not yet be
solved. Btw, I double confirm there is no discount..all default 0. How to
explain this.. item 1 extended price $1105 and item 2 also $1105...if i
change item 1 $1104 and item 2 $1106, then I will get the correct figure..

That doesn't mean you need to include it in the expression. Use:

Extended Price: CCur([Qty Order]*[Unit Price])

and for the total amount repeat the expression rather than referencing the
computed column:

Total Amount: CCur([Qty Order]*[Unit Price]*[Exchange Rate])

As regards the original inconsistencies do you by any chance have a
discount
rate of 0.5 in those rows?

Ken Sheridan
Stafford, England

0, there is no discount given to anybody, but i cant take it out cos i
modify
from northwind template.

[quoted text clipped - 4 lines]

Tq...coco

 

[.Len B]

I think Coco is saying
Original data $1105 x 2 x Exchange = Wrong answer
Change to $1104 x 2 x Exchange = Correct
Change to $1106 x 2 x Exchange = Correct

If true, it beats me.

Coco should type some data and the SQL of the query.

--
Len
______________________________________________________
remove nothing for valid email address.
| Coco111,
|
| Ummm, now I am a little confused and am now having a hard time
visualizing
| what you are doing. Initially, you said...
|
| >>> US$2210X1.385 (exchange rate) should be $3060.85 but it's result
shown
| >>> $1530.43.
|
| Now you are saying...
|
| >>> item 1 extended price $1105 and item 2 also $1105...if i change
item 1
| >>> $1104 and item 2 $1106, then I will get the correct figure..
|
| Perhaps you should type here a few lines of data and maybe even the SQL
of
| your query.
|
| --
| Gina Whipp
| 2010 Microsoft MVP (Access)
|
| "I feel I have been denied critical, need to know, information!" -
Tremors
| II
|
| http://www.regina-whipp.com/index_files/TipList.htm
|
| | >I did take out the discount in the expression. But problem still not
yet be
| > solved. Btw, I double confirm there is no discount..all default 0.
How to
| > explain this.. item 1 extended price $1105 and item 2 also $1105...if
i
| > change item 1 $1104 and item 2 $1106, then I will get the correct
figure..
| >
| > KenSheridan wrote:
| >>That doesn't mean you need to include it in the expression. Use:
| >>
| >>Extended Price: CCur([Qty Order]*[Unit Price])
| >>
| >>and for the total amount repeat the expression rather than
referencing the
| >>computed column:
| >>
| >>Total Amount: CCur([Qty Order]*[Unit Price]*[Exchange Rate])
| >>
| >>As regards the original inconsistencies do you by any chance have a
| >>discount
| >>rate of 0.5 in those rows?
| >>
| >>Ken Sheridan
| >>Stafford, England
| >>
| >>>0, there is no discount given to anybody, but i cant take it out cos
i
| >>>modify
| >>>from northwind template.
| >>[quoted text clipped - 4 lines]
| >>>>>>>>>>>>
| >>>>>>>>>>>> Tq...coco
| >
| > --
|
| >

| >
|
|

 

[Gina Whipp]

Len,

We know what he's saying and that we is why we made the request for the
sample data and SQL of the query.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I think Coco is saying
Original data $1105 x 2 x Exchange = Wrong answer
Change to $1104 x 2 x Exchange = Correct
Change to $1106 x 2 x Exchange = Correct

If true, it beats me.

Coco should type some data and the SQL of the query.

--
Len
______________________________________________________
remove nothing for valid email address.
| Coco111,
|
| Ummm, now I am a little confused and am now having a hard time
visualizing
| what you are doing. Initially, you said...
|
| >>> US$2210X1.385 (exchange rate) should be $3060.85 but it's result
shown
| >>> $1530.43.
|
| Now you are saying...
|
| >>> item 1 extended price $1105 and item 2 also $1105...if i change
item 1
| >>> $1104 and item 2 $1106, then I will get the correct figure..
|
| Perhaps you should type here a few lines of data and maybe even the SQL
of
| your query.
|
| --
| Gina Whipp
| 2010 Microsoft MVP (Access)
|
| "I feel I have been denied critical, need to know, information!" -
Tremors
| II
|
| http://www.regina-whipp.com/index_files/TipList.htm
|
| | >I did take out the discount in the expression. But problem still not
yet be
| > solved. Btw, I double confirm there is no discount..all default 0.
How to
| > explain this.. item 1 extended price $1105 and item 2 also $1105...if
i
| > change item 1 $1104 and item 2 $1106, then I will get the correct
figure..
| >
| > KenSheridan wrote:
| >>That doesn't mean you need to include it in the expression. Use:
| >>
| >>Extended Price: CCur([Qty Order]*[Unit Price])
| >>
| >>and for the total amount repeat the expression rather than
referencing the
| >>computed column:
| >>
| >>Total Amount: CCur([Qty Order]*[Unit Price]*[Exchange Rate])
| >>
| >>As regards the original inconsistencies do you by any chance have a
| >>discount
| >>rate of 0.5 in those rows?
| >>
| >>Ken Sheridan
| >>Stafford, England
| >>
| >>>0, there is no discount given to anybody, but i cant take it out cos
i
| >>>modify
| >>>from northwind template.
| >>[quoted text clipped - 4 lines]
| >>>>>>>>>>>>
| >>>>>>>>>>>> Tq...coco
| >
| > --
|
| >

| >
|
|

 

[Tom Wickerath]

Hi Coco,

As others have said, please copy the SQL (Structured Query Language)
statement for your query and paste it into a reply. If your query includes
other queries as sources of data, then paste the SQL for those queries as
well. It would also be helpful to give us some table design data (name of
table and names & data types of fields), along with some sample data.

Is there any chance that you have the Totals button pressed (which will add
a Group By clause to the SQL statement)? If I understand you correctly, this
data gives you 1/2 of the expected result:

ItemNo Price Discount
1 1105 0
2 1105 0

but this data gives you the correct result:

ItemNo Price Discount
1 1104.99 0
2 1105.01 0


If this is true, then it sure appears to me as if two records are being
grouped into one, because you do not have any other unique field present. Do
you even need the Group By, assuming you have it enabed?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Coco111 via AccessMonster.com]

Hi,

sorry, if i dont explain clearly...

query "order details extended"
SELECT [Order Details].ID, [Order Details].[Order ID], [Order Details].[PO
Number], [Order Details].[Noted 1], [Order Details].[Product ID], [Order
Details].Size, [Order Details].Special_Instruction, [Order Details].Quantity,
[Order Details].[Qty Order], [Order Details].Unit, [Order Details].[Unit
Price], [Order Details].Currency, [Order Details].[Exchange Rate], [Order
Details].Origin, [Order Details].Lot_Number, DLookUp("[Net_Weight]",
"Products","[ID] = " & [Product ID])*[quantity] AS [Nett Wt], ([Nett Wt]
*10/100)+[Nett Wt] AS [Gross Wt], [Order Details].[Noted 2], [Order Details].
[Status ID], [Order Details].[Date Allocated], [Order Details].[Purchase
Order ID], [Order Details].[Inventory ID], [Order Details].Discount, CCur(
[Qty Order]*[Unit Price]*(1-[Discount])) AS [Extended Price], [Order Details
Status].[Status Name], CCur([Qty Order]*[Unit Price])*[Tax Rate]/100 AS Expr1,
CCur([Qty Order]*[Unit Price])*[Tax Rate]/100+CCur([Qty Order]*[Unit Price])
AS Expr2, Customers.*, Orders.[Tax Rate], Orders.[Shipped Date], ([Extended
Price]*[Exchange Rate]) AS [Total Sales]
FROM (Customers RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID])
LEFT JOIN ([Order Details] LEFT JOIN [Order Details Status] ON [Order Details]
[Status ID] = [Order Details Status].[Status ID]) ON Orders.[Order ID] =
[Order Details].[Order ID];


query "order price totals"
SELECT [Order Details Extended].[Order ID] AS OrderID, Orders.[Customer ID],
Orders.[Shipped Date], [Order Details Extended].Currency, [Order Details
Extended].[Tax Rate], Sum([Order Details Extended].[Extended Price]) AS
[Price Total], [Order Details Extended].[Exchange Rate], ([Extended Price]*
[Exchange Rate])+(([Extended Price]*[Exchange Rate])*[Order Details Extended.
Tax Rate])/100 AS [Total In SGD], ([Extended Price]*[Exchange Rate]) AS
[Total Amount], (([Extended Price]*[Exchange Rate])*[Order Details Extended.
Tax Rate])/100 AS [Tax Amount]
FROM [Order Details Extended] INNER JOIN Orders ON [Order Details Extended].
[Order ID] = Orders.[Order ID]
GROUP BY [Order Details Extended].[Order ID], Orders.[Customer ID], Orders.
[Shipped Date], [Order Details Extended].Currency, [Order Details Extended].
[Tax Rate], [Order Details Extended].[Exchange Rate], ([Extended Price]*
[Exchange Rate])+(([Extended Price]*[Exchange Rate])*[Order Details Extended.
Tax Rate])/100, ([Extended Price]*[Exchange Rate]), (([Extended Price]*
[Exchange Rate])*[Order Details Extended.Tax Rate])/100
HAVING (((Orders.[Shipped Date]) Between #1/4/2010# And #1/31/2010#));

the query "order price totals" that give me the wrong figure. I use this
query to make report.

tbl design as below:-

tbl "order details"
ID order ID Product ID Quantity Qty Order Unit Price
Exchange Rate Discount
(autono.) (number) (number) (number) (number) (currency)
(currency) (number)

1 1000 1 1 1
1105 1.385 0
2 1000 2 1 1
1105 1.385 0

Noted : Quantity = is for inventory, Qty Order is for Invoice

I just TRY to change price in below tbl, and it give me the correct figure in
query "order price totals"..that puzzle me..cos i got a lot of records so far
no problem...only this that records that give me headache.

ID order ID Product ID Quantity Qty Order Unit Price
Exchange Rate Discount
1 1000 1 1 1
1104.50 1.385 0
2 1000 2 1 1
1105.50 1.385 0

Tq
coco

 

[Gina Whipp]

Coco,

The query "order price totals" is using Grup By because for whatever reason
you are Summing Extended Price in the query. Remove the Group By and do the
Summing on the report itself and your totals should then be correct.

Let's think in terms of apples... What is happening is the Group By is only
showing you the total for one line because they are identical and that is
what Group By does... Group By all like things. So if I have ten apples
and 7 of them are RED, one is yellow, one is green and one is rotten Group
By says okay... I'm going to GROUP all the red ones together (since they
are all RED anyway) and show 1 line for the and seperate line for the yellow
one, the green one and the rotten one.

That is why when you change the price it seperate them because now they are
not the same.

You could tell your Group By query to Sum the apples and and then you will
get the correct Totals because now you've said *How many RED apples do I
have?* But really, I'm not sure a Group query is even needed.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi,

sorry, if i dont explain clearly...

query "order details extended"
SELECT [Order Details].ID, [Order Details].[Order ID], [Order Details].[PO
Number], [Order Details].[Noted 1], [Order Details].[Product ID], [Order
Details].Size, [Order Details].Special_Instruction, [Order
Details].Quantity,
[Order Details].[Qty Order], [Order Details].Unit, [Order Details].[Unit
Price], [Order Details].Currency, [Order Details].[Exchange Rate], [Order
Details].Origin, [Order Details].Lot_Number, DLookUp("[Net_Weight]",
"Products","[ID] = " & [Product ID])*[quantity] AS [Nett Wt], ([Nett Wt]
*10/100)+[Nett Wt] AS [Gross Wt], [Order Details].[Noted 2], [Order
Details].
[Status ID], [Order Details].[Date Allocated], [Order Details].[Purchase
Order ID], [Order Details].[Inventory ID], [Order Details].Discount, CCur(
[Qty Order]*[Unit Price]*(1-[Discount])) AS [Extended Price], [Order
Details
Status].[Status Name], CCur([Qty Order]*[Unit Price])*[Tax Rate]/100 AS
Expr1,
CCur([Qty Order]*[Unit Price])*[Tax Rate]/100+CCur([Qty Order]*[Unit
Price])
AS Expr2, Customers.*, Orders.[Tax Rate], Orders.[Shipped Date],
([Extended
Price]*[Exchange Rate]) AS [Total Sales]
FROM (Customers RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID])
LEFT JOIN ([Order Details] LEFT JOIN [Order Details Status] ON [Order
Details]
[Status ID] = [Order Details Status].[Status ID]) ON Orders.[Order ID] =
[Order Details].[Order ID];


query "order price totals"
SELECT [Order Details Extended].[Order ID] AS OrderID, Orders.[Customer
ID],
Orders.[Shipped Date], [Order Details Extended].Currency, [Order Details
Extended].[Tax Rate], Sum([Order Details Extended].[Extended Price]) AS
[Price Total], [Order Details Extended].[Exchange Rate], ([Extended
Price]*
[Exchange Rate])+(([Extended Price]*[Exchange Rate])*[Order Details
Extended.
Tax Rate])/100 AS [Total In SGD], ([Extended Price]*[Exchange Rate]) AS
[Total Amount], (([Extended Price]*[Exchange Rate])*[Order Details
Extended.
Tax Rate])/100 AS [Tax Amount]
FROM [Order Details Extended] INNER JOIN Orders ON [Order Details
Extended].
[Order ID] = Orders.[Order ID]
GROUP BY [Order Details Extended].[Order ID], Orders.[Customer ID],
Orders.
[Shipped Date], [Order Details Extended].Currency, [Order Details
Extended].
[Tax Rate], [Order Details Extended].[Exchange Rate], ([Extended Price]*
[Exchange Rate])+(([Extended Price]*[Exchange Rate])*[Order Details
Extended.
Tax Rate])/100, ([Extended Price]*[Exchange Rate]), (([Extended Price]*
[Exchange Rate])*[Order Details Extended.Tax Rate])/100
HAVING (((Orders.[Shipped Date]) Between #1/4/2010# And #1/31/2010#));

the query "order price totals" that give me the wrong figure. I use this
query to make report.

tbl design as below:-

tbl "order details"
ID order ID Product ID Quantity Qty Order Unit
Price
Exchange Rate Discount
(autono.) (number) (number) (number) (number) (currency)
(currency) (number)

1 1000 1 1 1
1105 1.385 0
2 1000 2 1 1
1105 1.385 0

Noted : Quantity = is for inventory, Qty Order is for Invoice

I just TRY to change price in below tbl, and it give me the correct figure
in
query "order price totals"..that puzzle me..cos i got a lot of records so
far
no problem...only this that records that give me headache.

ID order ID Product ID Quantity Qty Order Unit Price
Exchange Rate Discount
1 1000 1 1 1
1104.50 1.385 0
2 1000 2 1 1
1105.50 1.385 0

Tq
coco

Hi Coco,

As others have said, please copy the SQL (Structured Query Language)
statement for your query and paste it into a reply. If your query includes
other queries as sources of data, then paste the SQL for those queries as
well. It would also be helpful to give us some table design data (name of
table and names & data types of fields), along with some sample data.

Is there any chance that you have the Totals button pressed (which will
add
a Group By clause to the SQL statement)? If I understand you correctly,
this
data gives you 1/2 of the expected result:

ItemNo Price Discount
1 1105 0
2 1105 0

but this data gives you the correct result:

ItemNo Price Discount
1 1104.99 0
2 1105.01 0

If this is true, then it sure appears to me as if two records are being
grouped into one, because you do not have any other unique field present.
Do
you even need the Group By, assuming you have it enabed?

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Coco111 via AccessMonster.com]

Hi Gina,

Actually, im not very sure also cos im just modify it from northwind template.