MS Access is a database management system that allows you to store, manipulate, and analyze data. One of the features of MS Access is the ability to create queries, which are statements that retrieve data from one or more tables or other sources. Queries can also perform calculations, such as adding, subtracting, multiplying, or dividing values from different fields or expressions.
In this article, we will show you how to divide output from query in MS Access, using two methods: using the division operator (/) and using the Split() function.
Using the Division Operator (/)
The division operator (/) is a mathematical operator that returns the quotient of two numbers. You can use the division operator in a query to divide values from different fields or expressions. For example, if you have a table called Sales, with fields Product, Price, and Quantity, you can create a query that calculates the average price per unit for each product, by dividing the Price field by the Quantity field. The query would look something like this:
SELECT Product, Price, Quantity, Price / Quantity AS AvgPrice
FROM Sales;
The result of the query would be a table with four columns: Product, Price, Quantity, and AvgPrice. The AvgPrice column would show the result of dividing the Price by the Quantity for each product.
Note that if you divide by zero, you will get an error message. To avoid this, you can use the IIf() function or the Nz() function to check for zero values and replace them with a different value. For example, you can use the IIf() function to return zero instead of an error, like this:
SELECT Product, Price, Quantity, IIf(Quantity = 0, 0, Price / Quantity) AS AvgPrice
FROM Sales;
Or you can use the Nz() function to replace null values with zero, like this:
SELECT Product, Price, Quantity, Nz(Price) / Nz(Quantity) AS AvgPrice
FROM Sales;
Using the Split() Function
The Split() function is a string function that splits a string into an array of substrings, based on a delimiter. You can use the Split() function in a query to divide a text field into multiple fields, based on a character or a string that separates the values. For example, if you have a table called Customers, with a field Address, that contains the street, city, state, and zip code of each customer, separated by commas, you can create a query that splits the Address field into four fields: Street, City, State, and Zip. The query would look something like this:
SELECT Address, Split(Address, ",")(0) AS Street, Split(Address, ",")(1) AS City, Split(Address, ",")(2) AS State, Split(Address, ",")(3) AS Zip
FROM Customers;
The result of the query would be a table with five columns: Address, Street, City, State, and Zip. The Street column would show the first element of the array returned by the Split() function, the City column would show the second element, and so on.
Note that the Split() function returns a zero-based array, which means that the first element has the index 0, the second element has the index 1, and so on. Also, note that the Split() function requires a delimiter to be specified, otherwise it will return the original string as the only element of the array.
