Solved

Make SQL VBA Query available to Access Form

Posted on 2011-09-23
12
219 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:OnsiteSupport
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now