How to convert sql code to access query

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

mgmhicksAsked:
Who is Participating?
 
PatHartmanCommented:
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
 
hnasrCommented:
Provide the sql script to create needed sql tables, and procedure with few test data records inserted into tables.
0
 
aikimarkCommented:
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
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.

 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Ohh my bad. Just moved from T-SQL. :) @Author - Pls ignore my comment.
0
 
mgmhicksAuthor Commented:
that's exactly what I ended up doing.  Worked great!
0
 
PatHartmanCommented:
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
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.