How to merge 2 rows into 1 in query (1 Viewer)

jaryszek

Registered User.
Local time
Today, 08:21
Joined
Aug 25, 2016
Messages
756
Hi,

i have query like here:

Code:
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:
1640270459526.png


so want i want to get is:
1640270553242.png


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
 

Attachments

  • Database13.accdb
    508 KB · Views: 323

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:21
Joined
Feb 28, 2001
Messages
26,999
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

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,611
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:

Code:
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

Grover Park George
Local time
Today, 08:21
Joined
Nov 25, 2004
Messages
1,775
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.
 

Users who are viewing this thread

Top Bottom