I need to create one record in a second table for every possible group of records in the join table. To clarify, the join table contains multiple records with the same ID. I need to "group" these records (I assume by a sort).

There are a couple things I need to do:

1. Gather the quantity of a field into a variable for the group of records which i will then pass to the single record created in the second table.

2. Assign a type value for the record in the second table based on type values in the group of records.

For number 2, lets assume that the type field can be assigned one of two values (A or . If all records are type A, then the value is A. If all records are type B, then the value B. If the group contains both A and B, then A is the type I need to pass to the record created in the second table.

Any thoughts or insight would be most appreciated.

Edited October 26, 200718 yr by Guest

Can you explain with real examples? I can't see why you'd create a redundant table, rather than use summary functions.

Here is an example:

I have a quote which is comprised of packages. These packages are comprised of items. (PackageItems is the join I was referring to). Based on business rules for the client, I need to register the quote. This process involves creating a registration record for the quote and also create registration line items. Essentially, the registration line items need to represent the items used from the items table. Because the items can be used in multiple packages, I need to summarize when creating in the registration line items. This must be done while leaving the current quote, packages and package items alone becuase they can be changed and possibly re-registered at any time.

So, the registration doesn't need the package info, just the tally of items in the quote?

Secondly, is it true that a registration is capturing a quote at a moment in time, since you say a quote can be registered and is editable after it's registered?

I played around with a demo (see attachment). Anything like what you might need?

Topic_191003.zip

Edited October 28, 200718 yr by Guest
added attachment