Transferring Excel Sheet To Access

[athyeh]

Hello,

Have a new question today that I need to brainstorm with people more knowledgable than me.

I have a workbook in excel that houses production information. The production information is manually recorded by operators (in another excel workbook that I created) and is emailed to me by each operator daily. I then manually input each operator's workbook from the excel log into the excel db.

The db workbook has several fields and a lot of them have vlookup's and if statements in them to help streamline/automate the data-entry process as much as possible.


I also have sheets in the notebook that are look-up sheets for my v-lookups and a sheet with lots of pivot charts on it.


The excel db has become quite large and is now several thousand rows long so I have begun thinking about migrating to a access db, but have concerns:

1) Does Access provide the same Pivot Charting and Analysis tools that Excel does?
2) Does Access provide the same types of Vlookup and automation tools that Excel does?
3) I have read articles about how one can import data from Outlook Emails... Can someone give me ideas or point to literature that can teach me the fundamentals on how to do this?
4) What am I missing/What are my options?

As always, thank you for your replies.

[June7]

1) Not exactly the same and Access 2013 eliminates Pivot Chart/Table feature

2) Not exactly the same - has domain aggregate functions (DLookup, DSum, DAvg)

3) Common topic, search forum and Google, not familiar with any books that cover the topic

4) Other database management software

[Dal Jeanis]

You have some big questions here.

The BIG PICTURE answer is, yes, you can do everything in Access that you need.

However, it's not trivial. You've spent a long time developing your current process, and developing a replacement will not be an overnight thing.

1-2) Access provides very similar tools, but Excel is admittedly more robust in the Statistical Analysis department.

3) Depends on what you're trying to do. You'll probably have to ask a more specific question to get useful pointers on pulling Outlook data into Access.

4) There's lots to say, so I'll just keep numbering.

5) Importing information into Access from Excel is pretty easy, assuming that the excel data is fairly well laid out. Typically, you would import it into a temporary table, run an analysis routine to catch or fix any errors in the data, and then append the data into the database and delete your temporary table.

6) Exporting information from Access into Excel is also pretty easy. That means that, while you are developing the Access equivalents for your analysis routines, you could in the meantime be using an Excel export of the Access db to use your Excel expertise and tools.

7) Before you attempt to import your db into Access, you should probably give some thought to the proper relational database design for what you are doing.
Right now, you have rows with cells with lookups that limit the values in the cell to values that are in a lookup table somewhere else in the spreadsheet (for example). The equivalent structure in Access would be a field in a table that contains a "foreign key" to a different table that cross-references the value for you. What's stored in the first table is just the key, not the actual word that would come back from the lookup.

I'd highly recommend reviewing the tutorials about database design and application design at MVP Roger Carlson's site http://www.rogersaccesslibrary.com/

8) You can do your design and normalization in stages. For instance, If your excel spreadsheet currently contains the lookup value in a particular field, you could let the database stay that way during development, and then later switch over to using a proper key field. You don't have to eat the elephant all at once.

9) When you have more specific questions for us, please do ask.

[athyeh]

So I'm going to need to get much more gung-ho about this. The number of operators that I have to track has increased astronomically, and now my inbox is being flooded with personal reports.

First off: How can I streamline this process.

Process: (Current State)
1. At the end of the day, each operator sends an email with an excel sheet attached.
2. This excel sheet contains macros and equations that track their self-reported production through the day.
Column Fields:
Time of Production (Hourly), Process Type, Total, Goal, Adjusted Goal, Description.
3. I then copy and paste all fields into my master spreadsheet.
4. Repeat this process for all operators.

I have read articles on how to import from Outlook but am still confused. Is there a way to setup a Macro or VBA code so that at the click of the button Access goes into my outlook and pulls information from my attachments (containing standard attach. name) in all emails containing standard email subject? Need help getting off the ground on this one...

Thank you for your replies.

[Dal Jeanis]

What I'd suggest is that you work it from the other end - make a macro that you can add into the excel spreadsheet that each person will be sending you. That way, one click or key-code in each spreadsheet will cause the data to move to your Access database, or onto your master excel spreadsheet.

You could do this all with excel VBA macros, actually. How I'd envision that is that you'd have a sheet in your master that was the control, which kept track of where the last thing was pasted and where the next needed to go. On that sheet you'd have a flag that showed whether any macro was in the process of updating the master. On the operator's sheet, you'd have a flag to show whether it was never imported, in process or import, or completed.

When you activated any particular operator's macro, it would

1) check the operator's sheet to make sure it wasn't marked as "already imported". If so, cancel with message.

2) Mark the operator's sheet to show import in progress.

3) connect to your master.

4) check to make sure the master isn't already being updated, in which case sleep for a while and check again.

5) when the master is available, mark the master's control sheet to show it is being updated.

6) read the Control sheet to find out where to put the data.

7) copy the data.

8) paste the data.

9) update the control sheet to get ready for the next import.

10) mark the control sheet to show it is available.

11) mark the operator's sheet to show that import is complete.

All of that should take less than a second.


Now, eventually, it would be much better to have all the operators enter their info into an Access frontend, and the information appear automatically in an access backend, but I'd suggest that you'd have a lot easier learning curve to just use Excel automation for your particular immediate need.

[athyeh]

Thank you for your replies.

Want to move straight to the DB option because Excel sheet has become too large to manage.

Already created a draft for a DB that is split and operators use a front-end db to enter their numbers into the back-end tables.

Design:

tblMainDB
[Operator] [ProdTime] [ProcessType] [Account#]
(Look-Up) (Look-Up) (Look-Up) (Input through scanning wand)

frmInput
[Operator] [ProdTime] [Process Type] [Account#]
(Drop-Down) (Drop-Down) (Drop-Down) (User Input)

Questions:

1) Is there a way to "Freeze" the Drop-Down options so that Operator use wand to scan-in several account #'s without having to manually create new records and re-select options.

Example:

John worked from 10:00 AM - 11:00 AM
John has a stack of Account #'s that he worked during that hour.
John opens up the DB, goes to the form:
Selects "John" from [Operator]
Selects "10:00 AM - 11:00 AM" from [ProdTime]
Selects "Bang Nails" from [ProcessType]

After selecting these options, John then clicks his cursor into [Account#] and is then able to scan-in 100 Account#'s rapidly, without having to re-select or press a button.... Upon scanning in one account number, the only thing that clears is [Account#], but new records are still getting created in the back-end table.

2) Also is there a way to make the form "pop-out" of the Access window?

3) Is distributing 50 front-end forms and possibly having operators use simultaneously advisable?

Thanks!

[June7]

1. I expect so, with VBA code

2. not sure what you mean by 'pop-out' - try setting the form Popup property, is that the effect you want?

3. 50 might sound like a lot but as far as I know should work

[Dal Jeanis]

1) The options aren't going to change unless the operator does something to change them. You can have the operator set the options, then click a button (for example) to put Access into the state (for example, popup a form) to receive the scanning wand input. When the operator is through with the wand, then he'd click a button (for example) to close the form, and the app would then move him to the most likely place that his next input would need to occur (for example, the time field or the company field.)

2) Look at modal and popup. It won't really be "outside" the access window, but it will work.

3) Yes, it can be done, but you might want to have them input occasionally during the day rather than 50 at once in the last 15 minutes before going home. That's a recipe for frustrated operators. Worst case scenario, you could have a bastardized system where their inputs went initially to 50 different personal temporary tables (if necessary, in several different local "server" database files) and then got pulled in by the main database on a nightly schedule. It's not standard, but there's no technical challenge there, and it would decrease the evening contention and the chance of corruption.

[athyeh]

Thank you for your replies.

So we tried this and the options do not change once the record is "scanned" but we have to manually click the "New Record" button to create new records, and when we create new records we have to re-select our options. Can someone point me in the direction of the VBA code or wherever the solution lies? I'm still very new at VBA.

[June7]

Code to carry forward the selected values to new record: http://access.mvps.org/access/forms/frm0012.htm

Probably code in the 'scanned' textbox AfterUpdate event can move to new record: DoCmd.GoToRecord , ,acNewRec

[athyeh]

Code to carry forward the selected values to new record: http://access.mvps.org/access/forms/frm0012.htm

Probably code in the 'scanned' textbox AfterUpdate event can move to new record: DoCmd.GoToRecord , ,acNewRec

What am I doing wrong here?

Private Sub Command11_Click()
Const cQuote = """"
tblMainDB!Operator.DefaultValue = cQuote & tblMainDB!Operator.Value & cQuote
End Sub

[Dal Jeanis]

athyeh -

The answer to your problem lies in the answer to this question - what precisely HAPPENs when the wand "scans in" its information?

For example, if it updates a text box with the scanned info, then use the After Update event of the text box to create and save the record and move to the next blank new record, setting/loading the default values for the next record if needed.

By the way, if the new records are being created in a subform, rather than the main form, then the values should not be going away like that. But you can always use VB to (1) save the options first (2) create / save the record, (3) move to new record (4) set the saved defaults. You just have to make sure to kill an empty record if it's accidentally created but isn't needed. (If the details that should have been scanned in but never were.)

[June7]

Is tblMain the name of form? Is this code behind form? Why don't you use the Me alias?

[athyeh]

Is tblMain the name of form? Is this code behind form? Why don't you use the Me alias?

I was unaware that "me!" referred to the table/form/report that you were on. I thought it was "programmer code" for: [insert your stuff here]. Feel dumb, but that's learning right?

[June7]

It refers to form or report, not table or query because those objects can't have VBA code behind them.