Stored procedure to fill in data (1 Viewer)

CedarTree

Registered User.
Local time
Today, 09:56
Joined
Mar 2, 2018
Messages
404
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.
 

Mark_

Longboard on the internet
Local time
Today, 06:56
Joined
Sep 12, 2017
Messages
2,111
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.
 

CedarTree

Registered User.
Local time
Today, 09:56
Joined
Mar 2, 2018
Messages
404
The data structure is what it is per client requirements.
 

Mark_

Longboard on the internet
Local time
Today, 06:56
Joined
Sep 12, 2017
Messages
2,111
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.
 

CedarTree

Registered User.
Local time
Today, 09:56
Joined
Mar 2, 2018
Messages
404
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:56
Joined
Jan 20, 2009
Messages
12,849
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.

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.
 

CedarTree

Registered User.
Local time
Today, 09:56
Joined
Mar 2, 2018
Messages
404
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?
 

Mark_

Longboard on the internet
Local time
Today, 06:56
Joined
Sep 12, 2017
Messages
2,111
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.
 

CedarTree

Registered User.
Local time
Today, 09:56
Joined
Mar 2, 2018
Messages
404
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:

CedarTree

Registered User.
Local time
Today, 09:56
Joined
Mar 2, 2018
Messages
404
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

Registered User.
Local time
Today, 09:56
Joined
Mar 2, 2018
Messages
404
Since I shifted gears with my other question above, should I post that as a new thread?
 

Mark_

Longboard on the internet
Local time
Today, 06:56
Joined
Sep 12, 2017
Messages
2,111
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.
 

CedarTree

Registered User.
Local time
Today, 09:56
Joined
Mar 2, 2018
Messages
404
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.
 

Vassago

Former Staff Turned AWF Retiree
Local time
Today, 09:56
Joined
Dec 26, 2002
Messages
4,751
Would it really be a one-time fix? Would new data not have this issue?
 

Users who are viewing this thread

Top Bottom