Table design for forms?

[CJ_London]

Now had a chance to have another look and think you have the following

you have two types of relationships with ClientT
- a one to many for each site (i.e. every site must have a client)
- other clients who are principal contractors who have a one to many relationship with the Jobs but are otherwise not related to the site i.e. a job could have two clients - one related via the site and one related via the job.

If this is the case then try the following

1. add a primary key to the Job_type tablel
2. link jobT to Job_TypeT on Job_type on one to many to this primary key - you can define the join as a left join (include all records from jobT)
3. remove the current relationship between ClientT and Job_TypeT
4. drag another ClientT table to the relationship window (it will be called ClientT_1)
5. create a relationship between Job_TypeT and ClientT_1 on principle contractor to Company_ID, also you can define the join as a left join (include all records from job_typeT)- it is not necessary to enforce referential integrity, but if you do, ensure Job_TypeT is on the one side.

When you create your queries, you can do something like this because of the left joins

nz(ClientT_1.[company Name],ClientT.[company Name])



will return the name of the contractor if a separate contractor exists and if not, the site client

[orange]

@HomegrownAndy,

I looked at your design attachment and also the description in post#12. I'm going to suggest you take the materials you have and
--work through this tutorial from Rogers Access Library (you have to work through it), and then
--take what you learned and apply it to your materials, to get a database design that meets your needs.

More info on Normalization starts here.


You mention Work and Contacts and Clients, but you also mention Cost and Jobs. The definitions seem a little vague to me, but you know your business better than any reader. It isn't clear to me --not stated explicitly -- what is the purpose of your proposed database.

JobType would be related to Job not Client --but again you know your business better than readers.

Good luck with your project.

[Homegrownandy]

This has helped a lot, I need to research some of the things you mentioned to fully understand them. I'm reading about left joins now.

Thanks mate,

[Homegrownandy]

The database will be used for a variety of things. Some of which have not been defined. The main purpose initially is to produce documents. Each job we do requires a lot of paperwork at different stages. I was planning on generating the reports using the database (or making it easy for someone else to do it).

Job type is supposed to be the bridge between clients and jobs. This was incorrect in the attached file and I was confused how to make these links. BUT ill attempt to clearly define the purpose of this table:

Different jobs require different documents and also different procedures throughout the project. On a basic level I want the ability to define what information is needed depending on the job type. I have to have the ability to easily manage groups of jobs. (I have no idea how this will work yet but if you guys agree its possible and a good way to do it then that's why its there)

BATCH should now be in the jobs table. because they are job specific. so currently the jobs type has little functionality as I have little idea of what im doing. But like I said, unless you disagree I believe its necessary.

As you said the client needs to link to jobs. the only reason its not directly is because I want jobs type to contain the information for a lot of jobs. It may as well be there.

You mention Work and Contacts and Clients, but you also mention Cost and Jobs.

I don't see where you are referring to here but ill happily explain If you tell me.

Ill research what you both have suggested and look into the links you sent. Then ill update and hopefully be more clear.

thanks guys!

[orange]

Did you look at the tutorial I suggested? You would have seen his business description - a clear overview of the things involved and how they relate in business terms. He also shows a procedure he uses to take the description, isolate entities and attributes, and to set up relationships. His blog(s) describe the Normalization process.

Do you plan to include document templates and complete(fill) these from the database?

[Homegrownandy]

I've printed off the tutorial you suggested, and read the normalization one. I'm pretty familiar with both concepts but refreshing this knowledge and seeing how I can apply it defiantly helps.

I did like the beginning, as you pointed out, its clear and informative. Ill look at producing something like this for my own benefit and anyone else that wishes to see or help.

I have done a few months of database design and implementation in college and uni, but that was so long ago. To be honest I never thought I would actually be doing it as part of my job.

Also. Yes, I have templates.

The current way I'm doing it is creating a mail merge list in excel then doing it from there. But with a database I can ensure all the data is correct and make amendments easy. Also its the same throughout the project this way.

Like now if one spreadsheet is updated here they all have to be obviously.

That's a pretty long reply for; yes and yes.

[orange]

Yes, I agree with the clear description --it will help you more than you know. Also, it will serve as a base from which you can build your data model.
And the model and the description together can be a lasting part of documentation and a key communications item.
You may also find this post helpful.

[Homegrownandy]

Just a quick one, Should batch be in each job or job type?

for example.. a project has 500 jobs. they need to be split into 5 batches of 100.

each job is assigned a batch but would that be in each job or..

would it be in the jobs type like..

current project - batch 1

current project - batch 2

What do you think?

[CJ_London]

sounds like you need a table for projects

tblProjects
ProjectPK autonumber
ProjectDesc text
...
...

tblBatches
BatchPK autonumber
ProjectFK long
BatchNo number

and you would link your job to the BatchPK

tblJobs
JobPK autonumber
BatchFK long
...
...

[orange]

I tend to agree with Ajax --sounds like you still haven't got a good, clear description of what you are trying to accomplish - or what your "business" is. Sounds like the terminology/terms is changing.

If you start with a clear description, and follow the process in the tutorial, you will get a data model.
If you have some sample test data, you can "test" your model and adjust as necessary using the stump the model approach).

You are too close to the problem in my view.

Take 30 minutes and work through the tutorial - could be the best 30 minutes of your database learning.

You could also review this post.

Good luck with whatever you decide.

[Homegrownandy]

Job Type is basically the projects table. projects is more logical tho.

So a batch table would consist of the project its under and the batch number.

this combination is one record and requires an auto number ID.

That's what I'm getting from what you said, Ill update my tables and ERD and post my new design brief.

Cheers.

If a job doesn't require a batch it could link to the batch table and that field could say "not required" or something similar.

Ill have a go anyway.

Thanks again, Andy

[Homegrownandy]

orange, I asked the question because that's exactly what I'm doing I'm writing the plan with the description and any other information that might help. But as you said there. I should get test data and stop messing about with theory. Ill see what works and what doesn't and develop the plan.

this thread is probably too confusing now to be any use.

Thanks so far, Ill make a new one once I'm a bit more into this.

[orange]

If you do make a new thread, then advise readers so and put in a reference to this thread.
Some readers want an answer to a specific problem --combo syntax, use of Datediff() etc.
No extra info -- "where does the comma go and that's all I want..."

While others want details and have a learning orientation- they want to know the who, what, where, when, why, how and how often details. As you know there can be a lot of learning from experiences described/documented in these forums that don't quite get taught in schools.

Regarding your current status, you can do a lot of data modelling, test case actions, adjustments with pencil and paper --no Access, no computer.

One interesting exercise, and a good practice to follow if you're really getting into database (and paying customers) is to define/describe each and every table and field. It can be quite a challenge, but it will clarify this sort of thing--

Job Type is basically the projects table. projects is more logical tho.

You may get some additional facts from Jerry Dennison's Normalization article.


Good luck. Happy to help.

[CJ_London]

you are being inconsistent in what you require

each job is assigned a batch

If a job doesn't require a batch

[Homegrownandy]

I'm planning on assigning those jobs to the batch 'not required'.