What is the best way to query data and put into a report?

[BABs]

I have an Access database that stores client, property, and inspection data.
I use this data to create reports with varying degrees of detail. I
originally tried to make a report in Access to pull the data needed using a
query of job number, but this was just too much trouble. The report contains
a lot of text and tables with the querried data inserted at certain
locations, some data being used multiple times. I don't have much experience
with Access reports, so I chose to use Word instead. What I have now is: an
Access form where the employee picks a job number from a combobox; using "on
update" code, a series of queries are run using the selected job number and
the results are exported to an Excel workbook with multiple sheets; then the
form closes and "on close" code opens a Word template where field codes pull
the needed data from the Excel workbook; once the document fields are
populated, the fields are unlinked, the template switched back to normal, and
the document saved with the job name. With all of this going on and all the
field codes, REF and IF fields, this process takes a long time, approximately
20 minutes per site and some reports have up to 15 sites. I have a feeling
that the way I am doing this is the LONG way around. Could someone
enlightened me on a more efficient way to accomplish this task, PLEASE?
TIA

 

[Fred]

This is analagous to you having a 10 story skyscraper, telling us (only)
about a few things going on on the 10th floor, and then asking people what
they think about the skyscraper and to possibly recommend a design for an
entire replacement skyscraper.

It was unclear whether or not you know/understand the structure/details of
what you have (which includes knowoing Access well enough to know exactly
what's happening)

- If so, I think that you'd have to narrow your question and provide more
thorough info for that particular question.

- If not, then I think that you will need to find a way to obtain many hours
of help.

 

[BABs]

Fred,
Nice analogy. In a way, I am a jack of all trades and master of none. I
don't claim to KNOW Word, Excel or Access because there is so much to know,
but I have learned enough to have built this whole gummed up report process
from the floor up (nice reference to the skyscraper).
What exactly do you need to know to give me some suggestions? I thought I'd
keep it simple and stick to the question of how is the easiest way to get
info from an access query into a word document, but as you've pointed out,
there's more to it than that.
In my db,
I have several tables that are linked by site name (i.e. lease, permit,
storage, concerns, etc). The site table links records together by job
name/number, so if I query for a job number, it will show me all the sites
for that job, if more than one. My report form asks for the job number that
the employee wants to create the report for. After update of the number in
the combobox, code runs a query to determine how many sites are in that job.
If only one site, ten different querries are coded to run and return all the
data for that site (I had to make several querries because each table could
have more than one record for a site, i.e. 5 leases and 10 permits for one
site), export the data to 10 sheets in an excel workbook (report info.xls).
If more than one site, it runs the same querries but then saves the workbook
as "site 1.xls", etc. Once the data is exported to excel, code closes the
form and opens a word .dotm which pulls the info from the excel workbook
using LINK, SET, REF, and IF fields. Finally, once the fields are populated,
the template is saved as a doc.
(in short?)
open report form in access
choose a job number/name from a combobox(list2)
a macro runs the query chain using (list2) as the criteria
docmd exports query data into excel (report info.xls)
code closes the report form
on close, code opens single site.dotm (access is finished)
word opens the template which has MANY fields linked to report info.xls and
looks like
blah blah blah { link to excel "site name"} blah blah blah {link to excel
"site address} blah blah blah {link to excel "number of leased parcles"}.
Parcel Owner Expiration
{link to excel Parcel1 Owner name expiration}
{link to excel Parcel 2 Owner name expiration}
etc or {link to excel table Parcels}
This site was inspected by {link to excel "inspector"}.


Does that make any more sense? I could send you the code and files, but the
step that takes the longest is just populating the .dotm fields with the
excel data.
Thanks for the help