Using Multi Select List boxes to Run Queries

[jsanders]

I have a simple question that I know will have a very complex answer


You have two sets of tables with identical structures. The first set contains data that is a model for the second set. The users run queries that append data to the second set of tables

Here is an example of the structure
TblSourceOne
SourceOneID
SourceOneData

TblSouceTwo
SourceTwoID
SourceOneID
SourceTwoData

TblSouceDetails
SourceDetailsID
SourceTwoID
SourceDetailsData

As you can see TblSourceTwo contains a foreign key from TblSorceOne and TblSorceDetails contains one from TblSourceTwo

The main table for the second set

TblTargetOne
TargetOneID
SourceOneID
TargetOneData

The user adds a record to the main table (TblTargetOne) then uses an append query to add all of the applicable records from TblSourceTwo to TbleTargetTwo

TblTargetTwo
TargetTwoID
TargetOneID
TargetTwoData

So now the user has a data model in TblSourceTwo which has been “copied” to TblTargetTwo

Next for each record in TblSourceTwo that has been copied to TblTargetTwo that has daughter records in TblSourceDetails another query is executed to copy all of the pertinate records to the TblTargetDetails

TblTargetDetails
TargetDetailsID
TargetTwoID
TargetDetailsData

Now the user has completely copied all of the necessary record for both table.

Finely the question

How can I use a multiselect list box to achieve this in one operation? I don’t care if it includes running multiple queries but it needs to appear as one single operation to the user.

 

[Rich]

I have to ask why you have tables duplicating data?

 

[jsanders]

I have to ask why you have tables duplicating data?

The data in the Source tables is a model for reoccurring events. But it doesn’t necessarily stay static once the source data is appended to the Target tables, it needs to be unique and editable for every future event. The Source tables serve only as a model of the possible collection of records that comprise the Events which are recorded in the target tables.