Solved

How to convert sql code to access query

Posted on 2016-11-09
8
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 46

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 29

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 29

Expert Comment

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

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 38

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

623 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