Table design for forms?

[Homegrownandy]

I have several standard tables and all is well. However the final one I have came to do I'm a bit unsure about.

I have a "Jobs_Type" table as a way to clarify certain information about the "Jobs".

All this needs to be is a few combo boxes and check boxes I believe, neither of which I have used.

Ultimately I want the user to be entering data for that job on one form. Then select the type and enter any other information. Based on the "Jobs_Type" Information, the reports may be different also.



Am I thinking this is more difficult than it actually is? I was wondering if there's anything I should consider at the stage I'm at, or just continue until I get to form design.

Thanks in advance!

Andy.

- Some things Im unsure about:

*I would like to use sub menus, SO, the user picks a "PROJECT" then under that project they may select "BATCH 1" or batch 2..3.. whatever they wish.
*The choice in "PROJECT" Should determine the sub menu. Some jobs may not be split into batches at all, or it may have a totally new format of delivery. The system needs to be flexible.
*What is the primary key? All my other tables are using auto numbers, BUT here I'm confused.

I'm sure, as with all my posts, this is straight forward. I'm just trying to avoid future problems.

[CJ_London]

*I would like to use sub menus, SO, the user picks a "PROJECT" then under that project they may select "BATCH 1" or batch 2..3.. whatever they wish.
 *The choice in "PROJECT" Should determine the sub menu. Some jobs may not be split into batches at all, or it may have a totally new format of delivery. The system needs to be flexible.

this is frequently referred to as 'cascading comboboxes' or similar - google to find out more or take a look a this link http://www.access-programmers.co.uk/...d.php?t=275155

*What is the primary key? All my other tables are using auto numbers, BUT here I'm confused.

Primary key is a field which has a unique value for each record - often this is an autonumber field which will generate a unique value. Note that autonumber is usually sequential (but not always) and should not be used as any meaningful data (such as an invoice number) because you have no control over the value generated.

None of this has to be difficult, but needs preplanning so you know what you want to and have considered all the implications - you will experience problems if your data is not normalised for example.

Consider your tables separately from the forms and avoid things like spaces and non alphanumeric characters in table and field names. Also be aware of reserved words and avoid using them - https://support.office.com/en-nz/art...7-da237c63eabe. And do not use lookups in the table design. I would also avoid multivalue and calculated fields - they all look useful in the early stages of learning access but later you come to realise their limitations

When you come to design your forms and reports, access will look at a table datatype to determine what type of control is required - most common is a textbox. But you can usually rightclick on a control and change it to something else if required (e.g. change a textbox to a combobox). If not, simply drag the control type you want to where you want it on the form, delete the old control' and rename the new control with the same name and controlsource

[Homegrownandy]

Although I'm a total novice, I have watched a few hours of tutorials, so I understand most of what you said. But I'm very appreciative for your time and effort to help.

I may not have explained my question very well regarding primary keys. I meant for this table specifically. Would it even need one?

I'm happy with the whole database up until this point. Everything ties into the 'jobs' table. But then I need to define what project and what batch it is. (If it even has a batch).

This is why I have made the jobs type table. I just cant clearly see a way of this working.

This table "Job_Type" Is only made as an organisation tool for each job.

SO. Would the primary key be a combination between projects and batch? for example project 1 and batch 1 would be the key and this combination couldn't be duplicated?

Its hard for me to explain without writing about the whole database and flooding you guys with too much information. So I've tried to be as to the point as possible. If there are any questions please ask away!

Thanks in advance.

andy.

[CJ_London]

This table "Job_Type" Is only made as an organisation tool for each job.

I don't understand what you mean by this

Would the primary key be a combination between projects and batch?

Sorry to be vague, but it could be - you don't need to have the primary key as a single field. But you also don't need to make 2 (or more) fields to be a primary key just to avoid duplicates - in the design ribbon, indexes option, you can set up multi field indexes to not allow duplicates.

Personally I always avoid multi fields primary keys and avoid multi field indexes wherever possible - I prefer to handle preventing duplicates within the form design and code.

Rather than posting your whole db, you only need to post info that is relevant - rough descriptions may mean something to you, but are meaningless to everyone else. Suggest post a screenshot of your relationships with tables expanded so all relevant fields can be seen is always a good option.

[Homegrownandy]

Thanks for the advice, I will do just that.

[Homegrownandy]

This table "Job_Type" Is only made as an organisation tool for each job.

Just FYI. I meant the Table is just used like a tool to organise jobs under whatever project or batch they were. Rather than having it on the same table.

This is the first time I've used a table like this but from what I can see it would give me the flexibility I need both now and in the future.

[Homegrownandy]

Database design.zipI've attached a word document with notes and a picture of my relationships.

I appreciate it may be a bit complex to understand, but any advice is appreciated.

Thanks a lot!

Andy,

[Homegrownandy]

I forgot to mention, I cant link jobs type in the jobs table to the jobs type table due to a lack of a primary key.

[CJ_London]

I'm not very clear on what you want to do with the Job_TypeT table

you can get back to the ClientT Table from the JobT table just by following the links

and I don't understand what the batch field is for.

What are the jobs? regular maintenance jobs and you want to know who is responsible for what? Or emergency work and you need to make a decision about who is doing what to fix it? And are StaffT records members of the hospital, site or Client?

I understand (I think!) that in the JobT table you need a JobType, but don't understand the logic that says that is the 'responsibility' of the client - perhaps for one batch but not another

With regards lack of primary key, why can't you add one?

Perhaps you can describe the business process that leads to the creation of a record in JobT - why/when is a record created? What is the purpose of assigning a staff member and jobtype?

[Homegrownandy]

THIS IS A ROUGH POST ( I will be home from the office soon and I will check and elaborate on what I have written here.)

A job typically is when an engineer visits a site and changes some electrical components. BUT it may also be something completely different. such as a fibre optic cable installation.

The cable installation may need half as much paperwork and permissions. This is why I'm trying to differentiate between job types. If I had a standard job table I wouldn't be able to have the flexibility to change what's required. (In my opinion

the job_type table is to define what kind of job it is. A specific job may in batch one or two or three.. It defines when work is to be complete or where it should be filed for example. Batch is needed to split up a project to enable us to plan.

Staff is a list of our staff. I didn't want to include the company name online (sorry for confusion)

Also, although you can get to the client through site owner you are correct. BUT we need to know who is the site owner and who's paying us to do the work. These are the same 80% of the time..


thanks for putting in the time and effort in to actually helping me.

Andy.

[CJ_London]

OK, from your description, you are a maintenance company, maintaining/replacing/installing equipment in hospitals. You client may be the hospital (HospitalT) or it may be the site owner (ClientT) depending on what needs to be done - and sometimes the cost might be split between hospital and site owner?

Also, from your relationships, only one staff member can be assigned to a job - is this correct? - I would think you need a link table between JobT and StaffT (which creates a many to many relationship) so many staff can be assigned to one (or many) jobs

[Homegrownandy]

No sorry I believe I said this in the word document. We install electrical components for any kind of monitoring usually. But also we do fibre installation, and possibly other electrical work.

The hospital to site link is only there because in each site we work on we need to know the nearest place to take someone that's been injured. In the future we may work on that same site several times. So being able to do additional work there with the details specific to that site already in place is great. (such as hospital or the site owner.)

Just for your understanding. The site may be a power station, and we have to install a system that will measure levels of power.

Cost is never split. The site owner may pay a contractor to do the work. But this contractor may sub contract it to us.

We would still need to communicate and work with the site owner and their contacts.

So clients is basically anyone we deal with from the office that is not in our company. They may be someone who works for the power station, or they may be another electrical firm.

In SiteT the site specifics are there. so the owner will be "company x" and this will remain for the foreseeable future, so its handy for it to remain so. (but it is possible site ownership would change to another client.)


JOB TYPES

the principle contractor is whomever is responsible for that project outside our company. Ideally its the same as the site owner. But often its a third party.

I do need a many to many on staff. You are correct. I'm finding it difficult to explain the current situation due to lack of experience with databases, so I appreciate your patience.

Cheers, Andy.

[CJ_London]

Hi Andy - I have a busy day today so will try to respond later this evening

[Homegrownandy]

Take your time mate, like I keep saying, I appreciate the effort you are putting in to actually understanding my issue.

[CJ_London]

Sorry not got back to you yet - regret paying clients take priority!