Sort records in a subform

  • Thread starter Thread starter Rick B
  • Start date Start date
R

Rick B

I have greatly modified the Northwinds database. My wife sells Mary Kay products and enters them in the invoie form. The invoice details contains the individual items. In most cases, the order that these items display or print would not be that important, but she hs these "kits" or "bundles" that she sells so the order is important.

For example...

PART DESCRIPTION RETAIL SELL QTY EXT SELL
0000K1 ***Kit 0001 Includes: 0.00 5.00- 1 5.00-
129779 Lipstick - Some Funky Pink Color 15.00 15.00 1 15.00
105689 Blush - some other color 12.00 12.00 1 12.00
197779 Satin Hands 55.00 55.00 1 55.00
0000K2 ***Kit 0002 Includes: 0.00 5.00- 1 5.00-
136779 Dermabrasion Gift Kit 55.00 55.00 1 55.00
245608 Lotion 8.00 8.00 1 8.00
107589 Compact 22.00 22.00 1 22.00
384668 Lotion 8.00 8.00 1 8.00


Since I need the kit line to come right after or right before a list of products, what would you recommend I do to keep them in order? I don't want to add an autonumber to my details table because she might screw up and enter them out of order, then go back and insert a line which would have a higher autonumber and would reorder when printed.

Not everything will be part of a kit. As a matter of fact, most things will not.

Each kit is unique. Kit 001 for "Sue" will contain different colors and styles of products thant kit 001 for "Joan" so I can't really build "defined" kits. I just need to apply a discount (or two or three) and keep the products with the kit.

I thought about adding a "kit part" field to the product and I could tell it which kit each part was part of (drop down with valid entries of K1 - K9), but I don't know if that is the best idea!

Let me know if I need to provide more details. Don't worry about preserving my current table structure. I can work with whatever suggestion you have.

Thanks for the advice!
 
I have greatly modified the Northwinds database. My wife sells Mary Kay products and enters them in the invoie form. The invoice details contains the individual items. In most cases, the order that these items display or print would not be that important, but she hs these "kits" or "bundles" that she sells so the order is important.
...

Since I need the kit line to come right after or right before a list of products, what would you recommend I do to keep them in order? I don't want to add an autonumber to my details table because she might screw up and enter them out of order, then go back and insert a line which would have a higher autonumber and would reorder when printed.

Not everything will be part of a kit. As a matter of fact, most things will not.

Each kit is unique. Kit 001 for "Sue" will contain different colors and styles of products thant kit 001 for "Joan" so I can't really build "defined" kits. I just need to apply a discount (or two or three) and keep the products with the kit.

I thought about adding a "kit part" field to the product and I could tell it which kit each part was part of (drop down with valid entries of K1 - K9), but I don't know if that is the best idea!

Let me know if I need to provide more details. Don't worry about preserving my current table structure. I can work with whatever suggestion you have.
Click to expand...

You have a one to many relationship from Kits to "parts" (even the
single-part kits will have a kit). As such you really need to model
the one-to-many real-life relationship with a one-to-many table
relationship. I'd say you need a Kits table and an Items table (can't
really call them parts).

Storing data in a Table does NOT define a sort order; a table is an
unordered "heap" of records. Even an Autonumber cannot be counted on
to keep order in this case, since you will probably need to remove and
add items - and you cannot add or edit an autonumber in the middle of
an existing sequence, only at the end, as you have realized.

Your Kits table will need to have both a kit number (your 001) and (I
presume) a CustomerID; it would be related many-to-one to the
Customers table, probably with the CustomerID and the KitNumber as a
joint two-field Priamry Key, or at least with a unique index on these
two fields.

John W. Vinson[MVP]
 
Back
Top