Check Tables

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

 

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

 

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