Relationship Advice

[Sadie Hewgill]

My database has a table called part numbers with a list of all part numbers, as well as the type of part. The part number is the primary key and should only correspond to one part. Then I have two (and eventually there will be more) tables that contain information for each part type. One is called Fasteners_mm, and the other Machined Parts. The part number is how these tables relate to the Part Numbers table. I have attached a picture of the relationships. There is another table, Part Type, which is just a lookup list of the different types of parts (Machined Parts and Fasteners_mm). I am not sure if I will keep it, so far it doesn't seem to be helping at all.
So my problem is that I want to prevent the same part number from being entered in both fasteners_mm and machined Parts. My form set up doesn't allow for this, but if someone were to edit the tables, there is nothing stopping them from changing the part type in the Part Numbers table so that it doesn't correspond to the right type in either of the other two tables. They can also go and enter a part number in fasteners_mm that is already in use in Machined Parts. How do I stop this?

 

[Dairy Farmer]

My form set up doesn't allow for this, but if someone were to edit the tables

Don't allow the users access to the tables.

Suggestion:
Split your DB into a FE and BE.
Password protect the BE. (if someone does find it they can't open it)
Link the Tables to the front end. It will ask for the password to link.
Disable Shift At Startup.
Hide Navigation Panel.
Set a startup form.
Make your FE a mde or accde or accdr.

 

[Sadie Hewgill]

I would like to design the rules right into the relationships if I can, but I will probably do the FE and BE thing anyways after I am done designing. My form set up won't allow duplicate part numbers because it auto generates the next one, and I have set the type to default. Still it would be nice to give the user more freedom to directly edit the tables. Any ideas how to do this?

 

[The_Doc_Man]

Still it would be nice to give the user more freedom to directly edit the tables.

All hope abandon, ye who enter here!

The flaw in your assumption is that your users know anything about (1) databases, (2) safe editing, (3) maintenance of relational integrity, (4) observance of constraints, (5) your design goals and viewpoint of how the data should really be used.

Assuming a 50/50 shot for each of the five factors, that is (1/2)^5, which is 1/32, or about a 3% chance of any single user doing the right thing.

Now multiply (actually, divide) those odds by your users. With a mere four users (not counting yourself) the odds that everyone does the right thing all the time is now 1/128 or less than 1%. If you really want to play those odds, I want to meet you over a poker table.

The fastest way I know to whack the bloody hell out of a database is to let users at the database window. Then, if you don't have an FE/BE split to go with it, let's add the probability that two users will try to do something similar at the same time and encounter lock issues leading to a deadlock. The moment that happens, you have to kick everyone out, do a compress and repair your DB, and HOPE you can recover enough of it to make it work again.

Dairy Farmer gave you excellent advice. I second his opinions.

 

[Sadie Hewgill]

Alright, I will give up on trying to make the database fool-proof. But right now, it doesn't have any real data in it besides test data. At what point in my design should I be splitting it into FE and BE? There may still be lots of fixing to do after I think it's up and running.

 

[Sadie Hewgill]

Also, if I am doing a query by form to allow the users to find records, it shows the results in table form. I should lock this so that they can't edit the tables right? But then I do want users to be able to edit data somehow. Is there a way to direct them to the form for editing the record they select?

 

[The_Doc_Man]

Once you allow query-by-form, you are still stuck in a cleft stick of your own making. A query made through the design grid is potentially writeable with few or no viable ways to stop it, particularly since MS got rid of workgroup-level security in Access.

There is no substitute for doing the research required to determine ahead of time what your users will want to see and pre-building that. Then put it all behind a switchboard and open the reports (NOT queries) via command buttons.

 

[Sadie Hewgill]

I do want the user to be able to edit data somehow in a controlled way. How do you suggest I do this? A report will just show the data. Is there a way of letting the user enter what info they want to see displayed in the report?

 

[accessfleet]

Hi Sadie:

Part number(s) are something I know about. (Fleet Maintenance 30 years worth.)

You may wish to consider that is is frequently possible to have different parts with the same part number. One way to uncomplicate this to allow same part number, but different vendors or manufacturers. Mfg part numbers is a good standard to stick to. Other wise, you'll assign your own part number and then have to reference it to the original PT#

With my mechanics, they gain access to the data from forms. This keeps extrainious informations from cluttering the screen and their minds.

Splitting the data base will help control mistaken changes to the data and should be done when system is generally working error free.


Front ends limit operator errors to their front end. The developers front end remians protected. also, Front ends provide for Record entry and update. The back end controls record deletes.

 

[Sadie Hewgill]

I am keeping our own unique part number as the primary key we have this for our own organization, but you are right it can have more than one mfr number. My database refernces the mfr and mfr part number. Do you only use forms to enter data? How can I show the results of a query in table or list form, but not allow editing, then have the user click on the record they would like to edit and this will open a form at that record where they can safely edit?

 

[accessfleet]

Yes we use only forms for data enter. People get funny when the screen changes. Something often experienced in the table view. Forms also permit changing the field color for criticle data, License plate# etc. This aids in on the fly updating missing criticle information.

We work on police, fire and municiple vehicles and equipment.

The vehicle is written up and repaired a work order is generated once all the parts and labor are accounted the your order is closed. I use print preview to view the detail for police and fire I use print to PDF, which allows me to send a copy to EPD and EFD to communicate the completed repair 1 to1 (one write-up, one workorder/repair)

The other report I use is based on a query Q_workorder_history_by_unit

A prompt for unit number is followed by start date and end date prompts.

Generating a repair history also using print preview... 1 to many ( one unit many repairs)

Eventually I want to develop a report that and tie repair and maintenance cost to each department. many to many ( many vehicles to many departments)

Would this reporting structure meet your needs?

Another tact I take is to place table specific query/report buttons on the form. Mostly fo my benefit for things we frequently use.

My last method to to place reports buttons on a reports form so that specific management reports and be generated as requested. They request the report, if it looks like something they will review from time to I keep tract of it on the reports form.

I hope something here you can use...

John..

 

[Sadie Hewgill]

How do you create your prompts for reports? I haven't really looked into reports much, a query by form seems suit my needs, and I have found out how to put buttons on my query by form form that will open the form at the records that were specified. So the user can view the list of records in table view when they make the query. I just need to find out how to prevent them from being able to edit in table view.

 

[accessfleet]

SELECT WORKORDERS.RadioUnit, WORKORDERS.[Workorder number], WORKORDERS.[DPS Workorder], WORKORDERS.OpenWorkOrderDate, WORKORDERS.CloseWorkOrderDate, WORKORDERS.Instructions, WORKORDERS.[Supervisor/CrewRemarks], WORKORDERS.[Work type], WORKORDERS.[CLOSED WORKORDER], WORKORDERS.Miles, WORKORDERS.Hours
FROM WORKORDERS
WHERE (((WORKORDERS.RadioUnit)=[Enter Radio Unit]) AND ((WORKORDERS.CloseWorkOrderDate) Between [Enter Start Date] And [Enter End Date]))
ORDER BY WORKORDERS.[Workorder number];


Sadie This is the one I just finished. It didn't work exactly right but Dcrake pointed me to the fix and it works just like it should now.

 

[Sadie Hewgill]

OK, so WORKORDERS is your table, and you selected all the fields you wanted in the report by using SELECT. And the user enters the specific radio unit and the start and end date. So this code gives you a pop up message? Where do you put this code? I haven't used reports at all yet.

 

[accessfleet]

Sadie: If you go injto your form properties (data) you may be able to make the restrictions you want at the form level.

Now

The query you are looking at is in design view SQL view. When you run the query the box appears "Enter Radio Unit" then then again. The gray box just jumps on the screen

My button is at the top of my work order form along with my print preview button.

 

[Sadie Hewgill]

How can a user delete a record? I tried putting a command button on my form that could delete the record, but this failed to update my tables.

 

[accessfleet]

I don't generally like to delete records. The tack I take is to use an "inactive record" y/n box. You could then code from view.

During the development stage I used some bogus data, I deleted it by going to DATA SHEET VIEW select the record(s) and hit the delete key.

 

[Sadie Hewgill]

I am using bogus data right now too, but it would be nice to have the autonumbers start from 1 again once I start entering real data and delete the fake stuff. Any idea how to do this?

 

[accessfleet]

Hi Sadie:

If you delete everything and start fresh wouldn't your auto generate number also start over?

What do your auto generate number reflect? Why must they retain sequential integrity?


reference data may mean more to you over time, at least in my way of thinking.

I use manufacturer part numbers exclusively. If my vendors use manufacturer part numbers in their systems then those numbers should reflect data I use to purchase repair and maintenance parts. With few exceptions this practice has worked for 30 years. Almost every vendor can cross-reference OEM or major brand part numbers. This is important when servicing 10 to 50's year, make and models of equipment necessary for city services.


I use the below referenced legend for unit identification.

This helps in a few ways. When looking at the unit you can tell the manufacture year by looking at the unit number.

When looking at a computer screen you can tell the make, type and year of the unit from a list on the screen or printed.


LEGEND FOR UNIT # 1 2 3 4 5 6
digit 1 = unit type 0=Tool
1=Car
2=Station wagon/ Suburban
3=Van
4=Light truck
5=Medium truck (11K - 26K)
6=Heavy truck (Over 26K) gvw
7=Heavy equipment
8=Other Capital assect (shop tool, office equipment, etc.)
9=Obsolete, unidentified auction item
0=Non powered tool

digit 2 = unit make

1=Gm
2=Dodge
3=Ford
4=International
5=Off road equipment
6=TrL/mounted equip
7=Engine power tool
8=Special use manufacture

digit 3&4 unit year 00=unknown year

digit 5 department code

di&6 inservice sequence number

John..

 

[Sadie Hewgill]

We are auto generating our own part numbers so that they don't get repeated. The issue is that we have been using a sequential part numbering system in real life and it has to match that