Check Tables

[KadeFoster]

OK sorted that, just made a 2003 mdb and imported the tables as i am only up to table stage atm.

 

[VilaRestal]

OK cool then please upload it (attach it to a post)

 

[KadeFoster]

Just fixing the fields will upload in a second thanks sooo much

 

[KadeFoster]

Okay here you go... there is relationship lines everywhere but i removed the lookups in tables... and fixed RI

 

[VilaRestal]

OK, the first and most obvious error is:

The primary key for all the lookup tables should be its autonumber ID field and that is the field that gets joined to the main tables' fields. (The autonumber is guaranteed to be unique and numbers are much more efficient in storage and indexing than text.)

And that's also true when joining a main table to another main table.

That means breaking the relationships, changing the datatype of those main tables' fields to number, and then making the joins to the lookup tables ID fields. Probably best to delete those few test records in the main tables first to make sure they won't get in the way.

Apart from that it looks like a good design. You're following good design principles in general and it looks normalized.

 

[KadeFoster]

This is sooo more complex then what i thought it was gonna be... are you on for a while?

 

[VilaRestal]

Databases always end up more complicated then you expect and usually sooo much more

I will look through it some more. As I said, it looks good to me apart from the wrong fields as primary key and joining on the wrong fields. Whether it's a really good design for what you want it to do is another matter but I expect you have because of the thought and care you're putting into it.

I'll keep checking this forum from time to time.

 

[Pat Hartman]

Kade,
You need to go back to the original database version you posted and start again from there. First remove the lookups from the table and we'll look at the schema again. Just change them back to text fields. Do NOT make any other changes at this point. You will still do lookups the same way but they will be done on FORMS rather than in the tables. I've enclosed a picture. Change the combo back to a text field and that will get rid of the table level lookup.
I'm sorry if I wasn't clear but the change you made made the problem worse but in a different way.

 

[KadeFoster]

My net has been playing up but i did this last night. I have to use an older format because access at work is old but here is what i have done. I think i have fixed my issues.

 

[VilaRestal]

Just a couple more changes needed that I can see:
tblUnits - make UnitID the primary key
tblHandoverInfo - make HandoverID the primary key
join tblLeave to tblHandoverInfo on HandoverID

As for what Pat previously said: I think now that you've gone to the trouble to do the lookups with ID numbers you should keep it that way. It is a better way to do it - much more efficient.

 

[VilaRestal]

Oh and get rid of the Level field in tlkpLevel.

It's a text field and you're only storing numbers in it and they are the same as LevelID. (And Level is a reserved word)

 

[KadeFoster]

OK fixed up what you said now where do i head from here ?

 

[KadeFoster]

Hmm, the levels refer to different levels in the mine i work in. they are numbers and letters. ie 21c 18a 18b 32d ... should i change that to minelevel or something?

 

[VilaRestal]

Yeah, if it will be a string then keep it but changing the name would save you hassle. Otherwise you'll forever be surrounding it by square brackets.

Where to go from here? You tell us

Do those tables store everything you're going to need?

If so then you move onto the front end: start thinking about and playing around with form design.

 

[Pat Hartman]

You've got Cascade Update and Cascade Delete specified for all the relationships that I checked. Get rid of CascadeUpdate. It will never happen when your PK-FK relationships are based on autonumber PKs since they cannot be changed. You may actually want all the Cascade Deletes but I doubt it. Generally when you make relationships with "lookup" tables, you do not specify cascade delete because you don't want to delete main records when a lookup record is deleted. For a stupid example - if someone makes a mistake and trys to delete "Male" from the sex code, wouldn't it be better for Access to say - sorry you can't delete that , it is already in use than to just go and delete all the males from your emloyee table? An example of when you want to specify Cascade Delete is in an Order Entry application. If you delete an order, it makes sense to delete all the order details. So, although Cascade Delete is indespensible when you need it, it is downright dangerous if you are not careful. So examine each relationship and ask yourself - If I delete the one-side record do I want the many-side records to be deleted automatically? For lookups the answer will always be NO. For other relationships it wil sometimes be yes and sometimes no. Another example from our Order entry application. Even though the relationship between Customers and orders is similar to the relationship between orders and order details, most companies do not want to delete customers if there are any associated orders so you would almost never specifiy cascade delete for this relationship. If you did want to delete customers, your business rule would be to either delete all the orders first, in which case you could use cascade delete because it would allow you to delete a customer if there were no orders or you would write program logic to look through the orders and if any was open or newer than a speciic date, disallow the delete.

 

[VilaRestal]

Yeah, it's a pity Access doesn't have the SQL Server delete options of Set Null and Set Default. They're so much more useful and usual (and less dangerous) than Cascade Delete.

I'd go so far as to say as a rule never use Cascade Delete at the table design stage. When the front end is built and you find a specific situation where it would be useful and not risky then consider applying to that one relationship.

 

[KadeFoster]

Ok, will fix all the relationships in regards to that and as i go along i will work out what i need. I think i have gotten everything. Have been asked to put in a basic inventory system into the db as well. Probably like 10-15 things but i will concentrate on getting this form frontended. And i also found out that work use an old version of access.

 

[KadeFoster]

Sorry for the long wait between replies i have been away on holidays.

Ok, am still on tables and trying to work out what to do next. I have started to attempt a form but have come to a problem. I am not to sure what to do. For every handover there can be many jobs done that day, different types of jobs as well. I have attached a pdf with the job types in it + the fields they each require.

My questions is.... is it possible to make a combo box that will display different fields from tables for each separate job type or should i be better off creating different tables for each type and link them with an id/name and have them open as a sub form on the handover form?

Sorry for the form question, kinda a table question to. And how do i do it? lol

Hope i have explained this well enough for you.