Microsoft Access Examples

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

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;

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"));

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;

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];