Convert ms access query to mysql

[MrMode]

I know nothing about MySQL and have to get my ms access database talking to a MySQL back end. I need to use pass through queries, simple ones are fine but anything slightly complex fails as I need to write them in MySQL language. Does anyone know of a tool that can rewrite ms access queries in mySQL syntax?

 

[jpadie]

sfaik there is no such thing; although one could certainly be written.

but since jet-sql is so non-standard i doubt whether there would be a great deal of interest in writing such a tool.

post a couple of jet sql queries and we can show you the equivalent in mysql. you can go from there.

 

[MrMode]

OK, understood.

Thanks for the offer, here are two, firstly a pivot:

TRANSFORM First([2014 02 7th AdvanceInfo].Advinfo) AS FieldValue
SELECT tb_company_basic.company_id
FROM (tb_company_basic INNER JOIN [2014 02 7th AdvanceInfo] ON tb_company_basic.company_id = [2014 02 7th AdvanceInfo].company_id) INNER JOIN tb_hmc_fields ON [2014 02 7th AdvanceInfo].web_id = tb_hmc_fields.web_id
GROUP BY tb_company_basic.company_id
PIVOT tb_hmc_fields.name;

And secondly a concatenate query:

SELECT DISTINCT SCCtblAssessedData.[Company ID], ConcatRelated("[tb_company_keyword_correlation].[keyword] & "", "" AS Keywords","tb_company_keyword_correlation","[company_id] = " & [tb_company_keyword_correlation].[company_id]) AS Keywords
FROM tb_company_keyword_correlation INNER JOIN SCCtblAssessedData ON tb_company_keyword_correlation.company_id = SCCtblAssessedData.[Company ID];

 

[jpadie]

you don't provide the underlying table structure but as a first off - you can't do pivots in mysql in the same way as access. instead you need to describe what it is you are trying to achieve in business terms (by reference to the columns in your tables) and then see how to achieve that using standard sql.

for the second ... again a guess as i have no table structure nor business aim to work from but this might be what you are looking for

SELECT      s.`Company ID` as 'Company ID', 
            CONCAT_WS(",",kc.keyword) as 'Keywords'
FROM        SCCtblAssessedData s
JOIN        tb_company_keyword_correlation kc
ON          kc.company_id = s.companyID
GROUP BY    s.`Company ID` ASC

note that having spaces in field names in mysql is a righteous PITA. better to use camelcase or whatever other structure you want for readability and avoid spaces.

 

[MrMode]

OK, thanks for this, I will try your suggestion and come back to you.

 

[MrMode]

Could I be cheeky and ask for help in converting this into MySQL?

SELECT PortalData.[Company ID], PortalData.[Start Date], PortalData.[Company Name], tblSupplierLocation.SupplierLocation, PortalData.Status, PortalData.[Address 1], PortalData.[Address 2], PortalData.Town, PortalData.County, PortalData.Postcode, PortalData.[Company Website], PortalData.[Telephone (main office)], PortalData.[Main Contact Firstname], PortalData.[Main Contact Surname], PortalData.[Main Contact Position Held]
FROM (SCCtblAssessedData INNER JOIN PortalData ON SCCtblAssessedData.[Company ID] = PortalData.[Company ID]) INNER JOIN (tblSupplierLocation INNER JOIN tblUKPostcodes ON tblSupplierLocation.SupplierLocationID = tblUKPostcodes.SupplierLocationId) ON SCCtblAssessedData.[SCC Data ID] = tblUKPostcodes.ID
WHERE (((tblSupplierLocation.SupplierLocation) Like "Somerset") AND ((PortalData.Status)="ready" Or (PortalData.Status)="almost ready" Or (PortalData.Status)="fss"));

Thanks

 

[ChrisHirst]

use ' instead of [ and ]

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[MrMode]

Ok, thanks I will try it.

 

[jpadie]

do ' work? I thought it had to be backticks in mysql. perhaps any delimeter is ok? or do you have to set ANSI_QUOTES to use apostrophes as text qualifiers?