Using an excel sheet as a database and more...

[Guest]

Hi, I like to think I'm experienced with Excel, but have next to no knowledge
of Access.

I've been trying to figure out how to take a spreadsheet in Excel that i
created and having Access pull information from the .xls, filtering it(see
below for specifics), and outputting it in a simple and condensed form of the
database.

I originally constructed the Excel file to be easily used by users that have
next to no experience with Excel. It consisted of two sheets: Shop Order
Info & Yearly Costs.

Shop Order Info consisted of:
Column A - Date shop order created
Column B - Date shop order closed
Column C - Part Number
Column D - Cost/part
Column E - Hours
Column F - Quantity

When a new Shop Order(Aka SO) was created, the user only needed to fill in
Columns A & C. When the SO was complete, the rest of the info was entered.

This was the simple, new user sheet.

The second sheet was far more complicated(I protected it when i wasn't
working on it to keep other users out) and it is what i want to do with
Access being that Excel had some problems correlating cells in the same row,
on the first sheet, to multiple rows on the second sheet while using an index
of Sheet 1's column D. (I could go into this further but this is not the
place for such a problem)

Yearly Costs

The point of this sheet is to take information from Open Shop Orders and
present it in a simpler/condensed fashion, making it easier to understand
1000's of rows of information.

Column A - Part #
Originally it just copied over the part number from Shop Order Info, but
that proved to be insufficient. I extended the formula to include:

IF("same part #" > 1) {""}
Else(Column A = 'Shop Order Info'!Column C(matching row))
(I had also at point included a '-year' to 'Shop Order Info' part # to make
it easier to correlate costs to specific years. But again there were
problems. This time with the -year being duplicated and therefore not finding
the related cost)

Column B+ - Years
-(Each row's header)2006, 2007, 2008, etc...costs per part for each part
#(cost/part outputted to part#'s row in relation to the year it was
completed)

-IF(# of SO for a given part>1){Output the cost of the newest cost for that
year}
Else(Output the cost for that part# and year)

So, based on the above requirements and information, is it possible to do
this with Access, and, if so, how would i go about doing so?

Thanks for your time.

 

[PFC Sadr]

a) uninstall Excel
b) read a half dozen books on SQL Server
c) spit on anyone that uses Excel for anything. Excel isn't a
database
d) don't bother with Access MDB.. just move into SQL Server

a 'condensed form of the database' is known as a QUERY

 

[Guest]

A)Are you saying that Access/Excel aren't capable of doing what I am trying
to do?
B)You're the 2nd person to tell me to learn Sequel.
C)Excel is a spreadsheet program that "can" be used as a database.
(Especially so with it's cell-relative correlation, it just doesn't have the
sentience/understanding to know what is in the cells)
D)Don't hate/spit on people that use Excel, especially if you're going to
insult someone who apparently isn't as "SQL-savvy" as you possibly could be.
Not everyone has the same level of experience with computers as others.
E) You're probably right with the suggestion to move to Sequel, I just
wanted to be sure that i couldn't deal with what i already had.

 

[Guest]

Also, I don't have the time to learn and set up a SQL server, nor do i have
the resources to get a SQL server running.

So, yea, anyone have any ideas about my first post?