Installation Count

[Richard_ATS]

Hi,



I am trying to further develop my basic database to allow me to prepare a material take off based on a quantity of installations... So far, my database shows individual materials and quantities associated with an installation. Now I want to be able to prepare something that would say require 10 of those installations and provide a breakdown of the total materials required for those 10 installations.

As an example, here is an Installation G01AA:



G01AA comprises of (materials) 1x EGDM11, 0.2x EGDM115 etc etc

I want to request 10 of these, but I need my output to read materials only i.e. 10x EGDM11, 2x EGDM115 etc etc

Any tips or pointers would be greatly appreciated.

Thanks,

Richard

[Micron]

Not sure I understand the requirement. I'm going to assume that you're asking for a way to relate multiple installations so that you can count/sum materials (i.e. you already have data and are looking for a way to summarize it rather than asking how to input it). Perhaps InstallationType would be a suitable grouping type, but you don't seem to have that. It would allow a grouping by installation type so that you can quantify the total amounts of materials required. If the data is correctly stored in the system, you still ought to be able to allocate individual quantities to each installation. Also, you'd need a field to flag active installations so that you don't include the completed ones.

If that is a lookup field I see in your picture, I suspect you plan to enter data directly into tables. Most of the time this is not a good idea.

[Richard_ATS]

Hi Micron, thanks for responding.

I apologize for not clearly stating my requirement, but yes you are thinking along the correct lines as to what I am struggling with i.e. I have the data available, I'm struggling with how to correctly group and ultimately total the data specifically into just the materials that are associated with the installations.....



The above is an example of a worksheet I create in excel then import/append into my access table. You can see there may be multiple installations associated with a single MTO number; and the installations may contain the same materials.... I therefore need to consolidate material totals from those two records into a single report for that MTO number.

Can you elaborate a little on the grouping type you suggested above?

Thanks

[Micron]

What makes a sensible group id is something you as the process owner has to decide. Based on your second post, the grouping would seem to be by MTO number, which you have added to the discussion. In query design view, try creating a TOTALS query where you Group By on MTO and count or sum (these are aggregate functions) the quantities. A select query is converted to a Totals query via the ribbon button Sigma symbol. I'd play with this, grouping first (left-most query field) by MTO then by InstNo. and sum the values for InstQty. Use the function selections available in the query Totals row. You cannot harm any of your data with this, as a Totals query is not updatable.

[orange]

You may get some ideas from this material.

[Richard_ATS]

Thanks guys!!
With both of your input, I was able to develop what I needed. I still need to clean things up but with a combination or tables, forms (and subforms), and queries I now have the basis of my system.
I will post some updates when I clean things up.
Thanks again