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

LVL 1
JustinWAsked:
Who is Participating?
 
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
 
NorieVBA ExpertCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.