Database Design problem (1 Viewer)

Hello!
As you can see in the picture below, I have only got three tables that actually are suppse to store data. My Material is sometimes being moved around between different contractors and different jobs. My Employer wants the information on the previous job and the previous job retained, to on item in the material list can have several jobs assigned to it. So far it is a simple many/to many relationship.
But how do I display the information in two different fields in a query/form? Is my design of the database appropriate for this?

Please help a newbie to Access!

BILD (click to enlarge):


Cheers

Chris

PS: I am using Access 2003 here.

PPS: I dont know where to put this, but I think, since it is more about the database design than the actual querying technique it should be okay here. (?)

Hi Chris

Welcome to the forum. First glance suggests this design is not right. The idea that you need separate tables for Old/New immediately rings alarm bells. The problem is I don’t really know enough about what you are trying to model to suggest the right model.

Here’s what I think I understand…
You have a Job. Materials are assigned to a job. But from what I read, they don’t stay with a job. So you need some way of tracking materials over time. So you need a material table and a job table (which you have). Then you need a table between these (lets call it tbl_MaterialAssigned) which also has from and to dates as well as PartID and JobID. This will allow you list the current assignment as well as any history.

I have a question about contractors. Are you really assigning materials to contractors or, is it the case that a contractor is assigned to a job (for a period of time) i.e. the relationship between Contractor and Material is through Job and not directly.

Handling old and new contractors should again be done in one table not two using dates.

Hth
Chris

Hi Chris

Thanks for thw quick reply. Let me clarify the information here a bit.

I have several subcontractors, who get assigned fixed tasks as part of a specific, big job.
The parts we supply to them are specifically for the task they are given. Sometimes the technical specifications change and already delivered parts will become (temporarily) useless until they are needed for another task. then they will be either used:

by the same contractor for a different job
or
by another contractor for the same job
or
by another contractor for a different job

the previous job and the previous location(contractor) of the part need to be stored somewhere

So, to sum it up, the database has the essential purpose of keeping record of "leftover" parts and the data connected to them.

The Inspectors for each of the contractors are not very versatile with access so I need a rather elegant design to include search functions or other, easy ways to find/edit/delete data.

It seems pretty hard to accomplish for me, but I hope it is easier than it looks.

Big Thanks from Kuala Lumpur, Malaysia

Chris

Perhaps then you need a new table called for Tasks (related to Jobs). Then you can relate materials to task and also relate contractors to task. I still feel that materials should be related to a job or task and not directly to a contractor. If you relate materials to a contractor you will never know which job/task they where used on.

If you are interested in the balance of stock then there are several ways to handle this. For your purposes though I suggest you have another table called Material Stock. Here you can record the opening balance of stock. Then using queries you will be able to calculate the balance using the Opening Balance and the Quantities Assigned (from the Assignment table.

Does all that make sense? I can do an example if needed.

Regards
Chris

hi Chris!
first of all: Thanks for putting so much thought and effort into it.
I understand your concerns and points of view generally, but i am afraid they do not fit the scope of creating the database in the first place.
the main purpose is to store the information WHERE (by which subcontractor) the item is stored.
The only items I want to list are those leftover pieces, not every single one that has ever existed in our or their posession.
The reason for this is, that the responsible quality inspectors are packed with work already can simply do not have the time to type in every single part, because these are hundreds, sometimes thousands for each job.
Furthermore it is not practicable for our company (we are only a small engineering office) to create a task for every set of drawing we recieve and pass on, because the can also easily be dozens, sometimes hundreds.

I know I insist on a very peculiar point of view, but this is the requirement my boss passed on to me.

Cheers

Chris

Simple Software Solutions

Lets talk turkey.

Lets say your company has a stock of 10k widgits and a new project comes on board. Bookem & Riskit have won the tender. The initial view was that 5k widgets are needed for the project, and are delivered to Bookem & Risket. It turns out that the widgets are not right material and they should have Dongles instead.

Your company then orders the 5k Dongles and gives them to the contractors. This means that there are 5k widgets gathering dust somewhere. Have they returned them to your company?
Are they in the back of the contractors wagon?
Are they somewhere else?

I am sure your stockroom manager/accountant would like to know.

These widgets cost £5 each, so you need to keep a track of them. You make a table in Access and this holds the fields

Project ID PK
Material Code FK
Contractor Code FK
Quantity Issued
Quantity Used
Balance Returned Y/N
Date of Issue
Date of Return
Quantity Returned

This table should have the minimum details to establish where the Widgets went, who to, how many were used/returned, when they were issued/returned.

So if it comes about that they can be reused elsewhere, or on the same project by a different contractor, you have traceability.

Just a thought

CodeMaster:

Chris

Ok, I appreciate what you are saying about storing all history so I appreciate you are not interested in a time based stock system. If I understand correctly, what you need is to be able to record the situation now.

Am I right in thinking that the materials you assign to Jobs should exactly equal the materials you assign to Contractors? Because if this is the case then you should only need to do the assignment once. The problem you have at the moment is that you are assigning materials to both Jobs and Contractors as separate tables (double work load and the risk that one will differ to the other), when in fact I think you should could do the assigning in one table. If materials assigned to jobs is completely independent to materials assigned to contractors then your original model maybe nearer the mark.

Secondly, the idea that you store current/old in separate tables is wrong. The most obvious solution is to have a Status field in an Assignment table that has values of current or old.

Take a look at the example. There is a form where you can maintain the assignments and current stock levels (and also the status). Also there is a simple report giving you the sum of materials by Contractor. It may not be what you want but it may help your thinking. Do take a look at the relationship view.

Hth
Chris

Hi DCrake!

Thanks for the reply. But my problem is, that 98% of the parts are being used and we ONLY want to keep track of the parts that are not being used whcih should come down to a few dozen only. all of these parts are stored by the respective subcontractor, where they were supposed to be used.
however, they will be moved around between SubCons and Jobs when necessary. (usually they get moved only once)
So we need to keep track of where they have been, where they are, which job they currently belong to and which they used to belong to before that. The point is also that it is not bulk quantities of 100s of identical pieces, but rather special, sometimes custom made overseas material (non-mild steel plates, sealing rings, flanges, paint...).
So when I have a list of Jobs, one part would make reference to such a list twice (old job, new job) and same goes for SubCon (current SubCon, previous SubCon).
I know I could make it just a regular text field and let our employees here type in the respective SubCon or Job every time. That would make search functions etc much easier. But tbh, the users of this program are local engineers and are not very versatile with computers. So they do not necessarily appreciate the importance of accuracy when typing, especially in access. That's why I need to create as many controls, i.e. drop-down menus etc,. as possible.

I seem to have difficulties getting my purpose across, but please be patient with a non-native speaker. I Hope this makes it a bit clearer.

Cheers

Chris


_____oh, have not read your post, chis before posting this. So here is my answer to that:

That might just work for my purposes. so I will go ahead and try this over my already packed weekend at home and will get back to you as soon as i can. If this would really do the job it would be awesome. For some reason or another I had not thought about simply creating a record for each combination of events that could happen to the part.

Thanks.

Hope you guys get a good start into your weekend.

Hi!
This DB design seems to do the job pretty well as far as I can tell. only had a minimum amount of time to look into it so far, but it seems good. I have modified it slightly, but made no bigger structural changes. However, I just have not come up with a solution for one scenario:
What if I have 20 items and only want to transfer 10 of those to the respective Subcontractor. How do I keep the history for both. (difficulty is increased since I want to track record of the date when every allocation was made).
Hope you guys had a nice weekend.
Cheers
Chris
_____EDIT________

Hi! No need to explain to me how to include the date of creation in a record. I will find out myself. there should be tons of tutorials on the internet for this...

Hello Everyone.
I am now almost sure, if not at least very confident, that this design will do the job for me.
However, I am looking for a way to make it possible to jump straight to a record in a form without having to use the right/click-->filter etc... function!
I'd be glad to find some help. Thanks!

Kurth said:

Hello Everyone.
I am now almost sure, if not at least very confident, that this design will do the job for me.
However, I am looking for a way to make it possible to jump straight to a record in a form without having to use the right/click-->filter etc... function!
I'd be glad to find some help. Thanks!

Click to expand...

Typically you would add some unbound fields at the top of your form where the user can enter certain criteria then click a button. In your case you could add a text box where the recordID is entered and press enter (or click a button) to find that record.

Form searching and filtering is probably one of the most topical subjects so I'd recommend searching the forums for examples/ideas. There are plenty of ways to design the screen and implement so have a good think of how you would want the interface to work. Post again when you get stuck or need to know how to progress.

Did you sort your earlier design issue with transferring only a part quantity?
Chris

I have resolved neither of the two problems yet, but I did find a post which might help me with the search function, but some questions remain.

I made a post in there stating my problem.

Cheers

Chris