Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

2 - Equal Grouping Levels

Status
Not open for further replies.
Jillp

Jillp

Technical User
Jun 7, 2001
75
US
Hello,

I have a report that I am trying to recreate from a form created many years ago by an ERP company. The ERP company used a temporary subset of the original data that resides locally. I would like to share a similar report outside of the ERP system via our intranet and so I am connecting through odbc to our database to accomplish this. Mimicing the original format is not getting it done, I am stuck on a grouping issue.

I have 1 project that connects to an assy table with 3 records. each assy record has both mtl items and opr items. The problem is the material and operation tables are equal. If I group on Material first then I am presented with the 1st piece of material and directly beneath are all operations in the assy, followed by the 2nd piece of material and, again all the operations in the assy. and of course if I group on both I get 1 pc of material followed by 1 operation and so on. I would rather not use a subreport because these reports must be efficient for shop floor employees.

I have found some questions with similar requests on various sites but no solutions that seem to jump out at me.

Thanks for any advice!

Jill

 
 http://files.engineering.com/getfile.aspx?folder=05f0bfff-fe25-49b2-9d82-e538f5306e65&file=Desired_Result.JPG
Sort by date Sort by votes
You haven't explained what you want the result to look like. Maybe show a sample of how the desired report would look. What is its purpose?

-LB
 
Upvote 0 Downvote
In previous post I attached a screen shot of the desired result.
 
Upvote 0 Downvote
If you have the ability to create a command, you could use a union statement to put both material and operation in the same field, as in:

select table1.assy, 'Material', table2.mtl, table2.qty
from table1 equal join table2 on table1.assy=table2.assy
where....

union

select table1.assy, 'Operation', table3.opr, table3.qty
from table1 equal join table3 on table1.assy=table3.assy
where...

Insert a group on {Command.Assy}, and then on {Command.Material} (the label, not the field).

The punctuation within the command depends on your database. Might take double quotes around the Material and Operaqtion lables, for example.

-LB


 
Upvote 0 Downvote
Thanks LB I will give this a try
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bbranco
  • Locked
  • Question
Query Help - Group By 2 tables
Replies
0
Views
405
bbranco
bbranco
JustMe24
  • Locked
  • Question
How to select from 2 rows in a table
Replies
1
Views
490
SoftwareRT
SoftwareRT
D
  • Locked
  • Question
Crystal Reports for Visual Studio 2022 Download
Replies
2
Views
7K
Joshua44
J
Betty Boop
  • Locked
  • Question
Report Shows Fewer Records After Grouping
Replies
5
Views
359
lbass
lbass
ramblinrose
  • Locked
  • Question
Column Chart percentages does not equal 100%
Replies
1
Views
225
Smith_J
Smith_J

Part and Inventory Search

Sponsor

Back
Top