Solved

How to convert sql code to access query

Posted on 2016-11-09
8
88 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 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 28

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 28

Expert Comment

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

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 37

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
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…

751 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