Avoiding Redundant Data: Help

[rm.harper]

Hi, Looking for a push in the right direction :banghead:

Objective: To create a Scheduled Work table to show when we work on a specified site and to include materials used/who supplied them

I currently have a few tables; Customers, Jobs, Materials, Suppliers.

What happens is we have jobs that are on going, we're not always working on them so we need to store info on when we are working there.

My Jobs table stores the basic info for each 'contract' we have, or site we work on if that makes more sense. it stores a job number (generated from an outside source) the customer the job is for and the location of that site the work is carried out on.

My scheduled works table should show the dates we work on a site, the materials used on that site and who supplied us with them.

I would also like to see what customer it is for but would that be in a report as that information is already stored in my jobs table?

I seem to be getting ahead of myself and trying to use source tables to see information that might be generated in a report once all the key information is put into tables.

Could someone please point me in the right direction on how to go about making this happen

Regards
R M Harper

 

[spikepl]

Not sure about your issue/concern. Just get cracking, and post back when stuck on something specific.

 

[jdraw]

Work through this tutorial to understand the process and the concepts. Then try it with you own description of what you're trying to do.

 

[rm.harper]

Im half way through my database at this point but Im stuck because two or more tables will have identical data, im trying to avoid this, but im unsure how to include data from one table to another without creating duplicate data. (would this only be done using lookup columns, want to try and avoid these now I know they can be damaging to a database).



Thanks very much for providing a link to this tutorial, this actually helped me out for further down the DB Line as well ha I just seem to be at logger heads with myself. Trying to include things that may not belong at this stage, such as material reports etc..

Thanks again
R M Harper

 

[jdraw]

It is quite rare that a well structured database would hae 2 tables with the same structure and exactly the same data.

because two or more tables will have identical data,

Suggest you show us your tables and relationships
in zip file, and some explanation of why your tables have same structure and data.

First guess is a structure issue and non Normalized table(s).

 

[rm.harper]

It is quite rare that a well structured database would hae 2 tables with the same structure and exactly the same data.



Suggest you show us your tables and relationships
in zip file, and some explanation of why your tables have same structure and data.

First guess is a structure issue and non Normalized table(s).

This is what I mean by jumping ahead of myself, I think im trying to include information that doesn't NEED to be there because im thinking ahead, hoping tables will show relevant information instead of using queries and reports.

Unfortunately ill have to pop round the forums and make a few more posts before I can attached documents to my posts. So bare with me while I get more involved around the forum and ill update my post once ive got permissions to use all functions.

Update: Includes database structure for checks.
As you'll be able to see this database has a few tables. The idea of this database is to record important information about the daily 'goings on'.

Table Explanations:

tblCustomer:
A Simple source table to hold general information on customers we do work for.

tblSuppliers:
Again, a source table to hold general information on our material suppliers.

tblJobs:
A source table to hold information on the job contracts we currently hold. (different then tblScheduledWorks, see below)

tblMaterials:
Table to hold materials that CAN be used on jobs. (not all materials are used on all jobs - these 'materials' could be seen as 'products' in other databases).

tblScheduledWorks:
This table holds and collects information on the dates we work on Jobs. We could work on a specific job 3-4 times a week so a date format works well. (I will need to use this table along with tblCustomer, tblOrders to collect information on ScheduledWorks - materials used etc...see Reports)

tblOrders
Input and store orders of materials and allocate them to ScheduledWorks entries.

Reports
Weekly Work Sheet (showing where we are working a certain week
Weekly Materials Sheet (showing what materials have been used where)

You can kind of see where my reports will end up from these two examples.

Please see attached
Thanks Again
R M Harper