How to build the relation of fields in microsoft access table?

[Guest]

I am using Microsoft Access 2003. In the access table I want to establish
relation between two fields of the same table. How can I do this?

 

[Steve Schapel]

J.A.Pandya,

Can you please give some more information, maybe with some example,
about what is meant by "establish relation between two fields of the
same table"? Thank you.

 

[Joseph Meehan]

I am using Microsoft Access 2003. In the access table I want to
establish relation between two fields of the same table. How can I do
this?

I suspect you need to either change your table design or more likely
just us a query to do what you want.

I think we need more specific information to be able to know what you
want.

 

[Guest]

Dear Steve,
At present I am working on technical inspection database. I have following
fields in the table.
1) Visual Inspection date
2) No of days for next Visual Inspection for Class-I
3) Next visual inspection due date.
The requirement is this visual inspection should be conducted every year, so
I have fix the default value 365 days for "No of days for next visual
inspection for class-I" field. Now I have prepare a form with Next visual
inspection due date and I have fix the data control source of this field with
"=Abs([Visual Inspection date]+[No of days for next Visual Inspection for
Class-I])" Equation.
Now I am getting the next due date automatically after entering Visual
Inspection date in form. But this next visual inspection date is not
appearing in table means this date is not being store in table. So I was
thinking to give data control source (relation) to fields of the table. Is it
possible to give such relation to fields of table?

 

[Guest]

Dear Joseph,
At present I am working on technical inspection database. I have following
fields in the table.
1) Visual Inspection date
2) No of days for next Visual Inspection for Class-I
3) Next visual inspection due date.
The requirement is this visual inspection should be conducted every year, so
I have fix the default value 365 days for "No of days for next visual
inspection for class-I" field. Now I have prepare a form with Next visual
inspection due date and I have fix the data control source of this field with
"=Abs([Visual Inspection date]+[No of days for next Visual Inspection for
Class-I])" Equation.
Now I am getting the next due date automatically after entering Visual
Inspection date in form. But this next visual inspection date is not
appearing in table means this date is not being store in table. So I was
thinking to give data control source (relation) to fields of the table. Is it
possible to give such relation to fields of table?

 

[Joseph Meehan]

Dear Joseph,
At present I am working on technical inspection database. I have
following fields in the table.
1) Visual Inspection date
2) No of days for next Visual Inspection for Class-I
3) Next visual inspection due date.
The requirement is this visual inspection should be conducted every
year, so I have fix the default value 365 days for "No of days for
next visual inspection for class-I" field. Now I have prepare a form
with Next visual inspection due date and I have fix the data control
source of this field with "=Abs([Visual Inspection date]+[No of days
for next Visual Inspection for Class-I])" Equation.
Now I am getting the next due date automatically after entering Visual
Inspection date in form. But this next visual inspection date is not
appearing in table means this date is not being store in table. So I
was thinking to give data control source (relation) to fields of the
table. Is it possible to give such relation to fields of table?

I suspect you need to either change your table design or more
likely just us a query to do what you want.

I think we need more specific information to be able to know
what you want.

OK to make a long story short, I don't think you want to store that
date. Storing a computed date or number or any result of a calculation is
not generally a good use of a database. You just re-compute it anytime you
want to see it.

In this case you will want the last date of inspection and the number of
days due for the next inspection stored in the "Inspection" table. This
table should store all information about individual inspections and results
of those inspections. I will guess you should also have a related table
"Locations" where you list things like descriptions and address of
locations. You may also want a client table related to the locations table
where locations are grouped by client and the client information like name
date of contract etc is stored.

You will want to add an (completed) indicator (Yes No check box) to the
Inspection table. Each inspection should create a new inspection table
record. When the next inspection is completed, then that indicator is
changed to Yes from the default No to indicate that the follow-up inspection
has been done.

You would use a query to list pending inspections and the number of days
left until due. This information can be computed by the query from the
information in the inspection table. Another query could show any past due
inspections and yet another could show those completed.

Forms based on the queries could also be used to display the data if you
like.