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

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.

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)

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.

Learn how to collaborate with office 365 Office Online

See the Basics of Office 365's Note Taking app, OneNote

Discover the basics of using Outlook 2016 from office 365.

Basic Overview of office 365 user portal

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

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

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 …

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:

• 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?…

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).