Conditional Grouping Calculation

[sportdlockport]

I'm trying to perform a calculation for specific criteria within a define group of data.

Example:
Field1 is the data for gouping.

If 78480 and 78478 in Field2 has the same value in Field1, then enter "Bundled" in Field3 on the same record line as 78480 in Field2. If 78480 and 78478 in Field2 does not have the same value in Field2, then enter "Separate" in Field3 on the same record line as 78480 in Field2.


Field1 Field2 Field3
1580 78480 Bundled
1580 78478
1580 93015
1580 78465
1582 78480 Separate
1583 78478
1583 80061

 

[sportdlockport]

Example correction:

Example:
Field1 is the data for gouping.

If 78480 and 78478 in Field2 has the same value in Field1, then enter "Bundled" in Field3 on the same record line as 78480 in Field2. If 78480 and 78478 in Field2 does not have the same value in Field1, then enter "Separate" in Field3 on the same record line as 78480 in Field2.

 

[PHV]

You may try something like this:
SELECT A.Field1, A.Field2, IIf(A.Field2=78480, IIf(IsNull(B.Field1), 'Separate', 'Bundled'), '') AS Field3
FROM yourTable AS A LEFT JOIN yourTable AS B
ON A.Field1 = B.Field1 AND A.Field2 = B.Field2 + 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sportdlockport]

All fields are within 1 table. Joins are not included.

 

[PHV]

Have a look at SELF join ...

 

[sportdlockport]

I’ve attempted to incorporate the IF statement provided. The results are close but not exactly what I’m looking for.

For Carrier 1582 and 1583, I was expecting to see “Separate” as the Code in place of the “Bundled” code since CPT 78478 is not grouped with CPT 78480.

Thanks for your assistance.



Contract CarrierCPT Code
10005 1580 78480 Bundled
10005 1580 78478
10005 1580 93015
10005 1580 78465

10006 1582 78480 Bundled
10006 1582 93015

10005 1583 78480 Bundled
10005 1583 93015
10005 1583 78465

10006 1584 78478
10006 1584 93015
10006 1584 78480 Bundled




SELECT Book1.Contract, Book1.Carrier, Book1.CPT, IIf([Book1]![CPT]="78480",IIf(IsNull([Book1_1]![Carrier]),"Separate","Bundled"),"") AS Code
FROM Book1 LEFT JOIN Book1 AS Book1_1 ON (Book1.CPT = Book1_1.CPT) AND (Book1.Carrier = Book1_1.Carrier) AND (Book1.Contract = Book1_1.Contract)
WHERE (((Book1_1.Contract)=10005 Or (Book1_1.Contract)=10006));

 

[PHV]

I'd suggest this instead:
SELECT A.Contract, A.Carrier, A.CPT, IIf(A.CPT="78480",IIf(IsNull(B.Carrier),"Separate","Bundled"),"") AS Code
FROM Book1 AS A LEFT JOIN Book1 AS B
ON A.Contract = B.Contract AND A.Carrier = B.Carrier AND Int(A.CPT) = Int(B.CPT)+2
WHERE A.Contract In (10005,10006);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sportdlockport]

Error message received:

Microsoft Access can't represent the join expression INT(Book1.CPT)=(Book1_1.CPT)+2 in Design View. It would be benifitial if the solution provided will allow me to utilize Microsoft Access functionality instead of manipulating code. I will use the solution to this issue for many other grouping calculations.


When I generate the query, the results below are provided. The correct code assignment yet not seeing all records from Book1 table.

ContractCarrier CPT Code
10005 1580 78480 Bundled
10006 1584 78480 Bundled

 

[PHV]

You really used a LEFT join ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sportdlockport]

yes, i did. Same as your sql statement.

 

[PHV]

With my ac2003, I have a table Book1:[tt]
Contract Carrier CPT
10005 1580 78480
10005 1580 78478
10005 1580 93015
10005 1580 78465
10006 1582 78480
10006 1582 93015
10005 1583 78480
10005 1583 93015
10005 1583 78465
10006 1584 78478
10006 1584 93015
10006 1584 78480[/tt]
And this query:
SELECT A.Contract, A.Carrier, A.CPT, IIf(A.CPT="78480",IIf(IsNull(B.Carrier),"Separate","Bundled"),"") AS Code
FROM Book1 AS A LEFT JOIN Book1 AS B
ON A.Contract = B.Contract AND A.Carrier = B.Carrier AND Int(A.CPT) = Int(B.CPT)+2
WHERE A.Contract In (10005,10006);
My result:[tt]
Contract Carrier CPT Code
10005 1580 78480 Bundled
10005 1580 78478
10005 1580 93015
10005 1580 78465
10006 1582 78480 Separate
10006 1582 93015
10005 1583 78480 Separate
10005 1583 93015
10005 1583 78465
10006 1584 78478
10006 1584 93015
10006 1584 78480 Bundled[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sportdlockport]

I really appreciate your help. I pasted your statement again and did get the correct results as you are showing. I must have missed something last time. However, I can't access Design View. I get the same error message above. I'm using Microsoft Access 2000 (9.0.6926 SP-3)I will need to build several of these code calculations and I'm not efficient at writing code without easy functionality.

 

[JonFer]

Make a query that selects everything from your table plus 2 fields:

CPT_INT: Int(CPT)
CPT2_INT: Int(CPT)+2

Then do the self-join using that query (A.CPT_INT=B.CPT2_INT) and you'll be able to see the design window.

John

http://www.interautomateconsulting.com

 

[sportdlockport]

Error message received when modified the table as suggested.

Microsoft Access can't represent the join expression A.CPT_INT = B.CPT_INT2 in Design View.


Code used:
SELECT A.CONTRACT, A.CLAIM, A.CPT, IIf(A.CPT="78480",IIf(IsNull(B.CARRIER),"Separate","Bundled"),"") AS Code, Int([A!CPT]) AS CPT_INT, Int([B!CPT])+2 AS CPT_INT2
FROM Book1 AS A LEFT JOIN Book1 AS B ON (A.CLAIM = B.CARRIER) AND (A.CONTRACT = B.CONTRACT) AND (A.CPT_INT=B.CPT_INT2)
WHERE (((A.CONTRACT) In (10005,10006)));

 

[lespaul]

Microsoft Access can't represent the join expression A.CPT_INT = B.CPT_INT2 in Design View.

That doesn't mean the query won't work, it just means that you HAVE to write something like this in the SQL view of the query.

solution provided will allow me to utilize Microsoft Access functionality instead of manipulating code.

writing queries isn't manipulating code. If you are going to be consistently doing queries, you're better off learning the SQL syntax and writing them your self instead of using the query design view.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

instead of using the query design view
I agree totally with you Leslie.
I hate the way the query wizard mess the SQL code ...

 

[sportdlockport]

I respectfully disagree. This is a Microsoft Access Query Forum and should be utilized to focus on the software's functionality. PHV, I appreciate that your were able to get the results I requested but this solution is not feasible if I were to build many scenarios for grouping calculations. The calculated join would not work for other scenarios, only this one. A way to get the same results; yet be able to easily build or modify the data is ideal. I'm not a programmer so I don't know what are my options and I can't afford to pay for a solution that doesn't seem overly complicated. I also realize this is a free service and users on this site take their valuable time to help others, which is wonderful. So I thank all of you for taking the time to assist. If anyone would like to provide a different solution, I thank you very much in advance.

 

[lespaul]

There is no solution to what you are trying to do that can be done using just the Design Query Grid.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[JonFer]

You need to create a separate query first that looks like this:

Select Book1.*, Int(CPT) as CPT_INT, Int(CPT)+2 as CPT2_INT
From Book1

Save this query as "qryBook1" and make sure it runs. Then use the SQL you posted but replace the Book1 table with qryBook1.

SELECT A.CONTRACT, A.CLAIM, A.CPT, IIf(A.CPT="78480",IIf(IsNull(B.CARRIER),"Separate","Bundled"),"") AS Code, Int([A!CPT]) AS CPT_INT, Int([B!CPT])+2 AS CPT_INT2
FROM qryBook1 AS A LEFT JOIN qryBook1 AS B ON (A.CLAIM = B.CARRIER) AND (A.CONTRACT = B.CONTRACT) AND (A.CPT_INT=B.CPT_INT2)
WHERE (((A.CONTRACT) In (10005,10006)));


John

http://www.interautomateconsulting.com

 

[skwdenyer]

sportdlockport wrote:

I respectfully disagree. This is a Microsoft Access Query Forum and should be utilized to focus on the software's functionality.

I find these forums very useful, so your attitude does rather annoy me! The answers proposed do use the software's functionality. The software includes SQL code entry as a way of achieving things that the query design grid can't cope with.

If you don't believe me, look up UNION queries in the help files for instance - MS say you can only do this with SQL code entry.

The query design grid is a very simplified tool to help novices build queries. Treat it as such, learn about databases, and get more work done! It is one of the tools provided by Access - SQL entry is another.

JonFer's solution will work for you. If you don't understand it, then please say so and I'm sure somebody will help guide you through it.

Good luck!