copying records problem

[drkhelmt]

I work for a MFG company. I am in the process of updating a sale database... I have the saleItem for all the items on a sale. Each saleItem has parts assigned to it, and work assigned to it. What I need to do is create a way to copy sale items since 2 sale items can be very similar, and later on create a way to copy sales, since sales and their saleItems can be similar.

I thought about doing it though recordsets and loops, but i wasn't sure if there is a better way. The tables are as follows:

sale (saleID,custID,...)
saleItem(siID,saleID,...)
partAssign(partAssignID,siID,partID,quantity,...)
workAssign(workAssignID,siID,workCoID,hours,...)

Any help is appreciated.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[TheAceMan1]

How are ya drkhelmt . . . . .

[blue]What I need to do is create a way to [blue]copy[/blue] sale items since 2 sale items can be very similar, and later on create a way to [blue]copy[/blue] sales, since sales and their saleItems can be similar.[/blue]

To [blue]copy[/blue] and do what with the [blue]copy[/blue]?

Be more specific about the intent of the [blue]copy[/blue]!

In the mean time, have a look at the following . . . this may clear some issues:

Normalizing Tables Table Relationships

See Ya! . . . . . .

 

[MajP]

I think I understand what you are asking, but I am not sure. I believe that you want a many-to-many relationship. If your not familiar with this you need to add a join table. I would have an items table full of the unique items that I sell. I would also have a sales table. But since one "sale" would be associated with more than one "item", and many "sales" could be associated to the "same" item, I have a many-to-many relationship.
So I create a join table that has the Sale ID and the Item ID in it. So assume I have two "Sales". Sale 1 has three Items, Sale 2 has four items. Some of the items are in common (Item 8, Item 16). Your Join table would look like:

SALE ID ITEM ID
Sale 1 Item 2
Sale 1 Item 16
Sale 1 Item 8
Sale 2 Item 16
Sale 2 Item 11
Sale 2 Item 9
Sale 2 Item 8

Building a nice many-to-many Form takes a little work. If this is what you need I could send an example. You never have to copy anything since you just pick from you unique set of items. I could also add a third column to my join table, such as "Item Quantity". Now I have unique information about a common item for a specific sale.

 

[drkhelmt]

MajP, you're on the right track, but instead of there being a many to many, the tables stay as one to many

So inorder to copy say, Item3, I need to copy the record in saleItem and paste it to saleItem with a new siID (Item4), and then copy the records in partAssign that are associated with Item3 and duplicate them to be assigned to Item4 and then do the same for workAssign.

Sorry for the confusion on this question.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?