Tables/query problem

[lironh]

Hi all,
I'm a newbie in access and i'm trying to make a small application in access that will have the following functionality:

- The user of the app can add new 'test flows' which can include 'test cases' and 'actions'.

- 'test cases' can include 'test cases' and 'actions'

- 'actions' are atomic units and include lines of code (text)

- The app should have a small query that can display a report with all the "Fathers" of a specific 'action' or 'test case'.

I need to build this tables somehow and not sure how, espcially because the 'test flow' table can include unknown number of 'test cases' and 'actions'.

Can someone please send me a .mdb file with this requirements implemented inside it and i'll analyze it and learn from it ?

 

[Pat Hartman]

You can ask people to build your application for you for free but you may not get any takers. I assume that you are being paid to produce the database. People here are happy to answer questions for you and give you guidence but you need to do the work yourself. Otherwise, you should hire a consultant.

 

[lironh]

hehehehe

Hi Pat,
Well ... as a matter a fact it's a Staj i'm doing and not getting paid for it and this app is a very (very) small part of my Staj.

Sure I need a direction here but I think that doing some tables in access is very easy and we're talking about 3 maybe 4 tables ...

I would like to get a direction ... anyone ? .mdb file will be help as well

 

[Pat Hartman]

Make a start, post what you have and THEN ask specific questions.

 

[lironh]

well ...

Currently I have the tables:
1. 'Actions' with the fields - 'actionID', 'actionName', 'codePath'
2. 'Test Cases' with the fields - 'testCaseID', 'tcName', and it need to have an 'actionsID' and 'usedTestCaseID' field but i'm not sure how to create it...
3. 'Test flows' wth the fields - 'testFlowID', 'tfName' and it need to have a 'testCases' field with all of it's used test cases but I don't know how to define it too...

I'm pretty newbie in queries definition too so the query im talking about should be done after the tables/relationships are defined.

Thanks ...

 

[Pat Hartman]

What you have described is a hierarchial relationship. That is implemented as a series of 1-many relationships. Each child table points to its immediate parent. So, tblTestCases includes the key of tblTestFlows and tblAction includes the key of tblTestCases. 1-many relationships are implemented by storing the key of the 1-side table in each row of the many-side table where it is referred to as a "foreign key". Think of it in terms of a real parent/child relationship. A parent can have many children but a child has only one parent. So rather than trying to figure out how many childIDs you need to store in a parent record, the problem is resolved by storing the parentID in the child record.
tblTestFlows:
testFlowID
tfName

tblTestCases:
testCaseID
tcName
testCaseID (foreign key to tblTestCases)

tblActions:
actionID
actionName
codePath
testCaseID (foreign key to tblTestCases)

 

[lironh]

thx Pat

But how do i make the specific query I need to get the fathers of each hierarchy level component ?

 

[Pat Hartman]

I forgot to give my normal speech about defining relationships. So, open the relationship window and add all the tables to it. Then draw join lines between the primary key of one table and the foreign key in its "child" table. Check the enforce RI box and select the Cascade delete option.

Use the query builder. Add the tables that contain the data you need to the QBE. Access will automatically add the join lines since you defined the relationships in the relationship window. Select the columns you need from each table.

 

[lironh]

double table

Hi Pat,
I've addede all the relationships but when I've added the testCase to testCase relationship I got two same tables (the second with _1 suffix) - is it what you intended to do by connecting a table to the same table ?

 

[Pat Hartman]

Yes, if you need to use a table more than once to create a relation or in a query, Access automatically suffixes the second and subsequent instances so they can be uniquely identified. The table is not being deleted. This is simply a convention that is used to keep the relations and joins straight.

 

[lironh]

New optional direction

Hi ...

I've decided that due to the similarity of 'Actions', 'TestCases' and 'Test Flows' that i'll put them all in the same table with another field of their type (0=action, 1=test case, 2=test flow) and with another 'types' table in order to know the name of the type.

Now the only question left is how to make the hierarchies -
1. Test flow can have a test case sons only
2. Test case can have test case or action son
3. Action don't have any sons

and ... another new two requirements -

1. The test flow which is the higher level can be built from test cases but their order is important, for example -
(test flow=tf, test case=tc, action=act)

TF0
---TC0
------ACT0
------ACT1
------ACT2
---TC2
------TC1
---------ACT3
---------ACT4
------ACT1
------ACT2
---TC0
------ACT0
------ACT1
------ACT2
---TC3
---TC2
------TC1
---------ACT3
---------ACT4
------ACT1
------ACT2

2. If a component was duplicated from another one the ID of the origin component should be saved as 'OriginComponentID'.

My question is - how do I preserve the order of the test flow ? as you can see TC2 for example appears few times and in different "trees" and how do I try to fullfil all requirements and still have normalized tables ??

The current tables I have are -

tblComponent - ComponentID (key), ComponentTypeID (foreign), Name,
OriginComponentID

tblComponentTypes - ComponentTypeID (key), Description

 

[lironh]

anyone ? PAT ?

Do you have any reply to my last question ?

 

[Pat Hartman]

I've been playing bridge for three days.

Your requirements have changed from your original description. You now seem to have a many-to-many relationship. M-M relationships require three tables to implement or two tables, one of which is related to itself. You can store a sequence number in the relationship record to keep the sets ordered.

 

[lironh]

three tables

How do I implement the three M-M tables? please rememmber that I'm trying to make here a M-M relationship for the same table ...
'OriginComponentID' should be connected to 'ComponentID' in order to know from where is the component was duplicated from,
and 'ComponentID' should be also connected to 'ComponentID' in order to know the hierarchy father of the component.

 

[Pat Hartman]

In the QBE, add the new relation table and then add the component table twice. In the relation table, join the first foreign key to the first instance of the component table and join the second foreign key to the second instance of the component table.