Merging multiple records into just one 1

[breezett93]

I am trying to remove all the zero values, and have the non-zero values form one complete record. In my example, that leaves TIMB and OAKW, but OAKW could be replaced by other choices. So, I never want TIMB to be the chosen value, always the other. I plan to use NOT "TIMB" as Criteria.

I tried using Group By and Max, but I get one record with OAKW values and one with TIMB values. Is there a better solution than what I tried?

Thanks

 

[Andrzejek]

What do you want as the outcome?

[pre]
Field1 Field2 Field3 Field4 Field5 Field6 Field7
31227 [red]???[/red] 1.1875 0.25 0.25 0.875 1.1875
[/pre]
???

(Your Field's names are not visible)


---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

I would like Field2 to be anything except TIMB. So, from the picture above, it should be OAKW

This is currently what the query results look like.

 

[Andrzejek]

Could you show your query?


---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

Certainly. Both SQL and Design view.

 

[Andrzejek]

The problem with blurry Picture is, you cannot copy-n-paste the text to modify it. :-(

My guess here is:

[pre]
Select Field1, Field2,
SUM(Field3) As Field3,
SUM(Field4) As Field4,
SUM(Field5) As Field5,
SUM(Field6) As Field6,
Sum(Field7) As Field7
From MyTable
Where Field2 <> 'TIMB' [green](anything except TIMB)[/green]
Group By Field1, Field2[/pre]

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

Sorry about that.

SELECT OrdDetBandTbl.OrdId, OrdDetBandTbl.EdbSpc, Max(IIf([EBId]=2,[EdbSz],0)) AS ETop, Max(IIf([EBId]=3 And [EdbId]="OS",[EdbSz],0)) AS ELeftOS, Max(IIf([EBId]=3 And [EdbId]="IS",[EdbSz],0)) AS ELeftIS, Max((IIf([EBId]=4 And [EdbId]="OS",[EdbSz],0))) AS ERightOS, Max((IIf([EBId]=4 And [EdbId]="IS",[EdbSz],0))) AS ERightIS, Max((IIf([EBId]=1,[EdbSz],0))) AS EBottom
FROM OrdDetBandTbl
GROUP BY OrdDetBandTbl.OrdId, OrdDetBandTbl.EdbSpc
HAVING (((OrdDetBandTbl.OrdId)=31227));

 

[Andrzejek]

So, you also have additional requirements never mentioned before, based on values in fields: [tt]EBId, EdbId, EdbSz [/tt]?

[pre]
SELECT OrdId, EdbSpc,
Max(IIf(EBId=2,EdbSz,0)) AS ETop,
Max(IIf(EBId=3 And EdbId="OS",EdbSz,0)) AS ELeftOS,
Max(IIf(EBId=3 And EdbId="IS",EdbSz,0)) AS ELeftIS,
Max(IIf(EBId=4 And EdbId="OS",EdbSz,0)) AS ERightOS,
Max(IIf(EBId=4 And EdbId="IS",EdbSz,0)) AS ERightIS,
Max(IIf(EBId=1,EdbSz,0)) AS EBottom
FROM OrdDetBandTbl
WHERE OrdId=31227
AND EdbSpc <> 'TIMB'
GROUP BY OrdId, EdbSpc
[/pre]
What do you want to do with records where [tt]EdbSpc = 'TIMB'[/tt]

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

That is correct. The results of this query will be displayed on a report. Unfortunately, the table was designed in a limiting way; so I had to do the extra requirements in the query. I am basically assigning numbers from the table to the respective field in the query. Below is what 31227 looks like in the table I'm pulling from. Instead of having all relevant information in one record, the table was designed to create one record for each difference. (I didn't create it. It's driving me nuts.)

My extra requirements shouldn't affect the combining of the two records though.

 

[Andrzejek]

>combining of the two records
Well, looks to me that you have 3 records: [tt]EdbSpc = TIMB, EdbSpc = OAKW[/tt], and [tt]EdbSpc = NULL[/tt] (???)


---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

That extra one is a mis-type; so I deleted it. There should be 6 records not 7. It's corrected now.

 

[Andrzejek]

OK, so there is no record where EdbSpc is empty.

If you want "anything except TIMB", then (as I asked before) - What do you want to do with records where EdbSpc = 'TIMB'


---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

I still need the numbers from records that have TIMB. I just don't want TIMB in the final record. Instead, I want the other option that will be present.
TIMB is a given in just about every order. So I can have it "hard-coded" on the report as a label/textbox. So I really just need the other component.
Unless there is a way for me to have both, but I didn't think the field would work properly if it had two items.

 

[Andrzejek]

You know, it would be a lot easier if you would use this approach:

This is the data I have:
Blah, blah, blah

I’ve tried this approach:
Select …
From …

And I’ve got this outcome:
Blah, blah, blah

But what I really need to have is this:
Blah, blah, blah

Otherwise, I have to play a guessing game here :-(


---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

Sorry, wasn't trying to be confusing. I thought we were on the same page so far.

This is the data I have:

https://res.cloudinary.com/engineering-com/image/upload/v1587664934/tips/Capture2_psemfj.png

I’ve tried this approach:
SELECT OrdDetBandTbl.OrdId, OrdDetBandTbl.EdbSpc, Max(IIf([EBId]=2,[EdbSz],0)) AS ETop, Max(IIf([EBId]=3 And [EdbId]="OS",[EdbSz],0)) AS ELeftOS, Max(IIf([EBId]=3 And [EdbId]="IS",[EdbSz],0)) AS ELeftIS, Max((IIf([EBId]=4 And [EdbId]="OS",[EdbSz],0))) AS ERightOS, Max((IIf([EBId]=4 And [EdbId]="IS",[EdbSz],0))) AS ERightIS, Max((IIf([EBId]=1,[EdbSz],0))) AS EBottom
FROM OrdDetBandTbl
GROUP BY OrdDetBandTbl.OrdId, OrdDetBandTbl.EdbSpc
HAVING (((OrdDetBandTbl.OrdId)=31227));

And I’ve got this outcome:

https://res.cloudinary.com/engineering-com/image/upload/v1587661357/tips/Capture2_krrnur.png

But what I really need to have is this:
31227 OAKW 1.1875 0.25 0.25 0.875 1.1875

 

[Andrzejek]

I would try this:

[pre]
SELECT OrdId, 'OAKW' As EdbSpc,
Max(IIf(EBId=2,EdbSz,0)) AS ETop,
Max(IIf(EBId=3 And EdbId="OS",EdbSz,0)) AS ELeftOS,
Max(IIf(EBId=3 And EdbId="IS",EdbSz,0)) AS ELeftIS,
Max(IIf(EBId=4 And EdbId="OS",EdbSz,0)) AS ERightOS,
Max(IIf(EBId=4 And EdbId="IS",EdbSz,0)) AS ERightIS,
Max(IIf(EBId=1,EdbSz,0)) AS EBottom
FROM OrdDetBandTbl
WHERE OrdId = 31227
GROUP BY OrdId, EdbSpc
[/pre]
With hard-coded 'OAKW'


---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

Here is the result of that query:

I'm not sure why the two records didn't merge since the common fields match.

 

[Andrzejek]

Try:
[tt]
...
GROUP BY 1, 2
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

Results:

"Your query does not include the specified expression 'OrdId' as part of an aggregate function."

Query for reference:
SELECT OrdId, 'OAKW' AS EdbSpc, Max(IIf(EBId=2,EdbSz,0)) AS ETop, Max(IIf(EBId=3 And EdbId="OS",EdbSz,0)) AS ELeftOS, Max(IIf(EBId=3 And EdbId="IS",EdbSz,0)) AS ELeftIS, Max(IIf(EBId=4 And EdbId="OS",EdbSz,0)) AS ERightOS, Max(IIf(EBId=4 And EdbId="IS",EdbSz,0)) AS ERightIS, Max(IIf(EBId=1,EdbSz,0)) AS EBottom
FROM OrdDetBandTbl
WHERE OrdId = 31227
GROUP BY 1, 2

 

[Andrzejek]

Another 'try';:

[pre]
SELECT OrdId, 'OAKW' As [red]XYZ[/red],
Max(IIf(EBId=2,EdbSz,0)) AS ETop,
Max(IIf(EBId=3 And EdbId="OS",EdbSz,0)) AS ELeftOS,
Max(IIf(EBId=3 And EdbId="IS",EdbSz,0)) AS ELeftIS,
Max(IIf(EBId=4 And EdbId="OS",EdbSz,0)) AS ERightOS,
Max(IIf(EBId=4 And EdbId="IS",EdbSz,0)) AS ERightIS,
Max(IIf(EBId=1,EdbSz,0)) AS EBottom
FROM OrdDetBandTbl
WHERE OrdId = 31227
GROUP BY OrdId, [red]XYZ[/red][/pre]

The [red]XYZ[/red] expression is just to find out if you get your output right


---- Andy

There is a great need for a sarcasm font.