Simple SQL Script Won't Run

February 21, 2010 12:31 PM

Looking for help with this SQL script running in Access 2007.

Situation:

My code is by no means elegant. What I am doing is populating a table with an "all possible scenarios" situation for use in a larger part of the project.

There are two main fields, advertising (AD) and sales force (SF) over 5 years. SF is measured in people and can be of size 0 to 3. However the *30000 converts the people to standard terms of money.

AD can be anything from 0 to 110000 each year.

The combined budget, that is years 1 through 5 of AD and SF can not be greater than 350000.

I do realize that using as many nested while statements is not the best way to program, but I couldn't find a for loop in SQL and I don't have the time to learn how to do this in VB, though if there is a good simple and tutorial of using VB to populate an Access DB in this manner, I'd appreciate that help as well.

Thanks in advance.

Code follows:



DECLARE @AD_YR_1 INT, @AD_YR_2 INT, @AD_YR_3 INT, @AD_YR_4 INT, @AD_YR_5 INT
DECLARE @SF_1 INT, @SF_2 INT, @SF_3 INT, @SF_4_INT, @SF_5 INT

DECLARE @BUDGET INT
SET @BUDGET = 350000

DECLARE @AD_YR_1 INT
SET @AD_YR_1 = 0
WHILE @AD_YR_1 <> 111000
BEGIN


SET @SF_1 = 0
WHILE @SF_1 <> 4
BEGIN

SET @AD_YR_2 = 0
WHILE @AD_YR_2 <> 111000
BEGIN


SET @SF_2 = 0
WHILE @SF_2 <> 4
BEGIN

SET @AD_YR_3 = 0
WHILE @AD_YR_3 <> 111000
BEGIN


SET @SF_3 = 0
WHILE @SF_3 <> 4
BEGIN

SET @AD_YR_4 = 0
WHILE @AD_YR_4 <> 111000
BEGIN


SET @SF_4 = 0
WHILE @SF_4 <> 4
BEGIN

SET @AD_YR_5 = 0
WHILE @AD_YR_5 <> 111000
BEGIN


SET @SF_5 = 0
WHILE @SF_5 <> 4
BEGIN

IF @AD_YR_1 + @AD_YR_2 + @AD_YR_3 + @AD_YR_4 + @AD_YR_5 + @SF_1*30000 + @SF_2*30000 + @SF_3*30000 + @SF_4*30000 + @SF_5*30000 <= @BUDGET
BEGIN

INSERT INTO ADS (AD_YEAR1, AD_YEAR2, AD_YEAR3, AD_YEAR4, AD_YEAR5, SF_YEAR1, SF_YEAR2, SF_YEAR3, SF_YEAR4, SF_YEAR5)
VALUES (@AD_YR_1, @AD_YR_2, @AD_YR_3, @AD_YR_4, @AD_YR_5, @SF_1*30000, @SF_2*30000, @SF_3*30000, @SF_4*30000, @SF_5*30000)

END




SET @SF_5 = @SF_5 + 1
END

SET @AD_YR_5 = @AD_YR_5 + 1000
END

SET @SF_4 = @SF_4 + 1
END

SET @AD_YR_4 = @AD_YR_4 + 1000
END

SET @SF_3 = @SF_3 + 1
END

SET @AD_YR_3 = @AD_YR_3 + 1000
END

SET @SF_2 = @SF_2 + 1
END

SET @AD_YR_2 = @AD_YR_2 + 1000
END

SET @SF_1 = @SF_1 + 1
END

SET @AD_YR_1 = @AD_YR_1 + 1000
END

posted by thebreaks to Computers & Internet (4 answers total)

This is much easier than what you are doing.

Create two tables, one for each dimension. Then do a Cartesian join (one without a predicate) to get all possible combinations of the two tables.

Your first table has three rows, values 0-3. Your second, values from 0-111000.

Search for my answer about an "artificial range" to make creating 111000 rows much easier.

In your case, you'll add the restriction (predicate) that "The combined budget, that is years 1 through 5 of AD and SF can not be greater than 350000."

Insert the result of the Cartesian into your "all possible" table. Done.
posted by orthogonality at 1:20 PM on February 21, 2010 [2 favorites]

Here's an alternative approach --- off the top of my head, not sure if it works:

Set up one table `ad`, with a single field `budget`, and records for every value from 0 to maximum.

Set up another table `sf` with a single field `people`, and records with values from 0 to 3.

This should produce all valid combinations:


INSERT INTO ads
SELECT * FROM
ad AS ad1, ad AS ad2, ad AS ad3, ad AS ad4, ad AS ad5,
sf AS sf1, sf AS sf2, sf AS sf3, sf AS sf4, sf AS sf5
WHERE
ad1.budget + ad2.budget + ad3.budget + ad4.budget + ad5.budget +
(sf1.people + sf2.people + sf3.people +sf4.people + sf5.people)*30000 < 111000

posted by Dr Dracator at 1:21 PM on February 21, 2010 [1 favorite]

(Note that this is still rater brute force: what you really want is a equation that expresses budget in terms of two variables, advertising and salesforce. But you asked about populating a table, so that is what I told you how to do.)
posted by orthogonality at 1:23 PM on February 21, 2010

Whoops, end of the last line should read < 350000.

Also, this is why AskMe should implement locking when people are on preview.
posted by Dr Dracator at 1:24 PM on February 21, 2010