relationships

[fathyano]

I have two forms, first one is for all the transactions with the date of each one, second one is for all the actions occured. The transactions and its dates is a filed in the general form (actions in general). I want to link the two tables with a relation, so that the LAST transaction (took place yesterday or today or at any date) is the only one to be viewed in this form, considering that every day or every few days a transation added to the transactions table, so I want to say to the form of the actions: read always the last recorded transaction only.

 

[jzwp22]

I'm not sure I understand your table design. Could you provide the structure of your tables (table name, field names and relationships between tables)? It sounds like you have a table of transactions and a table of action that are not transactions. Am I close? Is there some logical link between the two tables?

 

[MSAccessRookie]

I have two forms, first one is for all the transactions with the date of each one, second one is for all the actions occured. The transactions and its dates is a filed in the general form (actions in general). I want to link the two tables with a relation, so that the LAST transaction (took place yesterday or today or at any date) is the only one to be viewed in this form, considering that every day or every few days a transation added to the transactions table, so I want to say to the form of the actions: read always the last recorded transaction only.

I am not sure that I see a real need for two Tables here when a single Table that has a TransactionDate Column could have the same effect. Please provide more details.

 

[fathyano]

Ok, sorry for being not clear, and thanks for your care, here is the exact details:

• The database is for a law firm, for recording the details of the cases, theses details include:

1. 1. Name of the the parties of the disputes (our client and the opponent/s),
   2. The kind of the case, e.g civil, commercial, criminal, etc........
   3. First degree decision, second degree decision, third degree decision
   4. The related detail now is the hearings date and what is the decision of this hearing:

• • • The first field: - hearing's date, for example June 9, 2009.
    • The second filed is: - the decision of this hearing, for example the court decided to adjourn the case to allow a party to provide some document, and this party shall provide it at specific date; i.e the date of the next hearings, which is:
    • The third field: - the date of the next hearing of which the party shall provide the court with the required documents, this date is June 20, 2009.

2. AND SO ON

3. Here is the way of which the decisions written of the case paper file:

Date Decision
1/1/2009 appointing an expert
10/1/2009 for submitting the report
15/1/2009 parties comments
1/2/2009 expert's reply on the comments
15/2/2009 court decision

4. the court decided at 1/1/2009 to adjoiurn the case to 10/1/2009 to appoint an expert an declare his name

at 1/1/2009 the expert has been appointed and the case adjourned to 10/1/2009 to submit his report

at 10/1/2009 the court decided to adjourn the hearing to 15/1/2009 for the parties to submitte their comments on the report

5. Thats it, but I dont want to write all these dates and decisions in the form, I want it to be written in an independant form, written in it all the decision.

6. I want to link this form to the main form, like that:

hearing date: 1/1/2009
decision: appointing an expert
next hearing: 10/1/2009

but each hearing, these details always changing, so I want the form (automatically) to display the last two recent dates from the other form, which is:

1/2/2009
15/2/2009 court decision

It means that at 1/2/2009 the court has decided to adjourn the case to 15/2/2009 for issuing the decision.

Of course the next date shall be always in the future

I hope it is clear now, please I need too much care about it, taking into consideration that I am not too much professional in using access.

Thanks
​

 

[jzwp22]

Thank you for providing the details of what you are trying to accomplish with the database. You mention that you have two tables, could you provide the the structures of the two tables like I have shown below in a general format?

table name for first table
-name of first field in this table
-name of second field in the table
etc.


table name for second table
-name of first field in this table
-name of second field in this table
etc.

Alternatively, you could post a copy of your database with any sensitive data removed if that is easier for you.

 

[fathyano]

This is an example for my case,

Do i have to make a new table for each case???!!!! IMPOSSIBLE

Now I want to link the decisions table with the details form, so that only the last two fields appear as I explained before,

thanks

 

[jzwp22]

No, you do not need to make a table for each case, but you do need to change your current structure.

I would have a table to hold the case information

tblCases
-pkCaseID an autonumber primary key field
-CaseNo
-fkCaseTypeID a foreign key to a table that holds the various case types

tblCaseType
-pkCaseTypeID primary key, autnumber
-txtCaseType

Then I would have a table to hold people related to a case. Since you have many people related to a case, you have a One (case)-to-Many (people) relationship which is captured in a related table

tblCasePeople
-pkCasePeopleID an autonumber primary key field
-fkCaseID a foreign key to related back to tblCases
-personName
-Role (client or opponent)

Each case will have many decisions. In your current table, you do not relate the decision to a specific case. This table will do that

tblCaseDecisions
-pkCaseDecisionID an autonumber primary key field
-fkCaseID a foreign key to relate back to tblCases
-decisiondate
-decision
-NexthearingDate

You will have to capture all of the decision details, but you can use a query to show just the two most recent ones in your form. But let's worry about getting your tables structured correctly first.

I've modified the database you attached previously. I've included the tables I described above and a form.

You might benefit from the tutorials on this website