Create saved query in MySQL, nesting

Microsoft Access has a nice feature which lets you create queries and also link them together. This can also be done with other platforms like MySQL however the difference is individual queries cannot be saved and later linked, it must be all done in one script. Its basically multiple “SELECT * FROM” in one query.

Microsoft Access:
FROM table
INNER JOIN saved_query
ON table.field = saved_query.field;

FROM table as T
(SELECT * FROM another_table) as P
ON T.field = P.field

The difference between the two examples is MySQL needs to nest any additional query and also name each (eg: as T).

It’s a lot easier for me to write a query in Access. What I did was take the Access SQL code and replace (copy and paste) the saved queries with the full code.  See the complex example below.

Access code:
SELECT year.Year, [how many dividends were paid a year].*, [Price average per year].ticker, [Price average per year].AvgOfhigh, [how many dividends were paid a year].[sumofdividend]/[Price average per year].[AvgOfhigh]*100 AS yield
FROM ([year]
LEFT JOIN [how many dividends were paid a year]
ON year.Year = [how many dividends were paid a year].year)
LEFT JOIN [Price average per year]
ON year.Year = [Price average per year].year
WHERE ((([Price average per year].ticker)=[how many dividends were paid a year].[ticker]))
ORDER BY [Price average per year].ticker;

MySQL code:
SELECT t.Year, p.*, k.ticker, k.AvgOfhigh, p.`sumofdividend`/k.`AvgOfhigh`*100 AS yield
FROM (year as t
LEFT JOIN (SELECT dividend.ticker, Count(dividend.ticker) AS CountOfticker, Left(dividend.Date,4) AS year, Sum(dividend.dividend) AS SumOfdividend
FROM dividend
GROUP BY dividend.ticker, Left(dividend.Date,4)
ORDER BY dividend.ticker ) as p
ON t.Year = p.year)
LEFT JOIN (SELECT price.ticker, Left(price.Date,4) AS year, Avg(price.high) AS AvgOfhigh
FROM price
GROUP BY price.ticker, Left(price.Date,4)) as K
ON t.Year = k.year
WHERE (((k.ticker)=p.ticker))
ORDER BY k.ticker

the most common error I encounter during the conversion was that semicolon(;) and MySQL doesn’t use brackets([])

inner join another query,  create saved query in mysql, link queries, relationship multiple queries, connect query to one another, save query as table

Connect Access 2010 to MySQL

If you want to be able to use the tools found in Microsoft Access 2010 with your MySQL data here is how to do it.

1. You need download and install Connector/ODBC
2. Once installed open Access and click on OBDC Database under External Data, and select “Link to the data source by creating a linked table”.

3. Under “Machine Data Source” click “New”

4. Click Next

5. Select MySQL OBDC 3.51 Driver and click Next

6. Click Finish

7. Input your database data


For additional information go to