Feel like an idiot ~ For the Love of God, Can someone help?

[Champ]

I have a HUGE spreadsheet, 212 tabs at the bottom.
I need to create a databse that will allow me to transfer my excel
masterpiece - used losely - into a database and do a count, by month,
of all of this data. The goal is to see how many hammers, when I ask-query,
were used on each month and each year, along with the cost, color, etc.

My information is as follows:

1st tab:
Jan 1990

2nd tab:
Feb 1990

3rd tab:
Mar 1990

4th tab:
Apr 1990

Through current Month and Year.

Each tab/sheet is as follows:
cell A1 cell B1 cell C1 cell D1 cell E1 cell
3092323 hammer green 2 $14.34 pr


cell A1 cell B1 cell C1 cell D1 cell E1 cell

4456782 tape yellow 47 $3.71 ea

and so on, to fill 65,536 lines down by 31 lines across.(lots of information)

I want to bring the information, 8 of the 31 lines/cells across, from each
of the 212 tabs/sheets in this workbook and have them auto update the master
sheet information that is already put into a master sheet, as a count to the
appropriate cells, as follows:

cell A1 cell B1 cell C1 cell D1 cell E1 cell
4456782 tape yellow 47 $3.71 ea

cell A2 cell B2 cell C2 cell D2 cell E2 cell
4456782 tape yellow 47 $3.71 ea

cell A3 cell B3 cell C3 cell D3 cell E3 cell
4456782 tape yellow 47 $3.71 ea

and so on - basically I want to count, from each month tab and each year
tab, how many of each part went in each month and year, so I can build a
trend for inventory.
The cells in each page/tab; i.e. cell A1 on any given page is not the same
as any other page either. (that is due to some months that part was not used).
This is huge and I have no idea how to do it, so I do realize that this is
beyond what I know. Please help.

 

[Douglas J. Steele]

Sorry, but this newsgroup is for questions about Access, the database
product that's part of Office Professional.

You'd be best off reposting your question to a newsgroup related to Excel
(although, to be honest, Access would be a far better tool for what you're
doing than Excel!)

 

[Golfinray]

WOW!!!!! I feel your pain. Will each tab be a table? I would certainly try to
get to where each year is a table and not each month. This is not a good way
to do it, but it would be a starting point. Good luck!

 

[Mark Andrews]

Try something like this:
http://www.microsoft.com/technet/scriptcenter/resources/qanda/jan08/hey0121.mspx

Basically first goal would be to get everything into one table (with a date
as one of the columns).

HTH,
Mark
RPT Software
http://www.rptsoftware.com

 

[Champ]

I truly thought a databse question belonged in access.. I will re-word this
in Access..

Thanks folks.

 

[Champ]

Thanks Mark,
I am reading that as we speak and trying to digest what the web site is
teaching.
I have never built an Access database, so things are slow for now.
My tabs, in the Excel spreadsheet, do have each their own label' i.e. Jan
1989, Feb 1989, Mar 1989, etc.

I am trying - thank you very much.
Champ

 

[Douglas J. Steele]

Sorry, I missed the fact that you mentioned database on the second line. The
fact that you mentioned "auto update the master sheet information that is
already put into a master sheet, as a count to the appropriate cells" made
me assume Excel.

Ken Snell has some good information about importing Excel data into Access
at http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm

My advice would be to link to each spreadsheet, one at a time, and then use
that linked table as the basis for an Append query to put the data in its
final resting home.

 

[John W. Vinson]

I have a HUGE spreadsheet, 212 tabs at the bottom.
I need to create a databse that will allow me to transfer my excel
masterpiece - used losely - into a database and do a count, by month,
of all of this data. The goal is to see how many hammers, when I ask-query,
were used on each month and each year, along with the cost, color, etc.

You're absolutely right that this data would be much better in Access. Getting
it there will be a BIG chore however! Access is *not* "Excel on steroids";
it's a totally different environment, with a different mindset required.

Your 212 tabs will become <gasp> *one table*.
You will NOT have an "April 2000" table - instead there will be a Date/Time
field in the big table with a date value.

Take a look at some of the resources, especially Crystal's tutorial; her
description of "Normalization" should be helpful.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Mark Andrews]

Email me if you need help. See my site for contact info.

Mark
RPT Software
http://www.rptsoftware.com

 

[Fred]

My main usefulness comes from being less smart than these other guys.

Assuming that all of your sheets have the same columns with the same
headings, and that your headings are valid for Access field names.

If OK, decide that your dates are a certain same day of the month (like the
first) So June 1990 = 6/1/90 etc. Change names per your preference.

Use "get external data" to import the first sheets into a table named
"MainTable' Add a date type field "Date1". While note necessary, yet, add
an autonumber field "ItemIDNumber" and make that your PK, and then another
"WhenRecordCreated" date type field with Now() as the default value. (both
will help undo mistakes)

Make a update type query named "LoadDate", and tell it to update the Date1
field to [EnterDate] with a criteria that the Date1 field is null.

Import The Second Sheet into a table called "ImportTable"

Make an append query "LoadImportTableIntoMainTable" that appends the import
table into the main table. (don't run it yet)

Make a delete query"EmptyImportTable" that deletes all records form the
ImportTable. (don't run it yet)

Make a Macro that runs these queries in this sequence:

1. LoadImportTableIntoMainTable
2. LoadDate
3. EmptyImportTable

Now run the Macro. It will ask you for the date.

Import the third sheet. Then run the Macro. Repeat and rinse.

Maybee the other respondents can suggest shortcuts. This will be like
mowing a lawn with scissors, but once you're done, you'll be master of the
universe and can do anything quickly.

Sincerley,

Fred

 

[Mark Andrews]

The "repeat and rinse" drives the main question of "write more code to
automate the whole thing?" or just go insane for an hour or two doing a
sheet at a time.

If it was 10 sheets I would "repeat and rinse" at 200+ I would opt for the
other way. I'm assuming code can be written to do the whole thing, however
haven't done this particular exercise.

My two pennies,
Mark

 

[Fred]

Mark,

I'm sure you're right.

I was just trying to give him something more concrete, to start with,
particularly with him being new to Access.


Sincerely,


Fred

 

[John... Visio MVP]

Hello,

After all the suggestions you got from all the responders, if you need
help setting up a database from your workbook, contact me at
(e-mail address removed). I provide help with Access and Excel applications for a
very reasonable fee.

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support,
not as a venue for little stevie to hawk his questionable wares.

Stevie is a known troll who enjoys exploiting unsuspecting posters with
offers of questionable help at questionable prices. Stevie has proven many
times in these newsgroups that his free help is overpriced.

John... Visio MVP

 

[John... Visio MVP]

Email me if you need help. See my site for contact info.

Mark
RPT Software
http://www.rptsoftware.com

Careful, these newsgroups by Microsoft for FREE peer to peer support. I have
to keep reminding your nieghbour stevie of this fact and he will complain if
I do not mention it to you.

John... Visio MVP

 

[Mark Andrews]

John,

I wasn't necessarily going to charge him for help. Sometimes I'm just a
nice guy, or I'm bored so why not help others.

Note: I did try and provide some free support on this thread and I had the
feeling that this person might need some extra help.

I will try to be very careful in the future and not do anything that blurs
the line between free and paid consulting.

Mark