Solved

How to convert sql code to access query

Posted on 2016-11-09
8
54 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 17

Expert Comment

by:Pawan Kumar Khowal
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
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 17

Expert Comment

by:Pawan Kumar Khowal
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

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!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

13 Experts available now in Live!

Get 1:1 Help Now