#### Using Min to Calculate across a table

```I am attempting to massage data so I can see what the maximum number
of pieces I can produce based off of various components. My query to
figure out how many of each component is available per unit is
complete and the output is:

Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4

Does anyone have any suggestions as to how I could find the MIN of the
components and group by part number?

Cheers

```
 0
OlyOil
3/23/2007 5:58:11 AM
access.queries 6343 articles. 1 followers.

2 Replies
1170 Views

Similar Articles

[PageSpeed] 21

```See the MinOfList() function here:
http://allenbrowne.com/func-09.html

A better solution would be to create a related table with many records for
the combinations that are valid, instead of repeating fields in this table.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"OlyOil" <ENelson72@gmail.com> wrote in message
>I am attempting to massage data so I can see what the maximum number
> of pieces I can produce based off of various components. My query to
> figure out how many of each component is available per unit is
> complete and the output is:
>
> Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4
>
> Does anyone have any suggestions as to how I could find the MIN of the
> components and group by part number?
>
> Cheers
>

```
 0
Allen
3/23/2007 6:32:34 AM
```On Mar 23, 1:32 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> See the MinOfList() function here:
>    http://allenbrowne.com/func-09.html
>
> A better solution would be to create a related table with many records for
> the combinations that are valid, instead of repeating fields in this table.
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "OlyOil" <ENelso...@gmail.com> wrote in message
>
>
>
>
> >I am attempting to massage data so I can see what the maximum number
> > of pieces I can produce based off of various components. My query to
> > figure out how many of each component is available per unit is
> > complete and the output is:
>
> > Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4
>
> > Does anyone have any suggestions as to how I could find the MIN of the
> > components and group by part number?
>
> > Cheers- Hide quoted text -
>
> - Show quoted text -

Brilliant! I had to play around with the code until I figured out what
the issues were with the data, but the code is fantastic! Thanks again
for the workaround!

```
 0
OlyOil
3/23/2007 8:27:30 PM

Similar Artilces:

Calculated fields on a form
Hello I have a series of related tables (and the relationships work fine on the form) and I am trying to do a calculated field on a form : I have added a textbox and built the formula =[tblBrochures]![Quanity]-[tblOrders]![OrderAmt] both the variables are "single" But I get an error #Name? returned. ideally i would like to have the Quantity assigned the value of [tblBrochures]![Quanity]-[tblOrders]![OrderAmt]. Can someone please help? Are both of those fields, [Quantity] & [OrderAmt], bound to your form? If so, you can just omit the table names altogether. ...

calculating the name of a worksheet
is there a way to create a formula that changes the name of a sheet within another formula. In other words, the following is a reference to an external workbook within an IF Function -- '[SDS.xls]we121'!\$L\$3. The sheet name we121 will change on a wkly basis by 7 days. I would like to have the formula automatically look at the worksheet that is we121+7 OR we128. Is it possible? Thank you. One way to consider Earmark a cell say, A1 to contain the numbers: 121, 128, etc Then we could put in say, A2: =INDIRECT("[SDS.xls]we"&A1&"!\$L\$3") If A1 contains: 1...

create a floating bar graph with min and max (price for example)
How to I create a graph with floating bars showing price on the y axis so that I can set a custom min/max for each category. For example I want to compare the min/max price range for a variety of things - each is going to have a different min and max, but all are in the same basic range. I can add a line through the middle that shows average for all things within the category. There is a floating bar graph in my version of PPT, but they are horizontal and I want them to be vertical. - How? EXCEL 2007 I have just put up a file for you at:- http://www.pierrefondes.c...

I'm using about twice as many bytes of memory as the size of the file
Hi! I received this text marked with * below as an answer on a mail for several days ago when I used method CompressFile_1 for compressing file that is located at the bottom. *This will only work with text files, and you may even lose some information after going through the StreamReader and then the StreamWriter in case the file has some characters that don't fit into the default encoding that you are using. And besides, you read the whole file into memory before writing the compressed file. In fact, since the string is stored as Unicode, you are using about twice as many by...

Saving graph when using =(RAND) #2
Aha, the goal of this is to see on the same chart, all of the different iterations from pressing F9. Ultimately, I'd like to see something like 500 different lines on the same chart. Is there a less manual way to accomplish this? :) -- deacs ------------------------------------------------------------------------ deacs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2321 View this thread: http://www.excelforum.com/showthread.php?threadid=271282 ...

Using variable defined as As Font
Hi, I wish to extract all the font properties (Name, Bold, Colour etc) of a cell (eventually I will be doing this on a character by character basis within the cell) into a vba variable that I can manipulate within a macro. I thought of defining the vba variable As Font, as it has all the necessary properties, and simply doing an assignment. The code below shows my first attempt: Option Explicit Sub Test() Dim Highlighting As Font MsgBox Cells(1, 1).Font.Name Highlighting.Font = Cells(1, 1).Font MsgBox Highlighting.Font.Name End Sub The first MsgBox tells me the font name correctly bu...

Problem in replying when using word as editor
Hello, I have a problem that when I use word as the editor and I reply to a message the original message is either erased or written in garbaged symbols. This doesn't happen all the time just in certain messages. I think it is when someone sedn me the me in unicode 8 and/or the fonts are of a certain type. Can you please help me solve the problem? ...

how to use CMFCListCtrl
First off, I am new to programming for MFC, let alone Windows. I'm a Senior in college in CS, and have a decent grasp on programming concepts. So please bear with me if I make any crazy assumptions :) I am trying to make an application that essentially lists messages that get streamed into the system. So I am wanting the main view to be a CListCtrl, however I also want some of the added functionality of the new MFC feature pack's CMFCListCtrl, however I'm not sure how to use it and cannot find any tutorials or examples that use it. My app's main view is a CListView, ...

Using Time as axis, and displaying info on plots
Hi every one...i've been racking my brain out on this one. I'd like to come up with a way to display things better. Background: I am making a running worksheet that has events fo multiple days. each day has a varying number of events. In order t effectively display the data for my boss, the best way is to do it on 24 hour clock instead of the standard 12 hour clock. This allows us t see patterns occuring at certain times. The way I have it currently set up is converting times to angles an using the days as magnitudes. Basically doing a polar chart t cartesian conversion. This...

Using files from lotus 1-2-3
I am using Office 2003 with Microsoft XP Home Edition. I open files previously saved in Lotus 1-2-3 as *.XLS. I am able to edit and add data to the file, but when I print the file it has a dense, large, black overlay of the first characters in the file name, obliterating much of the data. This shows in the print preview and in the final printed copy. How do I eliminate this overlay? Hi Alpan, as far as I have learned during the past years the Lotus 1-2-3 converter to MS Excel is not quite good and has several issues. Have you ever tried to leave the Lotus1-2-3 file as it is and l...

Calculating Clock start time in Excel
Hi, I am trying to calculate when an inquiry comes in to our helpdesk outside working hours and when it was answered based on the follwing example. Example: Working hours are 08:00 - 17:00 Monday to Friday. If inquiry comes after 17:00, then the inquiry is attended the next working day and an answer is guaranteed by the close of following working day. If the inquiry comes during working hours, again the garantee is for the answer by close of next working day. I need to be able to measure how long an inquiry has missed the promised deadline by. Can anyone help please? -- Hani Muhtadi -...

Amending records across 4 tables
I have a table (tblcompany) that contains company information. I have 3 other tables that include various data streams but also include a company name (txtcompany) which is linked to the company name in tblcompany. I want to create a form that deals with a situation where a company changes its name. I know that I can create an update query for each table to change the name in each record where it appears but I want to create a user process. What I had in mind was an unbound form that had a combo box based on tblcompany from which the user could select the current company name. Then ...

Using Sales for Outlook from home
Is it possible to use Sales for Outlook from home, i mean, not on a vpn to your office network? If i take my laptop , which has Sales for Outlook installed, since i m able to use Outlook, as long as i have internet access, can i also have access to my CRM? Responses are much appreciated. Yes, you can use it from home. It is possible to "Go Offline" with the Outlook SFO. After that you can work with the Outlook SFO without a online connection to the MS CRM server. When you are back in the office you can go "Online" and all your changes will synchronized. -- _____...

how do you calculate "dividend yield using Excel?
Can you calculate "dividend yield using Excel? If so, how? There are a couple different approaches to dividend yields. The mos straight forward and simple is just dividing the annual dividend for company by the current stock price. This would be a simple divisio issue. It can also be calculated over some period by using th dividends over that period divided by either the beginning (typical) o average during that period and then annualizing. Don't think you nee any special function here, but if you are doing the second metho above, you can actually use the Yield function assumin...

Using OE with my Juno e-mail account
I use Juno e-mail but I noticed that I also have Outlook Express on my computer which I would prefer to use. Is there a way to send and receive e-mail using OE but keeping my Juno address and service? I have a standard dial-up connection. OS is Win98SE. If this is possible, I would need simple, step-by-step instructions for setting it up. Thank you, Relztrah Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You ca...

How do I eliminate non-used dates (weekends) from a chart?
My data uses only week day dates. The chart that results from it is adding weekend dates even though they don't appear in the range for the X axis. It leaves gaps in the chart as a result. Nanoking, One option is to reference your X-axis series with a formula that converts the date to text but still leaves the "text" date looking like a date. For example, assuming the data below starts in cell A1, convert column A to text by using this formula: =TEXT(A2,"mm/dd/yy") col A col B col C Orig. Date Text Date Data 01/01/04 01/01/04 5 01/02/04 01/...

Pivot table truncating values
I have a problem when creating pivot tables based on large amounts of data (for example 28MB). The pivot table truncates certain fields that appear as numeric, but are text, such as an account number. Does anyone have any ideas, or had this happen to them? ...

To allow a different unit of measure when using SOP/POP Commitment
Would like the ability to use a different unit of measure for Inventoried Items when using SOP/POP Commitments. For example, if I have an Item on a Purchase Order where I am ordering 2 Cases (1 Case equals 10 Each), I would like to link the Item on the Sales Order document where all quantities of 10 Each are back ordered to the Purchase Order Item. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

Selecting multiple filters in a pivot table
When I use filters on pivot tables that others create, I can select multiple values on which to filter. There is a little white box next to each item that I can select by putting a check mark in it. However, when I create a pivot table, I can only select one item from the filter at a time. How do you set up the pivot table so you can select multiple items? Thanks. -- Anne If a field is in the Page area, it doesn't have check boxes. Could that be the problem? To select multiple items from a page field, you can temporarily move the field to the row area, select the items, then m...

Getting output from the TREND function using VBA
The TREND function has the syntax TREND(known_y's,known_x's,new_x's,const). In order to output the array of estimated y values on a spreadsheet, the Excel Help entry specifies that a range must be selected beginning with the cell in which the TREND function is entered and extending downward so that the number of cells in the range is equal to the number of estimated y values. The user is then instructed to press F2, and then press CTRL+SHIFT+ENTER, causing the estimated y values to populate the selected range. That method works fine for outputting the estimated y v...

Modify Callout Written in VB using VS 2003 after upgrade to CRM 4.
Hi, We've recently upgraded to CRM 4.0 with no issues. After ensuring the Microsoft.Crm.Platform.Callout.Base.dll was available in the GAC of the CRM server the callouts functioned as they should. However, I found a bug in one of the callouts so want to alter the assembly to rectify the bug. I've already recreated the callouts using plugins which work fine but I want to alter the callout as well because we have clients that will soon upgrade to CRM 4.0 who have callouts and don't want the added expense of migrating these to plugins. They will want changes made to the ca...

Excel 2007 and refresh pivot table methodology
Hello, I have moved from excel 2003 to 2007 and come with a question regarding the way pivot tables refresh is handled. I have several pivot tables in different sheet of the same workbook and all have the same data input range . The question is that when I ask to refresh ONE pivot table excel will refresh ALL pivot tables in all sheets having the SAME data range source. It was not the case in 2003 and I'd like to ONLY refresh one specific table , is there a possibility to do that ? Thanks, Bernard ...

Can not delete from specified Tables
Below is my SQL Code - Confirmation Entries such that if PalmHeight1 = PalmHeight2 then Delete that entry in the second table. DELETE PalmHeight.Height, PalmHeightSecond.*, PalmHeight.RDate FROM PalmHeight INNER JOIN PalmHeightSecond ON (PalmHeight.Palm = PalmHeightSecond.Palm) AND (PalmHeight.Plot = PalmHeightSecond.Plot) AND (PalmHeight.Trial = PalmHeightSecond.Trial) WHERE (((PalmHeight.Height)=[PalmHeightSecond].[Height]) AND ((PalmHeight.RDate)=[PalmHeightSecond].[RDate])); Am not able to delete. Trial, Palm, Plot and Date is related. Is this where the problem is? Such that there are ...

how to recover Table Display Name?
In report writer Under Related Tables window shows [Not Found] from the "Tables:" list. If doulbe click on [Not Found], Table Definition window popup shows: *Table Name: RM_Class_MSTR *Display Name: BLANK *Physical Name: RM00201 Is that possible to recover a "Display Name" of the table? Thank you very much! Hi Sophie I don't know of any way to "fix" this. Can I suggest exporting the report with Tools >> Customise >> Customisation Maintenance and then deleting it from Report Writer and then importing it back. Make a backup copy of the rep...

using data from two tables
hello kind folks who have vast knowledge... I have a table of students who are employed by various companies. The table has a field where the employer's name is entered. AND I have a separate table of employer company details. I would like to be able to create a query that gives me the student names, their employer's name, and their employer's details. The employer's names are entered identically in the fields of both tables (ie spelling etc)... It seems like a simple problem but I just don't seem to be able to figure out how to do it.. any advice wo...