A
Andrew M.
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.
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.