Check Tables

[KadeFoster]

Could some one please check over my tables and suggest what i can do to clean them up and relationships. I have an idea but i am lost on the path to achieve it. Gotta get the foundation totally right before i try for the forms.

I am using access 2010.

 

[KadeFoster]

Yeah i thought my DB was a bit of a mess, just going through it now to fix up lookups and relationships as it looks complicated and messy to me and i got no idea lol

 

[KadeFoster]

So don't make a table with the options in them? then link to that table?

 

[KadeFoster]

Ok I have redone my tables and i think they are looking alot better then what i had before. I am still uncertain what to do with the Supervisor Field on the Handover Info Table and how to relate it to the employee details.

Also I am wondering if I should remove the Forklift info from the jobs table and make it its own table or include it into the Units table. Also if i have a JobType as Scaling how do i get that to relate to my scaling table? Do i have to include all the scaling Fields into the jobs table?... Will be like splitting off the forklift info from the Jobs table as well.. any suggestions...

 

[KadeFoster]

OK I have fixed a fair bit and i think i am on track now... let me know what you think...

Just ignore the forms that are in the DB as I am playing around with them to work out what things do and trying to work out how to get them nice and neat and professional looking... wish there was a program for designing them

 

[gemma-the-husky]

kade - out of interest, you really are going about this the best way. getting the table design right is the main thing to facilitiate swift and harmonious development

there is often a bit of "step-wise refinement" though. when you start to develop, you have to go back, and revisit something.

good luck with your project. I am sure Pat will help you get your design spot on.

 

[KadeFoster]

Thanks, well the basis of a DB is the tables that store the data so i really want to try and get them all sorted in the best way. Also i don't want this to fall over at work and be totally useless. I am struggling a bit but i think i am getting there. Its hard just using forums, chatting to some one would be much easier. I am kinda stuck at the moment and not sure what to do in regard to a couple of relationships i need and the forms i need to create for the data entry. Just hoping i have my tables set up right...

 

[KadeFoster]

Ok, I got confused at what you meant to i did what i did... lol. So if i use the form for the drops downs is there an easy way to update them? So making a table with answers for the form combos isn't the way to do it? Just manually input the options in each of the things i require. Jobs i am still trying to figure out how i want to relate that... One to many to HandoverID as there will b multiple jobs per handover. Will work on it now and fix up my mess.

 

[VilaRestal]

So making a table with answers for the form combos isn't the way to do it? Just manually input the options in each of the things i require.

I don't think that's what Pat meant or at least I hope it wasn't. I totally disagree if it was: having lookup values in tables has a number of advantages 1) you can enforce referrential integrity 2) its a centralised location to maintain them 3) you don't have to rebuild a front end to deploy changes. In fact having value list comboboxes to set the values of fields is generally bad practice except for the most simple and static lists.

However, I haven't got Access 2010 here so I can't comment on the design (Kade PM'd me and asked me to look).

But I did just want to say, don't get rid of the lookup tables.

I think what Pat meant was don't set comboboxes up at the table level. Still the field relates to a table but the comboboxes that lookup that table should be done at the form level. That way there's no possible conflicts between the two.

 

[KadeFoster]

Ok, so just at table level just have the fields related, and then at the form level put in the combo for that relationship selection.

 

[VilaRestal]

Exactly.

I think it's unlikely you'd encounter problems having the comboboxes set up at the table level but you could and you're not really saving yourself work - you've got to set it up one way or the other and you can easily copy and paste a combobox already setup for a particular field from one form to another.

 

[KadeFoster]

OK i totally understand now, will fix that up. Also i am having a problem with relationships as I'm not sure what tho relate a field to. I am try to create a Handover between 2 different groups of people. Each handover can have multiple jobs on it. Not sure weather to relate the jobs to the date or the handover id? I was reading that date is not the best thing to relate to.

 

[KadeFoster]

So you cannot view my DB? as its a different version?

 

[VilaRestal]

Yeah sorry.

If you're not using attachment fields or multivalue fields then you should be able to Save As Access 2007 Database and upload that.

 

[KadeFoster]

I don't have that option, only able to save it as a access 2002-2003, access 2000

 

[KadeFoster]

Hmm cannot save it as and earlier version hmm will have to rebuild it i guess

 

[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