DB Design Problem (fixed fields or flexible records?)

[DAmoss]

I have created the following 'Outputs' table which has a one to Many connection to an 'Evidence' table, the structures are shown below:

OutputsID
ProjectID
OutputReq (T/F)
OutputRef (Text)
Output (Text)
Definition (Memo)
EvidenceDesc (Text)
DateChecked (Date)

EvidenceID
Evidence (Text)
Checked (T/F)

ProjectID
ProjectName
ProjectRef

I have populated the above (fixed) tables with my 30 output records and linked each one of them to a number of associated evidence records.

My problem is that I want each of my main 'Project' records to have their own copy of the already entered output / evidence records.

Is there a way of doing this or should I start again and build all of my 30 outputs and associated evidence fields into a new table and attach that instead, this new table would contain an awful lot of fields and wouldn't be as flexible to add to later.

All suggestions gratefully accepted

 

[fneily]

What connects your Output record to the evidence table? Shouldn't you have OutputID as a common field in the evidence table? You said it's a one-to-many relationship.
When you include that field, then the Project table connects to the Output table that connects to the Evidence table. ProjectID - ProjectID(Output table) - OutputID - OutputID(evidence table). So, in a query linking the tables, you'd get a Project showing all its Outputs with the associated evidence records.

 

[DAmoss]

The ProjectID (Project Table) connects to the OutputsID (Output Table) in a one-to-many relationship, this then connects to the EvidenceID (Evidence Table) in a one-to-many relationship as well.

When I look at the first Project record, I get all the associated Outputs & Evidence data showing correctly, but the minute I create a new Project record, I have to re-enter all my supposed 'Fixed' data again.

Is there a way of using my prefilled Output/Evidence records for any new Project record created? Think of the already filled Output/Evidence records as a predefined template that needs attaching to each new project record.

 

[fneily]

Then the statement "The ProjectID (Project Table) connects to the OutputsID (Output Table) in a one-to-many relationship" is not true.
"using my prefilled Output/Evidence records for any new Project record created" shows that you have a many-to-many relationship between the Project table and the Output table.

So, you have to create what is commonly known as a junction table. Relational databases do not like many-to-many relationships. Junction tables, at minimum, contain the primary keys of the two tables. You'll be creating two one-to-many thusly:
tblProjOutPut
ProjOutputID Primary key
ProjectID
OutputID
Any other COMMON fields you need. (maybe you don't have any)

So now, many Projects can have the same output records and an Output record can belong to many Projects.

So the project table connects to the junction table, through projectid, and the junction table connects to the output table, through OutputID. Two one-to-many relationships.

 

[DAmoss]

fneily.

Thanks, I'll give it a go at the weekend and let you how I get on. I need to do some research on 'junction' tables first as this is new to me.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DAmoss]

PHV ... thanks for the pointer

Hmmm, I created my junction table as suggested and yes everyone of my Projects can now access all the prefilled information contained in the TblOutputs / TblEvidence.

But, there is a problem, I wanted each project to have its own copy of the TblOutputs / TblEvidence, not one global set.

Can anyone help or is this just not possible to do in Access?

Thanks

 

[fneily]

Do you mean each project has its' own Output TABLE and Evidence TABLE??? If that's it, you'll get no one to agree to that. If you read the article that was referenced by PHV, the point of a relational database such as Access (there are many others, such as Oracle) follow protocols. One aspect of these protocols is to avoid duplicate data. Your way would have just that. There is no reason to have multiple tables with duplicate data. None. Serves absolutely no purpose. Madness. I must lie down.

 

[DAmoss]

No I don't want want each project to have its own Output & Evidence tables

I don't think I'm explaining exactly what I want to well, so please accept my apologies. I'll have one more stab at explaining:

Imagine that each record in the Outputs table is a question and attached to each question is the Evidence (in this case a checklist of items) (Q&E's)

I've created all the questions and their associated checklists and stored them in the ... Outputs/Evidence tables. I now want to attach a copy of all these questions and checklists to each new project record that i create.

Maybe it would better if I just built in all the Q&E's as straight forward fields in my project record, that way they would all be available to each project record, it's just that I have an awful lot of questions and checklists!

Thinking about it ... is there a way of copying all the records & data attached to the first record and adding them to a newly created record?

Tnanks in advance for all your help.

 

[fneily]

Still lost. In a query, you'd have my junction table(tblProjOutput), tblOutput, and tblEvidence.

The junction table will have a line connecting OutputID from the junction table to OutputID of the Output table. There would also be a line connecting the Output table to the evidence table. You would then have a criteria for a specific ProjectID. It would produce a ProjectID with all its Output records and each Output's corresponding evidence records.

Your way is against the protocols of Normalization. Did you read the suggested article?

 

[MajP]

Take a look at these examples. Similar idea.

http://www.4shared.com/dir/6086223/c26decf2/PartCatClass.html

In the parts example the user has about 60 categories that get assigned to a part. After you add a part you can choose a button at the top of the form to assign all categories or you can use the list box to add or pick a category to add. It would be the same idea: Project = Part, Evidence/Output = category.

The other example is a horse show. Each horseshow has the same classes (events). So when you make a new horseshow you get all the classes assigned. Project = Show, Evidence/Output = classes.

In both cases you use and append query to append records from a reference table to a junction table.

 

[DAmoss]

fneily:
Yes I read all the articles, I also created a junction table as you said previously, but it doesn't do exactly what i'm after as each project can now alter the one set of Outputs and Evidence.

I've put a download link in so that you can see what I've done so far, I think i've done everything correct but I may have missed something in your previous explanations.

MajP:
Thanks for the link, but i can't seem to be able to download the examples, do you have to sign up to the website first?

 

[MajP]

Got to the bottom of the page.
Hit "download", a new screen pops up. Wait about 15 seconds and then it will let you download.

 

[fneily]

I have Access 2000 and you're database was created with a later version. I can't open it. Sorry.

 

[DAmoss]

fneily:
I've converted it to an Access 2000 version for you, let me know if you think it's right, cheers.

 

[DAmoss]

MajP:
i've now managed to download the examples but they won't open ... security warning, 'this file needs to be copied to a network location first' ???

 

[fneily]

Get rid of all the autonumber primary keys. As you can see with your tblProject, when you delete a number, autonumber doesn't go back and use that number again. It justs keeps incrementing. This will mess you up. Make up a primary key for all tables.
Also, you're using the primary key of the evidence table (The many side) to link to tblOutput (the one side). Not correct. The evidence table has it's own primary key. You'll then have OutputID as a field in the evidence table as a common field. This connects to the tblOutput.
Then add more data to your tbljunction.
Create a query with all the tables and you can then make a list of all projects with their corresponding outputs and evidence.
Or you can create a form with a combobox the will list the project names. When a user selects one, on the AfterUpdate event, you can run a parameter query using the combobox value and use the query as a source for a form or report.

 

[DAmoss]

MsjP:
I've now managed to get the examples working (once I remembered to unblock them!)

They look very interesting and i think they might actually do what i want.

 

[DAmoss]

fneily:
Thank you for all your help, but i've come to the conclusion that what i'm trying to do is probably beyond my own abilities!

I tried to follow your instructions but I got stuck and failed miserably, so it's back to the drawing board for me.