Datamartist gives you data profiling and data transformation in one easy to use visual tool.

Microsoft Access Examples

MS Access Query Examples

Datamartist Tool Visual Alternative

How to combine multiple tables: Union Query

select * from Table1 UNION select * from Table2

or name specific fields

select Field1, Field2 from Table1 UNION select Field1,Field2 from Table2

or change the field names to match using AS if the two tables do not have matching field names

select Field1,Field2 from Table1 UNION select FieldA as Field1,FieldB as Field2 from Table3

Datamartist Combine Block

Datamartist Combine Block

How to summarize sales data by month- use sum on QTY and average on PRICE

SELECT Year([SALES_DATE]) AS [Year], Month([SALES_DATE]) AS [Month], [Sales Data 2007].PRODUCT_ID, [Sales Data 2007].CUSTOMER_ID, Sum([Sales Data 2007].QTY) AS SumOfQTY, Avg([Sales Data 2007].PRICE) AS AvgOfPRICE
FROM [Sales Data 2007]
GROUP BY Year([SALES_DATE]), Month([SALES_DATE]), [Sales Data 2007].PRODUCT_ID, [Sales Data 2007].CUSTOMER_ID;

or create a single field in the format YYYY_MM to make it easier to sort multi year results

SELECT Year([SALES_DATE]) & "_" & Left("00",2-Len(Month([SALES_DATE]))) & Month([Sales_Date]) AS YYYY_MM, [Sales Data 2007].PRODUCT_ID, [Sales Data 2007].CUSTOMER_ID, Sum([Sales Data 2007].QTY) AS SumOfQTY, Avg([Sales Data 2007].PRICE) AS AvgOfPRICE
FROM [Sales Data 2007]
GROUP BY Year([SALES_DATE]) & "_" & Left("00",2-Len(Month([SALES_DATE]))) & Month([Sales_Date]), [Sales Data 2007].PRODUCT_ID, [Sales Data 2007].CUSTOMER_ID;

Datamartist Summarize Block

Datamartist Summarize Block

How to filter on specific records using WHERE (Customers with last name Smith and first name not starting with "A")

SELECT Customers.CUSTOMER_ID, Customers.FIRSTNAME, Customers.LASTNAME, Customers.ADDRESS1, Customers.CITY, Customers.STATE, Customers.COUNTRY
FROM Customers
WHERE (((Left([FIRSTNAME],1))<>"A") AND ((Customers.LASTNAME)="Smith"));

Datamartist Filter Block

Datamartist Filter Block

Example to create product segments by nesting IIF statements

SELECT Products.Product_ID, Products.Product_Name, Products.Product_Group, Products.Product_Category, Products.Product_SubCategory, Products.Shipping_Weight, Products.Color, Products.Price_Min, Products.Price_Max, IIf([Color]="Red" And [Price_Min]>1000,"Red and High Priced",IIf([Color]="Red" And ([Price_max]-[Price_min])>200,"Red Low Price wide price range",IIf([Color]="Red","Red Low Price small price range",IIf([Color]="Yellow","Yellow","Other")))) AS Product_Segment
FROM Products;

Datamartist Segmentation Block

Datamartist Segmentation Block

How to add a calculated field

SELECT [Sales Data 2007].SALES_DATE, [Sales Data 2007].QTY, [Sales Data 2007].PRICE, [Sales Data 2007].PRODUCT_ID, [Sales Data 2007].CUSTOMER_ID, [Qty]*[Price] AS Total
FROM [Sales Data 2007];

Datamartist Calculation Block

Datamartist Calculation Block

The Datamartist tool is an easy to use, graphical alternative to Microsoft Access.