Details on a Report

[silva]

I've put together a database for keeping track of vehicle maintenance. I'm
using three tables: The first has the information on the vehicle such as VIN,
make, model, etc. The next table keeps track of when and where service was
performed. It has the date, mileage, and service location. The third and
final table has all the details on the services performed, such as oil change
and tire rotation.

My question has to do with how a search would display items on a report.

What I would lke to be able to do is use a particular criteria in the query
that underlies the report and have all other items done in that service show
up as well. Currently, if I use something like "Oil Change" in the criteria,
it only shows "Oil Change" in the report. I would like to have all other
items such as "Replace Air Filter" and "Machine Brake Rotors" appear with it,
supposing they occured on the same visit, if that makes sense.

Here's my table layout (asterisks denote primary key):

[Table_1]
vehicle
plate
**VIN**
driver

[Table_2]
VIN
date
mileage
location
**record_id** (autonumber field)

[Table_3]
**line_num** (autonumber field)
record_id
description

[Table_1] and [Table_2] are linked via the VIN field.
[Table_2] and [Table_3] are linked via the record_id field.

If any further clarification or information is needed, please let me know.
I'd like to solve this dilemma.

 

[Allen Browne]

Use a subquery as the filter for your report.

If we assume the report is based on a query that uses the 3 tables and
returns Table_2.record_id (but not Table_3.record_id), you could put a
command button on a form and launch the report like this:

Dim strWhere As String
strWhere = "record_id IN (SELECT record_id FROM Table_3 AS Dupe " & _
"WHERE Dupe.[Description] = ""oil change"")"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html