Sorry, I was away for a bit, and got side tracked!
I have a tiered Access application running over a WAN, and performance has turned out
to be an issue. But to convert the Access queries into something that MySQL can
understand takes a while. I started converting them before, but ran into a sub
querie, and couldn't think of an alternate way to do it, so I gave up. The queries
are pretty hefty (by my standards anyway - since I don't really know SQL very well).
I'll paste one here so you can see what I'm working with. It'll take me a day or two
to get it to that point again. If you're still willing to help, I'll be happy to do
it and post it to the group.
Thanks!
Mark
PS. Here's the Access SQL querie that I have to convert...
SELECT CensusData.SiteKey, CensusData.Date, CensusData.Testing, LicensedBeds.SNF_Beds,
LicensedBeds.SCNF_Beds, LicensedBeds.SubAcute_Beds,
[CensusData]![Mix_SNF_Private]+[CensusData]![Mix_SNF_Medicare]+[CensusData]![Mix_SNF_Medicaid]+[CensusData]![Mix_SNF_Ins_Other]
AS Total_SNF,
[CensusData]![Mix_SCNF_Private]+[CensusData]![Mix_SCNF_Medicare]+[CensusData]![Mix_SCNF_Medicaid]+[CensusData]![Mix_SCNF_Ins_Other]
AS Total_SCNF,
[CensusData]![Mix_SubAcute_Private]+[CensusData]![Mix_SubAcute_Medicare]+[CensusData]![Mix_SubAcute_Medicaid]+[CensusData]![Mix_SubAcute_Ins_Other]
AS Total_SubAcute, IIf([SNF_Beds]=0,0,Int([Total_SNF]/[SNF_Beds]*10000)/100) AS
SNF_Per, IIf([SCNF_Beds]=0,0,Int([Total_SCNF]/[SCNF_Beds]*10000)/100) AS SNCF_Per,
IIf([SubAcute_Beds]=0,0,Int([Total_SubAcute]/[SubAcute_Beds]*10000)/100) AS
SubAcute_Per, [SNF_Beds]+[SCNF_Beds]+[SubAcute_Beds] AS Total_Beds,
IIf([SNF_Beds]=0,0,Int([Total_SNF]/[Total_Beds]*10000)/100) AS SNF_Per_Total,
IIf([SCNF_Beds]=0,0,Int([Total_SCNF]/[Total_Beds]*10000)/100) AS SNCF_Per_Total,
IIf([SubAcute_Beds]=0,0,Int([Total_SubAcute]/[Total_Beds]*10000)/100) AS
SubAcute_Per_Total, [Total_SNF]+[Total_SCNF]+[Total_SubAcute] AS Total_Census,
IIf([Total_Beds]=0,0,Int([Total_Census]/[Total_Beds]*10000)/100) AS Total_Per
FROM (SiteLookup INNER JOIN FilterMaxDataEntries ON SiteLookup.SiteKey =
FilterMaxDataEntries.SiteKey) INNER JOIN (LicensedBeds INNER JOIN CensusData ON
LicensedBeds.LicensedBedsKey = CensusData.LicensedBedsKey) ON
(FilterMaxDataEntries.SiteKey = CensusData.SiteKey) AND (FilterMaxDataEntries.Date =
CensusData.Date) AND (FilterMaxDataEntries.MaxOfTime_Stamp = CensusData.Time_Stamp)
WHERE (((CensusData.SiteKey)=[Forms]![Gen_OneFacility]![Site]) AND
((CensusData.Testing)=False) AND
(((([CensusData]![Date]>[Forms]![Gen_OneFacility]![StartDate] And
[CensusData]![Date]<[Forms]![Gen_OneFacility]![EndDate]) Or
([CensusData]![Date]=[Forms]![Gen_OneFacility]![StartDate] Or
[CensusData]![Date]=[Forms]![Gen_OneFacility]![EndDate])))<>False))
ORDER BY CensusData.Date;
>>> Ryan Wahle <[EMAIL PROTECTED]> 01/16/01 04:51PM >>>
What's your subquery and we can help you convert it to a join.
On 16 Jan 2001 15:59:48 -0500, Mark Marshall wrote:
> Hi all,
>
> Is there any kind of schedule that says when the next MySQL version will be
>available? I'd like to utilize MySQL here, but I can't really start to seriously
>work on it until sub-queries are possible.
>
> Thanks!
> Mark
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
