Use the query feature in Microsoft Access to join the tables

Use the query feature in Microsoft Access to join the tables and calculate aggregate sales for each product. Do not worry about the location of the sales—you are interested in total sales only. ·

You are running the sale from the start of January to the end of March. Given the seasonality of some of the products, you should look only at sales in the same time frame. Moreover, since decorating trends come and go, you should consider only the last two most recent years’ sales only for the selected season (January to March).

The query should join the necessary tables and apply criteria to limit the results to those required for subsequent contribution analysis. Hint: Create the required relationships first.

Tables PRODUCTS EEE SALES PRODUCTS DEPTH PRICE GROSS MARGIN PRODUCT ID TEM HEIGHT WIDTH 42 $313.99 LINGO sideboard 61.00% 100-100 66 152 116 41 $107.99 39.00% CIRKEL sideboard 100-15 87 100-20 GUSTAV coffee table 26 116 62 $107.99 61.00% 100-25 68 $374.99 GOLIAT desk with shelf unit 49.00% 72 156 100-30 SIGURD coffee table 48 114 82 $86.99 22.00% 100-35 39 $86.99 ISALA cabinet 59.00% 158 42 100-40 LIATORP conference table 58 192 115 $400.99 44.00% 100-45 41 $183.99 FREDRIK sofa table 63.00% 60 137 100-50 HEMNES occational table 87 127 43 $172.99 45.00% 100-55 63 $248.99 SMYCKA desk with shelf unit 66.00% 82 108 100-60 LACK side table 44 54 58 S10.99 48.00% 100-65 46 $215.99 MURBO cabinet 70.00% 127 74 100-70 BISLEV desk 72 137 76 $139.99 46.00% 100-75 KLINGSBO laptop table 38 $96.99 59.00% 66 89 200-10 MICKE visitor chair 75 54 52 s 32.99 36.00% 200-15 JONAS bench 45 $97.99 61.00% 112 200-20 YVRSNO chair 67 52 52 $75.99 53.00% 200-25 REKTANGEL swivel chair 78 $324.99 21.00% 73 200-30 EXPEDIT swivel chair 85 54 58 S107.99 37.00% 200-35 52 $42.99 FLORERA chair 58.00% 68 49 200-40 LYCKHEM armchair 75 79 73 $107.99 63.00% 200-45 KILBY armchair 89 $64.99 52.00% 93 66 200-50 CARMONA visitor chair 81 59 58 $215.99 63.00% 200-55 61 $75.99 35.00% SKUARE swivel chair 84 58 200-60 DOKUMANT bench 44 148 37 S107.99 72.00% 200-65 52 $75.99 VOLMAR chair 32.00% 77 45 200-70 MOSES stool 44 39 31 $42.99 70.00% 200-75 GRONO visitor chair 26.00% 92 67 99 S172.99 Record: 41 of 74 No Flter Search

Solution

SELECT SUM(SALES) AS TOTAL_SALES FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID WHERE TRANSDATE >=#01/01/2014# and TRANSDATE <=#31/03/2014#;

SUM will add all sales in the records returned by rest of the query.

INNER JOIN joins two table PRODUCTS and SALES depending on criteria when PRODUCTS.PRODUCT_ID is matched with SALES.PRODUCT_ID

Finally, it refine the result result using TRANSDATE >=#01/01/2014# and TRANSDATE <=#31/03/2014# so that only those records picked up which occurred in between January and March