Create relationship report access 2007

Document and print your database design - Access

create relationship report access 2007

Learn how to save Access table relationships schema and other design You can create a report that displays the relationships in your database as they. Print extended information about each field in the Relationships Report in your In Access , click the Create ribbon, and choose Module in the Other group. Actually, it's the same feature that allows you to create a relationship. Here's how to create Access can automatically generate a report based on your diagram.

You may not see this page of the wizard if you are selecting data from a single table. Click a field you want to group by.

Creating a one to many relationship in a Access 2013 College Database

Click the right-arrow to select a field; click a field and then click the left arrow to deselect a field. Use the up- and down-arrows to change the order of the groupings. If you are only using one table, this may be your first opportunity to select a field to group by. Repeat steps 3 and 4 for each field you want to group by.

Sort and summarize By using the Report Wizard, you can create up to four levels of sort. Access sorts the first level, and then sorts the second level within that sort, and so on.

Create a relationship - Access

If you have grouped your data, you can summarize it by displaying the sum, average, and minimum or maximum value for each numeric field. You can choose to have your report display just the summary data or each detail line and the summary data. There is also an option that allows you to display the percent the sum of each group is of the grand total. All of the fields in your report may not fit on a single page.

create relationship report access 2007

You can have Access automatically adjust the size of the font so that every field fits. Click the down-arrow and then select the field you want to sort by. Click the button to choose ascending or descending order. Clicking the button toggles between Ascending and Descending.

  • Create a relationship
  • Document and print your database design

You can sort up to four levels. Click the Summary Options button. The Summary Options window appears. Click to select the summary data you want.

create relationship report access 2007

Click to select whether you want detail and summary data or if you want summary data only. Click if you want to calculate the percent to the total for sums. The Summary Options window closes.

How to define relationships between tables in an Access database

Layout and orientation You can choose the layout and orientation of your report. You should make sure that the Relationships window looks the way you want before you generate the report.

create relationship report access 2007

Open the Access database that has relationships you want to print. On the Database Tools tab click Relationships. On the Design tab, in the Tools group, click Relationship Report. A report that depicts the Relationships window opens in Print Preview mode. To make any adjustments to the way your report will print, use commands on the Print Preview tab. On the Print Preview tab, in the Print group, click Print. Print the design characteristics of database objects You can use the Database Documenter to print the design characteristics of database objects.

OpenRelReport then returns the name of the last report that was opened, i. Back in our main function, we can now loop through the controls on that report, and find the list boxes.

create relationship report access 2007

The attached label of the list box has the name of the table in its Caption. The attached label is Controls 0so we will set a TableDef to the name in that caption, and we can get all the field information we need. Unfortunately, this fails if the name of the table in the Relationships window is an alias.

This happens when you have multiple copies of a table in the window - for example so you can join a table to itself, or create multiple joins between a pair of tables.

Relationship Report with extended field information

If we strike an alias, we are happy to just leave it displaying the default information, since there is probably another copy of the table that will have our extended details anyway. Since we anticipate errors on setting the TableDef, we call a separate routine to run that line.

We pass TdfSetOk the current database more efficient than reopening it every callan uninitialized TableDef variable so the subroutine can set it and pass it back, the list box so it can get the caption from the attached label, which could theoretically error as well if it had no attached labeland a string to append any error message to. If the function succeeds, it returns True, so our main routine tests that before operating on the returned TableDef variable.

Back in the main routine, we now pass the initialized TableDef variable to DescribeFields. This function will return a string of delimited values, suitable to use as the RowSource of the list box.

Microsoft Access: Relationship Report with Field Information

DescribeFields loops through the Fields collection of the TableDef, examining each one, and building up the string to return. We skip fields that have the dbSystemField attribute set used for resolving conflicts in replicated databases since Access skips these in the Relationships report, and we need the lines between tables to join up to the correct field.

The Select Case block examines the field's Type, and assigns our codes. Access uses the field's Attributes to distinguish between an AutoNumber and a Long Integer, and between a Memo and a Hyperlink, so we examine those in the appropriate cases.