Solved

Make SQL VBA Query available to Access Form

Posted on 2011-09-23
12
224 Views
Last Modified: 2012-05-12
I need to take a SQL query with a load of case statements and convert it to Access.
Since CASE is not available in Access Queries, I'm guessing I need to write it in VBA?

Can someone tell me how to pass the query results to a Form?

Thanks
0
Comment
Question by:OnsiteSupport
  • 6
  • 5
12 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36588107
Instead of CASE, you would use IIf or Switch.

What is the original SQL statement?

And for a primer on Switch: http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36588158
Going sideways as it were
<I need to take a SQL query with a load of case statements and convert it to Access.>
Why?
Access does passthrough queries.
If you can make your query into a View, Access can use Views like linked tables.

Maybe none of that is applicable--but maybe those capabilities were unknown unknowns to you.
0
 

Author Comment

by:OnsiteSupport
ID: 36588558
Here is the code.  When this is run in MySQL, it times out.  I'm guessing I have something that's bogging down the server.  My next attempt was to export the individual tables to Access and massage the data there.
SELECT MAX(CASE WHEN A.field_id=62 THEN value END) AS Approved,
X.Register_Date,X.Transaction_ID,X.ConfirmNum,C.EventID, C.title as Event,C.DTStart AS Event_Date, month(C.DTStart) As Event_Month,Substring(C.Topmsg,4,40) as Session, B.fee, B.paid_amount, 
CASE B.status 
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS Paid,
B.payment_method,
X.Type AS RegistrationType,
A.User_Id AS UserID,
Concat("http://www.girlscoutsjs.org/administrator/index2.php?option=com_dtregister&task=edit&controller=user&cid%5b%5d=", A.User_Id) AS HYPERLINK,
MAX(CASE WHEN A.field_id=2 THEN value END) AS firstname,
MAX(CASE WHEN A.field_id=3 THEN value END) AS lastname,
MAX(CASE WHEN A.field_id=4 THEN value END) AS address,
MAX(CASE WHEN A.field_id=5 THEN value END) AS address2,
MAX(CASE WHEN A.field_id=6 THEN value END) AS city,
CASE WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 1 THEN 'Alabama'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 2 THEN 'Alaska' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 3 THEN 'Arizona' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 4 THEN 'Arkansas' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 5 THEN 'California' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 6 THEN 'Colorado' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 7 THEN 'Connecticut' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 8 THEN 'DC' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 9 THEN 'FL' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 10 THEN 'GA' 
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 11 THEN 'HI'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 12 THEN 'ID'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 30 THEN 'NJ'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 32 THEN 'NY'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 38 THEN 'PA'
ELSE MAX(CASE WHEN A.field_id=7 THEN value END) END as State,
MAX(CASE WHEN A.field_id=8 THEN value END) AS zip,
MAX(CASE WHEN A.field_id=10 THEN value END) AS email,
MAX(CASE WHEN A.field_id=11 THEN value END) AS phone,
MAX(CASE WHEN A.field_id=20 THEN value END) AS cellphone,
MAX(CASE WHEN A.field_id=21 THEN value END) AS accomodations,
MAX(CASE WHEN A.field_id=22 THEN value END) AS girlemail,
MAX(CASE WHEN A.field_id=23 THEN value END) AS girlfirstname,
MAX(CASE WHEN A.field_id=24 THEN value END) AS girllastname,
MAX(CASE WHEN A.field_id=26 THEN value END) AS Program_Notes,

MAX(CASE WHEN A.field_id=63 THEN value END) AS Spook_Adults,
MAX(CASE WHEN A.field_id=64 THEN value END) AS Spook_Child,
MAX(CASE WHEN A.field_id=65 THEN value END) AS Spook_AdultsFREE,
MAX(CASE WHEN A.field_id=66 THEN value END) AS Spook_ChildFREE,

CASE  
WHEN MAX(CASE WHEN A.field_id=67 THEN value END) > 0 THEN 'Adult_Pay_5'
WHEN MAX(CASE WHEN A.field_id=68 THEN value END) > 0 THEN 'Adult_Pay_6'
WHEN MAX(CASE WHEN A.field_id=69 THEN value END) > 0 THEN 'Adult_Pay_10'
WHEN MAX(CASE WHEN A.field_id=70 THEN value END) > 0 THEN 'Adult_Pay_25'
WHEN MAX(CASE WHEN A.field_id=71 THEN value END) > 0 THEN 'Adult_Pay_2'
WHEN MAX(CASE WHEN A.field_id=72 THEN value END) > 0 THEN 'Adult_Pay_15'
WHEN MAX(CASE WHEN A.field_id=73 THEN value END) > 0 THEN 'Adult_Pay_8'
END AS Adult_Pay_Code,

CASE  
WHEN MAX(CASE WHEN A.field_id=67 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=67 THEN value END)
WHEN MAX(CASE WHEN A.field_id=68 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=68 THEN value END)
WHEN MAX(CASE WHEN A.field_id=69 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=69 THEN value END)
WHEN MAX(CASE WHEN A.field_id=70 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=70 THEN value END)
WHEN MAX(CASE WHEN A.field_id=71 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=71 THEN value END)
WHEN MAX(CASE WHEN A.field_id=72 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=72 THEN value END)
WHEN MAX(CASE WHEN A.field_id=73 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=73 THEN value END)
END AS Adult_Pay_Qty,

CASE WHEN MAX(CASE WHEN A.field_id=13 THEN value END) = 1 THEN 'No'
WHEN MAX(CASE WHEN A.field_id=13 THEN value END) = 2 THEN 'Yes' END as Add_Patch,

CASE WHEN MAX(CASE WHEN A.field_id=18 THEN value END) = 1 THEN 'Shoreline'
WHEN MAX(CASE WHEN A.field_id=18 THEN value END) = 2 THEN 'Colonial'
WHEN MAX(CASE WHEN A.field_id=18 THEN value END) = 3 THEN 'Sandy Hook'
WHEN MAX(CASE WHEN A.field_id=18 THEN value END) = 4 THEN 'Sunset'
WHEN MAX(CASE WHEN A.field_id=18 THEN value END) = 5 THEN 'Shore Pines' END as Region,

MAX(CASE WHEN A.field_id=16 THEN CONCAT("T",value) END) AS Troop,

CASE WHEN MAX(CASE WHEN A.field_id=17 THEN value END) = 1 THEN 'Daisy'
WHEN MAX(CASE WHEN A.field_id=17 THEN value END) = 2 THEN 'Brownie'
WHEN MAX(CASE WHEN A.field_id=17 THEN value END) = 3 THEN 'Junior'
WHEN MAX(CASE WHEN A.field_id=17 THEN value END) = 4 THEN 'Cadette'
WHEN MAX(CASE WHEN A.field_id=17 THEN value END) = 5 THEN 'Senior'
WHEN MAX(CASE WHEN A.field_id=17 THEN value END) = 6 THEN 'Ambassador'
WHEN MAX(CASE WHEN A.field_id=17 THEN value END) = 7 THEN 'Adult' END as Level,

X.Memtot AS GirlQTY,

CASE 
WHEN MAX(CASE WHEN A.field_id=39 THEN value END) = 0 THEN 'EBI_50PER'
WHEN MAX(CASE WHEN A.field_id=44 THEN value END) = 0 THEN 'EBI_750OFF'
WHEN MAX(CASE WHEN A.field_id=45 THEN value END) = 0 THEN 'EBI_600OFF'
WHEN MAX(CASE WHEN A.field_id=47 THEN value END) = 0 THEN 'EBI_400OFF' 
WHEN MAX(CASE WHEN A.field_id=51 THEN value END) = 0 THEN 'EBI_500OFF'
WHEN MAX(CASE WHEN A.field_id=53 THEN value END) = 0 THEN 'EBI_450OFF'
WHEN MAX(CASE WHEN A.field_id=55 THEN value END) = 0 THEN 'EBI_425OFF'
WHEN MAX(CASE WHEN A.field_id=60 THEN value END) = 0 THEN 'EBI_350OFF' 
WHEN MAX(CASE WHEN A.field_id=41 THEN value END) > 0 THEN 'EBG_350OFF' 
WHEN MAX(CASE WHEN A.field_id=43 THEN value END) > 0 THEN 'EBG_750OFF'
WHEN MAX(CASE WHEN A.field_id=46 THEN value END) > 0 THEN 'EBG_600OFF'
WHEN MAX(CASE WHEN A.field_id=48 THEN value END) > 0 THEN 'EBG_300OFF'
WHEN MAX(CASE WHEN A.field_id=50 THEN value END) > 0 THEN 'EBG_400OFF'
WHEN MAX(CASE WHEN A.field_id=52 THEN value END) > 0 THEN 'EBG_500OFF'
WHEN MAX(CASE WHEN A.field_id=54 THEN value END) > 0 THEN 'EBG_450OFF'
WHEN MAX(CASE WHEN A.field_id=56 THEN value END) > 0 THEN 'EBG_425OFF' 
END AS EBDiscountCode,

CASE 
WHEN MAX(CASE WHEN A.field_id=41 THEN value END) > 0 THEN MAX(CASE WHEN A.field_id=41 THEN value END) 
WHEN MAX(CASE WHEN A.field_id=43 THEN value END) > 0 THEN MAX(CASE WHEN A.field_id=43 THEN value END)
WHEN MAX(CASE WHEN A.field_id=46 THEN value END) > 0 THEN MAX(CASE WHEN A.field_id=46 THEN value END)
WHEN MAX(CASE WHEN A.field_id=48 THEN value END) > 0  THEN MAX(CASE WHEN A.field_id=48 THEN value END) 
WHEN MAX(CASE WHEN A.field_id=50 THEN value END) > 0  THEN MAX(CASE WHEN A.field_id=50 THEN value END)
WHEN MAX(CASE WHEN A.field_id=52 THEN value END) > 0 THEN MAX(CASE WHEN A.field_id=52 THEN value END)
WHEN MAX(CASE WHEN A.field_id=54 THEN value END) > 0 THEN MAX(CASE WHEN A.field_id=54 THEN value END)
WHEN MAX(CASE WHEN A.field_id=56 THEN value END) > 0 THEN MAX(CASE WHEN A.field_id=56 THEN value END) 
WHEN MAX(CASE WHEN A.field_id=39 THEN value END) = 0 THEN 1
WHEN MAX(CASE WHEN A.field_id=44 THEN value END) = 0 THEN 1
WHEN MAX(CASE WHEN A.field_id=45 THEN value END) = 0 THEN 1
WHEN MAX(CASE WHEN A.field_id=47 THEN value END) = 0 THEN 1
WHEN MAX(CASE WHEN A.field_id=49 THEN value END) = 0 THEN 1
WHEN MAX(CASE WHEN A.field_id=51 THEN value END) = 0 THEN 1
WHEN MAX(CASE WHEN A.field_id=53 THEN value END) = 0 THEN 1
WHEN MAX(CASE WHEN A.field_id=55 THEN value END) = 0 THEN 1
WHEN MAX(CASE WHEN A.field_id=60 THEN value END) = 0 THEN 1
END AS EBQty,

CASE
WHEN MAX(CASE WHEN A.field_id=57 THEN value END) > 0 THEN 'Adult_Group_600OFF'
WHEN MAX(CASE WHEN A.field_id=58 THEN value END) > 0 THEN 'Adult_Group_1000OFF'
WHEN MAX(CASE WHEN A.field_id=59 THEN value END) > 0 THEN 'Adult_Group_700OFF'
END AS 'AdultGroupCode',

CASE
WHEN MAX(CASE WHEN A.field_id=57 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=57 THEN value END)
WHEN MAX(CASE WHEN A.field_id=58 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=58 THEN value END)
WHEN MAX(CASE WHEN A.field_id=59 THEN value END) > 0 
THEN MAX(CASE WHEN A.field_id=59 THEN value END)
END AS 'AdultGroupQty',




/*
TShirts
*/


CASE WHEN MAX(CASE WHEN A.field_id=61 THEN value END) = 1 THEN 'None'
WHEN MAX(CASE WHEN A.field_id=61 THEN value END) = 2 THEN 'Sm'
WHEN MAX(CASE WHEN A.field_id=61 THEN value END) = 3 THEN 'Med'
WHEN MAX(CASE WHEN A.field_id=61 THEN value END) = 4 THEN 'Lg'
WHEN MAX(CASE WHEN A.field_id=61 THEN value END) = 5 THEN 'XL'
WHEN MAX(CASE WHEN A.field_id=61 THEN value END) = 6 THEN '2XL'
WHEN MAX(CASE WHEN A.field_id=61 THEN value END) = 7 THEN '3XL' END as ShirtSize

FROM `jos_dtregister_user` AS X 
INNER JOIN ( SELECT * FROM jos_dtregister_user_field_values)AS A ON X.userId = A.user_id
INNER JOIN ( SELECT * FROM jos_dtregister_fee) AS B ON X.userID=B.user_id
INNER JOIN ( SELECT * FROM jos_dtregister_group_event) AS C on X.eventId=C.slabId
INNER JOIN ( SELECT * FROM jos_dtregister_fields) AS D ON D.id=A.field_id
GROUP By X.userId
Order By X.Register_Date DESC

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:OnsiteSupport
ID: 36588610
Also, the above code is killing the server.  Anyone have some ideas on what is "wrong" with the query?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36588766
I'm not a MySQL guy.
You should maybe have had MySQL as the primary zone
The syntax is past me but...
CASE WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 1 THEN 'Alabama'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 2 THEN 'Alaska'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 3 THEN 'Arizona'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 4 THEN 'Arkansas'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 5 THEN 'California'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 6 THEN 'Colorado'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 7 THEN 'Connecticut'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 8 THEN 'DC'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 9 THEN 'FL'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 10 THEN 'GA'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 11 THEN 'HI'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 12 THEN 'ID'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 30 THEN 'NJ'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 32 THEN 'NY'
WHEN MAX(CASE WHEN A.field_id=7 THEN value END) = 38 THEN 'PA'
ELSE MAX(CASE WHEN A.field_id=7 THEN value END) END as State,

This stuff sure looks like you should have a table with StateID and StateName and just an inner join, and not all this case stuff.
Ditto ShirtSizes, Troops and Regions

A.field_id=73
Really, you have a table with at least 73 fields?
Maybe--or maybe your data isn't well normalized yet
http://www.phlonx.com/resources/nf3/

Poor data design is the death of many an app
0
 

Author Comment

by:OnsiteSupport
ID: 36589358
Nick-
This is written as part of a Joomla plug-in and I'm stuck with this horrible table structure. :(
I will start by chopping out the conversion case statements and we'll see if it makes a difference.

I would really like not to have to rewrite the code in Access.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36589435
<This stuff sure looks like you should have a table with StateID and StateName and just an inner join, and not all this case stuff.
Ditto ShirtSizes, Troops and Regions>

You could build local tables in Access for the stuff that makes sense (states, Shirtsizes, troops, regions etc) , and do a linked table to this Joomla monster.
You may then be able to build a saner and much simpler query in Access
0
 

Author Comment

by:OnsiteSupport
ID: 36589618
I'm wondering....what would be the equivalent query in Access...not VBA....to the following:

MAX(CASE WHEN A.field_id=2 THEN value END) AS firstname

Can a Switch statement do the same?
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36590017
A two case equivalent is IIF
IIF(SomeStatementThatWillEvaluateAsABoolean,ValueToUseIfTrue,ValueToUseIfFalse)
Switch is also available, but not something I have used

I am not a MySQL guy.
The Bold parts of your syntax I don't understand
MAX(CASE WHEN A.field_id=2 THEN value END) AS firstname
0
 

Author Comment

by:OnsiteSupport
ID: 36590065
Why not? LMAO....now you know how I feel :)

"value" is the name of a field in Table A
Not sure why they used MAX

Got this code from a support forum and it seemed to work (until it started crashing MySQL).  Utilization gets up to 100 percent.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36590142
Can you post which site?  Maybe I can reverse engineer it.  Try a sliver of the query to see if that MAX is doing anything.  A field named 'value' :(
0
 

Author Comment

by:OnsiteSupport
ID: 36906110
Nick-
There are problems with the application that go beyond poor naming conventions.  I've gone so far as to download the tables to access and report from there.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question