Report with multiple queries

[lauritz]

Hi,

I need to generate a report but am not sure how to select the correct data.

In my table I have a list of engines with a static number of parts. Each part has a remaining bearing life and a remaining component life field. For each engine I need to report the part that has the lowest remaining bearing life and the part that has the lowest remaining component life.

All help appreciated.

 

[dhookom]

Do you have table structures and sample records you would like to share? We have no idea how your tables are set up.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MajP]

You could probably do this in one query, but it would be hard. I would make two seperate aggregate queries. qryMinComponentLife, and qryMinBearingLife.
Here is qryMinBearingLife
Basically group on the the parent Engine ID and take the minimum bearing life for each engine's parts.

SELECT Min(tblEngineParts.intBearingLife) AS MinOfintBearingLife, tblEngineParts.intFKeyEngineID, First(tblEngineParts.strPartName) AS strPartName
FROM tblEngineParts
GROUP BY tblEngineParts.intFKeyEngineID;

Do the same for component life.

Now you could do this in several ways. You could build a form that groups these queries to the engine table by the engine ID, or you could embed them as subreports in the main report.

 

[dhookom]

Using First() in this SQL is not guaranteed to grab the strPartName associated with the Min(intBearingLife).

I'm holding off on providing a suggestion until we know something about the tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MajP]

Your right. I don't know what I was thinking. For some reason I was thinking you would pull the min record/s first, and if it was a tie it would return the first.

 

[lauritz]

Thanks for your help so far guys. What I have done is build a function which runs two queries, one to get the lowest bearing life and another to get the lowest component life. I then create a temp table record for each query for each engine. That's the easy part the data in the temp table is correct, but how do I now run this query and provide my report with the data??

 

[dhookom]

To "run this query and provide my report with data", you generally use the query in the report's record source.

We still have very little information about your tables and data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lauritz]

My table is as follows:

Field Name Data Type
engineId text (puk)
component text (puk)
PartNo text
SerialNo text
LifeHours number
LifeCycles number
TotalHours number
TotalCycles number
RemainingHours number
RemainingCycles number

For each engineId I need to find the component with the lowest remaining hours and the component with the lowest remaining cycles.

 

[dhookom]

To get the components with the least remaining hours, try something like:

SELECT "LeastHours" as Title, *
FROM [as follows]
WHERE EngineId & Component =
(SELECT EngineId & Component
 FROM [as follows] as AF
 WHERE [as follows].EngineID = AF.EngineID
 ORDER BY RemainingHours)

To get the least remaining cycles, try:

SELECT "LeastCycles" as Title, *
FROM [as follows]
WHERE EngineId & Component =
(SELECT EngineId & Component
 FROM [as follows] as AF
 WHERE [as follows].EngineID = AF.EngineID
 ORDER BY RemainingCycles)

YOu can save the two queries above to use as subreports or in a union query.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

I think I might have missed the "TOP 1" from the queries

SELECT TOP 1 "LeastHours" as Title, *
FROM [as follows]
WHERE EngineId & Component =
(SELECT EngineId & Component
 FROM [as follows] as AF
 WHERE [as follows].EngineID = AF.EngineID
 ORDER BY RemainingHours)

To get the least remaining cycles, try:

SELECT TOP 1  "LeastCycles" as Title, *
FROM [as follows]
WHERE EngineId & Component =
(SELECT EngineId & Component
 FROM [as follows] as AF
 WHERE [as follows].EngineID = AF.EngineID
 ORDER BY RemainingCycles)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lauritz]

Thanks for that. I'm still not clear on how I can save the two queries and use them as sub reports. I'm not very experienced with VBA. Thanks again for your help.

 

[dhookom]

You don't need vba to create queries and reports. A subreport is a report that is embedded on a main report. A report has a record source property that is a query, table, or SQL string.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]