Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

Convert MySQL Query to ACCESS Query

The following MySQL query works wonderful in MySQL, but not in MS Access.
Is this still possible to do in Access?

Thanks

use `file_room`;

SELECT
    fr.CIS_ACCOUNT_ID,
    fn.DATE_OF
    
FROM `fileroom` fr

LEFT OUTER JOIN `finance` fn
    ON fn.CIS_ACCOUNT_ID = fr.CIS_ACCOUNT_ID
        AND fn.DATE_OF = (SELECT MAX(x.DATE_OF) FROM `finance` x
                            WHERE x.CIS_ACCOUNT_ID = fn.CIS_ACCOUNT_ID)
                            
ORDER BY 2 DESC

Open in new window

0
JustinW
Asked:
JustinW
  • 6
  • 4
  • 3
2 Solutions
 
NorieCommented:
Justin

Generally yes but it would need some changes.

The main one I think would be the use of a subquery in a join, I didn't even know that was possible inMySQL.

Not sure how to replace it, what is the purpose of the query?

Other things you need to change:

1 Remove the ' around the table names.

2 Change things like this, where a table is given an alias.

'finance' fn

to this.

finance AS fn

3 Use the field name in the order by.

ORDER BY DATE_OF DESC

Without the subquery in the join, the query would look like this:


SELECT    fr.CIS_ACCOUNT_ID,    fn.DATE_OF    
FROM fileroom AS fr
LEFT OUTER JOIN finance AS fn ON fn.CIS_ACCOUNT_ID = fr.CIS_ACCOUNT_ID      
ORDER BY DATE_OF DESC

Which is accepted by Access, except forr the bit where it can't find tables called fileroom, finance etc.

That can be explained by those tables not existing in the database I used to check the code.

0
 
JustinWAuthor Commented:
I get a file every month with updated finance information about each asset we have.
I store all of these records in 1 table; which for 1 asset gives you a 9/30 version,
and 8/31 version, etc. (FINANCE)

I have another table that is a listing of each unique ID #, plus other important info
that doesn't change from month. (FILEROOM)

I'm trying to join the Finance table to the Fileroom table on the most current instance
of the record in the finance table.

Does this make sense?
0
 
lee555J5Commented:
The following should work, reinstating your MAX(date) requirement.
SELECT
  fr.CIS_ACCOUNT_ID, fn.DATE_OF
FROM
  fileroom AS fr LEFT JOIN finance AS fn ON fn.CIS_ACCOUNT_ID = fr.CIS_ACCOUNT_ID
WHERE
  fn.DATE_OF = (SELECT MAX(x.DATE_OF) FROM finance AS x WHERE x.CIS_ACCOUNT_ID = fn.CIS_ACCOUNT_ID)
ORDER BY
  fn.DATE_OF DESC;

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
NorieCommented:
I think I know what you mean.

What I think you can do is simply move the subquery to a WHERE clause.

SELECT    fr.CIS_ACCOUNT_ID,    fn.DATE_OF    
FROM fileroom AS fr
LEFT OUTER JOIN finance AS fn ON fn.CIS_ACCOUNT_ID = fr.CIS_ACCOUNT_ID    
WHERE  fn.DATE_OF = (SELECT MAX(x.DATE_OF) FROM finance AS  x  
                                   WHERE x.CIS_ACCOUNT_ID = fn.CIS_ACCOUNT_ID)
ORDER BY DATE_OF DESC

0
 
JustinWAuthor Commented:
Both were close, but its like its doing an inner join.
The FileRoom Table has 10k records, and the query
only returns 5k.

Not all records in the FileRoom table are in the Finance Table.
Thoughts?
0
 
NorieCommented:
Are all 10K records in the fileroom table unique?
0
 
lee555J5Commented:
As it is, it does work like an INNER JOIN because of the WHERE clause. So you want all records from fileroom and show the date when one is found?
0
 
JustinWAuthor Commented:
Yes,

however, I might end up going another direction, because
access won't allow me to modify any of the records returned
from this query, even though, the ones I'm trying to modify are
on the FileRoom side
0
 
JustinWAuthor Commented:
sorry, response @ imnorie
0
 
JustinWAuthor Commented:
@lee, No, just want the record with the most recent date joined to the FileRoom Table
0
 
lee555J5Commented:
My left and right were reversed. Try this
SELECT
  fr.CIS_ACCOUNT_ID, fn.DATE_OF
FROM
  fileroom AS fr LEFT JOIN finance AS fn ON fr.CIS_ACCOUNT_ID = fn.CIS_ACCOUNT_ID
WHERE
  fn.DATE_OF = (SELECT MAX(x.DATE_OF) FROM finance AS x WHERE x.CIS_ACCOUNT_ID = fn.CIS_ACCOUNT_ID)
ORDER BY
  fn.DATE_OF DESC;

Open in new window

0
 
JustinWAuthor Commented:
Its like Access isn't handling NULL the same as SQL.

All of the records in your query (lee) have a match in the Finance Table.
Again, its still performing like an inner join.
0
 
lee555J5Commented:
Modify the WHERE to not exclude records with no match in finance.
SELECT
  fr.CIS_ACCOUNT_ID, fn.DATE_OF
FROM
  fileroom AS fr LEFT JOIN finance AS fn ON fr.CIS_ACCOUNT_ID = fn.CIS_ACCOUNT_ID
WHERE
  fn.DATE_OF = (SELECT MAX(x.DATE_OF) FROM finance AS x WHERE x.CIS_ACCOUNT_ID = fn.CIS_ACCOUNT_ID)
  Or fn.DATE_OF Is Null
ORDER BY
  fn.DATE_OF DESC;

Open in new window

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now