Solved

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

Posted on 2008-10-11
20
346 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

832 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