Need help with report

[artemisia]

I'm a newbie to Access, and I have been elected to maintain and update our database at work. This past month I took a crash course in Access. Now the challenge begins and I need help.

I created a copy of our existing database and made changed to two tables…tblRatePlan and tblCompPlan. Then I imported the 2 tables, a query and a report created from a query from testing database into production database. Everything imported fine. The query worked correctly; however, the report does not work. I tried to duplicate the report in production but was unsuccessful. The output on the report contains errors in all calculated fields and does not print records.

I verified all fields from testing matched production. I verified relationships in production and noticed there was a change after importing. So I needed to re-establish relationships from (parent)tblRatePlan to tblPhoneInfo (child). When I enforce referential integrity an error message occurs and it states "Data in the table "Phone Info" violates referential integrity. Edit the data so that records in the primary table exist for all related records."

Any ideas on where to begin trouble shooting this problem or comments on what could be the problem? Your help is appreciated.

Thank You

 

[jeremie_ingram]

Sounds like one of the following...

1) One of the linked fields has to be a primary key.

2) The fields have to be identical, including data type.

3) You dont have a One-to-Many relationship established.

You cannot establish the ref integ if there are already records on the MANY side of the relationship that have no linked records in the ONE side of the relationship. These are refered to as orphaned records.

Check your primary keys. The ONE side of the relationship should have its primary key linked to the MANY side.

I would recommed looking into the help info regarding this as it goes into much greater detail. If you still have issues after checking these things out, let me know. You may want to review the previous databases relationships, or you could try importing them again and under the options make sure the relationships is check marked to include them.

 

[artemisia]

Thanks Jeremie,
I checked the linked fields and they all have primary keys.
I double checked the fields and they are identical, including the data type.
I checked the relationship before I imported and there was a One-to-Many relationship established. During the import, the relationsip was broken. I tried to re-establish the one-to-many and it gave me the above error. So....I'm still trying to figure it out.

In addition, I imported two other tables with data and the size of the database went from 39 mag to 162 mag. Any idea why this might occure?

And I thought this was going to be easy...LOL

 

[jeremie_ingram]

First, under TOOLS-OPTIONS then the GENERAL TAB make sure the COMPACT ON CLOSE is checked. Close and reopen it and check the size.

Another item, when you imported these tables, did you check the IPORT - RELATIONSHIPS box under OPTIONS in the IMPORT OBJECT WINDOW? This should have brought over everything in tact. Also make sure that QUERIES are imported as QUERIES and not tables, this could account for unexpected DB growth.

What is usggest, is create a brand new DB and import everything with the specs above. Import everything at one time to ensure that you dont break any relationships in the process, so select all tables, queries, and whatever else is there.

This should replicate everything as it should be. If not, try first importing the table definitions only and see if you can get it that way. Then you can always import the data to them later.

 

[artemisia]

I was just reading about compaction yesterday. I will check that out today. That could be part of the problem.

I did check the options before I imported.

When you import tables does Access rename them?
For example, I imported Rate Plan and Access renamed it Rate Plan 1. I deleted Rate Plan and this is when the broken relationship occured.

I know file names should not contain spaces. This one does. I never created this database. Someone else did. I am working on Phase 1 Update now. In Phase 2 Update I will change the names.

I'll do as you suggest and import everything at once. Maybe that will work.

Thanks for your suggestions.