By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,097 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Combining Like Fields within a Report

Abhean
P: 23
I am trying to create a report using a Query using 2 tables. The information is on the attachment.

What I am wanting to do is to be able to concatenate? the MatDescrip so those that are equal go to the same line in the report while those lines that are combined add together the OnHand amounts to give a a total on the report.

Expand|Select|Wrap|Line Numbers
  1. SELECT MaterialNum.MatDescrip, Roche.GTIN, Roche.Lot, Roche.Exp2, Roche.Ref2, MaterialNum.Type, MaterialNum.Family, Roche.OnHand
  2. FROM Roche INNER JOIN MaterialNum ON Roche.Ref2 = MaterialNum.MaterialNumber
  3. ORDER BY Roche.OnHand DESC;

1 Week Ago #1

✓ answered by NeoPa

This sounds exactly like Combining Rows-Opposite of Union.

Check it out and see how you get on.

Share this Question
Share on Google+
4 Replies


Abhean
P: 23
Dosent look like my attachment came through. Trying again. :)
Attached Images
File Type: png Table.PNG (13.1 KB, 124 views)
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 31,308
This sounds exactly like Combining Rows-Opposite of Union.

Check it out and see how you get on.
1 Week Ago #3

Abhean
P: 23
Woot! Thanks for the help. Only had to change a little as I was pulling from 2 Tables.

SQL, so far, looks like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT MaterialNum.MatDescrip, Max(Concat(MatDescrip,OnHand)) AS Features, Roche.Ref2, MaterialNum.Family, MaterialNum.Type
  2. FROM Roche INNER JOIN MaterialNum ON Roche.Ref2 = MaterialNum.MaterialNumber
  3. GROUP BY MaterialNum.MatDescrip, Roche.Ref2, MaterialNum.Family, MaterialNum.Type
  4. ORDER BY MaterialNum.MatDescrip;
Again, thank you for the help!
1 Week Ago #4

NeoPa
Expert Mod 15k+
P: 31,308
8-)

Glad I was able to help :-)
1 Week Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.