How to merge 2 rows into 1 in query

[jaryszek]

Hi,

i have query like here:

SELECT DeploymentName.Deployment, DeploymentName.Profile, Table2.ResultTable2, Table1.ResultTable1
FROM ((DeploymentName INNER JOIN Profiles ON DeploymentName.Profile = Profiles.Name) LEFT JOIN Table2 ON (Profiles.TableProfile = Table2.Name) AND (Profiles.TableName = Table2.TableName)) LEFT JOIN Table1 ON (Profiles.TableProfile = Table1.Name) AND (Profiles.TableName = Table1.TableName);

Result:

so want i want to get is:

so i would like to merge rows (ResultTable1 and REsultTAble2 colums) into 1.
How can i do this?

I would like to move sql after to use adodb recrodset in Excel.

Thank you for help and best wishes,
Jacek

 

[The_Doc_Man]

If you are trying to get two records to appear in the same record, you are going to have to use a JOIN. Which you are doing in your exhibit. So... what is the problem?

 

[plog]

I can make your query achieve what you want--but you gave us so little and your data is so genericized I can't tell if I am really helping you or not. This SQL will achieve the results you want:

SELECT DeploymentName.Deployment, DeploymentName.Profile, Max(Table2.ResultTable2) AS MaxOfResultTable2, Max(Table1.ResultTable1) AS MaxOfResultTable1
FROM ((DeploymentName INNER JOIN Profiles ON DeploymentName.Profile = Profiles.Name) LEFT JOIN Table2 ON (Profiles.TableProfile = Table2.Name) AND (Profiles.TableName = Table2.TableName)) LEFT JOIN Table1 ON (Profiles.TableProfile = Table1.Name) AND (Profiles.TableName = Table1.TableName)
GROUP BY DeploymentName.Deployment, DeploymentName.Profile;

But if that doesn't work on your real data I won't be surprised. Also, I see 2 things in your data that are incorrect:

1. Use of reserved words for field names. [Name] is a reserved word and shouldn't be used as a name. Instead prefix it with what the name is for, e.g. ProfileName.

2. Tables with duplicate structure. If you feel the need to have multiple tables with the exact same structure (Table1 and Table2) you need to make just 1 table to hold all their data. When you do that you are essentially storing a piece of data in the table name and that is incorrect.

Again though, your data is so genericized its hard to provide good help. I suggest you tell us what this database is for and what you are trying to achieve with this query. Perhaps post the actual database you are working with.

 

[GPGeorge]

Often, in trying to "simplify" things for a question, we end up obscuring the actual situation so much that's it's nearly impossible to figure out what is supposed to be going on. Plog has identified some problems. Another one is the odd way in which you are storing values in the two tables with regard to the Table1 and Table2. You store the name of the table in a field called "TableName" in each table. That clearly means something to you, but not to anyone not familiar with the REAL data.

Instead of trying to "help" by creating aliases for everything, tell us about the REAL situation and the data.