Help on a table design (Importing)

[Chris Salcedo]

Hi, I am new to access so I will start slow..

I need someone to look at a report that get generated at my work every
week. Its in excel but its formated for viewing not for data
extraction...

The report can have several hundred lines of info per project and there
are 400 projects that need to be stored.

What I need is for someone to look at the report and see if it can be
imported somehow...

anyone interested?? I can send an excel file with some example
reports...

 

[Vincent Johns]

Hi, I am new to access so I will start slow..

I need someone to look at a report that get generated at my work every
week. Its in excel but its formated for viewing not for data
extraction...

The report can have several hundred lines of info per project and there
are 400 projects that need to be stored.

What I need is for someone to look at the report and see if it can be
imported somehow...

anyone interested?? I can send an excel file with some example
reports...

My guess is that this will be a complex operation, though probably not
as involved as converting the project to Access. (I did one of those
recently and was happy with the results, but it took a couple of months,
not the sort of thing you could easily get done here.)

Have you tried importing a named range into a linked Access Table? If
that works, you're probably in good shape.

Otherwise, it might be fairly easy to set up a procedure in Excel to
print a selected page or range to a text file, then to import that into
Access via the Text Import Wizard. Doing it this way would allow you to
inspect the text file for format problems, such as integers mysteriously
turning into scientific notation.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

Hi, I am new to access so I will start slow..

I need someone to look at a report that get generated at my work every
week. Its in excel but its formated for viewing not for data
extraction...

The report can have several hundred lines of info per project and there
are 400 projects that need to be stored.

What I need is for someone to look at the report and see if it can be
imported somehow...

anyone interested?? I can send an excel file with some example
reports...

Well... the first thing to realize is that Access is not "a bigger
Excel". IT IS VERY DIFFERENT. Excel is a spreadsheet, the best of that
genre; Access is a relational database. The entire logic of these two
different kinds of program is different.

For one thing, there are only minor differences between data storage
and data display in Excel; in Access, these are very much separate.
Your Access Tables should contain only the "base data", no expressions
or calculations. Expressions and calculations should be done in
Queries; and finally, a Report will be based on a Query, and will
allow further calculations (such as subtotals and grand totals).

Importing an Excel Report into an Access Table is simply the wrong way
to go, and will guarantee frustration and a bad result. Instead, you
need to come up with a logical, normalized table design (with a
400-row table of Projects, a thousands-of-rows table of info about
projects, etc.) and populate this table (somehow) from the data in
your Excel spreadsheets. Then you'll need to build Queries to link the
data in the tables, and finally a Report to display the information in
the same appearance as the Excel report. The final product will look
the same but the underlying logic will - of necessity - be very
different.

Actually doing all of this for you would be well beyond what's
reasonable for a free peer-support newsgroup, but we'll be glad to
help you move in the direction you need to go.

John W. Vinson[MVP]

 

[Vincent Johns]

Hi, I am new to access so I will start slow..

I need someone to look at a report that get generated at my work every
week. Its in excel but its formated for viewing not for data
extraction...

The report can have several hundred lines of info per project and there
are 400 projects that need to be stored.

What I need is for someone to look at the report and see if it can be
imported somehow...

anyone interested?? I can send an excel file with some example
reports...

Well... the first thing to realize is that Access is not "a bigger
Excel". IT IS VERY DIFFERENT. Excel is a spreadsheet, the best of that
genre; Access is a relational database. The entire logic of these two
different kinds of program is different.
[...]

The final product will look

(here I would have said "might" look the same; once it's in Access you
have a lot of flexibility about what you do with the information, so it
could look the same or way different)

the same but the underlying logic will - of necessity - be very
different.

Actually doing all of this for you would be well beyond what's
reasonable for a free peer-support newsgroup, but we'll be glad to
help you move in the direction you need to go.

John W. Vinson[MVP]

I think John's description of the differences between Excel and Access
is an excellent summary, but you might be left wondering why bother
converting to Access, since it's likely to involve lots of work. I
would say that, once you've moved to Access, keeping track of your
information can be a lot easier and you have much more flexibility in
what you can do with it. Setting up a new report in Excel can be done,
but it's likely to be difficult.

Also, Access databases are more scalable. Excel spreadsheets are
limited in size, whereas you could have millions of records in an Access
Table.

OTOH, some operations are much better left in Excel, such as fancy
arithmetic (e.g., Excel can optimize systems of non-linear relations;
Access has no clue how to do that). Therefore, Microsoft has gone to
some effort to make it easy to share information between an Excel
workbook and an Access database.

So, I suggest not rushing into converting your system, but if you do so
successfully, you will likely be pleased with your new capabilities.
(And no, Microsoft did not pay me to say this.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.