Newbie: Create report from four XL tables?

[Ed from AZ]

I am working with a database that was old when the term "legacy" was
coined! I can run queries and save the results out as Excel files.
The report generator, though, is the pits and is not user-friendly.
(I'm on my second day of reformatting a 450-page report!)

The data I need for a report would have to be saved out from four
different queries: one query would have one line per key field, the
other three would have multiple lines per key field.

I touched Access briefly several years ago, and know just enough to
get myself in deep trouble. So would it be difficult for a newbie to
create a report in Access that pulls data from these four Excel
tables? Where would I go to begin this journey?

Ed

 

[Jeff Boyce]

Ed

Open an Access database (can be empty/new).

Go to the tables tab in the database window.

Use <File | Get External ... | Link...> and select Excel as a file-type.
Navigate to where the 4 Excel files are and tell Access to link to them
(?you may need to do one at a time...).

You now have four "tables" (linked tables) in Access. Use them as if they
were "local". Create queries. Create reports.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ed from AZ]

Thank you, Jeff! That seems easy enough even for me!

I assume I will have to go into each table in turn and define the key
field? Or would Access pick that up automatically? (I have Access
2003.)

Ed

 

[Jeff Boyce]

Hold on a sec...

?"key field" ... how did that get involved. Do your Excel spreadsheets have
"key fields"?

If you experience slow query performance, then you may need to "import" the
data rather than link to it. Once your Access database has a "local" table,
you can open the table definition and set primary key and indexing.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ed from AZ]

?"key field" ... how did that get involved.
Sorry, Jeff. I just thought I remembered that the tables all need to
be linked by identifying a "key" field - that way Access would know
that the one line in Table 1 goes with the several lines in Tables 2,
3 and 4.

My report layout was envisioned as:

Header with Table 1 info for Item 1

Table 2, Item 1, Line 1
Table 2, Item 1, Line 2
etc

Table 3, Item 1, Line 1
Table 3, Item 1, Line 2
etc

Table 4, Item 1, Line 1
Table 4, Item 1, Line 2
etc

Header with Table 1 info for Item 12

Table 2, Item 2, Line 1
Table 2, Item 2, Line 2
etc

Table 3, Item 2, Line 1
Table 3, Item 2, Line 2
etc

Table 4, Item 2, Line 1
Table 4, Item 2, Line 2
etc

Am I about ready to screw up what should be a very easy task?

Ed

 

[Jeff Boyce]

Ed

Not necessary to have designated primary key in Access, just a very very
very good idea <g>.

If you want to get the best use of Access' relationally-oriented functions
and features, then Yes, you will need to design and populate your tables
with that in mind.

If you are only using Access as a way to tie together 4 Excel files (AND if
the files have common fields on which you can join them in queries), you can
skip this step. Of course, if performance is an issue, you may have to
reconsider...

Regards

Jeff Boyce
Microsoft Office/Access MVP