make table query with related tables

[Access G-man]

I have an equipment maintenance database, I would like to put my inactive
(gotten rid of) equipment in an inactive table, and not loose the
relationship with the maintenance table. basically make two tables. I did a
make table query, made my inactive equipment table. I could not delete the
equipment from my first table due to key rules. I then made a relationship
from my equipment table to my maintenance performed table. could not make a
one to many and enforce integrity due to the first equipment table. I am
going to delete the relationships, run a delete query on my inactive marked
equiment in my equiment table, and see if I can make both equipment and
inactive equipment one to many and enforce integrity. Do you think this is a
good way.
I want to be able to mark a piece of equipment as inactive and have a macro
make it go from one table to the inactive table and not loose its maintenance
records.
Let me know if I am going at it correctly please.

 

[Tom van Stiphout]

On Tue, 22 Jan 2008 17:47:56 -0800, Access G-man

No I don't think this is a good way. Rather I would keep all equipment
in one table, and have an Active yes/no field.
In most queries you would only display the active equipment:
select * from Equipment
where Active=True

Another technique I have used is to have a dropdown showing first the
active equipment, then the inactive:
select * from Equipment
order by Active, EquipmentName
Your dropdown could have two columns: EquipmentName and Active.

A more elaborate technique uses a union query to first display Active,
then a separator line (perhaps containing text like 'please select
from above this line'), then the inactive items. Additional code can
ensure the user cannot choose from below the line.

Many options. It all depends how much effort you want to put into it,
and what would work best for your users.

-Tom.

 

[John W. Vinson]

I have an equipment maintenance database, I would like to put my inactive
(gotten rid of) equipment in an inactive table, and not loose the
relationship with the maintenance table. basically make two tables. I did a
make table query, made my inactive equipment table. I could not delete the
equipment from my first table due to key rules. I then made a relationship
from my equipment table to my maintenance performed table. could not make a
one to many and enforce integrity due to the first equipment table. I am
going to delete the relationships, run a delete query on my inactive marked
equiment in my equiment table, and see if I can make both equipment and
inactive equipment one to many and enforce integrity. Do you think this is a
good way.
I want to be able to mark a piece of equipment as inactive and have a macro
make it go from one table to the inactive table and not loose its maintenance
records.
Let me know if I am going at it correctly please.

You're making it much harder than it needs to be.

Add one Yes/No field, Active, to your equipment table. Set it to Yes/-1 for
all active equipment, No/0 for inactive equipment.

Base your forms and reports on a query selecting only active equipment.

If the field is indexed you'll never notice the performance difference (well,
if you have 15000 active records and 150000 inactive you might if you
concentrate...)

John W. Vinson [MVP]