Updating Groups of Records via a Form

[SplitEdgeMan]

I have a table with mass amount of records. I have two grouping
fields,[Job#] and [CO#]. I have one sorting field, [D#]. Each record
has has multiple other data fields. I have a report that prints all
items per Job# and also a reports that prints all items per CO#.

Each Job# has multiple CO#'s and each CO# has multiple D#'s. There are
multiple records where the only difference is the D#. Is there a way
that I can group all lines that are the same within a CO# with the
exception of the D# and update one field in all records?

Example:

Job# CO# D# Description Price Multiplier
1011 001 10 Hardware 10 00.00 0.00
1011 001 11 Hardware 10 00.00 0.00
1011 001 12 Hardware 09 00.00 0.00
1011 002 10 Hardware 10 00.00 0.00
1012 001 05 Hardware 01 00.00 0.00

I would like to set the Price and Multiplier for for the items with
Job# = 1011, CO# = 001, Description = Hardware 10. Price and
Multiplier should be able to be entered in via a form for this item.
The for would show the Job#, CO#, Description, Price, and Multiplier.
The Price and Multiplier should be set to update all records with
matching Description.

The GroupBy function of a Query does the grouping that I need, but
makes all records uneditable.

Any solutions?

 

[Vincent Johns]

This isn't exactly an answer to your request, but I hope it will help.
Based on what you say you want to do, I'm not happy with your Table design.

I have a table with mass amount of records. I have two grouping
fields,[Job#] and [CO#]. I have one sorting field, [D#]. Each record
has has multiple other data fields. I have a report that prints all
items per Job# and also a reports that prints all items per CO#.

Each Job# has multiple CO#'s and each CO# has multiple D#'s. There are
multiple records where the only difference is the D#. Is there a way
that I can group all lines that are the same within a CO# with the
exception of the D# and update one field in all records?

Example:

Job# CO# D# Description Price Multiplier
1011 001 10 Hardware 10 00.00 0.00
1011 001 11 Hardware 10 00.00 0.00
1011 001 12 Hardware 09 00.00 0.00
1011 002 10 Hardware 10 00.00 0.00
1012 001 05 Hardware 01 00.00 0.00

I would like to set the Price and Multiplier for for the items with
Job# = 1011, CO# = 001, Description = Hardware 10. Price and
Multiplier should be able to be entered in via a form for this item.
The for would show the Job#, CO#, Description, Price, and Multiplier.
The Price and Multiplier should be set to update all records with
matching Description.

Are you saying that [Price] and [Multiplier] should (always) have the
SAME values whenever the [Job#], [CO#], and maybe [Description] have the
same values? If so, there's no need for multiple copies. In fact, I
suggest that there's a good reason to get rid of duplicates (they take
extra effort to maintain, and you pay a slight performance penalty in
Access).

The GroupBy function of a Query does the grouping that I need, but
makes all records uneditable.

Any solutions?

So you can set up two Tables, with just one copy of [Price] and
[Multiplier] fields, and not have to worry about whether you updated all
of the matching values; you can update them once each.

[MassiveRecords]

MassiveRecordsID Job# CO# Description Price Multiplier
---------------- ---- --- ----------- ------ ----------
88597 1011 001 Hardware 10 00.00 0.00
23987 1011 001 Hardware 09 00.00 0.00
85678 1011 002 Hardware 10 00.00 0.00
34265 1012 001 Hardware 01 00.00 0.00

[D_NumberValues]

D# MassiveRecordsID
-- ----------------
10 88597
11 88597
12 23987
10 85678
05 34265

For data entry/editing, you have a couple of choices.

Quick & dirty: Define a subdatasheet in Table Design View that will
display the [D_NumberValues] corresponding to a given record in
[MassiveRecords]. Or do the same thing in Query Design View.

Fancier: Define a Form for [MassiveRecords] and a subform inside it for
[D_NumberValues].

You can use a Query to display the data in the format and order you're
accustomed to.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.