Table Design Question

[Guest]

We have an existing "Claims" Database. I just created a report that shows
Shipper Claims per year. I have a Totals Row which shows the number of
claims per year, the Total Claim Amount and the Total Amount Paid on the
Claims.

I want to add Gross Revenue (per year) and Gross Profit (per Year) so that I
may do a percentage calculation of the claims vs. GR and GP. Can anyone
suggest the best way I might do this? Do I have to add a table for each
shipper? Or, can I just put the GR and GP numbers right into the query the
report is based on and calculate from there? That would probably be the
easiest. If so, any suggestions how to do this?

Thanks!

 

[John Vinson]

We have an existing "Claims" Database. I just created a report that shows
Shipper Claims per year. I have a Totals Row which shows the number of
claims per year, the Total Claim Amount and the Total Amount Paid on the
Claims.

I want to add Gross Revenue (per year) and Gross Profit (per Year) so that I
may do a percentage calculation of the claims vs. GR and GP. Can anyone
suggest the best way I might do this? Do I have to add a table for each
shipper?

Certainly NOT!! Storing data in tablenames is *never* a good idea.

Or, can I just put the GR and GP numbers right into the query the
report is based on and calculate from there? That would probably be the
easiest. If so, any suggestions how to do this?

Without knowing your table structure it's very hard to say. I suspect
you may have fallen into the very common trap of designing your
database backwards - focusing on the report first, and then figuring
out how to structure the tables. Neither gross revenue nor gross
profits should be in your table AT ALL - that's what totals queries
are for!

What *is* the structure of your tables? Are you familiar with the use
of Totals queries, and/or the DSum() function?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks for responding, John. Unfortunately, I didn't set up this database
and now find myself trying to get it to give the information we're seeking.

How do I determine what the "structure" of the database is? While I'm not
overly familiar with Totals Queries or the DSum() function, I'm not sure
either of those will help me if the data doesn't exist anywhere in the
database. Would it be better for me to convert the report to a PivotTable
and then add columns showing total revenue for the year?

 

[John Vinson]

Thanks for responding, John. Unfortunately, I didn't set up this database
and now find myself trying to get it to give the information we're seeking.

How do I determine what the "structure" of the database is? While I'm not
overly familiar with Totals Queries or the DSum() function, I'm not sure
either of those will help me if the data doesn't exist anywhere in the
database. Would it be better for me to convert the report to a PivotTable
and then add columns showing total revenue for the year?

The structure of the database is available from Tools... Analyze...
Documentor, if you aren't comfortable with the Tables and Queries
windows. Essentially, I'd need to know what tables you have in the
database, and some indication of what those tables contain. Only with
that information would I be able to make any cogent suggestions about
how to calculate these sums.

Any summing operation - totals for the month, totals for the year,
etc. - should be done in a Query (or, sometimes, in calculated fields
on a form or a report). Totals should almost NEVER be stored in
tables. It's a very common error to assume that "if this field isn't
in the table then I can't put it on a report" - but it is an incorrect
assumption!

To create a Totals query, create a query using the table containing
the values that you want to sum. Change it to a Totals query by
clicking the Greek Sigma icon (summation sign, like a sideways W).
You'll be able to Group By, Count, Sum, Average, etc.; see the online
help for Totals for examples.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks again for your response. The problem with the "totals" is that the
Gross Revenue is found in a whole other program and is not tracked on a per
shipment, per week, per month, per year basis in Access. Do you think it
would be better if we just exported the report to Excel and analyzed that way?

Teri

 

[John Vinson]

Thanks again for your response. The problem with the "totals" is that the
Gross Revenue is found in a whole other program and is not tracked on a per
shipment, per week, per month, per year basis in Access. Do you think it
would be better if we just exported the report to Excel and analyzed that way?

<moderate frustration>

I have no idea.

Since you have only said that the Gross Revenue "is found in a whole
other program", I have no trace of an idea how you could obtain that
information for display in an Access report. I'm all but certain that
it *can* be done, but since you have not provided any useful
information about how it can be done, all I can say is... "I don't
know".

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Doug Munich]

So Gross Revenue Per Year would be the sum of some field like Premium
Paid over the year, and Gross Profit Per Year would be that total of Premium
Paid minus the Total Amount Paid (or Total Claim Amount)? And then you want
to see what percentage of your Gross Revenue Per Year each of your
Supplier's Revenue Per Year is?

I don't know if this is the best way, but one way that seems to work is
below. I'm using the Amount Paid field as an example. I assume you already
have Amount Paid by Year and by Supplier with subtotals.

First, add a report footer to the report and put a textbox there that
shows the sum of the Amount Paid field. Name this text box something like
txtGrandTotalAmountPaid. Then on a section header or footer (for example
for Year) you need a textbox that is the sum of Amount Paid for that
section - call this txtYearlyAmountPaid. Then in the same Year header or
footer you can put a textbox whose data is an expression like:

=100*CDbl([txtYearlyAmountPaid])/CDbl([txtGrandTotalAmountPaid])

This should display the percentage of the report total that that year's
amount paid is. I hope this helps.

Doug