Queries for seletion, Add, and Delete

[Guest]

Not sure how to properly configure queries so I can 1) View/Delete existing
Activites for a selected Vessel and 2) View/Add activity list items not yet
associated with a selected vessel. (filtered by VesselID). For this example I
am using a single form with the Vessel selection on top and a subform with
Activities on the bottowm but am open to other solutions.

Note I had a tabbed subform with 9 tabs and would like to eventually get it
working this way since I have 8 other tables like activities to deal with.

Design:

tblVessels:
vesselID, Autonumber, primary key
Name, text
....
tblActivities:
activityID, Autonumber, primary key
activity, text
....
tblVesselActivities:
activityID, number, primary key
vesselID, number, primary key

tblVessels have a (1 to Many) relationship with tblVesselActivities
tblActivities have a (1 to Many) relationship with tblVesselActivities
tblVesselActivities is a linking table between tblVessels and tblActivities

Not sure what is wrong or direction. Any help is appreciated.

 

[John Vinson]

Not sure how to properly configure queries so I can 1) View/Delete existing
Activites for a selected Vessel and 2) View/Add activity list items not yet
associated with a selected vessel. (filtered by VesselID). For this example I
am using a single form with the Vessel selection on top and a subform with
Activities on the bottowm but am open to other solutions.

Base the Subform - not on Activities - but on VesselActivities. It's
handy to have a Combo Box on the subform to store the ActivityID but
display the activity name, and to make it a Continuous subform. This
will let you select any existing activity; you can put code in the
combo box's Not In List event to open an Activities form to add a new
activity never before used if you wish.

John W. Vinson[MVP]

 

[Vincent Johns]

This doesn't exactly address your question, but some of what you said
raised a flag that I'd like to comment on...

Not sure how to properly configure queries so I can 1) View/Delete existing
Activites for a selected Vessel and 2) View/Add activity list items not yet
associated with a selected vessel. (filtered by VesselID). For this example I
am using a single form with the Vessel selection on top and a subform with
Activities on the bottowm but am open to other solutions.

Note I had a tabbed subform with 9 tabs and would like to eventually get it
working this way since I have 8 other tables like activities to deal with.

Since, as you say, the 8 other Tables contain similar information, what
is the value of keeping them separate? Even if the fields aren't
identical, you could have just one Table (with an extra field indicating
which of the 9 original datasets the record belongs to) and a collection
of fields that includes all that you might need. For example, suppose
that only Tables 5 and 8 include a [DanceInstructorID] field. I think
you could include [DanceInstructorID] in the combined Table without
wasting lots of space, even though none of the other Tables contains a
value in that field. (Generally, adding a foreign key doesn't need to
add more than about 4 bytes per record; in this case, you could probably
do it in 1 byte per record.)

You would still be able to use a tabbed form to display only the records
from a specified subset. (Or use some other control to select the subset.)

Design:

tblVessels:
vesselID, Autonumber, primary key
Name, text
...
tblActivities:
activityID, Autonumber, primary key
activity, text
...
tblVesselActivities:
activityID, number, primary key
vesselID, number, primary key

Here, I assume you mean that [activityID] and [vesselID] jointly form a
primary key to [tblVesselActivities], which is OK... but [activityID]
apparently also acts as a foreign key referring to [tblActivities], and
[vesselID] is a foreign key referring to [tblVessels]. Primary keys and
foreign keys serve different functions. For example, I expect that, in
this Table, the foreign keys are essential (Table is useless without
both), but it's possible that you never use this Table's (combined)
primary key anywhere in your database.

tblVessels have a (1 to Many) relationship with tblVesselActivities
tblActivities have a (1 to Many) relationship with tblVesselActivities
tblVesselActivities is a linking table between tblVessels and tblActivities

Not sure what is wrong or direction. Any help is appreciated.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Good help so far.

Now have two tables and I want to get the rults from subtracting all records
in table2 from table1.

 

[John Vinson]

Now have two tables and I want to get the rults from subtracting all records
in table2 from table1.

I'm confused. Which is Table1 and which is Table2? What do you mean by
"subtract" - arithmetic subtraction of a value, or a set operation
returning all records in Table2 which do NOT exist in Table1, or what?

John W. Vinson[MVP]