advice needed on table structure and userforms 2

[Kateyboo]

I am attempting to design a database in access 97 for the first time and have run into some difficulties.

The general idea is that users can report a defect they have discovered and any maintenance they have carried out. These are individual events but in order to report maintenance the user must search for the corresponding defect record first, by MotorNumber and DateReported.

So far I have created two tables, Defects and Maintenance, and a userform for the input of a defect. The user enters the table number and date, then selects up to three defects from drop down boxes.

All of this information is automatically being entered into the Defects table but how do I get it to go into Maintenance aswell? Is this the best way to go about the problem? If the information is put into the maintenance table then I can run a query to bring up the required fields on the Maintenance userform so that the user can type what maintenance they carried out next to the corresponding defect.

Ideally though, I would like the user to be able to report maintenance in one of two ways. If a defect has been discovered and fixed simultaneously, then it would be better for the user if on the Defect report userform there were a checkbox next to each defect saying “Fixed Yet?” and if they click it then a popup will come up just asking for what they did. Then all of the information would get put into both Defects and Maintenance tables. However, if the defect is left and maintenance is carried out at a later date, then the original defect needs to be searched for and this will require a separate form.

I am hoping that this is a nice simple problem for all you clever lot but not too beneath you to help me!

 

[Darrylles]

Hi,

You need to read up on how to design a 'relational' database.

There is no need (nor is it efficient) to duplicate Maintenance info. in the Defects table.

Each Defect should simply 'point' to a Maintenance record.

You may find someone kind enough to define a set of tables for you here, but it won't help you in the future if you don't learn about it so that you can do this yourself.

There are a lot of books on relational database theory and hundreds of sites on the Net.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Kateyboo]

Thanks very much for your help. I think I see what you are saying - in the maintenance table I should just have fields for the primary keys (motor number and date) and the maintenance performed on each defect. Rather than repeating what the defect was. So, this is what I think I should do:


Defects:

Motor Number Date Defect1 Defect2 Defect3


Maintenance:


Motor Number Date Maintenance1...2...3...



If you can see any obvious problems with this please let me know. Once my database is relational I would like to perform a query that will generate a defetcts report that will say whether or not each defect has been rectified rather than listing the actual maintenance performed. Can this be done by checking to see if anything has been entered into the corresponding maintenance record to the defect record?

Thanks again for pointing out 'relational' to me - I have now found quite a few good sites specific to helping me with what I want whereas I wasn't finding any good help before.

Cheers!

Kate

 

[JeremyNYC]

Kate,

You've got repeating fields in your first table (defect1, defect2, etc.). What happens when there's a fourth defect? You've got to redesign your table and all of your forms. No good. You need a separate table for defects and a lookup table between that and the first table. That table will hold just the primary key of the each of the two other tables. This forms a many-to-many relationship.

Check out "Fundamentals of Relational Database Design", by Paul Litwin. I recommend it so often I put it up on my website.

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.

 

[Kateyboo]

Thanks for your reply Jeremy,

That article was really helpful - I had no idea you could break down tables into so many smaller tables. But I can see why it needs to be done now. I didn't realise that you don't have to have everything you want to display in one report in the same table. Oops, silly me.

Anyway, I'll give it a go!

Thanks again,

Kate

 

[Darrylles]

Hi again,

Good job Kate - really glad to see your keen interest, i.e. you're not just after a quick or total solution.

I (possibly Jeremy also?) am more keen to invest more time and effort to help people like you.

I'll help anyone, but some will get me engrossed in the problem if I think they are interested in HOW TO GET to a solution rather than just solving the problem NOW.
Implementing a solution is easy, learning how to get to the solution is the way to learning how to solve future problems without help.

After saying that - I'm always here asking for help. (But only after plenty of research).

Keep it up Kate.

Kind regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk