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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"silva" <(E-Mail Removed)> wrote in message
news:44FB2D73-36F9-43C3-94BC-(E-Mail Removed)...
> 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.