Solved

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

Posted on 2008-10-11
20
343 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
 

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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now