Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

On an Access 2003 Report limit the output in a text Box to first Control Name

Posted on 2008-10-11
20
Medium Priority
?
358 Views
Last Modified: 2013-11-28
0n an Access 2003  Report, I have many Records ("Material" type) in a field with many differant records each.  Throuth the "Text" box control, I need to only enter the first of each types of materials.  The reason that I need to do this through the "Text" box (and not a query) is I need to sum the values in another field's "Text Box and associate them with  tha appropriated  "Material. "Text box.
 I need the SQL to go in the "Text" box.
0
Comment
Question by:SuperSnooper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 3
20 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 22695006
I'm sorry - i'm not following...

You have a report that you want to limit based on the content of a text box?
0
 

Author Comment

by:SuperSnooper
ID: 22695265
Yes in the "Materials. Feld only. The other fields I wand to sum the like material values. ie:
 
                    In Table  
_____________________________                                                  
 
                    Field1
                "Material"   Field2    Field3
Record1 -   "AAA"  -   "abb" - " acc"                
Record2 -   "AAA"  -   "bbb" - " bcc"  
Record2 -   "AAA"  -   "cbb" - " ccc"  
Record4 -   "AAA"  -   "dbb" - " dcc"  
Record5  -   "BBB"  -   "ebb" - " ecc"  
Record6  -   "BBB"  -   "fbb" - " fcc"  
Record7  -   "BBB"  -   "gbb" - " gcc"  
Record8  -   "BBB"  -   "hdbb" - " hcc"
ect.
   
                                         Output On Report
_________________________________________________________
"AAA"     Sum ("abb","bbb","cbb" ,dbb)      Sum "acc",bcc,""ccc","dcc")
"BBB"     Sum ("ebb","fbb","gbb","hbb )       Sum "ecc","fcc","gcc","hcc")  

Sorry...I seem to have difficulty making myself clear.    




0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22695278
OK. So you're not actually showing records on the report - just a calculation.
There's two ways to do this.

1. Use a pivot form. Similar to Excel's pivot tables, this will allow you to groub field 1 as a pivot and sum the resulting two columns as data fields.

2. Using recordsets/queries to create a recordsource for your report/form and then use grouping options in the report to display how you would like.

This second option is messier though.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:SuperSnooper
ID: 22695324
I understand the concept of pivot tables but have never used one. I don't really understand how to set one up. Can you get more specific on the Record Set/Querry method.

David
0
 
LVL 26

Assisted Solution

by:dannywareham
dannywareham earned 200 total points
ID: 22696700
Pivots are really easy to use (much easier than coding the queries).
Give it a go :-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22698931
SuperSnooper,

Is that your "Actual" Table data?

Because you cant Sum text fields, only numeric fields.
(You can "Count" text fields though)

Is that the "Actual" datayou want displayed on the Report?
Because I don't see any actual numeric "Sums" anywhere?

In other words,can you post you *exact* Table data and a sample of *exactly* what you want displayed on the report.

Thanks

JeffCoachman
Please clarify
0
 

Author Comment

by:SuperSnooper
ID: 22704220

Jeff,
his the actual fields.  I haven't attempted the sums yet because I wanted to get the Report "Material" fixed first.  I have 15 other reports in this module of my db. So I want this report to be in basically the same format as the others.
Danny,
I have set up a Pivit Table, but I can't get it to work right with the "Report" in the db. I am still open to soluntion on this proceedure though...I atleast learned how to set up a PT.

Thanks Guys. I appreciate Your Help
David  :)
0
 

Author Comment

by:SuperSnooper
ID: 22707577
Jeff,
I  need the printed detail to look like this:

Materials                            Number                              Units                                   Total
                                           Used                                                                             Cost

16d Nails       (Sum of "QunantyB"  for 16d Nails)      Pound         (Sum  ([QunantyB]*[UnitCostB]) for 16d)
2X4X8 SYP   ( "     "           "            "          2X4X8 )     Each         (   "             "                 "          "  2X4X8)
SackCrete     ( "     "           "            "          SackC)      50 #  Bag  (   "             "                 "          "  SackC)
Widgetts        ( "     "           "            "          Widge)      Each         (  "             "                 "            "  Widge)
ect.

I hope this is Clear.
David
Report.jpg
Query-For-Report.jpg
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22708559
SuperSnooper,

Try this:

Again, since you posted a screenshot instead of the *Actual* data as requested, I had to re-create the data from scratch.

It displays the data the way your requested.

I am sure you can adapt it for use in your database.

JeffCoachman
Access-EEQ23806798-ReportSummari.mdb
0
 

Author Comment

by:SuperSnooper
ID: 22712000
Jeff,

Your Post looks great.  The only problem is the pivot table (or whatever) that gives Exp1: ...ect does not show in your post. It also is not there when I copy your query to my by. Therefore when I open the query or report it gives me errors on all fields.  I also copied your report and table to my db to test and alter. If you will open your post on EE you will see what I mean.

David
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22715587
SuperSnooper,

I open the actual Database from my post and the query looked fine.
Where are you seeing "Expr1"?

If my report works then you will have to carefully translate everything I have done, in order for it to work in your database.

You can't simply "Drop" my solution into your database and expect it to work.
;-)

This is why I asked for the actual data, not a screenshot.
That way I could have used your data, and there would not be any problems.

Is there a reason why you refused to post the actual database?

JeffCoachman
0
 

Author Comment

by:SuperSnooper
ID: 22716113
The reason is that I didn't know how to get it to less than 5MB....My db is over 20.  I see after you sent me the soluntion.  I am learning.....  I have got your query in my db and can now see the Pivot table.  I created a PT in my query and it "looks" like yours.  I copied the text applicable boxes from your report to mine.

The only problems that I am having is getting the Report to use the PT right.  The individual material Total Cost and Number of Units sums are all the Grand Total Sums.
Also it shows all records on the report instead of just one for each type of material.

Please excuse me for being DUMB. I am not a programer...just muddled through it.  Electrical Engineers, you know, are basically DUMB. If I can ever get some time I want to take a course. I am just trying to help the peaple in the Carribian (at no cost) with their huricane clean up effort. They need a origanizational Tool.

If you want, I can now send you my stuff.

Thanks for you Efforts
David
0
 

Author Comment

by:SuperSnooper
ID: 22716448
If you have the time and still want to mess with me, I have attatched the file.

Thanks
David
Client-Report-db.mdb
0
 

Author Comment

by:SuperSnooper
ID: 22717409
Jeff,

I found out that the problem was not with the Pivot Table.  It was with the Report.  I put my Query W/PT in your report and it worked perfectly. So the question is how did you get your report to just contain one of each type of material?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22719309
"Please excuse me for being DUMB"
Don't put your self down, I'm still trying to figure out Ohm's Law!
(E=mc2?)
LOL!
Besides you figured it out without my help.
Sounds pretty "SMART" to me.

"how did you get your report to just contain one of each type of material?"
If you look at the report in design view you will see that I Grouped the Report by "MaterialB", and put the summaries in the MaterialB Footer, then hid the other un-needed sections.
;-)

JeffCoachman
0
 

Author Comment

by:SuperSnooper
ID: 22725213
Jeff'

I see the "MaterialB Footer" the only problem in a couple of my reports, I need "MaterialA". I can't find how you set the added footer or change the presant one.

David
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1800 total points
ID: 22726730
Open the report in Design view.
Right Click anywhere
Select "Sorting and Grouping"
In the dialog box you will select "MatreialA" (or whatever you like.)

Jeff
0
 

Author Comment

by:SuperSnooper
ID: 22730746
Jeff,

Thanks for ALL your help. I really appreciate it. I did learn three new things from this posting. Hope that you did not get too frustrated with me.

Thanks Again
David
0
 

Author Closing Comment

by:SuperSnooper
ID: 31506733
GREAT JOB...Very Helpful and INFORMATIVE!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22732980
;-)

Questions like these help to keep me honest, and on my toes!
;-)

JeffCoachman
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question