I'm totally stuck with my design and don't understand were the problem is.

[Real Wally]

Dear Access forum members,

My apologies for this very long first posting on this forum that has been extremely useful in getting where I am now with Access (I'm using Access97). I’ve been struggling with the design of a database for a few weeks now and although at first I thought things went pretty smooth and I learned a lot I have in fact not made a lot of progress. I am very confused with the relations between tables. All tables are now normalized (I think) as far as possible and I've ended up with many tables that are in a one-to-many relationship. Several tables, like e.g. tblCities and tblCountries connect to several tables where I need to included addresses. All these tables have look-up values. When I do queries now I do not get the results that I expect. I retrieve too many files from some tables but miss others so obviously something is not correct. I've been going through lots of questions and answers on this forum and although many things are slowly beginning to make sense I haven't been able to distill the solution to my problems. Is there someone willing to help me on my way?

I'll try to give a better description of my database. As you may understand from the tables listed below, an application will give rise to several publications, the agency dealing with an application is not necessarily the same one as the agency dealing with the publication. To further complicate things, we'll use multiple agent from the various agency, each publication is normally handled by only one agent though. Did I manage to confuse you too? There are more tables, but I think that if I get the relationships between the ones below to work correctly I can use that as a model for the others too.

If I do a query on e.g. publication-agency-agent I want to see which agent from what agency deals with what publication. What I see however is only a partial list of publication, listing every agent that I've got on file for a particular agency where only one agent actually handles the publication. If I set my join so that all publications are included and only those agents that have a matching record I get a join error.

My question is: What do I do wrong and How do I solve this?

Descroption of (some of ) the table below to illustrate the problem:
(Primary keys in bold)

TblApplication
ApplID(autonumber)
Application#
Applicant
Address
City
Zip
Country
Telephone
Agent

tblPublication
PubID(autonumber)
Publication #
Application
Agency
Agent

TblAgency
AgencyID(autonumber)
Agency
Address
City
Zip
Country
Telephone
Agent

Tbl Agent
AgentID(autonumber)
Agent
Agency
Telephone
Email
Fax

TblCities
CityID
City

TblCountries
CountryID
Country

 

[neileg]

Just a quick look, but the following points stick out.

You have agents in your agency table and agencies in your agent table. Assuming each agent only works for one agency, there is redundancy here. You need to hold the agency key against the agent, and that's all.

Similarly, you have the agent and the agency against the publications. Do you need this? Do you have to cope with a situation where the agents move around the agencies and need to track these separately?

Why do you have a City ID and a country ID? I can see why you might hold a city and country table for validation, but you don't use the IDs in any of your tables.

 

[Real Wally]

Thanks Neil,

City and Country IDs are now removed. I didn't understand that either but in the examples I saw a(auto)numberID was always included. I assumed I needed something like that.

Your correct in your assumption that one agent works for one agency. Don't I need a field in each tabel links an agent to an agency?

I also need to know what agency and what agent handles a publication. I see now that identifying the agent will also identify the agency so I can probably remove agency from the publication table but don't I need to identify the agent in the publication table? How else can I pull that info out in a query?

Thanks again

Walter

 

[neileg]

I still haven't had time to give this a lot of thought so this is 'off the cuff'. OK, here goes.

If you need to link the agent to the publication and you have the agent linked to the agency, then so long as your query brings in all three tables, you can see the relevant agency. What I meant was that you don't need both.

Your TblAgency contains an agent field. This is wrong, since the agency to agent relationship is one to many. You need to link the agent to the agency, not the other way round.

By the way, do you need a separate city and country table? I presume you want to distinguish between Newcastle, England and Newcastle, Australia. I think that suggests that you should keep the city and country in the same table. Even if two cities have the same name, they are different. I know this means repeating the Country data several times in the city table, but the overhead compared with repeating a country id is negligible.

 

[Real Wally]

Neil,

What you say about the agent field makes sense. I'll give it a try later on this afternoon.

The reason I've got cities and countries in seperate tables is that otherwise I'd be creating many-to-many relationships and as far as I've undertsood now those should be avoided. Or do you mean to put city and country together in 1 field?

Thanks again.

Walter

 

[neileg]

What do you want out of your City and Country tables? What exactly do they do for your system?

I think they are just lookup tables that ensure consistent spelling and naming. Eg, I work in Newcastle, but in reality it's Newcastle upon Tyne. That matters because there is more than one Newcastle in England and I wouldn't want to confuse it with Newcastle under Lyme. So I might want a City table that lists both Newcastles and forces the user to pick the right one. Similarly I want to distinguish between Birmingham, England and Birmingham, Alahbama.

So I think you're using these tables to populate the City and Country fields. The relationships are not relevant, and don't in truth exist in db design terms.

If you are going to base a query on your City field, and you have populated this with a name, eg Birmingham, then to ensure this is a unique identifier, you will have to bring in the Country, too, otherwise you will get Birmingham England confused with Birmingham Alahbama. You could have a combined City, Country table and assign a unique ID to each city. Populate your address fields with the ID and look this up each time you want to use it.

I've rambled on a bit here, but do you understand the difference I am making?

 

[Real Wally]

You're right that I'm just using these cities and countries tables as lookup fields. I won't be using them in a query. So, if I can get away with not having a 'proper' relationship between tose two and other tables than that's fine with me. In my inexperience I just thought it was sloppy planning not to have all those relationships nicely as a one-to-many or one-to-one.

About your previous suggestion to remove the agent from the agancy, that's done and seems to work fine. One problem however that still remains is that I'm unable to put data in some of the fields of forms based on this query. Is there a solution for that? If I make forms based solely on the tables I have a problem with referential integrity, I cn't save tables becomes data is missing in fields of other tables so I've got to open those, add the data only to find out that the same problem exists in reference to yet another table. How do you work around that? I had assumed that a table based on a query would be tha answer, but obviously it isn't.

Hope you have some more suggestions,

Walter

 

[neileg]

Base your forms on a query that brings together all of the fields you need.

Sorry, must crack on, got a deadline to hit!

 

[Real Wally]

Unfortunately that doesn't do the trick. I must have another flow in the design than?

I'll continue to work on this over the weekend. Maybe someone else has something to say over what has been discussed already?

Anyway Neil, thanks for your input. I'm glad I've got at least a few things sorted out so far and I'm sure with a little help from this forum I'll get it working one day!

Have a nice weekend,

Walter

 

[neileg]

OK, met my deadline.

For your referential integrity to work, you must establish a relationship between the tables in the query. You can either do this using the relationship manager, or you can set this up in the query.

So, if your form is based on a query that uses, say, Application and Agent tables, you need to link these on the agent field, which I guess will be the one called agent in the Application table, and the AgentID in your Agent table.

Does this help?

Going home now!

 

[Pat Hartman]

TblApplication
ApplID(autonumber)
ApplicationNum
Applicant (If this is a person, the fields should be -Prefix (Mr,Mrs,Miss,etc), LastName, FirstName, Suffix (Jr,Sr,etc.))
Address1
Address2
CityID
Zip
Telephone
AgentID

tblPublication
PubID(autonumber)
PublicationNum
ApplID
AgentID

TblAgency
AgencyID(autonumber)
Agency
Address1
Address2
CityID
Zip
Telephone

Tbl Agent
AgentID(autonumber)
Prefix (Mr,Mrs,Miss,etc)
LastName
FirstName
Suffix (Jr,Sr,etc.)
AgencyID
Telephone
Email
Fax

TblCities
CityID
CountryID
City

TblCountries
CountryID
Country

I renamed some of your columns to better conform to standard practice. The relationships should be established in the relationship window by drawing join lines between columns of the same name in different tables. For example, the city and country tables should be joined on CountryID. The application and publication tables should be joined on ApplID. Using a consistant name for the foreign key makes it easier for people to understand your relationships. This doesn't mean that ALL instances of a common name are joined. For example, tblApplication has a relationship with the city table using CityID. tblAgency also has a relationship with the city table using cityID. That doesn't mean that tblApplication and tblAgency have a relationship based on CityID.

 

[Real Wally]

Dear Pat and Neil,

I have made some changes using your suggestions and now have tables that I think are OK. I can extract all the information I need using queries view the data on forms.

The part that still doesn't work though is to add new data in these forms. I've found posts from others that give various solutions. Some say never to base your forms on a query, others say exactly the opposite and recommend to use forms based on tables that are derived from action queries and automatically update the linked forms. How that's doen is unclear to me.
I've tried to fill in the forms based on 'action tables' but cannot save them because fields in other tables that are requiered miss data.

Could you please let me have your views (and possible solutions) on this?

Cheers,
Walter