For my report, I am trying to generate monthly fees of $75 per million people of population and rounding up to the dollar. The report is composed of different companies in which each company has more than one city related to it. The city is listed under the appropriate company along with population data. I would like to be able to run a sum of each company's total populations for all of the cities that are related to them. And then I would like to generate the monthly fee based on that total pop. for the company, and then at report footer calculate the sum of the monthly fees.

What I have so far is the monthly fee for each company is MonthlyFee=Sum(75*([CityPop] and the total pop. per company is TotalPop=Sum([CityPop]). Those two work the way I want, but I can't figure out how to calculate the total of all of the monthly fees in the report footer. And I would also like the MonthlyFee to be rounded up to the nearest dollar.

Hi asweisman (what is your name?)

"monthly fees of $75 per million people of population "

use this same equation in your report footer -->

=75* Sum([CityPop])

"and rounding up to the dollar"

in that case, do this for your equation:

=(75* Sum([CityPop]) ) + 0.5
and set the FORMAT property of the control to:

$ #,##0

My name is Aaron by the way.
I appreciate your reply but I am still having trouble. I probably didn't explain what I need to do so well. So let me try again.
I am creating a report of the monthly fee of each company in the database. The fee is automatically calculated based on the population of the cities that the company represents.
So to calculate the monthly fee, I am using
MonthlyFee=Sum(75*([CityPop]/1000000))
and this equation does what I need. What I am trying to do is in the report footer place a control to calculate the total of all of the monthly fees. I tried
MonthlyTotal=Sum(MonthlyFee) but that returns a zero and I am not sure why. I would have no problem letting somebody take a look at the database if they don't understand but I would much rather someone contact me through email so that it doesn't publicly exist on the web.

Hi Aaron

Please post a relationship diagram of your database (JPG or PNG). Lay it out according to the guidelines in the relationship section of this document:

Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1595005
free 100-page tutorial that covers essentials in Access

we need to see your structure and relationships

Sorry for taking so long to get back to you. Here is the relationship chart.

Hi

PMFJI But your tables are NOT normalised

You have repeating Groups in Contacts table ie

ContactFNTwo
ContactLNTwo
ContactTitleTwo
ContactCellTwo
ContactEMailTwo

In your Cities table why have you these fields?

County
State
CompID

My 2 Bobs worth

Mike

There are sometimes 2 contacts per company and I don't want to create another entry when all of the other information is the same except for email and cell number. I have state and county because they are necessary for the table. CompID is what I am using in order to relate the Company table with the different contact/cities

[quote]There are sometimes 2 contacts per company and I don't want to create another entry when all of the other information is the same except for email and cell number. [/quote]
And that limits you to a max of 2 contacts. If you set it up as normalized you can add as many, or as few, as necessary and then when pulling information it is easy, but if you need to pull information later with your current setup it is not a simple matter.

[quote]
I have state and county because they are necessary for the table.[/quote]
No, you would store the County ID in the cities table and State ID in a Counties table.

[/quote] CompID is what I am using in order to relate the Company table with the different contact/cities [/quote]
You do NOT store CompID in with the cities table. You store the CITY ID in the COMPANIES table.

Can you please explain why CompID should be in the cities table as opposed to CityID in the Companies table? I don't understand what the difference is.


That is actually a good point for me limiting my contacts. I didn't think of that. So I would just get rid of the duplicate info like
ContactFNTwo
ContactLNTwo
ContactTitleTwo
ContactCellTwo
ContactEMailTwo
and then add a new contact even though he may have the same company info as someone else? How would I configure that table to be able to draw company info such as CompName and all of the other attributes of the Company table and apply it to the contacts table?

I know these are probably basic questions and I have tried to read as much as I can but without someone real to tell me what is wrong, it is hard for me to do everything right so please humor me and try to explain as much as you can when responding.
Thanks,
Aaron

I said CompID should NOT be in the Cities table. CityID should be in the Companies table. If you put the CompID in the Cities table (that is what you showed) then you would have the city name listed many, many times - one for each company, or you would only be able to have one company associated with a city and I'm sure you don't want that).


Your contacts would be added as a separate table:

ContactsTable
ContactID - Autonumber (PK)
ContactFName - Text
ContactLName - Text
ContactPhone - Text
ContactCell - Text
CompanyID - Long Integer (FK from Companies table)

etc.

Try to read Crystal's Normalizing Data document to try to understand how normalization works.

You are right, I mistyped and I meant to say should not. What I would like is for both the company to be associated with more than one city, AND each city can be associated with more than one company.



I am still not clear what to do with the current contacts table that I have. Since there are very few company entries that actually have a second contact, I could easily just delete the duplicate values in the original CONTACTS table. But do I make another table for the info above, and if so, how would I relate this new table to the CONTACTS table?



I read the article and I appreciate the link. Most of it I have read before in different places, but it gets more difficult when you try to put all of those ideas into action.

I have attached a slightly revised copy of the relationship page if that helps at all.
Thanks for all the help,
Aaron

rather than ContactID in the Cities table, you should put CityID in the contacts table. Why do you have a table called 'ContactTable' and another one called 'Contacts' ? The Contacts table should have all the Contacts -- if you want to rank them, you could put a field in there to do so

... Likewise for CompID and CityID unless you have companies in multiple cities, in which case you would need a cross-reference table with CompID and CityID -- I would make an Addresses table and put CityID there

What is the purpose of the 'CompanyL...' table?

There are two pieces of info that I need to track for each company in relation to cities. Every company has a physical address where their office or pobox is located. I also need to track every city that the company services. This could be up to 100s of cities in some states. What is a crossover table and how do I set one up?

Hi Aaron,

in that case, to track the Cities that a Company serves, you should set up a table like this:

CompanyCities
- CompCityID, autonumber -- PK
- CompID, long -- FK to rompanies
- CityID, long -- FK to Cities

you would, in this case, probably also want a unique index on the combination of:
CompID
CityID

so that combination would not be duplicated

PK = Primary Key
FK = Foreign Key

You only want each company to be ONE time in the Companies table. If you have multiple contacts, you need a related table to track them

You only want each city to be ONE time in the Cities table.

~~~

but this brings up another issue ... what about suberbs? If someone lives in Littleton, Colorado, the City that serves them would be Denver...

you can often use the first 2 characters of Zip Codes to identify a major area -- and then test a range on the third digit (or use Lat/Long -- if you want formula to calculate distance, ask, I have one). I have compiled a database with a Zip Codes table containing 42K+ records you can download here:

http://www.accessmvp.com/Strive4Peace/Reference/Index.htm

If you do use the Zips_US table, I would recommend linking as opposed to importing. The Zips_US table also has Counties as well as Lat/Long, which I would assume is for the post office