Do I need to create 6 Reports

[Bob Vance]

I have 6 queries just like this but they all have different
"tblHorseInfo.Service1," "tblHorseInfo.Service2," and so on . For me to
print the result of each query do I have to create 6 Different report one to
each query!

SELECT tblHorseInfo.HorseID, tblHorseInfo.ServiceStallion,
tblHorseInfo.Service1, tblHorseInfo.ServiceDate, qryHorseNameAll.Name,
Format([ServiceDate],"dd-mmm-yy") AS MyDate, tblHorseInfo.Status,
tblCompanyInfo.Warning1
FROM tblCompanyInfo, tblHorseInfo INNER JOIN qryHorseNameAll ON
tblHorseInfo.HorseID = qryHorseNameAll.HorseID
WHERE (((tblHorseInfo.Service1)=0) AND
((tblHorseInfo.ServiceDate)<Date()-[Warning1]) AND ((tblHorseInfo.Status)
Like "Active"))
ORDER BY tblHorseInfo.Service1;

 

[Piet Linden]

I have 6 queries just like this but they all have different
"tblHorseInfo.Service1," "tblHorseInfo.Service2,"  and so on . For me to
print the result of each query do I have to create 6 Different report oneto
each query!

SELECT tblHorseInfo.HorseID, tblHorseInfo.ServiceStallion,
tblHorseInfo.Service1, tblHorseInfo.ServiceDate, qryHorseNameAll.Name,
Format([ServiceDate],"dd-mmm-yy") AS MyDate, tblHorseInfo.Status,
tblCompanyInfo.Warning1
FROM tblCompanyInfo, tblHorseInfo INNER JOIN qryHorseNameAll ON
tblHorseInfo.HorseID = qryHorseNameAll.HorseID
WHERE (((tblHorseInfo.Service1)=0) AND
((tblHorseInfo.ServiceDate)<Date()-[Warning1]) AND ((tblHorseInfo.Status)
Like "Active"))
ORDER BY tblHorseInfo.Service1;

No. Create the unfiltered report, and then pass the filter in the
open event of the report.

 

[Clifford Bass]

Hi Bob,

No, especially if you fix your database design so that the service
information is stored in a separate table instead of putting multiple
instances in one table. Unless I am incorrectly inferring your structure.

tblHorseInfo
HorseID
HorseName
(other horse-specific info)

tblHorseServices
HorseID
ServiceNumber
ServiceDate
ServiceStallion
(other service specific info)

If fixing your database structure is not an option, you can, in code,
just change the report's Record Source property. You can either specify a
different query or a different SQL statement. In the list of fields to
select in each query, alias any columns that are service number specific so
that they all use the same alias:

select ... tblHorseInfo.Service1 as Service ...

select ... tblHorseInfo.Service2 as Service ...

Then in your report you use the alias as the Control Source instead of
the specific field.

Hope that helps,

Clifford Bass

 

[Bob Vance]

Thanks Piet, so I change the query to all records, no filter on tblService1
In my Open Event of my report what do I have enter to filter
Thanks Bob

I have 6 queries just like this but they all have different
"tblHorseInfo.Service1," "tblHorseInfo.Service2," and so on . For me to
print the result of each query do I have to create 6 Different report one
to
each query!

SELECT tblHorseInfo.HorseID, tblHorseInfo.ServiceStallion,
tblHorseInfo.Service1, tblHorseInfo.ServiceDate, qryHorseNameAll.Name,
Format([ServiceDate],"dd-mmm-yy") AS MyDate, tblHorseInfo.Status,
tblCompanyInfo.Warning1
FROM tblCompanyInfo, tblHorseInfo INNER JOIN qryHorseNameAll ON
tblHorseInfo.HorseID = qryHorseNameAll.HorseID
WHERE (((tblHorseInfo.Service1)=0) AND
((tblHorseInfo.ServiceDate)<Date()-[Warning1]) AND ((tblHorseInfo.Status)
Like "Active"))
ORDER BY tblHorseInfo.Service1;

No. Create the unfiltered report, and then pass the filter in the
open event of the report.