Okay, the code I sent is just an SQL statement. It will not work by itself.
Open your report in Design Mode and put the code in RecordSource property of
the report. You will have to change the names of the table I used to be like
the names in your table or query. See the instructions below:
(1) (2) (3) (1) (4) (5)
SELECT [_txtBill].BillDate, Min([_txtBill].BillOrder) AS MinOfBillOrder,
(6) (4) (7) (8)
(9)
Max([_txtBill].BillOrder) AS MaxOfBillOrder, Sum([_txtBill].BillAmount) AS
(10)
SumOfBillAmount
(1)
FROM _txtBill
(11) (1) (2)
GROUP BY [_txtBill].BillDate;
(1) Change this to the name of your table or query
(2) Change this to the billing date in your table or query
(3) Returns the lowest Order Number for the Date
(4) Change this to the Order Number in your table or query
(5) Use this name in your report where you show the first order number for
the date
(6) Returns the highest Order Number for the Date
(7) Use this name in your report where you show the last order number for
the date
(8) Adds up all the Billing amounts for the date
(9) Change this to the billing amount in your table or query
(10) Use this name in your report where you show the total billing amount
for the date
(11) Makes all the rows for this date add up to one row
Good Luck! Let me know how it works out.
filo666 said:
It doesn't work, something about a missing case.
Please explaindme as easy way as possible because, althougt I'm a preaty
well excel's programer, I'm just starting with acces, in fact this is my firs
program that I try to make in acces, so.
1)I suppose that the code you attached should be pasted in the
"on_click_event " of one of my form button?
2) The code is going to craete the query each time the bustton is pressed?
Please expleind me in a easy lenguaje.
Klatuu said:
Good luck, let me know how it works out.
:
Thanks, I'm going to try your code.
:
I made up my own names for the fields, but you can change this to suit your
needs.
Here is the SQL for the query that will get the result you want:
SELECT [_txtBill].BillDate, Min([_txtBill].BillOrder) AS MinOfBillOrder,
Max([_txtBill].BillOrder) AS MaxOfBillOrder, Sum([_txtBill].BillAmount) AS
SumOfBillAmount
FROM _txtBill
GROUP BY [_txtBill].BillDate;
:
Good morning, I have a query with the following columns:
Date Bill Order Amount
01/05/05 1101 1520
01/05/05 1102 432
01/05/05 1103 5869
01/01/05 1104 1225
02/05/05 1105 1115
02/05/05 1106 1245
and so on.
I want to generates a query (and then a report) like this:
Date from bill order To bill order
amount
01/05/05 1101 1104
9046
02/05/05 1105 1106
2360
and so on.
Any suggestions
TIA
