Using Calculations on report from 2 queries

[iattlakerod]

I've written a Maintenance database to capture Cost by (1) Repair Location, (2) Parts/Labor/Tires. Each time maintenance is done a specific unit is designated along with it's current mileage.

I've written a query to select date period [startdate]&[enddate].
and it captures the Unit's beginning and ending mileages.

Another query to retrieve the costs for each unit, by location, and by P, L, T.

To make a long question short, Can I write a report that uses both of these queries without duplicating data?

 

[barbarossaii]

Try a 'Sub-Report'

Hi iattlakerod,

you can display information from two queries in one report, if you use one query in the normal report and create a 'Sub-Report' for the results of the other query. Depending on where you insert your Sub-Report you can show totally different information (put the sub-report in the report footer) or information belonging to the records or groups of the main report (put the sub-report in the detail/group footer of the main report).

If you combine the query-results via sub-reports you should be able show:

unit ID.... its milage
costs of the unit

unit ID.... its milage
costs of the unit

unit ID.... its milage
costs of the unit

etc.


HTH,

Barbarossa II

 

[iattlakerod]

thanks for the help, that is going to work for all my reports w/mileage. Much thanks

 

[iattlakerod]

new question about same

Thanks for the information.

 

[iattlakerod]

Problem w/[startdate] andd [enddate]

I tried this and it seemed to work however the one problem I'm having is the date criteria. The Main query pulls out all of the cost items I want using a [startdate] and [enddate]. This is not however for some reason effecting the subreport to capture the mileage. No matter what dates I choose my mileages remain the same.

I tried adding a criteria to the subreport to select beginning and ending dates as well, but then when I run the report it prompts me first for the [startdate] then [enddate] and then again and again and again and again for several times. If not answered each time the report is filled with errors.

I hate to keep begging for the help but am totally lost at this point as to what to do to get around this problem. Any suggestions welcome......

 

[barbarossaii]

Hi iattlakerod,

to solve the described problems you should try to include the date as parameters in your 2nd query, but tell "the machine" it should use the parameters you entered once in your 1st query. Regarding the code, I am not sure but if in query 1 you got

between [startdate] and [enddate]

try in your SECOND query something like

between [queryname1]![startdate] and [queryname2]![enddate]

(or
between [queryname1]![columnOfDate]![startdate] and [queryname2]![columnOfDate]![enddate]
I've seen something similiar to this around here but I can't recall where).

HTH,
Barbarossa II

PS: Don't mind asking for help - You are welcome !
PS: I pull much help from this forum too !

 

[iattlakerod]

thanks again for the tip. I am going to give it a try. I've not been one to hang around these forums and such but I can see where it can be of much help. I'll post a response about how it works after trying. again, thanks

 

[iattlakerod]

Multiple query used in Report

fyi- still fighting with this dog!...This is one way i've set the queries. Now this works perfectly however I've preset the dates.

[GetMiles] query

SELECT DISTINCTROW Units.UnitYear, Units.UnitN, Max([speedometer])-Min([speedometer]) AS Mileage
FROM Units INNER JOIN RepairOrder ON Units.UnitId = RepairOrder.Unitid
WHERE (((RepairOrder.InvoiceDate) Between #1/1/2003# And #12/31/2003#))
GROUP BY Units.UnitYear, Units.UnitN
ORDER BY Units.UnitYear;

[Unit Cost] query

SELECT Units.UnitYear, Units.UnitN, RepairOrder.[Repair Location], Sum(CCur(nz([RepairOrderDetails].[partcost]))*CCur(nz([RepairOrderDetails].[quantity]))) AS Cost, Parts.PLT
FROM Units INNER JOIN (Parts INNER JOIN (RepairOrder INNER JOIN RepairOrderDetails ON RepairOrder.RepairOrder = RepairOrderDetails.RepairOrder) ON Parts.PartID = RepairOrderDetails.PartId) ON Units.UnitId = RepairOrder.Unitid
WHERE (((RepairOrder.InvoiceDate) Between #1/1/2003# And #12/31/2003#))
GROUP BY Units.UnitYear, Units.UnitN, RepairOrder.[Repair Location], Parts.PLT
ORDER BY Units.UnitYear, Units.UnitN;

THE REPORT:
Unit Cost is the main portion of the report.
GetMiles is the subreport.

Now when I change the "Unit Cost query" date criteria to:
[startdate] and [enddate]
I also change the "GetMiles query" to the following:

Field: InvoiceDate
Table: RepairOrder
Total: Where
Sort: "blank"
Show: "blank"
Criteria: between [unit cost].[startdate] and [unit cost].[enddate]

Problem: When I try to run the report I'm prompted for the startdate and enddate, then unit cost.[startdate] and unit cost.[enddate] and again and again and again.

Any suggestion? At this point I'd be glad to upload zip or e-mail or whatever. This thing has eatin' my lunch.

 

[barbarossaii]

Hi iattlakerod,

hmmmm, I've to admit that I am quite new to Access (and use the German A2000). A suggestion is that you create an unbound form w/ "fields" for the date and the other data. The form has to be open and then the entered variables can be used in several locations after starting the queries/reports via button on the form (create button w/ "toolbox").

In your queries you'd use

between [forms]![formname]![startdate] and [forms]![formname]![enddate]

(or something like that...).

I suggest that you try it with a simple example (if it works there you can be sure it will work w/ your more complex example)


HTH,
Barbarossa II

 

[iattlakerod]

hey hey hey. That solution worked great. You know even as little as I have worked in Access Databases I've seen that used, but the thought never entered my mind. Such a "No Brainer" fix. That was of course what I expected, that after getting my answer I'd say to self, "duh". Thanks a million. I'll be looking to use the forum more often in the future....