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.
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.