Solved

Access 2010: Report

Posted on 2013-06-11
21
283 Views
Last Modified: 2013-06-28
I am in over my head.  I have been using Access for years but have only done limited programing.  I have just started using sub forms, have no knowledge of modules, or programing language.

I am trying to print a report with all the information, for now, of a record.  This record is selected in the Query "QReportTotalByStNum" of attached data base.  My effort to do this is shown in Reports "RQReportTotal".  I cannot get it even to show one tab correctly.  I need to print all tabs for the record and control the layout of each tab so that more than one may print on a single 8.5 x11 sheet.  

Any help will be greatly appreciated.  If I should start from scratch to make life easier let me know what changes you suggest.

Timothy
PSAssm.accdb
0
Comment
Question by:TimothyA
  • 11
  • 10
21 Comments
 
LVL 21
ID: 39239627
The way I normally do this is to have a separate sub form for each child table.  A Tab control is great for organizing the sub forms.

You would do the same for your report. Each child table would be a separate sun report. You would place each sub report direct on the main report. No Tab control on the report.

After taking a cursory look at your tables, I am thinking that most of the child table, if not all,  should be combined into a single table with a separate record for each of the current child tables.
0
 

Author Comment

by:TimothyA
ID: 39239860
Coach,

Thanks for this information.  I will try to develop the sub reports tomorrow.

The reason I did not try to place all information in a single table was there are calculations by each table and then calculation on each table that can only be calculated from results derived from combined calculations derived from Querys involving many tables.

Thanks  for your quick and insightful reply.

Timothy
0
 
LVL 21
ID: 39240047
The reason I did not try to place all information in a single table was there are calculations by each table and then calculation on each table that can only be calculated from results derived from combined calculations derived from Querys involving many tables.

When you combined all the child tables into a single table you would add an addition field that would identify the record type. You want to avoid the table name being used as category data. You would create queries to select datasets that would be just like your original separate tables.  You can do all the same calculation are before. And there are things that you can do much easier with all the data in the same table.  For example, it allows you to easily add a new category without a lot of redesign work.
0
 
LVL 21
ID: 39240056
The more I look at your tables the more issues I spot.  

1) Most would agree that the Lookup Fields at the table level  causes more headache than the help.

See:  The Evils of Lookup Fields in Tables

2) Normalization issues in most of the child tables. Lot of repeat fields that probably need to be in separate records.. Fixing this now will make your reports and everything else a lot easier in the future. In my experience, this is critical to the success of your database.

3) Not a show stopper but just a better design: I would also use the Auto number field in the main table as the primary key and foreign key values for all relationships.
0
 

Author Comment

by:TimothyA
ID: 39240513
Coach,

Thanks.

Do you expect if I do all you suggested, I will not be getting querys that are too complexes?  That is why I have the three "Make Table querys" already.  These querys are at best cumbersome

Thanks again,

Timothy
0
 
LVL 21
ID: 39242412
Timothy ,

When working with a relational database queries are your friend.  They are what allow you to harness the power of a  properly normalized (designed)  database.

When a database is not properly normalized it usually is very complex. This makes creating queries very difficult.

When I see a database that has lots of tables and a few queries there is usually normalization issues.  When hired to improves one of these databases I normally reduce the number of tables and create a lot of queries. In a typical database I will have 3-4 times more queries than tables.

What you are trying to model has a lot of information/components to track.  It will be a challenge for even a very experienced database designer.

I would recommend breaking it down into steps.

1) Take the table T00Master and properly normalize it.
2)  Add a table to store the additional data.
3)  Test the design by storing the data from the table T01BuildingSite into the new table in step 2.
3)  Test the design by storing the data from the table T02WetWellStructure into the same new table in step 2.
Repeat for each table.  If designed properly, as you add the data from each table you should only need to add records to look up tables.

TIP: You want to avoid setting up your tables like you would create tabs in an Excel workbook.
0
 

Author Comment

by:TimothyA
ID: 39242547
Coach,

Thanks for your guidance.

From reading your responses I begin to believe I know even less than the little I thought I knew.

First small step:  Table: T00Master -  I thought I had it normalized.  Then I added the AutoNumber ID only to track Record creation order.  For example, how should I show repeated visits to the record when needed?

Am I overlooking something very obvious?

I will try not to stress your patience with me.

Timothy
0
 
LVL 21
ID: 39242659
Timothy,

Learning to normalize a database takes years of experience maintaining and updating an applications.  Fortunately for you there is the internet and EE. They was not around when I was learning this stuff.

I will create an example from your tables to illustrate what I am recommending.
0
 

Author Comment

by:TimothyA
ID: 39248877
Coach or Anyone,

I am stumped on the most fundament issue.  I am trying to modify data base system as suggested in post Posted on 2013-06-12 at 12:25:31ID: 39242412.  I have not been able to add even one field of any table to the new table "T00Base".  I have tried multiple ways with different fields; added field names to the "T00Base" table  as well as trying to have the query add them.  The end result is always the same:  (### record(s) due to validation rule violations).  

I called myself checking all the rules, but have not found the simple thing I am over looking.

I am presently trying to Append T00Base with only one field "BS_Safety" from T01BuildingSiteCopy.

I have attached the data base.

I eagerly await help.

Timothy
PSAssmTAMod.accdb
0
 
LVL 21
ID: 39253378
Timothy ,

I see some issues in what you are trying to do.

I am creating an example for you now. I will post it shortly..
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39254518
I normalized the master table and the building site table. I imported the data into my new tables. Look at the tables with a tbl prefix.
PSAssm--HiTechCoach.zip
0
 

Author Closing Comment

by:TimothyA
ID: 39254671
More than I expected.  Thanks
0
 
LVL 21
ID: 39255128
You're welcome!
0
 

Author Comment

by:TimothyA
ID: 39279786
Coach,

I am still in over my head.

I mistakenly thought I understood the logic of the tables you setup, but I realize I am lost.

Query Questions

I have combined all eleven partial tables that apply uniquely to each PS.  I have attached this revised database (PSAssm011).  First is the  table (tblTotal) what you expected as a starting point?

I then built queries (01 - 11).  In some of these queries I added calculations.  See in particular qry05C.  Fields with Hr/day and P1-P2_Dif work perfectly in the query.  I even tried to place these calculation in a separate query  -Calculations which also works perfectly .

Form Question

I am unable  to bring the query calculations into the form (frmTotal).  What is the best way to do this?  I could run them in the form.  Is this the best way?

Ultimately, each query will have a series of calculation run internally to its data and then there will be calculations involving all 11 queries.   All calculation will be mathematical on  number fields only.

Report Question

All information is to be delivered in a printed report by PS-ID.  I am hoping that all information from the 11 queries similar to that can be consolidated on two 8.5x11 sheets.

I eagerly await your response.

Timothy
PSAssm011.accdb
0
 
LVL 21
ID: 39279916
Timothy,

Took a quick peek at your new database.

I mistakenly thought I understood the logic of the tables you setup, but I realize I am lost.
Based on your new table tblTotal I would agree. Unfortunately it is still a spreadsheet and not a relational database.  It does not appear to be  based on what I was trying to recommending or my example..  I was expecting something like the  tblMasterAttributes  in my example.

First is the  table (tblTotal) what you expected as a starting point?
Unfortunately not what I was expecting. Your new table tblTotal still has the same design issues (repeating fields)  as your original design. It should look like the example table tblMasterAttributes I created.

Based on your naming,  I would have rename my table  tblMasterAttributes to tblMasterTotal and you are good to go.

NOTE: This is a difficult project to learn how to design a properly normalized relational database. Not just Access, but with any relational database.
0
 

Author Comment

by:TimothyA
ID: 39281150
Coach,

Thank for your quick reply.

I will try to digest all you have said.

Timothy
0
 

Author Comment

by:TimothyA
ID: 39282886
Coach,

As I try to rap my head around relational data, I noticed in your "PSAssm -HiTechCoach" database that table  "tblMasterAttributes"  is not linked in the relationships view to any of the other tables.  It seems to me, this may be very important to my coming to a clear understanding of the work you have accomplished.  My present thinking would not have it stand alone.  Can you shed some light on this issue.

Again thanks so much for working with me.

Timothy
0
 
LVL 21
ID: 39283064
Timothy,

Sorry abut that. I just forgot to create the relationship.

It should look like this
relationships
I included the  Categories tables to illustrate how to normalize data. You could create a lot of look up lists in a separate table for each list. A better way is to put them all in a single table. Take a look at these tables. You create separate queries, not tables,  for each list as needed. To add a new list, you only have to edit data.  The lists do not have to be related to each other to be in the same table. One list could be a name prefix like Mr., Mrs, etc.  Another list may be  equipment statuses. Another Building statuses.  What they all have in common is the data structure.

The idea is to apply the same design used for categories lists to your Master Totals.

I also attached an updated version.
PSAssm--HiTechCoach-B.zip
0
 

Author Comment

by:TimothyA
ID: 39284146
Coach,

A curiosity question?

What does the "LU" in "tblLUCategories", and "tblLUCatergoryList" represent?

Again as always thanks for your help.

Timothy
0
 
LVL 21
ID: 39284790
I prefix all my Look Up tables with LU for easier identification.  It is my preferred naming convention.
0
 

Author Comment

by:TimothyA
ID: 39285818
Thank you for the explanation of LU.  Sounds great to me.

Mythology question:  As I try to move all the current table information into a rational data system I am asking my self how to best proceed?  I am thinking as follow:
1.  Add descriptions in "LUCatogries" for each of the 11 divisions listed in original separate tables but now residing in the "tblTotal" of "PSAssm011".

2.  Add "tblTotal" field names to the "tblLUCategoryLists". Less any ID names.  I will then have to assign "LUCatListsKey"s  along with "LUCatListsSort" values.

Please let me know your thoughts on how best to start.

I will wait till I here back before I consider anything further.

I am under the gun to develop reports as mentioned earlier.

I greatly appreciate your help.

Timothy
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

708 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

13 Experts available now in Live!

Get 1:1 Help Now