Access/Excel/Word Challenge

[Guest]

Hi,

I'm trying to decide the best approach to this problem and would appreciate
any help:

1. I have a template for a report in Word that refers to fields in an Excel
worksheet.
2. The Excel worksheet, however, is not set up to easily use in the Word
template. Instead of one person with many fields (columns) I have the same
name multiple times, with one column listing multiple pieces of information
for each person, for example:

First Last Cars Devices
Jane Doe Altima Laptop
Jane Doe Lexus Desktop
Jane Doe Blackberry
Jim Smith Toyota Laptop
Jim Smith Desktop
Mary Jones Toyota Laptop

My first thought was to import the data into Access, normalize the data with
a tblPersons, tblCars, tblDevices and then do the merge.

My Problems:

1. The Word merge document has many pages, different first page header, and
many paragraphs of text that contains bold, different font colours, etc.

2. If I try to merge to Word, I get a separate "report" for each car and
device for each person, instead of being able to simply have their name once
and then place the car and device fields where I wish (and multiple times
throughout the reprt if required).

3. If I try to create a report in Access, I can't add the text from the
Word file that is formatted properly.

I may have to resort to code in Excel that transposes the fields unless I
can come up with a viable solution in Access.

Any idea would be greatly appreciated!

 

[Peter Hibbs]

Dee,

If you can get all the information into an Excel file you may be able
to use the Excel-Access Converter Utility program at :-
http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'
to copy the data into separate linked tables in your database
automatically.

HTH

Peter Hibbs.

 

[Guest]

Hello and thanks.

I don't have a problem importing them and setting up the tables. I guess my
question was more - what is the best approach in terms of which program(s) to
use.

I think at the moment my best bet is to import/link the Excel data into
separate tables, importing only the fields I need for each one, then using
these as the basis for a query that is the basis of my report.

It's more perhaps a question of getting the Word report format into the
Access report, but I guess simple text fields that are copied and pasted,
then placing the various fields where I need them will work. Access reports
aren't as easy to format, IMHO, as Word files, but it's doable.

 

[Peter Hibbs]

If you can get all the data into Access tables then you can create a
query bound to a report. You should be able to make the report look
(almost) identical to a Word document with the required fields. The
problem would be if the users wanted to change the layout of the
report themselves, which would be difficult. If that is likely then
another option would be to create the 'report' in Word and use
Automation to copy the data from a query into the Word document.
Obviously that would require a lot more coding. It may also be
possible to use Word mail merge by copying the data from a query into
a temporary table (which can be designed to hold the data in a
different way to the normal tables) and then bind the mail merge
document to that table. It all depends on how many records/documents,
etc that you have and the format of the data, etc.

Apart from that I don't have any other ideas, sorry.

Peter Hibbs.

 

[Guest]

Hi and thanks again.

I have imported, created relationships, and a query upon which I have based
my report. I then copied and pasted the Word text (it's a multi page report)
where I needed to.

The only problem I have now is that while I have the main heading FirstName,
LastName repeating properly on each page, instead of being able to list the
cars and/or devices one after another (a couple of times will be required
throughout the report), it creates a separate page for each item.

So, instead of:

Here is a listing of the cars we have on record for you:
Altima
Toyoto

it shows:
Here is a listing of the cars we have on record for you:
Altima
(new page)
Here is a listing of the cars we have on record for you:
Toyoto

If I can get it to list the items properly, I'm fine. If you have any ideas
on this, it would be greatly appreciated.

 

[Peter Hibbs]

I am assuming you are now talking about an Access report and that you
have one table (tblPersons) with ONE record for each person, i.e. Jane
Doe, etc and that you have another table (tblCars) linked to
tblPersons as a One to Many relationship with one or more car records
for each person. Your query is returning a list of persons and the
type of cars they have and is the source for your report.

If that is the case it sounds like you need separate headings in your
report. If you open the report in Design mode and click on the Sorting
and Grouping icon you can create a group header for your person
information (set the Group Header option to Yes) and the cars
information will stay in the Detail section of the report.

This will let you show the person information with the car information
under each person. Of course, there is lot more to it than that, you
will probably need field heading labels and set other options such as
Repeat Headings, etc, etc, but that is the basic principle. If that is
what you have in mind then I suggest you read up on this subject and
see how you get on.

HTH

Peter Hibbs.

 

[Guest]

Hi Peter,

Thanks again. I had actually already done what you had suggested. It works
fine until I insert the text from the Word file, at which point it creates
separate pages for each, say, car.

So, instead of a 5 page report, with the cars listed together at various
points, I end up with the first page, followed by 5 pages for each car!

Pulling my hair out over this one!

 

[Peter Hibbs]

Dee,

I don't understand what you are doing when you 'insert text from the
Word file', perhaps you could explain that in more detail.

Peter.

 

[Guest]

Sure. I start the report with the wizard just to get my fields and grouping.

Then, I copy and paste the portions of text I need from my Word file.

So, I end up with:

Employee Name, Number, etc. (Grouping by this)

Text from Word file

List of cars, for example

Text from Word file

List of devices, for example

Text from Word

List of cars again

Text from Word

I hope I've made it clear enough and thank you very much.

 

[Peter Hibbs]

Hi Dee,

I still don't see why you need to copy and paste any text from Word
(it probably wouldn't work anyway because of the embedded control
codes). Can you not just type in the text needed into the report. If
you are using Access 2000 or 2003 I would be prepared to look at the
database and see if I can do anything with it, no promises though. If
you are interested send me a copy of the DB and the Word file in a Zip
file to (e-mail address removed)_SPAM (remove the NO_SPAM bit
first). other than that I don't know what else to suggest.

Peter Hibbs.

 

[Guest]

I'm copying and pasting because I already have the text in a Word file.

The data is confidential, but I will create a "dummy" file and send it to
you later.

Thanks so much!