If-Then-Else or Case Statement (1 Viewer)

casaler · Aug 21, 2016

I have 2 tables, table 1 and table 2. I am trying to find the best coverage match from table 2 for the vehicle (VIN) on table 1 based upon the VINs information in table 1. I have a serious of logic steps in an If-Then-Else format, but I am not sure of the proper verbiage/syntax or how to apply in a query to generate a view of the results. Also, I would like to be able to loop the statement against the items in table 1. I have attached the tables and logic that I came up with.

Also, I am trying to fit/adjust it to the attached code.

pbaldy · Aug 21, 2016

In words, how would you describe the best coverage match?

casaler · Aug 21, 2016

the best match would the one were the parameters in table1 fit the logic, i.e., one of table 1s months or mileage are greater than table 2s months or mileage. In my example, the best match is coverage C because table 1s mileage is greater than table 2's coverage C mileage, but less than table 2's coverage D.

stopher · Aug 21, 2016

Following the logic on the first page of your pdf, I think you can just use a query:

Code:

SELECT TOP 1 tbl1.VIN, tbl1.Months, tbl1.Mileage, tbl2.VIN, tbl2.Coverage, tbl2.Months, tbl2.Mileage
FROM tbl1 INNER JOIN tbl2 ON tbl1.VIN = tbl2.VIN
WHERE tbl1.Months>tbl2.Months OR tbl1.Mileage>tbl2.Mileage
ORDER BY tbl2.Coverage DESC;

If you take the first lines of your logic:

Code:

If tbl1.MI > tbl2.CVG.D MI and tbl1.MN < tbl2. CVG.D MN
Then tbl2.CVG = D
 Else
If tbl1.MI < tbl2. CVG.D MI and tbl.MN > tbl2. CVG.D MN
Then tbl2.CVG = D
 Else
If tbl1.MI > tbl2. CVG.D MI and tbl.MN > tbl2. CVG.D MN
Then tbl2.CVG = D

This is the same as writing:

Code:

If tbl1.MI > tbl2.CVG.D MI [COLOR="Red"]OR[/COLOR] tbl1.MN > tbl2. CVG.D MN
Then tbl2.CVG = D

So I just created a query that returns all records that meet that criteria. In your example, with a join query, records A, B and C will all meet that criteria. But we are actually interested in the last one (as it happens alphabetically). Sort we do a reverse sort on the three records and just select the TOP 1. You can use this as a sub-query if you want to run it over many VINs.

I didn't really understand the other pages of your pdf so maybe I'm off course with this solution.

hth
Chris

casaler · Aug 21, 2016

First off, thanks for reviewing and simplifying my expression; I'll give it a try.

I used A, B and C as an example; some of the actual coverage names are PWRTRN, CAFUEL, BASIC, etc. and change with the VIN, repair, etc. Based upon your suggestion, it appears that I do not need a series of if-then-else statements.

Just so that I understand, your select query can be used to replace logic steps I wrote. It is also can be run using a standard query.

The other pages of code are currently used in another database that I have which calculates the mileage difference between a VIN. I thought that I would have to use the same approach for this problem and I included it so that the if-then-else code could be added.

stopher · Aug 21, 2016

casaler said:
I used A, B and C as an example; some of the actual coverage names are PWRTRN, CAFUEL, BASIC, etc. and change with the VIN, repair, etc. Based upon your suggestion, it appears that I do not need a series of if-then-else statements.

So rather than sort on Coverage (alphabetically), you could sort on mileage or months.

casaler said:
Just so that I understand, your select query can be used to replace logic steps I wrote. It is also can be run using a standard query.

If I've understood your logic correctly then yes.

casaler · Aug 21, 2016

I tried the suggested query against some actual data and did not get the desired OR " best" results. Attached is the database and a spreadsheet with the best results. when I ran your suggestion (qsPROGRAMMERS_SOLUTION), I got the correct results for 3 of the VINs. Based upon the results I am looking for, I believed an If-Then-Else statement was the right direction.

casaler · Aug 21, 2016

Here is a database sample and the desired results

stopher · Aug 22, 2016

As mentioned, the example I gave would need to be a subquery. Also you needed to sort on Months not on Coverage.

Create this query and name it qryGetMonthKey:

Code:

SELECT TABLE2.VIN, Max(TABLE2.MONTHS) AS MaxMonths
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.VIN = TABLE2.VIN
WHERE (((TABLE1.INSM)>[TABLE2].[MONTHS])) OR (((TABLE1.MILEAGE)>[TABLE2].[MILES]))
GROUP BY TABLE2.VIN;

The above query gives you the relevant month. So use it in a join to give you the rest of your data:

Code:

SELECT TABLE1.VIN, TABLE1.INSM, TABLE1.MILEAGE, TABLE2.CVG, TABLE2.MONTHS, TABLE2.MILES
FROM (TABLE1 INNER JOIN qryGetMonthKey ON TABLE1.VIN = qryGetMonthKey.VIN) INNER JOIN TABLE2 ON (qryGetMonthKey.MaxMonths = TABLE2.MONTHS) AND (qryGetMonthKey.VIN = TABLE2.VIN);

The last code in your spreadsheet does not seem right.

stopher · Aug 22, 2016

Here's your database with the added queries. Just run qryOutput.

Going back to your other method about using IF ELSE etc, do note that one the if statement reaches a true condition it does not check any other conditions. You can use this principle as follows. For simplicity suppose we are dealing with cases 1-5

If a>5 then
'do something for >5
ELSE IF a>4
'do something for >4 and <=5
ELSE IF a>3
'do something for >3 and <=4
etc...

The point is you don't have to test the bounds of the upper condition because if the upper condition has already been met then the program would not reach here anyway. Hope than makes sense.

hth
Chris

casaler · Aug 22, 2016

You choose MONTHS for the max data; does it matter? Would MILEAGE also work? I assume that it is one or the other. After seeing what you did, I get it. Thank you very much! This is going to be a big time saver for me!

stopher · Aug 22, 2016

casaler said:
You choose MONTHS for the max data; does it matter? Would MILEAGE also work? I assume that it is one or the other. After seeing what you did, I get it. Thank you very much! This is going to be a big time saver for me!

Yes you can choose max on mileage instead.

The main bit is the query qryGetMonthKey which filters all records less than the criteria for each VIN. But we just need a way to identify the highest record in each group. It can be the highest month or highest mileage. Once you have this "key" for each VIN you can join with the original data (table2 and/or table1) to pull all info.

If-Then-Else or Case Statement (1 Viewer)

casaler

Registered User.

Attachments

pbaldy

Wino Moderator

casaler

Registered User.

stopher

AWF VIP

casaler

Registered User.

stopher

AWF VIP

casaler

Registered User.

casaler

Registered User.

Attachments

stopher

AWF VIP

stopher

AWF VIP

Attachments

casaler

Registered User.

stopher

AWF VIP

Similar threads

Users who are viewing this thread