Solved

How to convert sql code to access query

Posted on 2016-11-09
8
65 Views
Last Modified: 2016-11-15
I have the following code,  is there a way to put this in a access query?

thanks


USE [CollectionSystem]
GO
/****** Object:  StoredProcedure [dbo].[Coll2014_GetWorkIDs]    Script Date: 11/09/2016 18:35:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Coll2014_GetWorkIDs]
	-- Add the parameters for the stored procedure here
	@PropID char(2)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
declare @WorkName nvarchar(100)
declare @WorkAddr nvarchar(100)
declare @WorkPhone nvarchar(100)

set @WorkName = (select demogseqno from demographicdefn where fieldprompt= 'Work Name' and PropertyId=@PropID)
set @WorkAddr = (select demogseqno from demographicdefn where fieldprompt= 'Work Addr' and PropertyId=@PropID)
set @WorkPhone = (Select demogseqno from .demographicdefn where fieldprompt  = 'Work' and PropertyId=@PropID)


select @Workname,@WorkAddr,@workphone

END

Open in new window

0
Comment
Question by:mgmhicks
8 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 41881612
Provide the sql script to create needed sql tables, and procedure with few test data records inserted into tables.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41881623
Try this in an Access query (paste into the SQL window).
select Top 1 (select demogseqno from demographicdefn where fieldprompt= 'Work Name' and PropertyId=[@PropID]) as [@Workname],
	(select demogseqno from demographicdefn where fieldprompt= 'Work Addr' and PropertyId=[@PropID]) as [@WorkAddr],
	(Select demogseqno from .demographicdefn where fieldprompt  = 'Work' and PropertyId=[@PropID]) as [@workphone]
From msysObjects

Open in new window

0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41881659
Try..I think aikimark got it right. Just appended few changes. Removed @ Variables and system table.<<nopts>>

CREATE PROCEDURE dbo.Coll2014_GetWorkIDs
(
  @PropID char(2)
)
AS 
select Top 1 
        (select demogseqno from demographicdefn where fieldprompt= 'Work Name' and PropertyId=[@PropID]) as [Workname],
	(select demogseqno from demographicdefn where fieldprompt= 'Work Addr' and PropertyId=[@PropID]) as [WorkAddr],
	(Select demogseqno from .demographicdefn where fieldprompt  = 'Work' and PropertyId=[@PropID]) as [workphone]
From demographicdefn

--

Open in new window


Hope it helps!!
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41881669
Ohh my bad. Just moved from T-SQL. :) @Author - Pls ignore my comment.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41882411
The code is running three separate queries.  I don't know why it would do that unless the table is designed to hold only a single field per row.  In that case, you would create a base query something like:
select PropertyId.fieldprompt,demogseqno from demographicdefn where fieldprompt In  ('Work Name'. 'Work Addr', 'Work') and PropertyId=Forms!someform!PropID

Then use that query in a CrossTab query.  Make sure you define the PropID parameter specifically since Crosstabs REQUIRE explicit parameter definition.

So, the first query returns up to three rows - it is possible depending on your business rules for one of the rows to be missing so you may need to account for that.  The crosstab pivots the three rows into a single record.  I assumed that the PropID argument would be coming from a control on a form.  If that is not the case, you will need to modify that part of the SQL.
0
 

Author Closing Comment

by:mgmhicks
ID: 41886612
that's exactly what I ended up doing.  Worked great!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41888216
This is an unusual table design.  I have only used it a couple of times in all my 45 years of database design.  There is a high level of overhead with it but if you are working with complex products such as insurance policies that are in constant need of "new" data elements each time a company offers a new product, it works very well.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 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

12 Experts available now in Live!

Get 1:1 Help Now