Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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

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
SuperSnooper
Asked:
SuperSnooper
  • 11
  • 6
  • 3
2 Solutions
 
dannywarehamCommented:
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
 
SuperSnooperAuthor Commented:
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
 
dannywarehamCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SuperSnooperAuthor Commented:
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
 
dannywarehamCommented:
Pivots are really easy to use (much easier than coding the queries).
Give it a go :-)
0
 
Jeffrey CoachmanCommented:
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
 
SuperSnooperAuthor Commented:

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
 
SuperSnooperAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
SuperSnooperAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
SuperSnooperAuthor Commented:
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
 
SuperSnooperAuthor Commented:
If you have the time and still want to mess with me, I have attatched the file.

Thanks
David
Client-Report-db.mdb
0
 
SuperSnooperAuthor Commented:
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
 
Jeffrey CoachmanCommented:
"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
 
SuperSnooperAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
SuperSnooperAuthor Commented:
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
 
SuperSnooperAuthor Commented:
GREAT JOB...Very Helpful and INFORMATIVE!
0
 
Jeffrey CoachmanCommented:
;-)

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

JeffCoachman
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now