1669103717

## What is Microsoft Access? An Introductory Guide

Let us dive into this MS Access tutorial and learn more about. MS Access is a Microsoft-based relational database management software. It is a part of the a suite of applications provided by Microsoft Office. It stores data in its own format.

## What is MS Access?

Since the onset of computers, back in the early 1950s, businesses have wanted to use computers for storing data and manage their stored data when required. This led to the generation of databases. But the problem with the early databases was, all the data was stored in file format, and it was very difficult to find and relate 2 data sets as and when required. This gave rise to the concept of RELATIONAL DATABASE (RDBMS).

RDBMS, since its discovery, was implemented by many tech giants like IBM, ORACLE etc.  So, the same amount of research work was going on in Microsoft in the 1980s, and after a lot of planning and prior testing, Microsoft launched MS Access on 13th November 1992. Like most RDBMS softwares, it allows users to get the maximum benefit from their data by allowing some incredibly easy to perform tasks like sorting, comparing, basic operations like searching and replacing and much more.

Microsoft developed a database engine called Microsoft jet database engine, which helped to store the data at the backend. MS Access, since then, is undoubtedly one of the best RDBMS software that is available in the market today. It has objects which will help you to perform operations and store data. It has a lot of advantages, especially handling complex queries. As a software, MS access follows split database architecture, which means,  whatever data we are storing into the tables are stored in the backend in one file, and all other things, which includes the forms, queries, reports etc., are stored in the other file, which helps to easy data fetching and smooth operations on to them.

In terms of security and data privacy, MS access is something that a user can trust blindly. It offers data protection by using passwords and prevents any unauthorized access to the database. Also, you can lock your data and limit the accessibility of who can view a  particular section of the data. This has been of great help to most users as someone won’t wish to reveal some sensitive information to anyone who wishes to view it.

As an overall analysis, I could say that it is one of the best possible options available for all medium and small enterprise-level users and who wish to get such service at a very affordable price segment.

## MS Access- RDBMS

When you used a database program like access back in 2016, you can’t just start entering data. Instead, you need to create a relational database design, dividing your information into one or more tables, each made up of fields (which are like any columns in a spreadsheet). But now, since the RDBMS is set, these issues have been addressed. RDBMS have a particular model which will allow a user to define what category of data he wants to store, how he wants to store and what sort of operations he wishes to have on the stored data.

Also, at times, we need to have control over who can access our data and who all can have a look at the data. Sometimes, we need to store our data so that only a few allowed people would be able to view a few sensitive sections of the data. This can be controlled by using the MS access data control feature. Access RDBMS. No matter how complex your queries are, they can still process your queries. The main need to develop an RDBMS was when traditional databases failed to address a few situations like drawing relations between 2 stored tables or values. So, a whole new set of logic systems was designed, and it was called relational algebra. That was how it all began. MS access is now one of the most used and most sought after enterprise-level database software preferred by most professionals.

Below is an example of an ms access RDBMS model.

MS access 2016 version

In all ways, MS access RDBMS is one of the best ways to increase data handling efficiency.

## Understanding the Relational Database Concept

This is the theoretical model upon which all the databases work in today’s era. So, why am I stressing more on this particular concept is because, if you understand this concept well, then not only you would be able to grasp the technical aspects in a better way, but also, you will have a great working opportunity in future if you choose a career as a database administrator. So, why were we required to develop this technology in the first place?

Because there was a very serious issue of data duplicity. You may think that, even if duplicate data occurs, so what? It can be deleted later on. But let me ask you then, what if you have the same record of data that is repeated (duplicated) 100 times. Now, if at one instance, you need to change the data for that particular field, then individually, you would need to change the data for almost all 100 fields! Which is a very daunting task. Data should be precise enough without any significant redundancy (in technical terms, data duplicity is called redundancy).

So that’s why we need a relational database model, which could help us solve these real-life issues. Now, here comes another important point. If a certain record of data is repeated too many times, and it is unnecessarily taking up too much space on the hard disk of the computer, or if you store it in a cloud-based storage system, in both ways, your storage is wasted in some other manner. It is a wastage of computational resources.

Ever wondered why we write better algorithms? Is it just to bring some newness into an already existing problem-solving technique? No. Let me explain, computational resources are very much limited (since we don’t have a computer with infinitely large RAM and infinite hard disk). So that’s why we need better algorithms, and in terms of core computer science terminology, we say that we need a better time-space tradeoff. The same analogy goes in the case of databases. We require some technique, which is very much efficient in managing computational resources. So, in the digital world, we are limited to such resources, and hence redundancy can cost too much.

## Components of MS Access –Objects

Microsoft Access is designed to help its users connect with Microsoft databases at the back-end and help them manage and store their data efficiently. Using Ms Access has been extremely helpful to many enterprises since they can manage and access their data in a more user-friendly manner. Coming to Ms Access, it has four entities that help people to have hassle-free work. These entities are called objects. In brief, objects are the collection of tools supported by MS access that helps the users get their job done.

There are four types of objects in MS access:

1. Tables – Tables are the places where any information is stored. The most basic work of any database is to store useful information, and tables are meant for that purpose. Tables don’t perform any other operations but simply store the data that would be used later for other useful operations. Below is an example of how an MS access table looks like:

MS Access 2016 table

2. QUERIES- Queries are the statements that will ask the software to conduct a particular detailed search from the database. Consider the example of a  library having at least 1000 books. Now, there would be a database where all the relevant information of those books are stored. If you need a  particular book, then you don’t have to search the whole database to get that book’s information. Instead, what you could do is simply write a  query statement that will fetch all the information about the book which you are looking for. Queries are very helpful when it comes to extracting an only useful piece of information from a heap of data. Below is an example of an MS Access query.

3. FORMS– Forms display data from your table itself and help in performing the necessary tasks on the. Forms are basically an easy way to guide people using the software to perform various operations like sorting, adding, displaying in a more presentable manner, and so on. Whatever you do in a form, you are actually doing it on the table itself. You can directly do it from the table also, but then, the main challenge in doing so is, if your data set is of very large size, then it might be a problem for you to delete the non-useful data by finding it from the whole table and tracing out the useful columns for performing the important operations. Below is an example of how a form looks like-

4. REPORTS- Reports are static, easy to understand, easy on the eyes documents, which are presentable and portable. Colours can be added to it, and it & help in better managing the data that we need to present to someone. Reports are the objects which help in showcasing a particular section in order to highlight the important section. It’s more of an on-paper presentation of data. Below is an example of a report of data.

5. MACROS– Macros in MS access is used for automation purposes. It can automate tasks like opening forms, querying, changing values or even running other macros. It helps in adding functionality to your forms, reports and controls. This helps in improving efficiency when using the database.

If you need to manage the data well, then coordination of all four entities are required. If you consider the case of a book directory in a library, then the place where all the information of books is stored is called tables, if you want to search for a particular book, then the search command is the query, the place where you put your search criterion in the form, and last but not least, the result which the query has produced can be represented in a portable format as a report.

## How to create a Database?

So now, as we have discussed the basic concept of MS Access, without beating around the bush, let’s start to learn how to create the database and get started with MS access. So basically, to begin with, there are many ways in which you can create a database, with templates and without templates being one the most used ones. Let’s see each one of them one by one.

WITHOUT TEMPLATES (BLANK)- If you have a very specific requirement, then consider this to be one of the best options. It is like starting from scratch to building up the final model. Without templates, of course, the work would be more, as all the controls are in your control. Consider it like using the manual mode of a camera  DSLR, since everything you are controlling, that’s why the initial set up work is more.

WITH TEMPLATES – There are several templates that are already present in the Access framework. In the access setup, you can put your already existing data into these predefined frameworks and start a whole new database.

BY DOWNLOADING DATA TEMPLATES – Apart from the data templates that are already existing in the access, you can also download new data templates that are available on the MS Access website. Once you are done creating the database, then you can directly begin to enter the data into the empty cells.

Regardless of how you are creating the database and the table, it’s always a good idea to set the field properties. Few properties are available in datasheet view, while few others are available in design view. The field properties are basically the parameters of the field, which will give a user a rough idea about that particular field.

So let’s start to analyse the field properties one by one-

Field Size- The field size represents the size of the maximum string that a field can accommodate inside it. For the text, it is 255 characters at the max. For memo, it’s even more. So, each particular data type has a specific field size.

Format- It reflects how the data has to be displayed. You can customize and select your own format.

Input mask- Input mask is referred to as the property that will allow all the data that is having a particular format. You can imagine this like an Instagram filter, which will allow all the images which have that particular format.

Default value- Default value is the property, which will display the normal (default) value of any data whenever new data is being entered into that field. Say suppose, for a medical practitioner, if you try to enter the BP count for ten patients, then each time the systolic pressure is entered, it would display 120mm/hg, and each time diastolic pressure is entered. It would display 80mm/hg.

Required- Ever filled a google form where fields are marked with red Asterix (*) symbol? So what does that mean specifically? It means that it is a compulsory field that has to be filled before submission. In the same way, in Access, we have the required property, and if we enable yes into the required field, then Access won’t allow new data to be entered unless we fill that particular field. This is done to ensure that the required and mandatory field is not left vacant, even by mistake.

Copy-paste/import your data- This is one of the ways when you just want to import the data which is already being present, and no need to create and sort the whole data once again. You can do this by going to the External data section in the import and link group. You can select the data format, say, for example, XML, excel sheets, Text file etc.

Note: If you can’t find the data type present in the options, then try saving the data in a more common format, and then you can import the data into the access successfully.

You have the freedom to choose how and where the imported data would get stored in the present database in MS access. This is a great benefit for all the users.

## MS Access-Data Types

Like in the case of most of the databases, we have various data types in MS access also. When we enter any data into a computer, the computer mainly categorizes the data into data types and then by categorizing the data, it stores it. The concept of data type is not only important for learning the basics of MS Access but also, It’s important because it will help you to understand the concept of VBA, which is the bedrock of Access. To learn more about VBA, take up VBA Courses that will help you upskill. There are mainly ten types of data types supported. Also, there is another data type called lookup. But in order to activate the lookup data type, we need to activate the lookup wizard in the design sheet view. So now, let’s look at the main ten types of data types supported in the MS access.

TEXT – It is one of the most commonly used data types. It includes any combination of text, number and spaces. Now, the thing which needs to be understood here is, even though the data type can take entries in numbers, there should not be any need to have any calculations on the numbers that we have entered under the text category. The upper limit of the characters that can be entered into the text data type is 255.

MEMO- The memo is used when we have the requirement to store more than 255 characters into a text field. Since the upper limit is bound to be 255, so in case if we required more than that, so we would require another data type separately for that purpose, this was the main mindset when memos were created by the developers. You can also store some notes regarding the record by using this data type.

Date/time – In the business world, we often come across scenarios where we need to analyse the market trends in a quarter and also, we need to check the main peak time for our goods to be sold to the customers. So for that reason, we have the utmost important data types, called date time. Date time basically stores numerical type data, which is available to perform any operations also. Date/time can be very useful to people who are having work to record a particular time/event and perform analytics on that record later.

CURRENCY- This data type is very much relevant to store any data which involves money and which may be subjected to calculations. Suppose I store data involving USD, and later I wanted to convert it into INR, and for that reason, we can easily store and get the required value by using this data type currency.

Number- This is the most used data type after the text. Basically, a number is a collection of several other similar data types. The number is what we can say is the “int” data type in most of the programming languages. Its main function is to store numerical types of data which can be used to perform and subjected to calculations. The number has Byte, Integer, Long Integer, Single, Double, Replication ID, and Decimal as its subtypes, each having its own specification. Double is specifically used to store numbers that have decimal to be stored. In the same way, a long integer is used for entering data that is having integers that would be having non zero values on the right side of the decimal. A byte is referred to as the subtype where it can store bytes of data.

Auto number- The auto number is the data type that is used to uniquely identify the various records stored in the table. It is basically the long integer type of data type,  and this data type is automatically created and stored for each new entry that is being stored in the table.

Yes/no – Well, this data type is the typical Boolean data type that will store whether the stored record is true or false. It’s like a true/false type of statement. The boolean data type is primarily used in cases like, say suppose, I store a data called “permanent employee” or “contractual employee”. So in case a person working in the position of a permanent employee, the record entered would be yes or else, it has to be stored as no.

OLE data type- This data type is not very widely used in our day to day usage.  But then again, it can’t be ignored completely. This is required when we store something which is having a binary file, say suppose a picture and a sound file.

Attachment – Attachment is the data type that is newly included in the access ecosystem. By using this data type, we can attach image files, charts, documents etc., to a record.

Hyperlink – This helps in storing any URL address of a website into the record.

## MS Access –Create Tables

In relational databases like MS access, you store any information in the form of records (rows) and in the form of fields (columns), together of which constitutes a table. A table is created such that we can derive various relationships between the different data sets. Let’s take a real-life example to understand it more clearly. Say suppose you join an organisation in the role of an HR manager.

Now, the department of payroll asks you to provide the details of all the employees currently working in a particular department. So, you would most probably try to develop an MS Access database, with all the employees’ information like their employee id, DOB, date of joining, number of days present and also, number of days taken leave or marked absent. So, whatever fields I just mentioned are all independent entities that are required to be entered and saved into a single place. This place is technically called a  table. Before entering any particular data, you need to enter the data type for that particular field. If you try to enter any data which is not of the mentioned data type, then Access would not take the entry.

Among other advantages of creating a table in Access, one of the major advantages is it prevents data redundancy. That is, it would help you prevent duplicate entries or repeated entries of particular data. Even if redundant data is not reduced to 0 levels, it can significantly lower the redundancies already present in the data. This is of great help because manually reducing or eliminating duplicate information can be a daunting task all by itself.

So let’s see how a table is opened in a datasheet view and how helpful it can be in managing the information.

Whenever you go to the navigation pane, and double click on a particular table, then the view in which the table opens is called a datasheet view. You can mark the important section of your table in this section. It is best suitable for viewing and editing the entries. At the bottom left corner of the datasheet view, you would be able to see the number of records in the table.

An example of how a table looks like in datasheet view (image source: google)

But if you want to set field names and their corresponding data types, you would need to go to the design view. In design view, it’s actually easy to manage view, and it controls the overall layout of the table that you are seeing.

So when we open the design view, we can create the table by following these simple, easy steps-

• In the Design view, go to the application ribbon and select the Create tab and  then the Table Design button.

Adjusting the data types– Now you need to think, which field would contain  what type of data type, and accordingly select the data type. One thing to be  noted here is, if any particular data type doesn’t need any calculations, then that  data type can be selected as text instead of number. Say suppose, even though the zip code of a particular person’s address is not typically a text, but still we  can select the data type as text instead of number, since we usually don’t  perform any calculations on someone’s zip code.

But on the other hand, someone’s salary field should be set as number, because  you may need to calculate salary, apply deductions on it and hence it is always  suggested to do it in this way.

• While you save a table, maybe you would be prompted by the Access, to create  a primary key. It’s a good practice to always accept the suggestion and create a  primary key, the importance of which I would explain in the later part of this article.

## All About The Primary Key

All of us, at some point in our lives, have come across this term called the primary key. So what’s the use of a primary key?

Let us take an example to understand it in a better way. Suppose I have a firm, my own company, and I want to store the record of each person working in my organisation. Then, when I have a few fields like DOB, date of joining, age, salary and employment ID, then certainly, there are chances that any two records of two different persons can have almost everything similar to each other. What I mean to say is, two people, who have joined the company on the same date, have been offered the same salary package, and coincidentally have their birthdays on the same date, and they may even have the same age also.

Now, to make the situation worse, there may be one in a hundred cases where even the names of both the persons become the same. ( I myself had five students named Ayush in my high school). So, it becomes a matter of confusion for the HR department to keep track of them, and it may lead to serious misconceptions at times. So, in that case, We have to define a particular field, which we call the primary key, which will be unique for each employee.

Each employee, here, in our example, can be assigned a unique employment id, which can serve as the primary key. It should be kept in mind that computers, as a device, originated as a military device whose sole work was to coordinate with the military requirements. So, typically, the databases were created, they created a unique identification number, which would be similar to the unique military id that was assigned to each machine during the WW2 era. You can also think of it as the aadhar card number.

There is actually a requirement to assign a field to be as a primary key. You cannot simply assign any field randomly to be the primary key. The main criterion is, it should not contain similar data for 2 different entries. And then, no particular cell should be left vacant. If these two conditions are satisfied, then you are all ready to set that field as a primary key.

## Query Data And Query Criterion

When you want to review the most current data, or you want to summarize the previous data, or you have some specific such role. Obviously, the first thing which would come to your mind is writing a query statement.

Queries are of several types. We will discuss each type of query one by one in great detail.

SELECT QUERY

If your information base has a table with a great deal of data about items and  you need to audit a rundown of items and their costs, here’s how you’d make  a select inquiry to restore only the item names and the individual cost:

Open the information base and on the Create tab, click Query Design.

In the Show Table box, on the Tables tab, double-tap the Products table and close the exchange box.

In the Products table, suppose that you have Product Name and List Price fields. Double-tap the Product Name and List Price to add these fields to the grid.

On the Design tab, click Run. The inquiry runs and shows a rundown of items and their costs.

PARAMETER QUERY

Open the database and on the Create tab, click Query Design.

In the Show Table box, on the Tables tab, double-tap the Products table and close the exchange box.

In the Products table, suppose that you have Product Name and List Price fields. Double-tap the Product Name and List Price to add these fields to the grid. On the Design tab, click Run. The inquiry runs and shows a rundown of items and their costs.

You can likewise determine what kind of data a parameter ought to acknowledge. You can set the information type for any boundary. However, it is particularly imperative to set the information type for numeric, cash, or date/time information. At the point when you indicate

the information type that a boundary ought to acknowledge, clients see error messages in the event that they have entered some unacceptable kind of information.

Also, you can make use of this feature in a more efficient way. Let me teach you how. Make estimations dependent on your information.

You ordinarily would not utilize tables to store determined qualities, similar to subtotals, regardless of whether they depend on information in a similar data set, in light of the fact that determined values can get obsolete if the qualities that they depend on changes. For instance, you would not store somebody’s age in a table, in light of the fact that consistently you would need to refresh the value; all things considered, you store the individual’s date of birth, and afterwards, utilize a question to figure the individual’s age.

For instance, on the off chance that you have an information base for certain items, you’d prefer to sell. This data set has a table considered Orders Details that has data about the items in fields. For example, You can calculate the subtotal by using a query that multiplies the quantity of each product by the unit price for that product, multiplies the quantity of each product by the unit price and discount for that product, and then subtracts the total discount from the total unit price.

So now, since we have learnt a few concepts about the query, let us analyze the basic query criterion.

So what is a query criterion?

These are the basic rules through which queries are guided. It’s like TCP/IP of Queries.

A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value, use the HyperlinkPart expression. The syntax for this expression is as follows: HyperlinkPart([Table1].[Field1],1) =

“http://www.microsoft.com/”, where Table1 is the name of the table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you want to match.

## Features

1. It is easy to understand, as well as for use
2. Sending and receiving files or applications
3. Provides information for individual and smaller teams
4. Allows creating forms, tables for storing information

1. Quick and easy to form
2. It produces pliability and invincible data
3. It helps to manage and organize the specified data
4. Easy to search and get a database

## Limitations

1. Not suitable for the web
2. Security and data probity
3. It is not obtainable over the internet

## Uses

1. Used for official work i.e., in schools, or offices
2. It is also used for business purposes
3. Used for programming evolution

## Basic terms

Database file – It is a file which stores the entire database and is saved to storage devices such as RDBMS.

Data types – Data types are the elements or components of each field. Each field consists of only one data type like text, number, data etc. Such as tables, query, form, reports, and modules.

## CREATE QUERY –

Queries are selected data based on limitations. It can help in relating data from one table or to more, or with other queries. Queries can be SELECT, INSERT, UPDATE, and DELETE.

A query can be based on tables or any other queries. You can use a query to view a  subcomponent of your data or to answer questions about data. To create a query, you should open the tables or queries on which you are going to base a query. A query is a vital attribute of MS access that helps to recover and perceive the information of the defined database.

You can save and go through the same queries repeatedly. If you regularly want to run a variety of specific queries, consider using a limited query. Limited query perceives information in a collective process. You can specify the data, set the different kinds of data, but you have to set data in a numeric, or time data way. The specified data should make the user understand errors and grammatical mistakes if they enter any wrong input, as it accepts the text data without displaying errors.

## Designing Action Queries –

This document provides basic techniques and methods for creating, designing and accessing queries in MS Access. It allows you to delete and append records of data. It helps to transfer data from one table to another one also helps you update the information. Action query makes changes to the data and always has a backup plan before performing it.

#### Steps –

1. Assemble a backup
2. Create a select query and have trial it for the result
3. Convert it to an action query
4. Run the query to make changes to the data

## Create an Update Query –

An MS Access update query makes an update of determined values in a table for all records or those records have particular criteria. It updates records manually in a table of a database, or a sequence or a table’s datasheets. As it changes records manually, it takes too much time. It is also incapable to send errors to itself as you update the records.

#### Steps –

1. Create a select query
2. Apply query criteria
3. View, click on the arrow to the right of the query
4. Press the button and choose Update Query
5. Now, we need to run this query, using and pressing the Run icon

## Types of query

#### 1. Select query

A select query is a collection of objects or data that shows information in a datasheet. A query does not store data, it displays data that is stored in tables. If we want to use data, we have to use all of the data from any one of the selected tables.

Benefits –

1. It helps to view data from the field or table in which you are interested by choosing it.
2. It can combine data from several data sources.
3. It helps to view records that meet criteria, and helps to see and  save all the selected records.

Create a select query:

• Choose the tables and queries which you want to use in data.
• Classify and identify the fields which you want to include from the data sources as per your choice.
• The query returns to specify criteria to limit the records.

#### 2. Cross tab

Cross tab query is a query that summarizes, and organizes the data in Access. It is easy to understand. Cross tab calculates a sum, average or other aggregate functions. It contains date/time for column heading.

Benefits:

1) It is easy to understand and for use.
2) It can automatically make an update.
3) It has a list of fixed inputs to use in the column heading.

Create crosstab query:

• On the create table, click query wizard in the queries group.
• In the New Query dialog box, click the crosstab Query Wizard.
• Click OK.
• Select the table, and then click NEXT.
• Choose the field and then click NEXT.
• SELECT category and click NEXT to continue.
• Choose date / time field of column headings
• Select ID and click Next.

#### 3. Action query:

Action query are queries that can add, change or delete  various kinds of records at one time.

Steps to create an action query –

• Double click on the Table name in the tables tab.
• Run the query, to display the data.
• Select the Append button in Query Design View.
• Select the table Name from the list and click OK.
• Click YES to confirm.

#### 4. SQL query:

• It is a language used in computers for working with sets of fields,  facts and figures related between them. Steps to create:
• Open your database and click the CREATE tab.
• Select the POWER table.
• Click the Add button and then click the close button.
• Click the home tab and the view icon which is at the left side corner.

#### 5. Parameter query:

A parameter query is a query which acts as variable limitations that can be changed at any time. It can be updated easily. It is a part of the database and has a larger proclamation to create a criteria.

A person can filter the data, records or information through the help of parameter query. Whenever, we start or create a parameter query, it asks for a value, then it starts running. Using the values, the query can make different results based on  different inputs or values. We can add SELECT, APPEND, MAKE TABLE or TO UPDATE.

Steps –

• Create a query – Firstly , create a query in QUERY DESIGN.
• Select and choose the tables – Select the items in the query by clicking ADD  option.
• Add the fields – Add the fields, which you would like to add by double clicking.
• Use and run the parameter Criteria – If the users will type anything, it will  be shown as the user’s prompt in the parameter criteria. At last, make sure,  Click the RUN icon to run the query.
• Supply the parameters – You have to add value at the prompt.
• The results – At last, the results are based on the specified parameter supplied .

The parameter query is beneficial to us as it has a long previous time record from the starting of records even if you make changes also or updated into a new record. Here, it runs as fast as earlier, with different values which have different  inputs. One more thing, it doesn’t edit the criteria every time but it has the  records of the query prompt when you need. Whereas, it has also some limitations – As the parameter queries or mistakes and shows in various places. In this case, it also has a few or limited internet.

## ALTERNATE CRITERIA

A criteria consists of field references and operations. It applies to a number of fields where it records the price, data and time, i.e. no. of years, date and time. And it can be applied to any type of field which shows records of the database. It is classified into different rows and columns of the database field. If we add alternate criteria in various fields for different rows. We can also perceive information for users i.e. last name or the job title. Alternate criteria helps to proclaim some specified  items from an Access database.

It is a field where values are given to determine  the particular records. Sometimes, queries can be saved and run repeatedly, so in this case we add alternate criteria. We can add criteria by following two ways – First, we can use the OR operator to combine two sets of criteria. Second, we can also grid or make it separately in different rows.

Steps –

• Open database
• Select Query Design
• Press on Add option and make add by pressing it twice.
• Double click on all the selected fields, you want.
• Now, you can see that alternate criteria in different rows of the field or in  multiple fields.

Logical and OR conditions in MS Access Query criteria –

The Access query design grid offers almost a wide range, possibilities for  filtering data. The application of AND and OR logic in the correct combination and this represented in the grid. These are keys to which we can filter correctly by constructing the set of data which can search and seek it.

The Unfiltered Query:

This is the select query to which we will apply for the given criteria to filter the data, information and records. These records are sorted by date, and on the date it’s started earlier.

Applying logical AND to filters:

Here, expressions are related by logical AND that implies those expressions which are inclusive. The selected query has filtered all records and data relating to address your thinking as a logical expression. In the Access query,  the design grid is applying filters by putting all the patterns, charts  and records into the criteria part. And so, you can link the filters by logical  AND. After doing it, when the given criteria is related to the text, we can type  the quoted text, mark or leave Access to put them in for future. Number are restricted to be applied in the ANDs. You may use logical AND within only one criteria expressions. Access doesn’t make differences between upper and  lower case.

## Applying logical OR to filters:

A logical OR determines exclusive criteria i.e., items which are related to the output  of the criteria In this query, the design grid may consist of inputs, whereas logical OR filters into different rows. As an alternative order, people can be using criteria rows to create logical OR conditions, which we might create within the same row.

## RELATING DATA –

It refers to the process of organizing rows, interpreting columns and tables of  a relational database . It provides and improves the whole performance and  probity of information and records. It is also called a ‘Relational Database‘ because it consists of one or more data in columns or tables. Each row attached  to one another records and each table describes the data information in a table as each information is related to another one.

It defines relationship  works by making data in both tables and have a unique consistency for each row in the table Each data classified and dependent on another one. Here, users can add records, tables regarding the entire given information . A sample data  can be prepared for Create a query that determines some database in which  you can add or copy data. A “Database“ is mainly a collection of related data, which contains the same types of data which are related to each other and the tables are going to be related with each other in a particular database.

Uses –

• Use for storage in devices.
• Used in various data structures .
• Used in for storing information systems.

A datasheet is a simple way to look at your data in rows and columns. We can create a new web table, Access automatically creates two views. A table open in a Datasheet view resembles an Excel worksheet.

Steps –

• Select the views in the Datasheet view.
• You can now see that inserting new data and updating the existing data.
• Click Yes and you will see that the selected record has been added to it.

## CREATE RELATIONSHIP –

Create Relationships determines the related records of data from each table  for a desired result. It defines by adding the tables which you want to  combine or relate to the Relationships Window by dragging the table to the another one. It provides a detailed record of data in the table. A relationship can be created in MS access by combining data of two different tables. Each relationship consists of two tables only.

Relationships are carried out from queries i.e., we have to create queries to make a relationship between tables and records –

• One to one relationship
• One to many relationship.
• Many to many relationships.

Steps –

1. On the database Click Relationships group .
2. Secondly, click Relationships.
3. On the Design tab, click Add tables in the Relationships group.
4. Select queries and then click Add.
5. After adding queries, click close.
6. Move it from one table to the other one.
7. Add then separately click each one of the fields and drag it.
8. Click on the field name.
9. Select the field which you want and click create.

Edit a relationship –

You can also make changes and edit the Relationship of data. It allows to change the table Relationship by changing the  tables or queries of both sides. You can define relationships by  the given facts, figures, charts and information of the data.

Steps –

Double click the Relationship line, it becomes thicker when it is selected.
On the database tab, click the Edit Relationships as it appears.
In the group, click Relationship and then Click All Relationships.
Double click the Relationship line once again, or you can click Edit Relationship.
Last one, click OK after making additional changes.

Update of Relationships –

Steps –

1. Click Relationships in the Relationships group on the database tools.
2. Click All Relationships in the Relationships group on the design tab.
3. Click the relationship line for the relationship that you I want to change.
4. Double click the relationship line, the Edit Relationships dialog box appears.
5. Select the Enforce Referential Integrity.
6. Select either the Cascade Update Related Fields or the Cascade Delete Related Records or you can also select both of them.
7. After making additional changes and then click OK. Delete a table relationship –  If you want to remove a table relationship, you must delete the relationship in the Relationship Window.

Steps –

1. Click Relationship in the Relationship group on the database.
2. Click All Relationships in the group on the design table.
3. Click the relationship line, it appears thicker when it selected.
4.Press the DELETE key.
5. A confirmation message should be displayed that would you sure to delete the data or not, so Click YES.

## ONE TO ONE RELATIONSHIP –

It is related data in MS access. It is data created only for its related fields, as  it has primary records or unique content. As we can say also, it is a link between the information in two tables, where each record in each table only appears once.

In one to one relationship, only one record in a table is associated with one and only one record in the other table. Each record in Table A can have only one  matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most  information related to this would be in one table. You might use a one to one  relationship to divide a table with many fields. You can create one to one relationship by linking the index in one table and an index in another table which shares the same value and input. The best process is to create relationships to have the secondary table look up value from the first table.

Referential data integrity helps to keep data clean by deleting related records.

Steps of creating a one to one relationship by adding a lookup field to a table:

1. Open a table.
2. In Design view, add a new field.
3. Select Data type value.
4. Select LOOKUP WIZARD , in this the default is set to look up values of  the other one.
5. Select NEXT.
6. Select the content that you want to include in the table and then  press NEXT.
7. Add the field that contains the key which people want to use in the  Selected field, select NEXT.
8. Set an order and change the width of the field.
9. And at last, on the final screen, clear the Enable Data.

ONE TO MANY RELATIONSHIPS –

In one to many relationships, one record in a table can be correlated with one or more than one record in another table. It returns related records when the value in the Customer ID field in the Orders table is the same as the value in the Customer ID field in the customers table. It can be  identified by the infinity symbol on the line connecting the tables.

Steps –

• Click the Office icon on the upper left hand corner.
• Click Access options, Access options dialog box appears.
• Click the Current Database button.
• Select Overlapping Windows in the Application Options Section, under Document windows options.
• Click OK.

Steps to create one to many relationships –

1) First, clear the layout by clicking on the option, clear layout on the Design tab.
3) Click on the Save icon and enter tbl Tasks.
4) As the table name, and go to the Relationship view.
5) Click on the show table option.
6) Add tbl projects and tbl Tasks and close the show Table dialog box.
7) Click the create button. We now have a one to many relationship.

MANY TO MANY RELATIONSHIP –

A many to many relationship exists when one or more items in one table can have a  relationship to one or more items in another one. It contains orders placed by multiple customers and a customer may place more than one order. It requires a little extra work up. It demonstrates a four step process for creating an associate  table to handle the relationship. It occurs when both of the tables contain records that are related to more than one record. You must create a third table because sometimes, Access doesn’t support a many to many relationship. It contains a primary key and a foreign key to each data table. Many to many relationships are  knowing data and take the place of understanding how all the data fits together to accomplish the desired result. It is easier to spot during the Design process.

Steps  to accommodate many to many relationship –

• Delete the existing relationship between the two tables.
• Create the table and include a foreign key field for both related  data.
• Create a one to many relationship between each of the data tables  and appropriate foreign key in the associate table.
• Delete foreign key fields in the many tables of the original one to  many relationship.

Associate table to base form on a query:

• Add all the non – key fields, you have to modify or may need from both the many and one table.
• At the foreign key that represents the one side from the associate table.

It is a type of mathematical data which refers the relationship between two entities i.e., A and B. This relationship is usually implemented by means of an associative table A many to many relationship between entity types represented by a logical model database table. It occurs when multiple records in a table are associated with multiple records in another table.

Example – such as many to many relationships exists between employee and projects.

## WILDCARDS –

Wildcards are special characters that can stand in for unknown characters  in a text value and are located for various items with similar but not an identifier one. It helps to perceive data based on a specified pattern. Access have two sets of wildcards characters because it supports two  standard for structured Query Language.

ANSI – 89

It describes the traditional Access SQL syntax , which is the default for Access databases. The wildcard characters conform to the Microsoft Visual for Application (VBA) specification. You can use the ANSI – 89 wild cards while running queries and finding and replacing operations.

ANSI – 92

It helps to use when you want your syntax to be compliant  with a Microsoft SQL server database. You can use the ANSI – 92 wildcards when you run queries against Access projects. Access files connected to Microsoft SQL server database. SQL uses the standard so Access used ANSI – 92 standard.

Using of wildcard –

Match all characters in your data:

• Open your query in Design view. Under queries, right click the  query and click Design view.
• In the criteria cell under the field you choose, add an asterisk or any side of your criteria or on both sides.
• On the Design tab, in the Results group, click the RUN icon.

Match a character within a pattern :

• Open your query in Design view.
• Type the operator like in front of your criteria, you want to use in the Criteria cell.
• Replace one or more characters in the criteria with a wildcard character.
• On the Design tab, Click Run in the Results group.

Retrieve a list of companies A through H:

• Open your query in Design view.
• Enter LIKE in the criteria cell of the field you want to use.
• Within the double quotes, add a pair of square brackets and the  range of characters .
• You can also use wildcards outside the brackets.

Purpose –

The wildcard is an advanced search technique that can be used to maximize your search results in library databases. Wildcards are used in searching items to represent one or more characters.

LIKE mean in Access –

The LIKE condition allows you to use wildcards in the where clause of a SQL statement in Access 2007. It helps to perform pattern matching.

You can use wildcard characters in Access to add an additional level of  flexibility to your query criteria. Characters of wildcards within Access  represent unknown values. The asterisk ‘ * ‘ and the question mark ‘?‘ are  two main characters of wildcards in Access. The asterisk represents  multiple unknown characters. Whereas the question mark represents one  unknown character. You can also type as many question marks as needed  to fill in the required unknown characters. Whenever we type wildcard characters in Access into the criteria row of the grid, Access places the word “ LIKE “ before them. This is simply the required syntax. It is not  necessary for you to type it with yourself if you do not want to, as Access  adds it for you.

CALCULATED EXPRESSION –

A calculated field is a column in a query that results from an expression. An expression is a combination of mathematical or operators, constants, functions, table fields and properties that evaluates to a single value.

Forms and reports:

The tables in this section provide examples of expression that calculate a value in  a control located on a form or report. You can enter an expression in the Central  Source property of the control to create a calculated control.

Text operations –

The expression in the table, use the ampersand (&)and the plus (+) operators to  combine text or to create a calculated control.

Arithmetic operations –

You can use expressions to add , subtract, multiply and divide the values in two or  more fields or controls. You can also use it to perform on dates.

You can use the PAGE and the properties of PAGE to display or print page  number in forms or reports. Properties are available, so they do not appear on  the sheet for the report. It helps to identify the data collection in a table.

Values in other controls –

Sometimes, you need a value that exists somewhere else, such as in a field or  control on another form or report.

Average values –

You can use a type of function called an aggregate function to calculate value for  one or more fields or controls.

SQL aggregate functions –

You can also use a type of function called a SQL or domain aggregate function when  you need to calculate values selectively. A “domain “ consists of one or more fields in  one or more tables.

Data operations –

Tracking dates and follow times is a fundamental database activity. You can use  arithmetic operations on dates to calculate the date 30 days after the date in the  Invoice Date field and control.

Conditions of only two values –

Here, it is given three agreements, First it is an expression that must return in True  value and second, it’s values are returned in FALSE one.

## Queries and filters:

This part contains expressions that you can use to create a calculated field  in a query or to supply criteria to a query. You can use criteria in the query to  limit the records that you work with it.

Text operations –

The expressions in the table use the & and + operators to combine text strings,  and use built in function to operate it.

1. If creates a field called Full Name that displays the value in the First  and the Last Name fields.
2. Creates a field called Address that displays the values.
3. Creates a field called Product function to use the left function to  display the value of product name .
4. Creates a field called Type code, and then uses the right function to  display the last two characters of the values.
5. Creates a field called Area code.
6. Name the calculated field and the use function to calculate the total  items of applied discount.

Arithmetic operations –

You can use expressions to add, subtract, multiply and divide the values in two  or more fields or controls.

1. It creates a field that displays freight charges plus 10% in a field called  Prime Freight.
2. Creates a field called Order Amount that displays the values of the product.
3. Creates a field called Lead Time and then displays the difference between  the values Required Date and Shipped Date.

Data operations –

All databases store and track date and times. You work with it by setting the date  and time fields in the data type.

SQL aggregate functions –

It performs to summarize the data or you can say, sum, count and average the  data. It allows to count the number of records in the query, calculate the  percentage of freight charges and calculate the average freight on all orders to  combine in a total query.

Fields with missing data –

Here, the expression shows work with fields with potentially missing information, such as those unknown or undefined values. You encounter null  values, such as an unknown price for a new product or a value that a employee forgot to add. The ability to find the null and values can be a critical part of  database operation.

Calculated fields with subqueries –

You can use a subquery to create a calculated field that displays data about the category name, product Id from the categories table is the same as the product  one.

Match text values and data criteria –

Here, the expressions determine the whole or partially text values. And the date criteria expressions demonstrate the uses of dates and related functions in  the criteria expressions i.e., Shipped date, Required date and Order date.

Find missing data –

A null value represents the absence of information, it does not represent a zero or any value at all. Access supports the idea of missing information because  the concepts play a vital role in the probability of a database. And here, databases must be able to record information of displaying orders for customers whose  fields are null or contains a value.

Match record patterns with LIKE –

The LIKE operator provides flexibility when you are trying to match rows that  follow a pattern, because you can use LIKE with wildcard characters and  defined patterns for Access to match. It finds all records in the Ship Name field.

Match rows with SQL aggregates –

A domain aggregate function needs to sum, count or average values selectively. It displays all the orders.

Match fields with subqueries –

You can use a subquery to calculate a value for a criteria. It displays products price, product unit price above the average and earning of each sales  representatives.

Update queries –

You can use an update query to modify the data in one or more fields in a database. The field consists of Title, date of project when started and confirmation code.

SQL statement –

SQL ( Structured Query Language) is a query language that is used in Access, it  creates queries. Design View can also be expressed by using SQL.. It displays the  values in the First and Last Name field and displays the values of product Id  and product name. It also records the similar matches of category id.

TABLE EXPRESSION:

Here, there are two ways to use expressions in table –

Field default values –

Whenever you design a database, you might want to assign a default value to a  field. Here, Access supplies the default value .When a new record contains the  field is created or when an object contains, the control is created. The table  represents default values for a field, and if a control is bound to a field in a  table, takes precedence.

Field validation rules –

You can create a validation rule for a field or control by using an expression. To create a validation rule, we have to identify the properties of the field or control it. The component holds the text that displays the rule is violated.

MACRO EXPRESSION:

Sometimes, you might want to carry out an action in a macro if the given condition is TRUE. It must resolve either TRUE or FALSE value.

### Sorting and Filtering Records

We know that Access allows us to work with a humongous amount of data. But, going through such data while querying or manipulating is highly difficult and almost impossible for a human. So, we use sorting and filtering functionalities provided by the MS Access application to make our tasks easier. Sorting and Filtering are two tools that help us organise our data in our desired format and then view it. Let us understand each of them:

1. Sorting: In Sorting, you organise data in a particular order, a logical order. This results in the stored data being easier to read and understand over unsorted data. There are a number of ways you can perform sorting on the data. By default, the records are sorted by their id numbers. Some of the other ways include: sorting by name, last name, order_date, city, zip code, and category, etc.
2. Filtering: This feature allows you to view only the records or data you want to see. While creating filters, you set the criteria or the constraints for the data you want to view. The filter then hides the unwanted data or records and only the desired ones are displayed to you. This increases efficiency and reduces the distraction caused by unwanted data.

## Difference between Access and Excel

While both of these belong to the Microsoft Office Suite, they are not the same. They have very different functionalities.

Let us briefly understand the difference between Access and Excel:

## MS Access Tutorial FAQs

Q: What do you use Microsoft Access for?

A: Microsoft Access helps in fulfilling business requirements for several types of small-scale database solutions. MS Access is a very productive tool; therefore, valuable results can be fast produced, which ultimately helps the business. You can also call it an information management tool that helps in storing information for reporting, reference, and analysis.

Q: How can I learn MS Access?

A: There are MS Access tutorials for beginners available online. However, if you want to learn it with the simplest instructions, you can refer to the tutorials offered by Great Learning.

Q: Is MS Access hard to learn?

A: MS Access is not extremely hard to learn. However, those who are in general familiar with database concepts and relational database concepts will find it easier to learn.

Q: What is the difference between Excel and Access?

A: Microsoft Excel is an application in which spreadsheets are used for creating charts, graphs, and tabular models. Microsoft Access is yet another application that works as a database program and helps in assembling as well as sorting the data. MS Access is mostly used for spreadsheets and even financial calculations.

Q: What are the disadvantages of Access?

A: The disadvantages of MS Access include that entire data is stored in one file, limited database system, time-based transactions are difficult to carry in.

Q: Why is Microsoft Access so popular?

A: Microsoft Access is popular because even beginners can come up with a simple database quite quickly. The experienced MS Access developers can create a huge range of software functionality much faster as compared to the other development platforms.

Q: Which language is used in Microsoft Access?

A: An object-based programming language that supports MS Access is Visual Basic for Applications (VBA).

Q: Is Access harder than Excel?

A: Access is helpful in managing data, while Excel helps in analyzing data. Access helps in keeping data organized and is available to several users at a time. However, for producing higher quality charts, performing difficult calculations, you would need Excel.

Q: Can Access do calculations like Excel?

A: In the Access queries, a calculated field can very easily be created. Access can also calculate values that have been entered manually.

This brings us to the end of the blog on Microsoft Access Tutorial. We hope that you found this helpful. If you wish to learn more about such concepts, you can check out Great Learning Academy’s pool of free online courses.

Also, do check out our tutorials on other topics to up-skill and power ahead.

Original article source at: https://www.mygreatlearning.com

#MSAccess #Microsoft

1667636100

## Introduction

This repository contains examples and best practices for building recommendation systems, provided as Jupyter notebooks. The examples detail our learnings on five key tasks:

• Model: Building models using various classical and deep learning recommender algorithms such as Alternating Least Squares (ALS) or eXtreme Deep Factorization Machines (xDeepFM).
• Evaluate: Evaluating algorithms with offline metrics
• Model Select and Optimize: Tuning and optimizing hyperparameters for recommender models
• Operationalize: Operationalizing models in a production environment on Azure

Several utilities are provided in recommenders to support common tasks such as loading datasets in the format expected by different algorithms, evaluating model outputs, and splitting training/test data. Implementations of several state-of-the-art algorithms are included for self-study and customization in your own applications. See the Recommenders documentation.

For a more detailed overview of the repository, please see the documents on the wiki page.

## Getting Started

Please see the setup guide for more details on setting up your machine locally, on a Data Science Virtual Machine (DSVM) or on Azure Databricks.

The installation of the recommenders package has been tested with

and currently does not support version 3.10 and above. It is recommended to install the package and its dependencies inside a clean environment (such as conda, venv or virtualenv).

To set up on your local machine:

To install core utilities, CPU-based algorithms, and dependencies:

Ensure software required for compilation and Python libraries is installed.

On Linux this can be supported by adding:

sudo apt-get install -y build-essential libpython<version>

where <version> should be the Python version (e.g. 3.6).

On Windows you will need Microsoft C++ Build Tools.

Create a conda or virtual environment. See the setup guide for more details.

Within the created environment, install the package from PyPI:

pip install --upgrade pip
pip install recommenders[examples]

Register your (conda or virtual) environment with Jupyter:

python -m ipykernel install --user --name my_environment_name --display-name "Python (reco)"

Start the Jupyter notebook server

jupyter notebook

Run the SAR Python CPU MovieLens notebook under the 00_quick_start folder. Make sure to change the kernel to "Python (reco)".

For additional options to install the package (support for GPU, Spark etc.) see this guide.

NOTE - The Alternating Least Squares (ALS) notebooks require a PySpark environment to run. Please follow the steps in the setup guide to run these notebooks in a PySpark environment. For the deep learning algorithms, it is recommended to use a GPU machine and to follow the steps in the setup guide to set up Nvidia libraries.

NOTE for DSVM Users - Please follow the steps in the Dependencies setup - Set PySpark environment variables on Linux or macOS and Troubleshooting for the DSVM sections if you encounter any issue.

DOCKER - Another easy way to try the recommenders repository and get started quickly is to build docker images suitable for different environments.

## Algorithms

The table below lists the recommender algorithms currently available in the repository. Notebooks are linked under the Example column as Quick start, showcasing an easy to run example of the algorithm, or as Deep dive, explaining in detail the math and implementation of the algorithm.

NOTE: * indicates algorithms invented/contributed by Microsoft.

Independent or incubating algorithms and utilities are candidates for the contrib folder. This will house contributions which may not easily fit into the core repository or need time to refactor or mature the code and add necessary tests.

### Algorithm Comparison

We provide a benchmark notebook to illustrate how different algorithms could be evaluated and compared. In this notebook, the MovieLens dataset is split into training/test sets at a 75/25 ratio using a stratified split. A recommendation model is trained using each of the collaborative filtering algorithms below. We utilize empirical parameter values reported in literature here. For ranking metrics we use k=10 (top 10 recommended items). We run the comparison on a Standard NC6s_v2 Azure DSVM (6 vCPUs, 112 GB memory and 1 P100 GPU). Spark ALS is run in local standalone mode. In this table we show the results on Movielens 100k, running the algorithms for 15 epochs.

## Code of Conduct

This project adheres to Microsoft's Open Source Code of Conduct in order to foster a welcoming and inspiring community for all.

## Contributing

This project welcomes contributions and suggestions. Before contributing, please see our contribution guidelines.

## Build Status

These tests are the nightly builds, which compute the smoke and integration tests. main is our principal branch and staging is our development branch. We use pytest for testing python utilities in recommenders and Papermill and Scrapbook for the notebooks.

### AzureML Nightly Build Status

Smoke and integration tests are run daily on AzureML.

## References

• D. Li, J. Lian, L. Zhang, K. Ren, D. Lu, T. Wu, X. Xie, "Recommender Systems: Frontiers and Practices" (in Chinese), Publishing House of Electronics Industry, Beijing 2022.
• A. Argyriou, M. González-Fierro, and L. Zhang, "Microsoft Recommenders: Best Practices for Production-Ready Recommendation Systems", WWW 2020: International World Wide Web Conference Taipei, 2020. Available online: https://dl.acm.org/doi/abs/10.1145/3366424.3382692
• L. Zhang, T. Wu, X. Xie, A. Argyriou, M. González-Fierro and J. Lian, "Building Production-Ready Recommendation System at Scale", ACM SIGKDD Conference on Knowledge Discovery and Data Mining 2019 (KDD 2019), 2019.
• S. Graham, J.K. Min, T. Wu, "Microsoft recommenders: tools to accelerate developing recommender systems", RecSys '19: Proceedings of the 13th ACM Conference on Recommender Systems, 2019. Available online: https://dl.acm.org/doi/10.1145/3298689.3346967

## What's New (July, 2022)

We have a new release Recommenders 1.1.1!

We have introduced a new way of testing our repository using AzureML. With AzureML we are able to distribute our tests to different machines and run them in parallel. This allows us to test our repository on a wider range of machines and provides us with a much faster test cycle. Our total computation time went from around 9h to 35min, and we were able to reduce the costs by half. See more details here.

We also made other improvements like faster evaluation metrics and improving SAR algorithm.

Starting with release 0.6.0, Recommenders has been available on PyPI and can be installed using pip!

Here you can find the PyPi page: https://pypi.org/project/recommenders/

Here you can find the package documentation: https://microsoft-recommenders.readthedocs.io/en/latest/

Author: Microsoft
Source Code: https://github.com/microsoft/recommenders

1661460686

## Microsoft Azure DevOps Solution | Data Efficiency With Microsoft Devop

Agenda:

1. How to use the Azure DevOps tools for continuous delivery

2. How to support the team with Azure

3. Learn about work items, product backlogs, sprint backlogs, and more

4. Learn about Azure DevOps Repos

5. Look at Azure DevOps pipelines

6. How to use Azure DevOps artefacts

1654860780

## Microsoft Teamsで出来ること・メリットをわかりやすく解説！

この動画のテーマに関連する多数の実践課題をご用意しているので、「Teamsがわかる」だけでなく、「Teamsが使いこなせる」レベルまでスキルアップできます！

▼サマリー
00:00　本日のテーマ
01:55　1.Teamsの仕様について
04:04　2.Teamsで業務を完遂できる
06:51　3.料金について
07:56　本日のまとめ

1652982300

## 【Power Apps初心者枠第5回】Power Automate で通知機能を追加

Power Automate の使い方をご紹介します。

1652968800

## 【Power Apps初心者枠第4回】モデル駆動型アプリで Excel 出力

DataverseのテーブルをPowerShellを使用してWeb APIでCRUD操作します。Power AppsでDataverseにデータを蓄積し、Power Automate for desktopでオンプレ基幹にデータを入力したいと思い試しました。Mac版PowerShellを使用していますがWindowsでも可能です。

1652958000

## 【Power Apps初心者枠第3回】Microsoft Dataverse で対戦結果保存

今回は Power Apps のデータを Microsoft Dateverse へ保存する方法を解説します。
デモとして前回作成したじゃんけんアプリの対戦結果が保存できるように改良していきます。

1652140740

## DevSkim: Set Of IDE Plugins & Rules That Provide Security Capabilities

DevSkim

DevSkim is a framework of IDE extensions and language analyzers that provide inline security analysis in the dev environment as the developer writes code. It has a flexible rule model that supports multiple programming languages. The goal is to notify the developer as they are introducing a security vulnerability in order to fix the issue at the point of introduction, and to help build awareness for the developer.

### Features

• Built-in rules, and support for writing custom rules
• Cross-platform CLI built on .NET 5.0 for file analysis
• IDE plugins for Visual Studio and Visual Studio Code
• IntelliSense error "squiggly lines" for identified security issues
• Information and guidance provided for identified security issues
• Optional suppression of unwanted findings
• Broad language support including: C, C++, C#, Cobol, Go, Java, Javascript/Typescript, Python, and more.

### Repository Structure

This repository contains DevSkim and its official supported plugins. Issues and contributions are accepted here for:

• DevSkim Library
• Location: ./DevSkim-DotNet/
• DevSkim CLI
• Location: ./DevSkim-DotNet/Microsoft.DevSkim.CLI/
• DevSkim Visual Studio Extension
• Location: ./DevSkim-DotNet/Microsoft.DevSkim.VSExtension/
• DevSkim Blazor
• Location: ./DevSkim-DotNet/Microsoft.DevSkim.Blazor/
• DevSkim Visual Studio Code Plugin
• Location: ./DevSkim-VSCode-Plugin/
• Common Rules and Guidance
• Location: ./rules/default/

## Official Releases

DevSkim.Blazor is available to run in your browser as a Blazor WebAssembly application.

Platform specific binaries of the DevSkim CLI are available on our GitHub releases page.

The C# library is available on NuGet as Microsoft.CST.DevSkim.

The .NET Global Tool is available on NuGet as Microsoft.CST.DevSkim.CLI.

The Visual Studio extension is available in the Visual Studio Marketplace.

The Visual Studio Code plugin is available in the Visual Studio Code Marketplace.

DevSkim is also available as a GitHub Action to itegrate with the GitHub Security Issues pane.

## Installation

### Visual Studio Extension

The DevSkim Visual Studio extension can be downloaded and installed from the Visual Studio Marketplace.

Alternatively, in Visual Studio, open the Extension Manager (Menu: Extensions -> Manage Extensions), search for "Microsoft DevSkim", select the entry, and click on the Download button.

### Visual Studio Code Plugin

The DevSkim Visual Studio Code plugin can be downloaded and installed from the Visual Studio Code Marketplace.

Alternatively, In VS Code, launch the Quick Open bar (Ctrl + P), and run the following command: ext install ms-cst-e.vscode-devskim

### .NET Core App (Self Contained)

Download the platform specific binary archive for your system (Windows, Mac OS, Linux) from the releases page. Extract the archive, navigate to the DevSkim folder from a command line, and invoke devskim or devskim.exe.

### .NET Core Global Tool

If you already have .NET 5.0 installed, you can install the DevSkim CLI dotnet global tool by running the following from a command line:

dotnet tool install --global Microsoft.CST.DevSkim.CLI

This will add DevSkim to your PATH. You can then invoke the devskim command from a command line.

### .NET Core Runtime Dependent App

First download and install the .NET Core 5.0 runtime. Then download the DevSkim netcoreapp archive from the releases page. Extract the archive, navigate to the DevSkim folder from a command line, and invoke dotnet devskim.dll.

## Basic Usage

### DevSkim CLI

devskim analyze c:\path\to\FilesToAnalyze

### Visual Studio Extension / Visual Studio Code Plugin

Once the DevSkim plugin is installed and enabled, simply write some code, and feedback will be provided inline if issues are detected.

## Writing Rules

Please see Writing Rules for instructions on how to author new rules.

## Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.

## Reporting Issues

### Reporting Security Vulnerabilities

To report a security vulnerability, please see SECURITY.md.

Author: microsoft
Source Code: https://github.com/microsoft/devskim

1651730100

## Microsoft Windows

Python programming on Microsoft Windows.

• Python(x,y) - Scientific-applications-oriented Python Distribution based on Qt and Spyder.
• pythonlibs - Unofficial Windows binaries for Python extension packages.
• PythonNet - Python Integration with the .NET Common Language Runtime (CLR).
• PyWin32 - Python Extensions for Windows.
• WinPython - Portable development environment for Windows 7/8.

Author: vinta
Source Code: https://github.com/vinta/awesome-python

1649506560

VFS for Git

## What is VFS for Git?

VFS stands for Virtual File System. VFS for Git virtualizes the file system beneath your Git repository so that Git and all tools see what appears to be a regular working directory, but VFS for Git only downloads objects as they are needed. VFS for Git also manages the files that Git will consider, to ensure that Git operations such as status, checkout, etc., can be as quick as possible because they will only consider the files that the user has accessed, not all files in the repository.

Note: for new deployments, we strongly recommend you consider Scalar instead of VFS for Git. By combining the lessons from operating VFS for Git at scale with new developments in Git, Scalar offers a clearer path forward for all large monorepos.

## Installing VFS for Git

VFS for Git requires Windows 10 Anniversary Update (Windows 10 version 1607) or later.

To install, use winget to install the microsoft/git fork of Git and VFS for Git using:

winget install --id Microsoft.Git
winget install --id Microsoft.VFSforGit


You will need to continue using the microsoft/git version of Git, and it will notify you when new versions are available.

## Building VFS for Git

If you'd like to build your own VFS for Git Windows installer:

• .NET desktop development
• Desktop development with C++
• .NET Core cross-platform development
• Include the following additional components:
• .NET Core runtime
• Windows 10 SDK (10.0.10240.0)
• Install nuget.exe
• Create a folder to clone into, e.g. C:\Repos\VFSForGit
• Clone this repo into the src subfolder, e.g. C:\Repos\VFSForGit\src
• Run \src\Scripts\BuildGVFSForWindows.bat
• You can also build in Visual Studio by opening src\GVFS.sln (do not upgrade any projects) and building. However, the very first build will fail, and the second and subsequent builds will succeed. This is because the build requires a prebuild code generation step. For details, see the build script in the previous step.

You can also use Visual Studio 2019. There are a couple of options for getting all the dependencies.

• You can install Visual Studio 2017 side by side with Visual Studio 2019, and make sure that you have all the dependencies from Visual Studio 2017 installed
• Alternatively, if you only want to have Visual Studio 2019 installed, install the following extra dependencies:

Visual Studio 2019 will automatically prompt you to install these dependencies when you open the solution. The .vsconfig file that is present in the root of the repository specifies all required components except the Windows 10 SDK (10.0.10240.0) as this component is no longer shipped with VS2019 - you'll still need to install that separately.

The installer can now be found at C:\Repos\VFSForGit\BuildOutput\GVFS.Installer.Windows\bin\x64\[Debug|Release]\SetupGVFS.<version>.exe

## Trying out VFS for Git

• VFS for Git requires a Git service that supports the GVFS protocol. For example, you can create a repo in Azure DevOps, and push some contents to it. There are two constraints:
• Your repo must not enable any clean/smudge filters
• Your repo must have a .gitattributes file in the root that includes the line * -text
• gvfs clone <URL of repo you just created>
• Please choose the Clone with HTTPS option in the Clone Repository dialog in Azure Repos, not Clone with SSH.
• cd <root>\src
• Run Git commands as you normally would
• gvfs unmount when done

## Note on naming

This project was formerly known as GVFS (Git Virtual File System). You may occasionally see collateral, including code and protocol names, which refer to the previous name.

Notice: With the release of VFS for Git 2.32, VFS for Git is in maintenance mode. Only required updates as a reaction to critical security vulnerabilities will prompt a release.

Author: Microsoft
Source Code: https://github.com/microsoft/VFSForGit

#git #Microsoft

1648671960

Microsoft.Fast

## Introduction

The Microsoft.Fast.Components.FluentUI package provides a lightweight set of Blazor component wrappers around Microsoft's official FluentUI Web Components. The FluentUI Web Components are built on FAST and work in every major browser. To get up and running with Microsoft.Fast.Components.FluentUI see the Getting Started section below.

The source for @fluentui/web-components is hosted in the Fluent UI mono-repository. Documentation on the components is available on docs.microsoft.com.

## Getting Started

To get started using the Fluent UI Web Components for Blazor, you will first need to install the official Nuget package for Fluent UI. You can use the following command:

dotnet add package Microsoft.Fast.Components.FluentUI


Next, you need to add the web components script. You can either add the script from CDN directly, or you can install it with NPM, whichever you prefer.

To add the script from CDN use the following markup:

<script type="module" src="https://cdn.jsdelivr.net/npm/@fluentui/web-components/dist/web-components.min.js"></script>


The markup above always references the latest release of the components. When deploying to production, you will want to ship with a specific version. Here's an example of the markup for that:


<script type="module" src="https://cdn.jsdelivr.net/npm/@fluentui/web-components@2.0.2/dist/web-components.min.js"></script>


The best place to put the script tag is typically in your index.html (_Layout.cshtml for blazor server project) file in the script section at the bottom of the <body>.

If you wish to leverage NPM instead, run the following command:

npm install --save @fluentui/web-components


You can locate the single file script build in the following location:

node_modules/@fluentui/web-components/dist/web-components.min.js


Copy this to your wwwroot/script folder and reference it with a script tag as described above.

:notebook: Note

If you are setting up Fluent UI Web Components on a Blazor Server project, you will need to escape the @ character by repeating it in the source link. For more information check out the Razor Pages syntax documentation.

### Using the FluentUI Web Components

With the package installed and the script configured, you can begin using the Fluent UI Web Components in the same way as any other Blazor component. Just be sure to add the following using statement to your views:

@using Microsoft.Fast.Components.FluentUI


Here's a small example of a FluentCard with a FluentButton that uses the Fluent "Accent" appearance:

@using Microsoft.Fast.Components.FluentUI

<FluentCard>
<h2>Hello World!</h2>
<FluentButton Appearance="@Appearance.Accent">Click Me</FluentButton>
</FluentCard>


:bulb: Tip

You can add @using Microsoft.Fast.Components.FluentUI to namespace collection in _Imports.razor, so that you can avoid repeating it in every single razor page.

### Configuring the Design System

The Fluent UI Web Components are built on FAST's Adaptive UI technology, which enables design customization and personalization, while automatically maintaining accessibility. This is accomplished through setting various "design tokens". The easiest way to accomplish this in Blazor is to wrap the entire UI in a FluentDesignSystemProvider. This special element has a number of properties you can set to configure the tokens to your desired settings. Here's an example of changing the "accent base color" and switching the system into dark mode (in the file app.razor):

<FluentDesignSystemProvider AccentBaseColor="#464EB8" BaseLayerLuminance="0">
<Router AppAssembly="@typeof(App).Assembly">
<Found Context="routeData">
<RouteView RouteData="@routeData" DefaultLayout="@typeof(MainLayout)" />
</Found>
<NotFound>
<LayoutView Layout="@typeof(MainLayout)">
</LayoutView>
</NotFound>
</Router>
</FluentDesignSystemProvider>


:notebook: Note

Provider token attributes can be changed on-th-fly like any other Blazor component attribute.

If you are attempting to configure the components for integration into a specific Microsoft product, the following table provides AccentBaseColor values you can use:

For a list of all available token attributes, see here. More examples for other components can be found in the examples folder of this repository.

⭐ We appreciate your star, it helps!

## Joining the Community

Looking to get answers to questions or engage with us in realtime? Our community is most active on Discord. Submit requests and issues on GitHub, or join us by contributing on some good first issues via GitHub.

If you don't find a component you're looking for, it's best to create the issue in our FAST repo here and limit issues on this repo to bugs in the Blazor component wrappers or Blazor-specific features.

We look forward to building an amazing open source community with you!

## Contact

• Join the community and chat with us in real-time on Discord.
• Submit requests and issues on GitHub.
• Contribute by helping out on some of our recommended first issues on GitHub.

Author: Microsoft
Source Code: https://github.com/microsoft/fast-blazor

1647671479

## Automated installation Of The Microsoft IE App Compat Virtual Machines

Overview

Microsoft provides virtual machine disk images to facilitate website testing in multiple versions of IE, regardless of the host operating system. With a single command, you can have IE8, IE9, IE10, IE11 and MSEdge running in separate virtual machines. Each virtual machine comes with the selected version of IE already installed. IE11 machines can be selected with Windows 7 or Windows 8.1.

The original script works as it originally did, with hard-coded urls and md5 values. The 'ievms-node' script uses Node to hit the Microsoft API and grab the current correct url and md5 hashes. This version should be a little more future-proof, but obviously requires having Node installed.

This is a hacked on fork that has been altered to work with the currently available versions and URLs for the VMs on Microsoft's site. The original repo can be found here. The linked pledgie is for the original author.

Known Issues

• IE11 - Win81 MD5 URL is dead, and I'm unable to determine a working one. MD5 has been temporarily hard-coded as a workaround.

Quickstart

Just paste this into a terminal:

curl -s https://raw.githubusercontent.com/amichaelparker/ievms/master/ievms.sh | bash


or:

curl -s https://raw.githubusercontent.com/amichaelparker/ievms/master/ievms-node.sh | bash


Requirements

• VirtualBox > 5.0 (http://virtualbox.org), select 'command line utilities' during installation
• Curl (Ubuntu: sudo apt-get install curl)
• Linux Only: unar (Ubuntu: sudo apt-get install unar) (Might need this for Mac as well)
• Node (Recommended)
• Patience

NOTE Use ievms version 0.2.1 for VirtualBox < 5.0.

Installation

1.) Install VirtualBox and check the Requirements

To install IE versions 8, 9, 10, 11 and EDGE use:

To install specific IE versions (IE8, IE9 and EDGE only for example) use:

curl -s https://raw.githubusercontent.com/amichaelparker/ievms/master/ievms-node.sh | env IEVMS_VERSIONS="8 9 EDGE" bash

3.) Launch Virtual Box.

4.) Choose ievms image from Virtual Box.

The OVA images are massive and can take hours or tens of minutes to download, depending on the speed of your internet connection. You might want to start the install and then go catch a movie, or maybe dinner, or both.

## Recovering from a failed installation

Each version is installed into ~/.ievms/ (or INSTALL_PATH). If the installation fails for any reason (corrupted download, for instance), delete the appropriate ZIP/ova file and rerun the install.

If nothing else, you can delete ~/.ievms (or INSTALL_PATH) and rerun the install.

## Specifying the install path

To specify where the VMs are installed, use the INSTALL_PATH variable:

curl -s https://raw.githubusercontent.com/amichaelparker/ievms/master/ievms.sh | env INSTALL_PATH="/Path/to/.ievms" bash


## Passing additional options to curl

The curl command is passed any options present in the CURL_OPTS environment variable. For example, you can set a download speed limit:

curl -s https://raw.githubusercontent.com/amichaelparker/ievms/master/ievms.sh | env CURL_OPTS="--limit-rate 50k" bash


## Disk requirements

A full ievms install will require approximately 69G:

Servo:.ievms amichaelparker$du -ch * 11G IE10 - Win7-disk1.vmdk 22M IE10-Windows6.1-x86-en-us.exe 11G IE11 - Win7-disk1.vmdk 28M IE11-Windows6.1-x86-en-us.exe 1.5G IE6 - WinXP-disk1.vmdk 724M IE6 - WinXP.ova 717M IE6_WinXP.zip 1.6G IE7 - WinXP-disk1.vmdk 15M IE7-WindowsXP-x86-enu.exe 1.6G IE8 - WinXP-disk1.vmdk 16M IE8-WindowsXP-x86-ENU.exe 11G IE9 - Win7-disk1.vmdk 4.7G IE9 - Win7.ova 4.7G IE9_Win7.zip 10G MSEdge - Win10-disk1.vmdk 5.1G MSEdge - Win10.ova 5.0G MSEdge_Win10.zip 3.4M ievms-control-0.3.0.iso 4.6M lsar 4.5M unar 4.1M unar1.5.zip 69G total  You may remove all files except *.vmdk after installation and they will be re-downloaded if ievms is run again in the future: $ find ~/.ievms -type f ! -name "*.vmdk" -exec rm {} \;


If all installation related files are removed, around 47G is required:

Servo:.ievms amichaelparker\$ du -ch *
11G    IE10 - Win7-disk1.vmdk
11G    IE11 - Win7-disk1.vmdk
1.5G    IE6 - WinXP-disk1.vmdk
1.6G    IE7 - WinXP-disk1.vmdk
1.6G    IE8 - WinXP-disk1.vmdk
11G    IE9 - Win7-disk1.vmdk
10G    MSEdge - Win10-disk1.vmdk
47G    total


Features

## Clean Snapshot

A snapshot is automatically taken upon install, allowing rollback to the pristine virtual environment configuration. Anything can go wrong in Windows and rather than having to worry about maintaining a stable VM, you can simply revert to the clean snapshot to reset your VM to the initial state.

## Guest Control

VirtualBox guest additions are installed after each virtual machine is created (and before the clean snapshot) and the appropriate steps are taken to enable guest control from the host machine.

If one of the comically large files fails to download, the curl command used will automatically attempt to resume where it left off. Unfortunately, the modern.IE download servers do not support resume.

## Reusing Win7 VMs

Currently there exists a bug in VirtualBox (or possibly elsewhere) that disables guest control after a Windows 8 virtual machine's state is saved. To better support guest control and to eliminate yet another image download, ievms will re-use the IE9 Win7 image for IE10 and IE11 by default. In addition, the Win7 VMs are the only ones which can be successfully "rearmed" to extend the activation period.

NOTE: If you'd like to disable Win7 VM reuse for IE10, set the environment variable REUSE_WIN7 to anything other than yes:

curl -s https://raw.githubusercontent.com/amichaelparker/ievms/master/ievms.sh | REUSE_WIN7="no" bash


NOTE: IE10 uses Win7 by default in this fork, and IE11 can use Win7 or Win81 based on the 'REUSE_WIN7' flag. It currently defaults to 'no', installing with Win81.

Acknowledgements

• modern.IE - Provider of IE VM images.
• ntpasswd - Boot disk starting point and registry editor.
• regit-config - Minimal Virtualbox kernel config reference.
• uck - Used to (re)master control ISO.

Author: Amichaelparker/
Source Code: https://github.com/amichaelparker/ievms
License: None. (To quote Morrissey, "take it, it's yours")

1645268400

## Hidden IDisposable Issues In Microsoft .NET

As an experienced .NET software engineer, whenever I am hired into a new team, I almost immediately get tasked to fix coding issues. I guess since I am dotNetDave and I wrote the only coding standards book for .NET! Out of all the millions of lines of code I have reviewed in the past 20 years of .NET, hands down the #1 issue are developers not properly disposing of objects that implement IDisposable. In some teams, it has taken me many months of hard work to fix these issues that will always lead to virtual memory leaks.

1645210800

## Getting Started with The Microsoft Graph toolkit

In this blog, you will learn how to show all response value from Microsoft Graph API in Microsoft Graph Toolkit get component. There are a lot of web components available in the Microsoft Graph Toolkit. We can use these components across multiple applications, ex., SharePoint Framework webpart, Node.js web application, etc.

1643605500

## 時間を節約する3つのMicrosoftProjects

コミュニティのほとんどの開発者は、VSCodeとTypeScriptを知っています。ただし、この投稿には、次のアプリケーションの構築に使用できる、Microsoftによって開発されたあまり知られていないツールのリストが含まれています。これらは高速で信頼性が高く、Microsoft開発者がワークフローで使用します。

しかし、MackbookとLinuxでアプリケーションを開発するのが好きなら、私はあなたを責めません。彼らには長所と短所があります。私もMackbookを持っていて、LinuxやWindowsに戻ることすら考えられません。

Microsoftは、複数のストリームから収益を生み出しています。誰もが知っているので、私はおそらくそこに行かないでしょう。たくさんの商品があります。しかし、この組織は、人々の生活を楽にするオペレーティングシステムやOffice365などのデジタル製品を作成して販売するだけではありません。開発者が手間をかけずに必要なアプリケーションを構築できるように、多くのことを行ってきました。

しかし、Webに関しては、Github上に多数の優れたプロジェクトがオープンソース化されています。開発者は、これらのフレームワークとライブラリを使用してアプリケーションを構築できます。

1. FluentUI

スクリーンショット

Fluent UIは、Microsoftによって開発されたオープンソースのクロスプラットフォーム設計システムです。複数のアプリを構築する場合、デザイナーと開発者は、コンポーネントを定義するための標準的な固定設計を学び、考案する必要があります。Fluent UIは、MicrosoftのUI処理方法です。おそらくマテリアルデザインの代わりになります。

MicrosoftアプリのUIが魅力的であることに気付いた場合は、このプロジェクトを確認する必要があります。Webの場合、残りの部分でReactおよびFabricコアを使用する開発者向けのFluent UIReactパッケージとして利用できます。

すべてのAPIをインストールして、MicrosoftのようなUIの構築を開始します。

FluentUIへのリンク

2.劇作家

スクリーンショット

Microsoftがテストを気にかけていなかったと思うなら、おそらくあなたは間違っています。劇作家は、最新のWeb用のエンドツーエンドのテストライブラリです。Python、JavaScriptなどの両方で利用できます。見逃せない軽量ライブラリです。私に言わせれば、ウェブ上のテストフレームワークよりも10倍優れていると思います。

3. LUIS.Ai（コグニティブサービスの一部）

スクリーンショット

LUISは、Microsoftのコグニティブ機械学習ツールキットの一部です。アプリにチャットボットまたはSiriのような機能（テキスト）を作成することと考えてください。

これは、ウェブアプリやモバイルアプリで自然言語を構築して統合するための機械学習ベースのサービスです。自然言語理解、エンティティ抽出、それはすべてのことを行います。「自然言語処理のN」を知らずにNLPを統合したい場合は、このプロジェクトをチェックする必要があります。

LUISへのリンク

Microsoftには、開発者が日常業務を行うのに役立つさまざまなプロジェクトが多数含まれていますが、上記は私のお気に入りの3つです。

それで、DIYとして、私はあなたにいくつかの掘り下げをして、あなたのお気に入りのマイクロソフトプロジェクトについて知らせてくれるようにお願いします。あなたはそれらがたくさんあることを知っています。しかし、あなたはそれを試したことがありますか？それとも、スタートアップが彼らのツールを使って会社を設立するのをまだ待っていて、それからあなたは同じことをしようとしますか？

コメント欄で教えてください。会話ができたら嬉しいです。

#Microsoft #developer