Table/Database Design Help

[noaccessguru]

Hi guys (sorry I apologise for the long winded post!)

I’m hoping someone might be able to give me some advice on the best way to set up my database….or shed some light on how I can do it….(without VBA really!)

Ok…..so I have a Deviation Database where our sites need to record deviations from a process.



I have 8 different sites, all of which will have a couple of different people that will be accessing the database to enter a Deviation…so 2 people may enter a record at the same time (I will be splitting the database etc)

I have a table with all the fields needed to record the Deviations and say as an example 3 of the fields are site e.g ‘VIC’, ‘NSW, ‘SA’ etc, another is the Financial Year e.g ‘14’ and then another is Number e.g ‘0002’ (which is basically a count per site, so this will be a sequential number). – I also have a ‘Sites’ table, which is the record source for the ‘Site’ field.
I then have a Calculated Field which calculates these 3 fields together, to provide the ‘Deviation Number’, which for example may be(Site-FY-0001)….I then have a report where this calculated number will become the file name. (I know people dont like using Calculated fields, but I think it will be fine for what I need it for)

Since all the sites will be recording into the 1 table, I cant make the ‘Number’ field (e.g 0001, 00002) a unique number, I need to allow duplicates (as VIC will have a 0001, and NSW will have a 0001 etc).

I was hoping I can record all of this into 1 table, otherwise if I have to create a table for each site to be able to make the ‘Number’ field unique, then will have to create Forms for each individual site, queries for each individual site etc etc…(I have a Form to enter a new record and also a form to Edit a record (where they select the ‘Deviation Number’ to edit, which is the Calculated field), also then need to print the report for that Deviation (so will need a report for each site etc)

I have a query where the the user will select the Site and it will tell them what the ‘Last Number’ was for that site e.g 0002 so they will know that the next ‘Number’ to enter for that site is 0003.
But if 2 people check the ‘Last Number’ at the same time, and then go to enter their new record, they will both be going to use ‘0003’

Im not very good with VBA so if the work around is this option, I don’t think its going to be feasible. I can obviously create queries, forms, reports etc for each site if I create a table for each site, but will then have to do this x8, which is starting to make the database rather large….

I hope this all makes sense and someone might be able to give me an idea on the best way to set up my table/s? I NEED to have a unique identifier for each Site
Or do I need to split the database and then create a ‘Front End’ for each site so that I can make the ‘Number’ field not allow duplicates in each sites Table?!

If it was only 1 user at each site then it would be fine!

Any help much appreciated!

[ssanfu]

OK, you have said what you are trying to do, now what do you have as far as table designs?
Table names, field names and field types????

(I'm not an Access guru either, but I'll try and help)

[noaccessguru]

Hi Steve,

Ok so initially I had 2 tables with the below fields:

Tbl_Plants:
ID – Auto Number
PlantCode – Text
PlantName - Text


Tbl_DevList
ID – Auto Number
Date Initiated – Date/Time
Site – Text (Which is a Combo Box and the Record source is from the Tbl_Plants table)
Type – Text (Value List)
Brand – Text
Number – Text (this is the sequential number I need as unique for each site 0001, 0002, 0003 etc)
Financial Year – Text
Originator – Text
Where – Memo (where in the process the deviation occurred)
What – Memo (what parameter is out of Spec)
Reason – Memo
Status – Text
Form – Hyperlink (hyperlink to a document in Sharepoint)
Title – Calculated (this field is the ‘Site + “-“ + Type + “-“ + What)
Deviation Number – Calculated (this is ‘Site + “-“ + Financial Year + “-“ + Number)
Comments - Memo

So this is all fine, but I need to have the 'number' field a unique number but I cant if I have this as 1 table only.

If I happen to make a table for each site (so that I can make the number not alloq duplicates), the fields will be exactly the same in each table which is fine, but then how can I make a table with 'All Deviations'.....

Hope this all makes sense.

This is my only hurdle now......Im doing it with a table for each site, but yeah now I have the issue withhaving a query that will show me all the Deviations from all the sites...
(wont let me do a 'Make Table' as it doesn't allow Calculated fields??

[ssanfu]

"Type", "Number", "Where", & "Form" are reserved words and shouldn't be used as object names. Also shouldn't use spaces in object names.

See
Reserved words: http://allenbrowne.com/AppIssueBadWord.html
10 Commandments: http://access.mvps.org/access/tencommandments.htm
Lookup FIELDS: http://access.mvps.org/access/lookupfields.htm
Calculated fields: http://allenbrowne.com/casu-14.html


Having "ID" as a field name in more than one table (I never use just "ID") can get confusing and cause problems in queries.
"PlantID" and "DevID" would be much better names.

Field names of "Where" and "What" are descriptive. How about "WhereDeviate" and "WhatParameter" as field names?? Also, since you want to use "WhatParameter" in a title, I would change the field type to Text. Still up to 255 characters.

Since "Title" and "DeviationNumber" are calculated, they shouldn't be stored in a table. They should be calculated in a query.

BTW, the record source for a form should always be a query (IMO).

The attached mdb is an example of what I think I understand you want....... Select a plant in the "Add Deviations" form, then add / edit the data.
The unique number is calculated when saved....

The attached mdb is an example of using one table for the deviations, instead of 8 tables.
Keep in mind, you should split this into a FE and a BE - each user gets a FE on their computer and the BE is on a server or somewhere everyone can link to it..
Try adding real data in Plants and deviation...

[noaccessguru]

Thanks heaps for all this info Steve!

I will have a look into and play around with the database you have provided and read up on some of the info you have given me, aswell as change the field names

Also, ID is just an AutoNumber in each of the tables, so will make another field the Primary Key, dont particularly need the ID anyway.
I am planning on splitting the database and providing the users the front end aswell

[ssanfu]

Also, ID is just an AutoNumber in each of the tables, so will make another field the Primary Key,

You can keep the autonumber field, just give it a different, descriptive name.

If there are 3 guys named John, it gets confusing which one you want.... right?? It is not only for you, but for anyone that inherits the dB. In 6 months, you will look at the dB and say "What the jello was I thinking??? What is going on????"
Been there, done that, banged my head against the wall... (Hahahahaha, where's the aspirin???))