Newbie: Create report from four XL tables?
Ed from AZ
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
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" <prof_...@yahoo.com> wrote in message
news:1194013136.4...@v23g2000prn.googlegroups.com...
Ed from AZ
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
On Nov 2, 7:43 am, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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" <prof_ofw...@yahoo.com> wrote in messagenews:1194013136.4...@v23g2000prn.googlegroups.com...
>
>
>
> >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- Hide quoted text -
>
> - Show quoted text -
Jeff Boyce
?"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" <prof_...@yahoo.com> wrote in message
news:1194015958.3...@k35g2000prh.googlegroups.com...
Ed from AZ
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
On Nov 2, 8:24 am, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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" <prof_ofw...@yahoo.com> wrote in messagenews:1194015958.3...@k35g2000prh.googlegroups.com...
> >> - Show quoted text -- Hide quoted text -
Jeff Boyce
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
"Ed from AZ" <prof_...@yahoo.com> wrote in message
news:1194024666....@i38g2000prf.googlegroups.com...