Need a very weird query

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hello

In the database I am working on contains these 3 fields. My problem is
that the number of items is multiplied by the number of items. Where
it says 4 it is really 1 of 4 being shown as 4 4's. It is showing the
total items times the number of items. So it is really 4 and 6 and 3
but it is coming out as shown below. I need to make it into 1 item per
row. I cannot alter the database so I must do it as a query. Also the
total cost column is the same way. I need to do something if a zero is
in the item cost column, delete those records and select the first
item or the first 120.56 and delete the rest. I don't know if this
makes sense because my head is spinning. Any help would be greatly
appreciated
Bob


No_Items Item_Cost Total_Cost

4 13.64 48.82
4 14.36 48.82
4 14.01 48.82
4 6.81 48.82
6 0 120.56
6 0 120.56
6 0 120.56
6 0 120.56
6 0 120.56
6 0 120.56
3 8.06 21.06
3 5.50 21.06
3 7.50 21.06

HELP!!!!
 
On Fri, 13 Feb 2004 23:55:51 -0500, Bob <[email protected]>
wrote:

Sorry what I need is where the total number of items has multiplied to
showing singles. And where the the zero is deleted except the first
line. I hope this makes sense

From This

No_Items Item_Cost Total_Cost

4 13.64 48.82
4 14.36 48.82
4 14.01 48.82
4 6.81 48.82
6 0 120.56
6 0 120.56
6 0 120.56
6 0 120.56
6 0 120.56
6 0 120.56
3 8.06 21.06
3 5.50 21.06
3 7.50 21.06

To This

No_Items Item_Cost Total_Cost

1 13.64 48.82
1 14.36
1 14.01
1 6.81

6 0 120.56

1 8.06 21.06
1 5.50
1 7.50
 
Hi Bob,

What is/ are the table structure(s) you are trying to query? How are you
deriving No_Items?

The following example looks at my tblProducts table, grouping ONLY by
Product Cost. The other two fields are simply calculated based on the number
of items of a given cost.

SELECT Count(tblProducts.ProdCost) AS No_Items, tblProducts.ProdCost AS
Item_Cost, [ProdCost]*[No_Item] AS Total_Cost
FROM tblProducts
GROUP BY tblProducts.ProdCost;

The output would look like this:

No_Items Item_Cost Total_Cost
133 0.00 0.00
1 0.01 0.01
3 0.75 2.25

Looking at your desired output, I'd say that you'd need to group by some
other identifying field in your query. Then in a report, you can hide the
Total_Cost field, add a new text box directly on top of Total_Cost with its
control source of Total_Cost, and set its running sum property "Over Group."

Without further clarification, that's all I can suggest.

HTH,
Robert Dale
 
Back
Top