Report sorted by date...

[Joven76]

I need to make a report that shows information in a specified date range... Sounds easy right??? Nope... My wife says I never do anything easy ...

I have created a query called "Expense Mileage" from the table that has my information in the following fields, which I want to build my report from

[MileageID]
[Date]
[StartingMileage]
[EndingMileage]
[Reason]

In my report I just need the date, starting & ending mileage, and reason... I will be calculating the totals in the report itself...

So, question 1... I have been told that I can create a form, which I've already done and called Required Date Range. It has 2 unbound fields called [StartingDate] & [EndingDate], in which I can input the required date range, and the report will use that to filter the dates I want, but how do I get the report to pull up that form first so I can input the date range, then use it to filter in the query and display what I want on the report???

My 2nd question is a little more involved... I have another area in the database where I track the mileage of a delivery on an order... The first is tracking the mileage going on sales calls and other marketing expenses... That "Order Mileage" query has the following fields:

[OrderID]
[Date]
[CustomerLastName]
[CustomerFirstName]
[Mileage] - we go ahead and calculate the mileage here and input the total..


I would like to show the mileage for orders in one part of the report, then below that show the mileage from expenses, utilizing the same date range, but separated in their own separate categories... Is this possible or am I just going to have 2 reports for mileage???

Thanks in advance for your help...

 

[jmh944911]

suggested design changes for item 1

You can accomplish what you want. I suggest that some small design changes will make the whole thing simpler.
1) create a query for your source table. Use the inputs from the form in the query something like this :
Between [Forms]![frmReport Menu with Dates]![BeginningDate] And [Forms]![frmReport Menu with Dates]![EndingDate]

(so your query will do the selection work for you)

2) Base the report on the query and not on the table

The moving parts:
1) base data table with your records
2) date input form
3) query - source from (1 - selection based on (2
4) report based on 3)

 

[jmh944911]

Design suggestion for item 2

There needs to be some way of connecting the expense records to the order. If you have an established internal identifier then use that to join the records from expenes and orders (assumption that they are in different tables). If there is no unique ID then you might get by joining on customer and date. So your query has:
1) two source tables - expenses, and orders
2) the tables are joined based on something as noted in opening paragraph
3) the grid will contain the join filed and a value field from expenses and a value field from orders.
4) use the report to sum the values for a grand total.
5) subtract on sub total from another :
unbound field = (orders total value) - (expenses total value)