sum unique records with multiple fields

[Bon]

I have a query with fields qty1, length1, lumber1, qty2, length2, lumber2,
etc... up to 20. I want to sum all qty fields when length and lumber are the
same, for a report. Showing only unique values in the lumber field.
example: 2 67 2x4
3 67 2x4
1 49 2x6
1 49 2x6
To show: 2x4 2x6
5 67 2 49

 

[Allen Browne]

Bon, the solution here will be to redesign the tables so it works as a
relational database, not like a spreadsheet.

Whatever your main record is (an order perhaps), it seems like it can have
up to 20 entries. In a relational database, you create a related table where
you can have many *records*, instead of many columns in the one table.

You will end up with 2 tables, like this:

Orders table:
- OrderID AutoNumber primary key
- OrderDate Date/Time
- ClientID who ordered this.

OrderDetail table:
- OrderDetailID AutoNumber primary key
- OrderID which order this row belongs to.
- LumberID what kind of lumber was ordered.
- LumberLength Number how long
- Quantity Number how many

There will be other tables too, such as a table of clients (since one client
hopefully has many orders), and a table of lumber (defining the products you
sell. In fact, this might contain other products like fixings as well.)

You will interface this as a main form bound to the Orders table, with a
subform bound to the OrderDetail table. Show the subform in Continuous Form
view, so you can see each order line item on a row of its own, one under the
other.

You can now create a report, with a group header for LumberID and
LumberLength, and sum/count whatever you need.

If this idea is new to you, you might like to get Microsoft's Northwind
sample database and see how it works. Open the Relationships window (Design
tab of ribbon in Access 2007, or Tools menu in earlier versions.) You will
see the tables are set up very similarly to what we suggested for you.

For another example of designing these one-to-many relationships, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
For further reading on the art of normalization, here's some more links:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Bon]

Thanks for your response Allen,
The thing is, this database is for building pre-fab houses. There is a Job
ID, a Plan ID and a Wall ID. Each Job has multiple Plans which in turn has
multiple Walls for each plan. My goal is to make a Framing List report (which
I have succeeded) and a Cut List report that shows the Lengths to be cut and
from which Lumber, with no repeats of the Length. The data that is entered is
for each Wall. The reason I added multiple fields (qty1,qty2,length1,length2
etc...) was so I wouldn't have to enter Job ID, Plan ID and Wall ID for each
individual row of each individual Wall every time I entered a Wall. Each Wall
has multiple rows and there are many Walls for each Plan.
I appreciate your help. I'm splitting up my tables now and am currently
reading up on relational databases and normalization. I will reply back when
I am finished.

 

[Allen Browne]

Okay. Sounds like you are thinking of the one-to-many relations:
- one job has multiple plans
- one plan has multiple walls
- one wall requires multiple products
etc.

Using forms and subforms, you won't have to enter these things multiple
times.

 

[Bon]

Thanks Allen, I got it. That's way better!
Just two more things. Is there a way to keep the order of records for each
wall, in a report as entered in the form? And is there a way to relate more
than 3 fields to each other? I need 4.

 

[Allen Browne]

Use the Sorting And Grouping pane in report design to define how you want
them sorted.

There will need to be some field to do this. An autonumber would work.

Not sure what you meant by 'relate more than 3 fields.' You can create a
relationship that involves 4 or more fields if you need to. Or you can
create 4 or more different relationship between fields if you need to do
that.

 

[Bon]

Right, I will use an autonumber field.
I used the subform wizard to create relationships between fields, but it
only let me enter 3 fields to relate to each other in my 2 tables.

 

[John W. Vinson]

Right, I will use an autonumber field.
I used the subform wizard to create relationships between fields, but it
only let me enter 3 fields to relate to each other in my 2 tables.

The wizard isn't very clever.

You can manually edit the Master and Child Link Fields to include up to (IIRC)
ten fieldnames, separated by semicolons.

Note that having a Subform with master/child link fields does NOT create a
relationship between the tables; that's a completely separate operation, that
must be done in the Relationships window (or in code).

 

[Bon]

Thanks John,
I did just that.
--
Bon

The wizard isn't very clever.

You can manually edit the Master and Child Link Fields to include up to (IIRC)
ten fieldnames, separated by semicolons.

Note that having a Subform with master/child link fields does NOT create a
relationship between the tables; that's a completely separate operation, that
must be done in the Relationships window (or in code).