Separating records in One field and displaying into Two fields

[Guest]

I was wondering whether this can be done using a query. I don't want the
existing field to be split into more than 1 field. Just keep it as it is.

I have 2 tables: Case and CaseDet, and both are linked by CaseID. The
scenario is that 1 case has many case details. In the CaseDet table, there
are two other fields called CaseCo1 and CaseCo2.

If CaseCo1 = ABC, then value of CaseCo2 is displayed in Col1
If CaseCo1 = XYZ, then value of CaseCo2 is displayed in Col2

End of the day, I want to display the result in a report:

No | CaseID | Col1 | Col2
-------------------------------------
1 | 1000 | 100 | 200
2 | 1001 | 50 | 50
-------------------------------------
| 150 | 250

Do let me know whether such an idea is possible. Thank you very much.

Best regards,
Kelvin

 

[Jeff Boyce]

Kelvin

Use a query. Use an IIF() expression to determine, FOR EACH COLUMN, what is
displayed in that column.

 

[Guest]

Hi Jeff,

Thanks for the speedy reply. I was wondering whether you can elaborate more.
I am quite new to this. Hopefully, you can draft some sort of an SQL
statement.

Thank you very much.

Best regards,
Kelvin

 

[Guest]

Dear Jeff,

Thanks for your advise. The query did split into two columns but the listing
of each case is repeated based on the number of records found in CaseDet.

No | CaseID | Col1 | Col2
-------------------------------------
1 | 1000 | 0 | 200
2 | 1000 | 100 | 0
3 | 1001 | 50 | 0
4 | 1001 | 0 | 50
-------------------------------------
| 150 | 250

How do I configure the list so that the cases does not repeat itself and
still display the view as what I wanted as below:

No | CaseID | Col1 | Col2

 

[Guest]

Jeff,

I'm sure you've figured this out already, but I notice that you've got both
columns set up to give you the same result under two different
circumstances...

If CaseCo1 = ABC, then value of CaseCo2 is displayed in Col1
If CaseCo1 = XYZ, then value of CaseCo2 is displayed in Col2

In both cases, the value of CaseCo2 is displayed.

From your earlier posts, it seems as if you would like an either/or
scenario... I'm guessing that the problem you are having is that you have
not specified what you would like the expression to do if the value does not
equal your criteria (abc, xyz). I would construct it something like this:

Col1:IIf([CaseCo1] = ABC,[CaseCo1],0)
Col2:IIf([CaseCo1] = XYZ,[CaseCp1].0)

Of course, these two expressions will only recognize the values ABC or XYZ,
anything else will produce a 0 in the column. If you would like nothing to
appear, then replace the 0 with a zero-length string ("").

 

[Guest]

I'm sorry. I should have addressed my response to Kelvin, not Jeff.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Jeff,

I'm sure you've figured this out already, but I notice that you've got both
columns set up to give you the same result under two different
circumstances...

If CaseCo1 = ABC, then value of CaseCo2 is displayed in Col1
If CaseCo1 = XYZ, then value of CaseCo2 is displayed in Col2

In both cases, the value of CaseCo2 is displayed.

From your earlier posts, it seems as if you would like an either/or
scenario... I'm guessing that the problem you are having is that you have
not specified what you would like the expression to do if the value does not
equal your criteria (abc, xyz). I would construct it something like this:

Col1:IIf([CaseCo1] = ABC,[CaseCo1],0)
Col2:IIf([CaseCo1] = XYZ,[CaseCp1].0)

Of course, these two expressions will only recognize the values ABC or XYZ,
anything else will produce a 0 in the column. If you would like nothing to
appear, then replace the 0 with a zero-length string ("").
--
Why are you asking me? I dont know what Im doing!

Jaybird