sum between dates

[chaosguy]

Hey I'v been trying to figure this out for week and no luck. Anyways I got this query that pulls values from a table. I then inserted a filter that would filter the query between two dates. I want the query to just give me one line showing the total value of a order, the total value for balance left on orders and total value of amount paid on orders.

I also want the query to give the number of records or order between those two dates. I want it to display on one line so i can use it in a report.

The information is pulled from the order table that have
order ID (primary key)\
order date
order cost
amount paid
balance on order


the query order date has the criteria between[form]![reports form]![frStartDate] and [forms]!reports form]![frEndDate]
the quesry also ahve all the other field in the order info table order table and it SHOULD show the total for all of um (but it doesn't). I created the query using the wizard, and on the summery i clicked on sum for the three fields.

any help would be very much appreciated. thanks in advance for ur help.

p.s HERE'S THE SQL if it helps.

SELECT DISTINCTROW [Order Info].[Order No], [Order Info].[Order Date], [Order Info].[Order Status], Sum([Order Info].[Order Cost]) AS [Sum Of Order Cost], Sum([Order Info].[Amount Paid]) AS [Sum Of Amount Paid], Sum([Order Info].Balance) AS [Sum Of Balance], Count(*) AS [Count Of Order Info]
FROM [Order Info]
GROUP BY [Order Info].[Order No], [Order Info].[Order Date], [Order Info].[Order Status];


Chaosguy - To die would be an awefully big adventure.

 

[lynchg]

What output are you getting?

I suspect that you are getting multiple lines, if that is true then it must be possible to have either multiple Order Date values per Order No, or multiple Order Status values per Order No, or both, or some combination of those.

In order to get one record in a Group By, there must only be one "occurance" of the combination of the fields in your Group By clause.

 

[chaosguy]

so is there anyway i can overcome this? how do i make query that does what i want? yeah i'm getting multiple lines.

please help u guys

Chaosguy - To die would be an awefully big adventure.

 

[Lilliabeth]

Don't put order date or order status in the query. Group by Order No.

 

[chaosguy]

hey ya buddy, thanks for all ur help, that last post sort lite up a bulb in my head, lol. anyways i created two queries.

one with order no and order date
the other with order number, order cost, balance and amount paid.

i used a filter in the first one, and the second one i took the order number from the first and made the second one with the other fields. I then changed the order number to count. so i got all i need now. one line with all the totals, and a count of how much orderd in the order no colum. thanks again for ur help guys. bye

p.s yeah it works fine.

Chaosguy - To die would be an awefully big adventure.