Problems sorting and summarizing

[flurdy]

I am a filemaker novice, and have finally come to the conclusion I probably won't figure this out by myself...

I am creating a lighting audit database which records existing location information pertaining to light fixtures. These existing locations are then analyzed and new, more energy efficient light fixtures are specified.

I have created an existing (EXI) fixture table, and new (ECM) fixture table to record all of the details. I have also created a location table, which will automatically populate a list of locations which can be matched back to a drawing or plan. It is here where the specifications are decided.

My problem comes when I try to create a summary of the scope of work. I want the EXI's and ECM's to be sorted and summarized together in a table with quantities. Not all EXI locations will have the same ECM type, and new ECMs can be assigned to multiple EXI types.

Something like...

EXI 1 name - Qty ECM 1 name - Qty

EXI 1 name - Qty ECM 2 name - Qty

EXI 1 name - Qty ECM 3 name - Qty

EXI 2 name - Qty ECM 1 name - Qty

EXI 2 name - Qty ECM 2 name - Qty

EXI 2 name - Qty ECM 3 name - Qty

etc...

I've been trying to get this to work for several days and am just hitting a wall. I think I may be missing some simple, but critical step.

I did get a version of this to work in access several years ago, but it was pretty bloated and tricky to work with and I wanted to try it out with filemaker.

Any help anyone could give would be much appreciated.

[Søren Dyhr]

I want the EXI's and ECM's to be sorted and summarized together in a table with quantities. Not all EXI locations will have the same ECM type, and new ECMs can be assigned to multiple EXI types.

You're almost in goal, it's indeed a many to many structure, and all quantities goes right into to join table, from where the summary is done. Sorting should be done twice, first the foreignkey from EXI and then by the foreign key from ECM, the more human readable gestures are ushered in by the relations to you subsummary report.

Checkout JMO's many2many template http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

--sd

[flurdy]

Soren,

Thanks for the response.

I initially attempted to do basically what you said, which is create a join table (my location or line by line table) which is where the user will enter the name of the EXI and EXI Qty and then the ECM name and ECM qty. My table successfully pulls all of the respective information about the ECM's and EXI's. I can also get the data sorted the way I would like.

I guess where I get lost is in creating a report showing actual summary information. I've tried creating subsummaries, but I must have my summary fields in the wrong table, or maybe I'm just missing the process entirely. I've been able to get this sort of thing done in spreadsheets and Access, so I'm starting to think that Filemaker may not be for me.