Solved

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

Posted on 2008-10-11
20
352 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 50 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 450 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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