Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-10-2019, 11:02 AM   #1
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Stored procedure to fill in data

I am VERY new to stored procedures, so I'll admit I'm being a little lazy here (I just bought an Amazon book on Sprocs that I'm still expecting). But I also want to gain wisdom around programming philosophy (I'm very comfy with VBA). What I want to do is write a Sproc that does what I outline below ... Anyone have 5 mins to spare to outline the programming?

Table A looks like this
Person Year Company
John 2001 <Null>
John 2000 <Null>
John 1999 ABC
John 1998 <Null>
John 1997 XYZ

What I want to do is loop through the table, starting with the lowest year (1997 in this example). In 1997, we know John was with Company XYZ. Then move to 1998, which is blank. Since XYZ was his most recent company, I want to fill in XYZ in 1998. Then move to 1999, where we know he was with ABC. In 2000, we will fill in ABC since we assume he was the same company. And so on.

Thoughts? Sorry to ask so much.

CedarTree is online now   Reply With Quote
Old 05-10-2019, 11:24 AM   #2
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Stored procedure to fill in data

Why do you have Name/Year/Company like this?

To me, this says "Only add a record when the they change employers" and don't try to have "Per year". Then you can grab the highest one by year to find out who they currently work for and your not saving redundant information.
Mark_ is offline   Reply With Quote
Old 05-10-2019, 11:29 AM   #3
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Stored procedure to fill in data

The data structure is what it is per client requirements.

CedarTree is online now   Reply With Quote
Old 05-10-2019, 12:17 PM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Stored procedure to fill in data

Quote:
Originally Posted by CedarTree View Post
The data structure is what it is per client requirements.
My condolences.

And I am guessing you are trying to automate updating of data that isn't coming in how you need it? Important question for design, do you need to also verify per year which company they were at? And instead of NULL any instances where you would have a blank?

Just trying to rule out other issues you may run into.
Mark_ is offline   Reply With Quote
Old 05-10-2019, 12:33 PM   #5
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Stored procedure to fill in data

If I'm understanding correctly, I believe the only requirement is to work "up" through the history and fill in blanks with the last known company.

P.S. I'm wondering too if people have successfully stored the Sproc coding in Access and dynamically create/run the Sproc right from Access. I think that would be cool versus using sql studio.
CedarTree is online now   Reply With Quote
Old 05-10-2019, 03:43 PM   #6
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,579
Thanks: 88
Thanked 1,488 Times in 1,404 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Stored procedure to fill in data

Quote:
Originally Posted by CedarTree View Post
What I want to do is loop through the table, starting with the lowest year (1997 in this example).
A loop is not required. It can be done with an Update version of the Select query that should be used to return the required structure from the underlying data instead of the proposed misguided strategy dictated by the client.

It involves selecting the value from the record with the Maximum Year that is less than the record with the Null. Vastly more efficient than a loop.

Quote:
Originally Posted by CedarTree View Post
P.S. I'm wondering too if people have successfully stored the Sproc coding in Access and dynamically create/run the Sproc right from Access. I think that would be cool versus using sql studio.
You can create the stored procedure using a Pass Through query.
Galaxiom is offline   Reply With Quote
Old 05-10-2019, 06:50 PM   #7
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Stored procedure to fill in data

Could you give a bit more guidance re: the query method? Would you use a simple Select syntax or some kind of minimum/maximum function?

CedarTree is online now   Reply With Quote
Old 05-10-2019, 08:02 PM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Stored procedure to fill in data

What you are looking for is the latest record that doesn't have NULL for company. Effectively Select top 1 from table where company is not null order by date. This will give you the last time a given company has been used. Then you would update table set company = last company where date > last date and company IS NULL.

This is within a query that returns every person that you want to deal with, so it is in effect a sub-query. It gets a little more complicated as you'd have to make sure you hit each person who has at least ONE null, so you may want to run it a couple times.

To me, I'd find it far easier to talk to the customer about simply removing all redundant entries and all entries that are null. This mean you'd get rid of three of your sample records and only keep

John 1999 ABC
John 1997 XYZ

You KNOW that John was still with XYZ in 1998 and you KNOW John is with ABC from 1999 on. Only record when you have changes rather than pretend this is a spreadsheet.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
CedarTree (05-11-2019)
Old 05-11-2019, 06:20 AM   #9
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Stored procedure to fill in data

Okay let me shift gears here a bit. I want to learn how to run t-sql from Acccess. I can run basic queries (updates, inserts, etc.) using a pass-through. But I want to run something more like this (silly test)...

Code:
USE TestDB;
GO
DECLARE @PID as INT;
DECLARE @Row as CURSOR;
SET @Row = CURSOR FOR
SELECT PID FROM MyTable
OPEN @Row;
FETCH NEXT FROM @Row INTO @PID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PID < 1800
    UPDATE MyTable SET Test = 1 WHERE PID = @PID;
FETCH NEXT FROM @Row INTO @PID;
END
CLOSE @Row;
DEALLOCATE @Row;
When I run this in SSMS, it runs fine. When I run it as a pass-through from Access, nothing changes. So is that Access can pass-through something like this? Or my pass through coding is wrong? Here's my P-T coding:

Code:
Sub subExecuteSQL(pSQL As String)
On Error Resume Next

    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = gsConnection
    qdf.sql = pSQL
    qdf.ReturnsRecords = False
    qdf.Execute
    qdf.Close
    Set qdf = Nothing

End Sub
P.S. If I take the sql statement from Access and copy/paste into SSMS, it runs perfectly. So the sql syntax seems to be fine.

Last edited by CedarTree; 05-11-2019 at 06:35 AM.
CedarTree is online now   Reply With Quote
Old 05-12-2019, 04:22 PM   #10
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Stored procedure to fill in data

And apologies... I guess my brain wasn't running at full power earlier. The reason those other rows have a blank "Company" is that those rows contain other information (like pay, etc.) so we need to keep the rows... but we want to fill in the Company for intervening years so we can run some queries off that.
CedarTree is online now   Reply With Quote
Old 05-13-2019, 07:28 AM   #11
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Stored procedure to fill in data

Since I shifted gears with my other question above, should I post that as a new thread?
CedarTree is online now   Reply With Quote
Old 05-14-2019, 08:54 AM   #12
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Stored procedure to fill in data

Guys... any help here please?
CedarTree is online now   Reply With Quote
Old 05-14-2019, 02:54 PM   #13
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Stored procedure to fill in data

From how you posted your original question I don't think your approach is the best. This doesn't sound like something you want to fix with a stored procedure on a server. This sounds like a data validation issue.

Were it me, i'd give a user a list of all entries for "John" in descending order. I'd also have the same query in ascending order so I can find the closest match for each "Null" and offer it to the users as a "Best fit". I'd have a user review and update the records, especially if these are some kind of accumulator records.

I'd work on a stored procedure that can create them with your data that gets fired off as part of an end of year process, but I'd not do this as a stored procedure for a one time data fix.
Mark_ is offline   Reply With Quote
Old 05-14-2019, 03:36 PM   #14
CedarTree
Newly Registered User
Diamond Supporter
 
Join Date: Mar 2018
Posts: 150
Thanks: 44
Thanked 1 Time in 1 Post
CedarTree is on a distinguished road
Re: Stored procedure to fill in data

The data needs to be stored the way I laid it out b/c of other fields. But after the fact, I want to run a procedure to clean up the data. But I'd like to do it by sending a T-SQL as a outlined above since it should run faster on the server rather than manipulating the data in Access.
CedarTree is online now   Reply With Quote
Old 05-20-2019, 08:01 AM   #15
Vassago
Administrator
 
Join Date: Dec 2002
Location: Jacksonville, FL
Posts: 4,336
Thanks: 81
Thanked 90 Times in 85 Posts
Vassago has a spectacular aura about Vassago has a spectacular aura about Vassago has a spectacular aura about
Re: Stored procedure to fill in data

Would it really be a one-time fix? Would new data not have this issue?

__________________
Vassago
Vassago is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieve column names and column data from MS-SQL Stored Procedure ino_mart Modules & VBA 2 12-06-2011 12:14 AM
Problem with loading data into a SubForm (datasheet) from a stored Procedure azizsohail Modules & VBA 3 07-13-2011 03:34 AM
Is it possible to create a stored procedure that process data from different DBs accesser2003 General 6 07-30-2008 07:51 PM
Access 2003 data project sql back end stored procedure parameter trouble robbycraig Forms 0 07-19-2005 04:50 AM
[SOLVED] Stored Procedure and Data Access Page Victoria Bennet General 0 02-12-2003 09:03 AM




All times are GMT -8. The time now is 09:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World