Database setup... 1

[jerimy75]

I posted on here yesterday, and it seems like I may have set up my table wrong, so now I am trying to seperate into three different tables.

One will include project number, description, project file location, as builts on hand, and hard copy on hand.

Table two includes project number, type, and type2.

Tabel three includes project number, and 16 fields for streets.

My first question (I will have many), is how do I link the tables so that information entered on a form or a complete table which includes all of the above will go to all of the seperate tables. I have the project number selected as the primary key on all three tables and have tried to link the tables from the 'get external data, link tables menu' but now I can not input any data into my form. I would eventually like to preform searches from a form using either one or multiple search criteria, and have it return all matching files. I am a begginer, so if you can help please use laymans terms. Thank you in advance.

 

[grnzbra]

I believe there is a limit to how many tables can be joined in a query an still be able to update the records, and it is not a large number. Perhaps you could use a form and sub-form.

16 fields for streets? Please elaborate about the nature or each of these fields.

In table 2, what are type and type 2? Does this table have only the three fields?

 

[jerimy75]

This is for projects and blueprints that date back to the early '70's. I am trying to set the database up as a way to track, list, and search for projects.

Say for instance that we were going to do a Water Main project on street A, I want to be able to search not only for all Water Main Projects (type, and type 2) but also for any projects that deal with street A. Or only those projects that deal with Water Mains and Street A.

The type and type 2 include: Water Main, Water Major Facilities, Sewer Main, Sewer Major Facilities, Government, Streets, Parks, and Refuse. The reason there are two is because sometimes a project will include two types, ie. Street overlay/ Water Main reconstruction.

The large number of streets included is because sometime if we work on a main street, many side/ residential streets are affected even though they are not the main focus of the project.

For table two, yes there are only three fields: Project Number, Type, and Type 2. I included the Project Number on all tables because I thought I could use this as a link so that if I input a new project on a form, the information would disperse to the correct tables and subsequent fields.

Am I way off here? I realize I don't know much about Access but am I on the correct train of thought? Thank you for any help you can offer.

 

[ad2]

I don't know if you need a type and a type two. If a project has more that one type of repair - Street overlay/ Water Main reconstruction - wouldn't you have a Project table, a Type Table and then a join table? That way a project could have many repair types?

 

[JeremyNYC]

I do think you have a long way to go before you get the model right. It sounds like a very complex model, actually. I would do a bunch of reading on the subject of data normalization. I've posted Paul Litwin's "Fundamentals of Relational Database Design" on my website, because I think it's a great place to start. There are manyother things to read.

But you are going to want to get rid of repeating values, as ad2 pointed out for types, and also for streets. What happens if there's a third type? Or a seventeenth street? You have to redesign your table, your queries, your forms, and your reports. Bummer.

If this is a first project, it's a very big one. Definitely do some reading, as you don't want to get into building other objects until you've got your table structure nailed down.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[grnzbra]

You would want one street field. If you have a project that has "A Street" in the first street field and another project that has "Flork St." in the first street field and "A Street" in the second street field, and you wanted a list of projects that involved "A Street" you would need a query that had a WHERE clause that looked like:

WHERE Street1 = "A Street" OR Street2 = "A Street" OR Street3 = "A Street" etc for 16 fields.

A better way to do it would be to have a streetname table, the project table and a ProjectStreet table. The ProjectStreet table would have 1 record for each street involved in any give project. It would consist of ProjectID, StreetNameID. Then, using a query, you could join the project table and the street table through the ProjectStreet.

 

[jerimy75]

Thanks for your imput all. And yes this is for the most part my first project using Access, so I suppose I will just have to read up as JeremyNYC suggested. Once I do that and try to tackle this project again, you might be hearing from me. Thanks again!

 

[ad2]

One of the best places to start is the North Wind sample database that comes with Access. Start by looking at the tables. You can study the Order Details join table between Products and Orders.