Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 2007 Report

Posted on 2014-03-13
5
Medium Priority
?
282 Views
Last Modified: 2014-06-11
Hello,
This inquiry is related to a travel business and the costs that are being reported.  In one report we capture the total insurance field.  On a second report, the cost summary report, we capture the total cost of a trip - including airfare, hotels,etc. This total cost doesn't itemize the details, but just provides a total minus any payments made by the customer.  However this summary report doesn't include the total insurance.  So when a customer has made a payment toward the trip, the difference showing is the insurance paid.

For example -
Total cost   = 1,000
Total payments = 1,200

Balance due =(200)

In this example the customer's total payments also included the insurance.

My question is how can I capture the cost of the insurance as part of the Total cost so that this report balances out correctly?  I would classify myself as a basic user and struggle with reports.  But it seems to me that I have to find where the total cost is captured and find out what it's components are so that I can add the insurance component.    My question is two fold -

1.  Normally where could I look to find the Total Cost (again including various fees, and expenses)?  

2.  Once I find where the Total Cost is, how could I add the insurance amount to it?
0
Comment
Question by:Sabealgo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39928744
Your questions are highly dependent on the structure of your own database -- which we cannot see unless you upload a sample copy.

<<  Normally where could I look to find the Total Cost>>

Total cost is typically not something that is stored in databases.  You can calculate it on an as needed basis in forms and reports.  In a report, you would set the recordsource property to the correct table or query, and then place a textbox in the report headed or footer with it's control source set to something along the lines of

= SUM(YourCostField)

Open in new window


The exact syntax can vary based on the table structure in your database.

<< 2.  Once I find where the Total Cost is, how could I add the insurance amount to it?>>

You can combine things in the control source of a textbox as follows:

= Sum(YourCostFIeld) + SUM(YourInsuranceField)

Open in new window


... again highly dependent on your own database.  If possible, please attach a sample database in your next comment.  No data needed, if data sensitivity is an issue... just the tables, forms, reports, queries involved so that we can see the structure.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39928752
Awesome answer, Miriam ... I was at a loss where to start with so little information :-)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39928811
Thanks, Graham :)
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 39928857
Certainly, not knowing the details of your database makes targeted help difficult.  

I think the key in solving the problem is a better understanding of reports.  First, make a copy of your database before you start looking in the design.  AND, look in the copy you made to protect you from making accidental changes!

Reports are based on queries.  The two reports you mention both have a query that provides the records with the costs.   When you look at the report in design view, you can open the property sheet.  In the top, use the combo box to chose the Report as seen in the picture below.  Open Report Properties
Click on the data tab, then click on the button with the ... to the right of the record source.

This opens the underlying query used in the report.   It sounds like the query used to total costs against payments is not including insurance or is excluding the insurance costs.  Compare this query against the one used to provide insurance costs.  It may better illuminate your solution.
0
 

Author Closing Comment

by:Sabealgo
ID: 40127505
Thank you for the feedback.  I tried to get additional information regarding the structure of the database, but ran in to a wall.  This solution helped.
Sorry for the delay in resolving.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

604 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