INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Convert ms access query to mysql

Convert ms access query to mysql

(OP)
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?

RE: Convert ms access query to mysql

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.

RE: Convert ms access query to mysql

(OP)
OK, understood.

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

CODE -->

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:

CODE -->

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

RE: Convert ms access query to mysql

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

CODE

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.

RE: Convert ms access query to mysql

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

RE: Convert ms access query to mysql

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

CODE -->

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

RE: Convert ms access query to mysql

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

RE: Convert ms access query to mysql

(OP)
Ok, thanks I will try it.

RE: Convert ms access query to mysql

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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close