Update multiple records at once

[ariklewis]

I have an imported table of piece marks.

Piece Mark	Quantity
10AP1	3
a10	12
10AP2	12
a10	36
10AP3	4
a10	12
b10	4
10AP4	2
a10	8
10AP5	6
a10	24
10AP6	65
c10	260
10AP7	28
d10	112

I need to be able to search through them to assign the major piece marks (the ones with Caps ie. 10AP1) and their minor piece marks (them ones with lower ie a10). The piece marks are in order, so each major piece has its corresponding minor piece listed below is till there is another major piece.

The goal is to assign 10AP1 as finished and have its corresponding pieces ie. a10 marked as finished also.

[June7]

Without a defined relationship (two tables with primary/foreign key association) need to know what criteria determines which records to update. Are all of the majors of this data sample to be marked? Do you want b10, c10, and d10 to be marked also? Is the criteria any minor value with 10 in it?

If you want to mark only 10AP3 and its two related minors, this will require VBA code using recordsets and even then I am not sure it would work right. Is there a unique ID field in this table?

[boblarson]

Well first off this data should probably have the minors in a separate table as they are really a many-to-many relationship.

So in the

tblMajors
MajorID - Autonumber (PK)
MajorPiece
Qty
Finished

tblMajorMinors
MajorMinorID - Autonumber (PK)
MajorID
MinorPiece
MinorQty
Finished

So the data would be as:

MajorID   MajorPiece       Qty       Finished (True/False)
    1           10AP1           3           True
    2           10AP2         12            False
    3           10AP3           4           False
 
 
MajorID    MinorPiece       MinorQty        Finished
1                a10            12               True
2                a10            36               False
3                a10            12               False
3                b10             4                False

So something like that.

[ariklewis]

The criteria for the update would come from a form. The form would search major pieces only. Upon marking 10AP3 finished, a10 and b10 directly under the major would be updated also. Each major's minor is listed directly under it. I agree that they should be separated but this comes from an export out of another program, so how would I separate the data?

[boblarson]

The criteria for the update would come from a form. The form would search major pieces only. Upon marking 10AP3 finished, a10 and b10 directly under the major would be updated also. Each major's minor is listed directly under it. I agree that they should be separated but this comes from an export out of another program, so how would I separate the data?

Well, first off, if the pieces under it are complete when the major is done, you don't need to have that field in the table for the minors. You could probably just leave it to the majors.

As for entering it in the table, that is a bit more complex. Even if you were to leave it in the original table, it isn't necessarily going to fit together because records in Access, even during the import process, aren't stored in any meaningful order. You need to assign order to them. So you would likely need to assign a numbering sequence while the data is still in Excel BEFORE you imported. Otherwise you would have a problem. The numbers should probably be assigned as the same to each set of data. Then you could simply bring it all into a single INTERIM table and then use a couple of append queries to move the appropriate parts out to their respective tables.

[ariklewis]

The piece marks aren't coming from Excel, they are coming from an industry specific program. When I export from the program and into Access, this is the format they come in. There aren't any ID's or any way to differentiate the minors belonging to the majors except that the minors are lowercased and are placed directly below the Uppercase majors. If during the import I assign an ID to them, can a query or VBA be done to find a major, and extract the next X records as long as they are lowercase and append them to a minors table with the major ID?

[boblarson]

The piece marks aren't coming from Excel, they are coming from an industry specific program. When I export from the program and into Access, this is the format they come in. There aren't any ID's or any way to differentiate the minors belonging to the majors except that the minors are lowercased and are placed directly below the Uppercase majors.

Don't export them directly to Access. Go to Excel instead and add the identifiers there. As I said, you cannot add the identifers during the import to Access because it will not necessarily keep the records together when importing. This is a very important concept to understand. Unless you have something that can be sorted on BEFORE YOU IMPORT, you can have your data all messed up.