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?

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.

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?

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.

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.

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?

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.

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?

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.

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?

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..

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.

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.

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.

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.

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.

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.

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?

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..

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

Hi Sadie:

So you want new system to start where the old system left off. When I transitioned from my old dos based system to new access based system I started a fresh work order number series to permit me to see at a glance old system numbers vs new system numbers.

How many old part numbers do you currently have?

How many new part numbers do you expect to generate each year?

I have found that things level off after awhile.

John..

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?

When Access uses the autonumber field which the majority of developers use for Primary Keys they will never reset to 1. They go up from 1 and continue. If you create 5 records and then delete the last 4 the next autogenerated number will be the one after the last and not the one before the 3 deleted records.

As accessfleet indicates as with OEM or Product codes each record is regarded as unique in that databases history and in relational database design the link between tables is completely fundamental to the idea behind relations - renumbering a particular field in one table will not renumber the equivalent numbers in any related (but separate) table thus destroying the integrity of the relationship.

Think of it like x and y chromosomes. Change your chromosomes and your parents will no longer be your parents you will be someone elses child!
A single change will change EVERY single relation forward and back.

Ok, so here's the dilemma then, I shouldn't be auto numbering my part numbers because I want to get them back if i delete them. But I want the numbers to be sequential and the user to automatically be presented with the next available part number when they enter a new part. I have heard I can use Dmax +1. How do I implement this?

Hi Sadie:

I just spotted this on wikipedia made me think of your project. You keep thinning about it and yoll getter done.

John.

Keys are some of the most important objects in all relational databases as they tie everything together. A primary key (http://www.access-programmers.co.uk/wiki/Unique_key) is a column which is the identifier for a given entity, where a foreign key (http://www.access-programmers.co.uk/wiki/Foreign_key) is a column in another table which refers a primary key.
These keys can also be made up from several columns, in which case they are composite keys. In many cases the primary key is an auto generated integer which has no meaning for the business entity being represented, but solely exists for the purpose of the relational database - commonly referred to as a surrogate key (http://www.access-programmers.co.uk/wiki/Surrogate_key).
As there will usually be more than one datasource being loaded into the warehouse the keys are an important concern to be addressed.
Your customers might be represented in several data sources, and in one SSN (Social Security Number) might be the primary key, phone in another and a surrogate in the third. All the customers needs to be consolidated into one dimension table.
A recommended way to deal with the concern is to add a warehouse surrogate key, which will be used as foreign key from the fact table.[1] (http://www.access-programmers.co.uk/forums/#cite_note-0)
Usually updates will occur to a dimensions source data, which obviously must be reflected in the data warehouse.
If the primary key of the source data is required for reporting the dimension already contains that piece of information for each row If the source data uses a surrogate key, the ware house must keep track of it even though it is never used in queries or reports.
That is done by creating a lookup table which contains the warehouse surrogate key and the originating key [2] (http://www.access-programmers.co.uk/forums/#cite_note-1). This way the dimension is not polluted with surrogates from various source systems, while the ability to update is preserved.
The lookup table is used in different ways depending on the nature of the source data. There are 5 types to consider [3] (http://www.access-programmers.co.uk/forums/#cite_note-2), where three selected ones are included here:
Type 1:
- The dimension row is simply updated to match the current state of the source system. The warehouse does not capture history. The lookup table is used to identify which dimension row to update/overwrite.
Type 2:
- A new dimension row is added with the new state of the source system. A new surrogate key is assigned. Source key is no longer unique in the lookup table.
Fully-logged:
- A new dimension row is added with the new state of the source system, while the previous dimension row is updated to reflect it is no longer active and record time of deactivation.

Work should be put in to guidance on which situations the options apply to. Is that solely a business decision?
Which factors influence the choice? The update strategy might (full wipe, incremental etc.)

Ok, so here's the dilemma then, I shouldn't be auto numbering my part numbers because I want to get them back if i delete them. But I want the numbers to be sequential and the user to automatically be presented with the next available part number when they enter a new part. I have heard I can use Dmax +1. How do I implement this?

Sadie this comes up every now and then. Ask yourself is it really important to have completly sequential numbers. Will it be a problem if 99% are sequential and there's gaps here and there where records have been deleted?

And why shouldn't you be autonumbering your parts...? Isn't DMax + 1 autonumbering them in a different way. All numbers are related so its the nature of arithmatic that to count is to autonumber.

The autonumber facility essentially does exactly what you are asking it keeps a private tab of the latest number and then allocates the next number when a new record is created. It ensures that this is totally unique and it ensures that no other future record has the same number from now until eternity.

It will have the result that the total record numbers aren't likely to be the maximum PKID value. For example if you create a database and then make 40 dummy records and then delete. Although there may be no records in the database when you come to create a new record the first record is likely to be autonumbered 41.

Compacting the database would reset the autonumber to 0 such that the next record is 1. However if there is one record in the database with PKID of 41 compacting the database will result in a new autogenerated number of 42.

That's just the way the world is.

If you really must have sequential numbers as a code. For example you only have 10 products and for branding purposes you want to almost brand the values 1 to 10. I would avoid trying to manipulate the PKID and create a separate field that you just enter a value into but then this field essentially becomes the products name.

Be warned though I think if you create enough "products" and you will start getting duplicates.

Hi Sadie:

I think Lightwave is on point. Your system will need to be flexible. Historically things change. If you design a rigid system, the changes will make you nurts.

unfortunately there is a system already in place. We have already assigned numbers to real parts and now those numbers have meaning. So I have to be able to change the numbers I am autogenerating to be these specific numbers, and then I would like to have them sequentially generated after that. I think Dmax gives more flexibility there since the data type is actually a number i should be able to edit it

Sadie Ok I see you have historical information.

If I were you I would keep using autonumber to generate PKID that are used in the background to link any tables. I would then store the product code in a separate field and for all new records going forward this could be autogenerated. You could use a number of different ways to generate this including DMax. If you are ditching the old system you probably won't have to edit this product code very often. If you are keeping the old system you will need to go back to it and obtain the product codes to generate new codes to ensure that the two sytems match.

Assuming you don't have to go back to the old system
In Access 2003 you can generate an automatic product code as follows...

Within the form create a text box for the field Product Code.

Right click within the fields and select Properties a new windo appears and move the cursor to Default Value.
Clicking on the dots button to the right will take you through to a half way house where you can choose the formula creator if you want you could create a simple default of something like (format may vary but experiment)

="Suffix" & [PKID]

This will be subsequently editable and only created when a new record is created. You shouldn't have any issues either with linking tables because the real linking occurs on the PKID so all data integrity is maintained. For referential integrity Access 2003 requires tables to be related via long integer fields so linking them through a product code may not work if there are already characters within historical product codes. You may need to do some initial data manipulation if you have historical related tables as they will not contain the surrogate PKIDs relating to the header table that will be created the first time you import the old information.

Creating these surrogate ID field and updating the related tables will be a quick job though once you understand what you are doing.

Sorry came to the party a bit late and didn't read all of the first page missing it completly apologies if I have stated points already made.

I found a way to reset my autonumbers so now I think I will just go with that, and not delete any until I put the real data in.

Sadie, just remember this: AUTONUMBER is a valuable tool but it must NEVER EVER have a meaning before-the-fact other than "unique identifier for a record." To use an autonumber is to invite grief if the number has ANY OTHER MEANING AT ALL.

Among other things, if you have a problem entering a record and have to back it out, the autonumber DOESN'T back out. It was already committed. DON'T rely on autonumbers for anything that has external meaning. I'm telling you this now to save your sanity later.

Use autonumbers for things like "transaction ID keys" or "arbitrary invoice numbers." Things that don't have to be sequential and in fact could easily be random without loss of function. Using autonumbers for anything structured is a serious mistake.

Hi Sadie:

What the doc man said is a more precise way of saying what I tried to share with you.

It sounds like you are ready to go so good luck..

John.