Query help...

Assuming the flag has some meaning, create two queries.  One to select flag value c and the other to select flag value e.  Then create a third query that joins the first two.

Select q1.[Name], IIf(Nz(q1.F1,0) = 0 , "-", q1.F1) & "(" & IIf(Nz(q2.F1,0) = 0, "-", q2.F1) & ")" As fld1, IIf(Nz(q1.F2,0) = 0, "-", q1.F2) & "(" & IIf(Nz(q2.F2,0) = 0, "-", q2.F2) & "0" as fld2
From q1 inner join q2 on q1.[Name] = q2.[Name]

I made the following using single query for one field only, no errors but no result yet (which is an error by itself):

Select 
   c.[cmName]
   , IIf(Nz(c.onService,0) = 0 , "-", c.onService) & "(" & IIf(Nz(e.onService,0) = 0, "-", e.onService) & ")" As onServiceStr 
From (
   Select * From tStat Where CforCaseEforEvent="c") c 
   Inner Join  (Select * From tStat Where CforCaseEforEvent="e") e 
  on c.cmName=e.cmName

Select allOpen in new window

Is it possible to make this work?

Thanks,

Mike

place this codes in a regular module

Function getValues(vName As String, idx As Integer)
Dim rs As DAO.Recordset, sql As String, xVal As String

If idx = 1 Then
sql = "select f1 from table3 where [name]='" & vName & "' order by flag"
Else
sql = "select f2 from table3 where [name]='" & vName & "' order by flag"

End If

Set rs = CurrentDb.OpenRecordset(sql)
Do Until rs.EOF
    If xVal = "" Then
    xVal = IIf(rs(0) = 0 Or IsNull(rs(0)), "-", rs(0))
    Else
    xVal = xVal & "(" & IIf(rs(0) = 0 Or IsNull(rs(0)), "-", rs(0)) & ")"
    
    End If
rs.MoveNext
Loop
getValues = xVal
End Function

Select allOpen in new window

then create a query like this

SELECT Table1.Name, getvalues([name],1) AS F1, getvalues([name],2) AS F2
FROM Table1
GROUP BY Table1.Name, getvalues([name],1), getvalues([name],2);


.

not entirely certain of access syntax, but it appears to be a group by that's needed with min() and max() values concatenated

select
  name
, min( IIf(Nz(F1,0) = 0 , "-", F1) ) & '(' + max( IIf(Nz(F1,0) = 0 , "-", F1) ) & ')' as F1
, min( IIf(Nz(F2,0) = 0 , "-", F2) ) & '(' + max( IIf(Nz(F2,0) = 0 , "-", F2) ) & ')' as F2
from yourtable
group by name

now checking the solution.

Hi Paul,

FYI, I will be using this in SQL Server. I am in process of converting an Access prototype application to asp version with SQL Server as backend.

Thanks,

Mike

mmmm, not quite what you asked for

    INSERT INTO yourtable
    	([Name], [F1], [F2], [flag])
    VALUES
    	('A', 7, 3, 'c'),
    	('B', 13, 4, 'c'),
    	('C', 0, 6, 'c'),
    	('A', 11, 8, 'e'),
    	('B', 19, 9, 'e'),
    	('C', 4, 0, 'e')
    ;

**Query 1**:

    SELECT
      name
    ,   MIN( CASE WHEN ISNULL(F1,0) = 0 THEN '-' ELSE convert(varchar(5),F1) END )
      + '('
      + MAX( CASE WHEN ISNULL(F1,0) = 0 THEN '-' ELSE convert(varchar(5),F1) END )
      + ')'
    ,   MIN( CASE WHEN ISNULL(F2,0) = 0 THEN '-' ELSE convert(varchar(5),F2) END )
      + '('
      + MAX( CASE WHEN ISNULL(F2,0) = 0 THEN '-' ELSE convert(varchar(5),F2) END )
      + ')'
    FROM yourtable
    GROUP BY name
    	
    	
    

**[Results][2]**:
    
    | NAME | COLUMN_1 | COLUMN_2 |
    |------|----------|----------|
    |    A |    11(7) |     3(8) |
    |    B |   13(19) |     4(9) |
    |    C |     -(4) |     -(6) |



  [1]: http://sqlfiddle.com/#!3/a7ce1/4

Select allOpen in new window

closer...

    INSERT INTO yourtable
    	([Name], [F1], [F2], [flag])
    VALUES
    	('A', 7, 3, 'c'),
    	('B', 13, 4, 'c'),
    	('C', 0, 6, 'c'),
    	('A', 11, 8, 'e'),
    	('B', 19, 9, 'e'),
    	('C', 4, 0, 'e') 
    ;

**Query 1**:

    SELECT
          name
        ,   CASE WHEN min(ISNULL(F1,0)) = 0 THEN '-' ELSE convert(varchar(5),min(f1)) END
          + '('
          + CASE WHEN max(ISNULL(F1,0)) = 0 THEN '-' ELSE convert(varchar(5),max(f1)) END
          + ')'
        ,   CASE WHEN min(ISNULL(F2,0)) = 0 THEN '-' ELSE convert(varchar(5),min(f2)) END
          + '('
          + CASE WHEN max(ISNULL(F2,0)) = 0 THEN '-' ELSE convert(varchar(5),max(f2)) END
          + ')'
    FROM yourtable
    GROUP BY name
    

**[Results][2]**:
    
    | NAME | COLUMN_1 | COLUMN_2 |
    |------|----------|----------|
    |    A |    7(11) |     3(8) |
    |    B |   13(19) |     4(9) |
    |    C |     -(4) |     -(6) |



  [1]: http://sqlfiddle.com/#!3/86767/1

Select allOpen in new window

Not sure how to get 6(-) for C / F2
Are you certain it's not -(6) ?

What are the data types of F1 and F2?

Is it possible to make this work?

Get rid of the subselect and use a straight join of the two queries.  That will allow you to test the individual pieces to see where the error is.

1.  Is your question - what is the syntax for formatting the fields as --- -(4)?  I showed you how to do it with IIf()'s inside the query.  Rey offered a function -  I don't created functions unless there is no other way since I use primarily SQL Server BE's and VBA/UDF functions cannot be sent to the server.  That means Access has to take apart the query and decide what can be sent and what can't and it doesn't always make the correct decision which causes Access to request way too much data.
2. Are you trying to do this with Access SQL syntax?  I ask because several folks have posted T-SQL solutions because you posted the question to the SQL Server forum.

@Pat see ID: 39987516
>>"FYI, I will be using this in SQL Server. I am in process of converting an Access prototype application to asp version with SQL Server as backend."

additionally a self join isn't going to produce 3 rows

    CREATE TABLE yourtable
    	([Name] varchar(1), [F1] int, [F2] int, [flag] varchar(1))
    ;
    	
    INSERT INTO yourtable
    	([Name], [F1], [F2], [flag])
    VALUES
    	('A', 7, 3, 'c'),
    	('B', 13, 4, 'c'),
    	('C', 0, 6, 'c'),
    	('A', 11, 8, 'e'),
    	('B', 19, 9, 'e'),
    	('C', 4, 0, 'e') 
    ;

**Query 1**:

    select
    *
    From yourtable q1 
    inner join yourtable q2 on q1.[Name] = q2.[Name]
    

**[Results][2]**:
    
    | NAME | F1 | F2 | FLAG |
    |------|----|----|------|
    |    A |  7 |  3 |    c |
    |    A | 11 |  8 |    e |
    |    B | 13 |  4 |    c |
    |    B | 19 |  9 |    e |
    |    C |  0 |  6 |    c |
    |    C |  4 |  0 |    e |
    |    A |  7 |  3 |    c |
    |    A | 11 |  8 |    e |
    |    B | 13 |  4 |    c |
    |    B | 19 |  9 |    e |
    |    C |  0 |  6 |    c |
    |    C |  4 |  0 |    e |

Select allOpen in new window

The self join produces a Cartesian Product.  You need to get set 1 and set 2 from the table and join set 1 to set 2.  Set 1 contains the records with the c flag.  set 2 contains the records with the e flag.  If you do this with SQL Server, you could use views instead of queries.  View 1 would select the c records, view 2 would select the e records.

If you have only the two flag values then the following self join might work:

select
    *
    From yourtable q1
    inner join yourtable q2 on q1.[Name] = q2.[Name] and q1.[Flag] <> q2.[Flag]

Also,
If you only want T-SQL syntax, you should specify that and not even post in the Access forum.  The functions I used to format the calculated column will not work in T-SQL either.  

Just because the BE is SQL Server does not mean that you can't use querydefs assuming that the FE will be Access.  If the FE is not Access and the BE is not Access, then the question is in the wrong category.

Good point regarding the topics Pat.

Regarding the self-join, my intended point was that a self-join isn't wanted for the solution as there are only 3 rows in the expected result (from the original 6). To reduce the rows a GROUP BY is required.

A group by will not reduce the Cartesian Product to what the OP requested but my additional criteria should.

Rey Obrero and PatHartman,
The final code will be transformed to be used in SQL Server. I have nor mentioned this in my original question (the first phase of my question). In the follow up question I am preparing to post shortly, I will request stored proc solution.

John_Vidmar,
Your solution works perfectly.


PortletPaul,
re:> Not sure how to get 6(-) for C / F2.... Are you certain it's not -(6) ?

Answer: The patern is c-value(e-value) which means we need to have 6(-) not -(6).

Your solution (ID: 39987537) works but it takes longer to display the results.

re:> If you only want T-SQL syntax, you should specify that and not even post in the Access forum.

Answer: You are right. This is what I will do next. This will be the second phase of my initial question.

awking00,
re:> What are the data types of F1 and F2?

Answer: They are integer.

================
I will close this question shortly.

These discussions have helped me with the direction the application needs to take.

tSource:
Name	F1	F2	flag
A	7	3	c
B	13	4	c
C	0	6	c
A	11	8	e
B	19	9	e
C	4	0	e
			
tTarget
Name	  F1	        F2	
A	7(11)	       3(8)	
B	13(19)	       4(9)	
C	-(4)	       6(-)

Select allOpen in new window

Now, in the new question I will post in SQL Server section shortly, I will ask for help to put together a stored procedure (possibly using cursor) to take data from tSource and put it in tTarget. This way, I can schedule to run the stored procedure once every hour (or on demand) instead of with each changes users make to the data.

Thank you all for very helpful inputs.

Mike

Here is the link to the new question:
https://www.experts-exchange.com/questions/28410672/Stored-Proc-help.html

Sorry for the delay and thank you for good comments.

As the logo of EE (a race car flag indicates), the first (fastest) solution gets the points.

Regards,

Mike