Microsoft Office

68K

Solutions

42K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Share tech news, updates, or what's on your mind.

Sign up to Post

Shuffle - 1 - Examine Data in Access that was imported from Excel
Examine data that was imported from Excel. Filter data in Access just like you do in Excel, for just what you want to see.
Turn on a totals row to sum columns. Best-fit columns and change column widths. See how a new AutoNumber value  is automatically assigned, and totals are changed, when a new record is created. Look up a customer code using a quick query.

Here are the other videos in this series:

Shuffle - 2 - Totals Query
https://www.experts-exchange.com/videos/61035/Shuffle-2-Totals-Query.html

Shuffle - 3 - Table Design
https://www.experts-exchange.com/videos/61036/Shuffle-3-Table-Design.html

Shuffle - 4 - Append Query
https://www.experts-exchange.com/videos/61037/Shuffle-4-Append-Query.html

Here is the video that shows how to do the import:

import an Excel spreadsheet into an Access database
https://www.experts-exchange.com/videos/60914/import-an-Excel-spreadsheet-into-an-Access-database.html

1. Open a table in Datasheet view to look at information

2. Click the Filter dropdown at the top of a column to specify criteria for the rows displayed

3. Resize columns to be wider or narrower, double-click to best-fit

4. Add new records at the bottom

5. Look at each column and determine what type of object it is.


      There needs to be a table to describe each type of object.
1
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Shuffle - 2 - Totals Query
Data was imported into Access from Excel. In part 1, we examined it, and learned how to do a few things in datasheet view like filtering, sizing columns, and showing totals. To get unique values, we'll make a query and then change it to a Totals query, and use an aggregate function to calculate something. We'll use Group By, Max, and Expression.

Here are the other videos in this series:

Shuffle - 1 - Examine Data (imported from Excel)
https://www.experts-exchange.com/videos/61034/Shuffle-1-Examine-Data-imported-from-Excel.html

Shuffle - 3 - Table Design
https://www.experts-exchange.com/videos/61036/Shuffle-3-Table-Design.html

Shuffle - 4 - Append Query
https://www.experts-exchange.com/videos/61037/Shuffle-4-Append-Query.html

1. To change a query to an aggregate query, click 'Totals'


in the Query Setup group of the Design ribbon tab

2. Group By the key field in the Total row

3. Change Group By to Max for other fields

4. Create an Expression with Count(ID) for the number of records

1
Shuffle - 3 - Table Design
We are going on a deep dive to the foundation of data structure -- what is in the  design view of a table? What else you can do? Use a Unique Index to prevent duplicate values from being added. We discuss Ignore Nulls, the Primary Key, AutoNumber and its properties, create a Calculated field with an Expression to combine data from other fields, tracking fields for when records were added and edited, Field Names, reserved words, and tips for naming fields, and field properties like Data Type, and Size.

Here are the other videos in this series:

Shuffle - 1 - Examine Data (imported from Excel)
https://www.experts-exchange.com/videos/61034/Shuffle-1-Examine-Data-imported-from-Excel.html

Shuffle - 2 - Totals Query
https://www.experts-exchange.com/videos/61035/Shuffle-2-Totals-Query.html

Shuffle - 4 - Append Query
https://www.experts-exchange.com/videos/61037/Shuffle-4-Append-Query.html

Look up Problem names and reserved words in Access, by Allen Browne:
http://allenbrowne.com/AppIssueBadWord.html

1. In table design, you can use an AutoNumber for the primary key


in the Query Setup group of the Design ribbon tab

2. Don't use reserved words in field names


      or spaces or special characters
      except underscore (_) is okay.

3. Start field names with a letter

4. Ensure Data Type and Size are good.

5. Put key fields at the top


      and tracking fields at the bottom.

6. Define an Expression for calculated fields.


      Make text bigger in the Zoom Box (Shift-F2) by clicking the Font button.

7. Use Now() for the Default Value of tracking fields


      for when a record was added and edited.

8. Set a Unique Index to prevent duplicate values.

1
Shuffle - 4 - Append Query
We have a Totals query, and change it to an Append Query, so we can use it to add records. Create an an expression for a calculated field. Get errors (oops!) when we run the append query, but see how to fix them :)

To Summarize, in part 1, we examined data and made a query. In part 2, we changed the query to a Totals query to get unique values and used an aggregate function in an expression. In part 3, we took a deep dive into table design. Now, we have created and run an append query. Records are now in the customers table, and we have two more tables
to put records in before the import from Excel is done. Look for those lessons in the future.

Here are other videos in this series:

Shuffle - 1 - Examine Data (imported from Excel)
https://www.experts-exchange.com/videos/61034/Shuffle-1-Examine-Data-imported-from-Excel.html

Shuffle - 2 - Totals Query
https://www.experts-exchange.com/videos/61035/Shuffle-2-Totals-Query.html

Shuffle - 3 - Table Design
https://www.experts-exchange.com/videos/61036/Shuffle-3-Table-Design.html

1. Open a table in Datasheet view to look at information

2. Click the Filter dropdown at the top of a column to specify criteria for the rows displayed

3. Resize columns to be wider or narrower, double-click to best-fit

4. Add new records at the bottom

5. Look at each column and determine what type of object it is.


      There needs to be a table to describe each type of object.
1
How to Install and Make an Add-in for Access
Learn how to install an add-in for Access. Add-ins can save time, and make your code more accessible. Where does the add-in go?

Do you want to create an add-in? What must it contain? Learn about the USysRegInfo table and what needs to be in it.

The add-ins demonstrated here can be downloaded (free) from:

Tools > Add-in > Document SQL, RecordSource, RowSource
http://msaccessgurus.com/tool/Addin_DocumentSql.htm

Tools > Add-in > Workspace Save and Restore
http://msaccessgurus.com/tool/Addin_Workspace.htm

The add-ins are just ACCDB files that have been renamed to have an ACCDA extension.

1. To install an add-in, run Access as Administrator

2. Open a database

3. Drop down the list under the Add-ins icon on the DATABASE TOOLS ribbon tab

4. Choose the Add-in Manager

5. Choose Add New...

6. Browse to the ACCDA file and click Open

7. Close the Add-in Manager

8. Your new add-in should now be on the Add-ins menu

2
import an Excel spreadsheet into an Access database
Learn how to Import data from Excel into a Microsoft Access database using the wizard. Change data types and modify table design. Add another field and create an update query to fill it out. Also see a neat trick to copy the full path and filename of the active workbook in Excel.

1. To import data from Excel using the wizard, click the Excel icon in the Import & Link group of the EXTERNAL DATA ribbon tab


      (You might have to click the drop-down arrow for more choices to see the Excel icon.)
 

2. Choose the Excel file you want to import from


 

3. Choose the worksheet or range


 

4. Verify that the first row contains the column headings


 

5. change data types


 

6. let Access add a primary key


this will be an AutoNumber field, which is a good idea to have in every table.
 

7. change the table name to be logical, and preface with 'import_'

     the reason to preface the table name is so that when they are listed alphabetically, the import tables will be next to each oher. Once data is in Access, it needs to be normalized.

 

8. modify the table design (to add a field for date)


      In this example, we just added another field. In reality, you would also change Data Type and Size, remove tag-along properties like Format, set text to have Unicode Compression, and more
 

9. make update query to create values in the new field

     construct date using the DateSerial function, with parameters for the numeric year, month, and day

 

10. save the query


      (so you can modify it for another worksheet of data)
1
LVL 50

Expert Comment

by:Subodh Tiwari (Neeraj)
Very helpful tutorial Crystal! 👍
1
LVL 25
thank you, Subodh!
1
Create Dropdown menus in Excel 2016
This video takes you through the steps to create dropdown validation menus in an excel spreadsheet.  For the purposes of the video, Microsoft Excel 2016 was used.
2
Repair corrupt PST File when ScanPST.exe fails
There are many cases found where ScanPST.exe fails to repair corrupt Outlook PST File. When user tries to repair PST using Inbox Repair tool and it throws below error:

•      Inbox Repair tool does not recognize the file
•      ScanPST.exe hangs in between the repairing process
•      Fatal Error 80040818

Here In this video,  let’s see what to do when ScanPST.exe fails and how Stellar Phoenix Outlook PST Repair
(https://www.stellarinfo.com/outlook-pst-file-recovery.php ) helps as the best ScanPST.exe alternative.

Thanks to Stellar Phoenix Outlook PST Repair that helps users where ScanPST.exe fails to repair corrupt PST file.
0
LVL 1

Expert Comment

by:Kieran Stephenson
Microsoft provides the Inbox Repair tool (scanpst utility) to scan and repair PST file. But sometimes it may fail or not work if the corruption level is severe. In such a situation, Outlook experts always recommend using a professional PST Repair tool. There are many vendors in the market which provide such software with the free demo version. By using the demo version, you can check the preview of the recoverable data. And if you're satisfied with the results, then only you should purchase the software.

Also, you may refer to this article to compare top 5 PST Recovery software - https://www.semnaitik.com/best-pst-repair-tools/

Thanks.
0

Expert Comment

by:Sunil Sharma
Read the blog posted at the https://www.recoveryfix.com/blog/ways-to-recover-corrupt-outlook-pst-files and know easy steps on how to repair corrupt PST file when ScanPST.exe fails.
0
Microsoft Office 365 Backup and Restore Solution - Trend You Need To Know
Microsoft Office 365 Backup and Restore Solution by SysTools to export Office 365 mailbox to PST / EML file format on Windows OS. On Mac, tool backup O365 to PST / MBOX / MSG / EML / EMLX file formats. Not only this, restore option helps to import single as well as multiple PST file back to Office 365 tenant.

Visit Official Site to Know More: https://www.systoolsgroup.com/office365-backup/

1. Download & Install.

Visit the official site to "Download & Install" Microsoft Office 365 Backup and Restore software by SysTools

2. Login Using Office 365 Credential

Enter username & password and click on "Login" button.

3. Select File Format

Select PST or EML file format to backup Office 365 mailbox in that particular format

4. Apply Date Range Filter

This option is provided to backup Microsoft O365 Mailbox within a particular date range.

5. Export Button

Click on "Export" button to start the backup process.
1
Office 365 Basics. Module 6 - Office Online
Learn how to collaborate with office 365 Office Online
1
Become a Certified Penetration Testing Engineer
LVL 13
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Office 365 Basics. Module 5 - OneNote
See the Basics of Office 365's Note Taking app, OneNote
0
LVL 10

Author Comment

by:Alan Cox
I will be. I will also be adding more content to the OneNote 2016. e.g. tagging, OneNote/Outlook integration, ect.
2
LVL 4

Expert Comment

by:Kathy Jacobs
LMK if you have questions or need help. Used to be a OneNote MVP. Know you have  lots of other good sources too.
2
Office 365 Basics. Module 4 - Skype For Business
Learn the basics of Skype For Business in office 365
1
Office 365 basics. Module 3 - Outlook 2016
Discover the basics of using Outlook 2016 from office 365.
1
Office 365 Basics. Module 2 - OneDrive
Overview of OneDrive and collaboration.
1
Office 365 Basics Module 1 - Overview of portal
Basic Overview of office 365 user portal
1
How to Repair Corrupt Outlook PST with Stellar Phoenix Outlook PST Repair
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. Use the most recommended software is Stellar Phoenix Outlook PST Repair which repairs your corrupt Outlook PST file and restores all mailbox contents.
2
LVL 8

Author Comment

by:Bharat Bhushan
Yes Gregory Rankin,

With the technician version of Stellar Phoenix Outlook PST Repair, you can save the repaired PST into Office 365. Also it allows to save the PST into MBOX and DBX .

The best advantage of this software is it compress and split PST before saving. You can get more info from here: https://www.stellarinfo.com/advanced-outlook-repair.php
0

Expert Comment

by:Vvip Sharmaji
Read the blog post at the following web link: https://www.recoveryfix.com/blog/how-to-recover-corrupt-or-damaged-outlook-pst-files and easily resolve your all Outlook related issues.
0
How To Insert Foreign and Special Characters when in Windows Applications
Enter Foreign and Special Characters

Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~

Use these codes in any Windows application! ... whether it is a Microsoft Office product like Excel, Word, PowerPoint, Access,... or a simple program like NotePad.

Along these lines, I've written a tool to make it easier to input and lookup Unicode when you are in Access, and am making another video on Unicode - so be on the lookout for it.

Please Like, Comment, and Share with your friends ~

have an awesome day,
crystal

1. To insert a special character, make sure Num Lock is on


2. Hold down the ALT key


3. Type the ASCII code on the numeric keypad


4. Release the ALT key

0
How to Add a "Save As" icon to the Quick Access Toolbar
How can you see what you are working on when you want to see it while you to save a copy?

Add a "Save As" icon to the Quick Access Toolbar, or QAT.

That way, when you save a copy of a query, form, report, or other object you are modifying, you can still see what you are doing and can give your object a better name.

While Access is used to demonstrate,  you can do this in other Office products like Word, Excel, and PowerPoint too.

have an awesome day,
crystal

1. click on down arrow at end of QAT to Customize it


2. Choose "More Commands..." from the drop-down menu


3. choose to show 'All Commands'


4. click on the last command in the list of QAT commands on the right


   or whatever command you want to add the command below

5. double-click <Separator> in the left list to add it to the right list below what is selected


6. in the left list, doube-clicm Save As to addit below what is selected (now the separator)


7. click OK to close the Customize the Quick Access Toolbar dialog box


8. Enjoy faster saving!

1
How to Enforce Referential Integrity on Access Relationships
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other.

As you build tables in Microsoft Access, or link to tables that are in SQL Server, Excel range, or other database, put them on the Relationships Diagram

Stretch or shrink fieldlists so everything shows

To create a relationship in Access, click on the Primary Key in the main table, drag to the Foreign Key in the related table, and let go.

In the dialog box that pops up,
check: Enforce Referential Integrity

This is important so that there are not values that don't match up to anything.

Error creating relationship?

If there is bad data, a relationship with referential integrity cannot be created. Fix the bad data and try again.

If the table is linked, you can show it on the diagram but you cannot create a real relationship that is not already there. If relationships are not defined, you can drag lines without enforcing  referential integrity to show what is supposed to match.

Define relationships and arrange the diagram as you build your tables.
Enforce Referencial Integrity unless you have a specific reason not to.

have an awesome day,
crystal
 ~ connect to me, let's build it together
http://www.msAccessGurus.com

1. To create a relationship in Access, open the Relationships Diagram

2. click on the Primary Key in the main table

3. drag to the Foreign Key in the related table

4. and let go of the mouse.

5. When the dialog box pops up, check: Enforce Referential Integrity

6. and click Create

7. Rearrange the Relationships Diagram so everything shows and flows

1
Ensure you’re charging the right price for your IT
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Subform to show Calculations in Microsoft Access
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data.

A Microsoft Access subform is used to show relevant calculations for data in the mainform.

A subform control is a container, like a bucket, for a form or report. Its properties specify: what it contains (Source Object), how it is linked (Link Master Fields, Link Child Fields), what it is called (Name), how big it is (Width, Height), where it is  (Top, Left), what it looks like (Border Color, Border Style, Special Effect), whether or not it shows (Visible), if the user can modify values (Locked, Enabled), and so on.

As you navigate from record to record in the mainform, data in the subform automatically changes. LinkMasterFields and LinkChildFields are used to synchronize the forms, without any more effort on your part!

On the mainform, LinkMasterFields is a combobox that stores CustomerID but shows the customer name and more. What is displayed in the combobox is influenced by Column Count, ColumnWidths, and ListWidth.

The form inside the subform control is a regular form designed to be used as a subform; and in this case, to display information only, so things like RecordSelectors and ScrollBars are turned off. The RecordSource for the subform (where it gets its data from) is a query that has 2 queries below it doing more calculations. See how queries are stacked to get …
0
Mainform + Subforms designed for efficiency and flexibility
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. When a sale is made, it may be paid outright, carried in-house, or purchased by a finance company.

Using a mainform with subforms allows data entry into multiple tables with referential integrity. There are also calculations, and ways to find and filter information; Synchronizing subforms so they update with the record changes using LinkMasterFields and LinkChildFields; Calculating sum and total number of orders and payments, and the balance due; Displaying the company logo; and more.

Learn how to undo creating a new record, use shortcuts and quick ways to enter data, and about setting the Enter key behavior in the Access Options so pressing Enter goes down one row, just like in Excel. See how easy it can be to disburse all the is remaining on an allocated amount left to a single order.

See how Anchoring subforms and controls enables them to stretch and shrink as the display area changes.

Future lessons will go into the nuts and bolts of how this form works, address issues, fix problems (there are some), and enhance capabilities.

~~~

Did you learn something new?

Please Like, Comment, and Share with your friends. Thank you.

Through sharing, we will all get better.

have an awesome day,
crystal

1. Download sample database

0
How to Create Calendar Reports from Access Queries
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.

Visualize your data!  ... really see it

To use the code to create a calendar from a query, simply import the (only) module (mod_HtmlCalendarReport_s4p) from the download (see Step 1, below)  into your working database (and then compile and save, of course). You can get the same code by choosing File, Import in the VBE (Visual Basic Editor -- Alt-F11) and choosing the BAS file.  

Importing VBA doesn't, of course, include easy-to-launch macros (even though the macros run VBA procedures) ... triggering them is a snap -- especially if you use a menu form with buttons and other controls.  

Please, as you make changes, rename the 'test' procedures to have more meaningful names depending on how you customize them and how they are used. And add your own comments ... and indent?

If you feel like sharing, your ideas are welcome, and appreciated.

Using vba:
Application.FollowHyperlink Create_HtmlCalendar(sQueryName)
  • will open a web page with the calendar created from the query specified by sQueryName
  • The Create_HtmlCalendar function creates the calendar as an HTML file (web page)
  •   it returns the path and filename when done.
  • You have a calendar file you can email and share with others.
  • you may wish to add VBA Error Handling (Basics video)

What do you need to know to use this feature?
2

Expert Comment

by:susan nichols
Thank you for your answer! Currently I'm handling travel for our division, so I'm using this to generate calendars of upcoming travel and conferences so other departments can easily see who is going where and when since they have to do things on those dates. I've got it working perfectly doing the single month, but being of the "I want my db to do the thing and then googling how to do the thing" variety of access user, I'm not sure how to start with your instructions (this is sad, I know this is sad).
0
LVL 25
hi Susan,

sorry, I did not see notification of your comment, if there was one. Several people have asked about this. I've made a note to modify the code to do multiple months

have an awesome day,
crystal

free code you can use in your projects
https://msaccessgurus.com/code.htm
0
display Triangles! and Circles! in a Microsoft Access Query -- Get Previous Record too
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased, or a down triangle if it decreased ... and stagger the markers for even greater grasp.

This lesson also covers how to handle non-American date formats, and optimize performance with a subquery.

If you like this video, please Like, Share, and Comment ~ thank you

1. Make a new query based on a table (MyData) with a date (TheDate) and a value (Price)


   - add date and value fields to the grid
   - sort in decending order by date

2. Add another copy of the table to the query


   - Access will '_1' to the end of the name of the copy at the top of the fieldlist to make it unique.
   - This table will represent the record for 'yesterday',  or whenever the previous value was recorded.
   - add date and value fields to the grid and give them aliases (for instance, PrevDate and PrevPrice) since field names have to be unique

3. Create a calculated field to show the difference


   - for instance --> Diff: CCur( MyData.Price - MyData_1.Price )
   - between a value in the reference record and the previous record
   - Wrap with function to convert to currency to ensure the result is the correct data type
   - the calculated field name (alias) is 'Diff' since it appears before the colon

4. Create a calculated field to show the Unicode symbol corresponding to a Circle or Triangle to graphically represent the difference

0

Expert Comment

by:Andy Brown
Nice work Crystal - thank you for sharing.
1
LVL 25
thank you, Andy

Unicode:

Note: Some fonts have more, and better, Unicode representations than others. For Windows standard built-in fonts, Arial Unicode MS and Lucida Sans Unicode have fair coverage.  Common fonts such as Arial, times New Roman, and Calibri can be okay too.

If you cannot show the Unicode characters used to demonstrate, try these instead:

filled circle  --> 9679

down-pointing triangle --> 9660

up-pointing triangle --> 9650
0
How to export Office 365 mailboxes to PST using eDiscovery
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on that topic here).
0
How To Make a Graph with Microsoft Access
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formatting, chart type, titles and legend.

"A picture is worth a thousand words"

1. Make a query to show what you want on the X-axis (Category) and Y-axis/es (Values)


2. Create a Chart object on a form or report using the Chart tool


3. Follow wizard steps, but don't worry about the data


4. Set the RowSource for the chart object to be the data that you want


5. Resize and Format the chart -- and set/change other properties such as text for Title(s)


6. To progammatically modify the chart, watch the next video in this series, 'Manipulate Graphs in Microsoft Access using VBA'.

3
LVL 50

Expert Comment

by:Subodh Tiwari (Neeraj)
You are awesome Crystal! Great video tutorial. :)
1

Microsoft Office

68K

Solutions

42K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.