total group & max function

I am using Access 2003
I created a Db for Machine Mtce.  The table consists of 3 tables, (1) Basic 
Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists 
of different prev mtce (PM) that need to be completed at various time frames. 
 Ex:  "Mach 100" could have 3 different operations; Oper # 100A to change the 
oil due every 90 days, Oper # 100B to check the belts due every 180 days, 
Oper # 100C to grease the machine due every 365 days.
The 3rd table is the "PM History" table which includes the history of all of 
the PM's completed with Work Order (WO#) and date completed.  All tables are 
linked by Mach #.
I am trying to create a report that will look at the latest date a PM was 
done (from the pm history table) for a Mach# and by Oper#.  Then to that date 
add the [freq] from the Operations table to come up with a NextPMDue date.  
Ex:  Last pm, for Mach 100, Oper 100-B, was completed on 1/5/06 and due again 
in 180 days.  Therefore the new pm due date would be 7/5/06.  I think this 
takes the use of the 
"max" function and probably the " total" group by function.  I am now very 
good at using either of these functions.

Is there anyplace on the internet that could help explain to me how to 
accomplish this or to use these functions.  I do not know how to write SQL or 
VBasic.  I would appreciate any help you can give me.

aurora
0
Utf
2/7/2008 7:45:00 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
679 Views

Similar Articles

[PageSpeed] 20

Make a PM Records table like this ---
Mach#	Operations	Sched	Complete	Comments
1	A	1/2/2007	1/3/2007	No problems
1	B	1/2/2007	1/3/2007	No problems
1	C	1/2/2007	1/3/2007	No problems
1	C	1/3/2008	1/8/2008	
1	C	1/8/2009	
Create an index of the first three fields set to unique.

The Operations Table to have the following ---
Mach	Operations	Interval	Increment	Activity
1	A	90	d	change the oil
1	B	180	d	check the belts
1	C	1	yyyy	grease the machine
The increment is used in DateAdd function with interval for the next 
scheduled date.  

Use these two queries after you have a PM record for every Mach/Operation 
combination.  Set the record dates so as to generate the next PM on your 
desired dates.
   Mach PM Record MAX ---
SELECT [Basic Mach Infor].[Mach#], [Mach PM Records].Operations, Max([Mach 
PM Records].Complete) AS MaxOfComplete
FROM [Basic Mach Infor] LEFT JOIN [Mach PM Records] ON [Basic Mach 
Infor].[Mach#] = [Mach PM Records].[Mach#]
GROUP BY [Basic Mach Infor].[Mach#], [Mach PM Records].Operations;
This query select the last complete date but you may wish to use the last 
schedule date. 

    Mach PM Record Gen ---
INSERT INTO [Mach PM Records] ( [Mach#], Operations, Sched )
SELECT [Mach PM Record MAX].[Mach#], [Operations Table].Operations, 
DateAdd([Increment],[Interval],[MaxOfComplete]) AS x
FROM ([Mach PM Record MAX] LEFT JOIN [Operations Table] ON ([Mach PM Record 
MAX].[Mach#] = [Operations Table].Mach) AND ([Mach PM Record MAX].Operations 
= [Operations Table].Operations)) INNER JOIN [Mach PM Records] ON ([Mach PM 
Record MAX].[Mach#] = [Mach PM Records].[Mach#]) AND ([Mach PM Record 
MAX].Operations = [Mach PM Records].Operations)
WHERE ((([Mach PM Records].Complete) Is Not Null))
GROUP BY [Mach PM Record MAX].[Mach#], [Operations Table].Operations, 
DateAdd([Increment],[Interval],[MaxOfComplete]);

-- 
KARL DEWEY
Build a little - Test a little


"Aurora" wrote:

> I am using Access 2003
> I created a Db for Machine Mtce.  The table consists of 3 tables, (1) Basic 
> Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists 
> of different prev mtce (PM) that need to be completed at various time frames. 
>  Ex:  "Mach 100" could have 3 different operations; Oper # 100A to change the 
> oil due every 90 days, Oper # 100B to check the belts due every 180 days, 
> Oper # 100C to grease the machine due every 365 days.
> The 3rd table is the "PM History" table which includes the history of all of 
> the PM's completed with Work Order (WO#) and date completed.  All tables are 
> linked by Mach #.
> I am trying to create a report that will look at the latest date a PM was 
> done (from the pm history table) for a Mach# and by Oper#.  Then to that date 
> add the [freq] from the Operations table to come up with a NextPMDue date.  
> Ex:  Last pm, for Mach 100, Oper 100-B, was completed on 1/5/06 and due again 
> in 180 days.  Therefore the new pm due date would be 7/5/06.  I think this 
> takes the use of the 
> "max" function and probably the " total" group by function.  I am now very 
> good at using either of these functions.
> 
> Is there anyplace on the internet that could help explain to me how to 
> accomplish this or to use these functions.  I do not know how to write SQL or 
> VBasic.  I would appreciate any help you can give me.
> 
> aurora
0
Utf
2/7/2008 10:32:02 PM
Reply:

Similar Artilces:

Can I use TODAY Function in a formula that will not change it the.
Hi please post your question as body of your message. Guessing you probably want something like: http://www.mcgimpsey.com/excel/timestamp.html -- Regards Frank Kabel Frankfurt, Germany "Tucson Guy" <TucsonGuy@discussions.microsoft.com> schrieb im Newsbeitrag news:0488EA8C-8BA7-4816-AEF9-C62166FB926D@microsoft.com... > ...

Looking for FAQ for the group or web tutorial
I tried the "Teach yourself VC++ in 21 days" book and though it gave me some understanding of the language, it seems to have also taught me some bad habits. I want to have a strong grasp of VC++ and the MFCs and be able to write solid code. Is there an FAQ or good all-around tutorial site that teaches proper VC++ coding? Thanks. ...

Use of "--" in functions??
I'm new to compiling and using functions of anything other than a relatively simple basis. I've been reading a lot of questions and answers on this group and am amazed at some of the complex functions that can be built up. I am starting to understand most of them but keep seeing "--" used in functions - why is this used? Does it only apply to certain functions? I've tried typing the some of the functions posted without them because I thought they may just have been used to clarify the structure of the function but have noted that the end result is different with and w...

OT: Google groups problem
I seem to have lost the ability to reply to any message that I have "starred" for tracking. If I open a recent message from the board, there is a "reply" option at the end, but not on any of the messages I have starred. It could be that the last message in the thread is mine, but I have replied to my own meesage before to clarify and it worked then. I'm stumped... Ideas? Thanks! PS: If I don't reply, your suggestion didn't work :) Hi Roscoe, If I understand you correctly, Google does not provide a means of replying to older messages -- say older than fiv...

Indirect function in Forecast function
I'm trying to create 2 array references that will be used in a forecast function for the known x's and known y values using an indirect function. one is to reference another sheet (Known Y's) and the same sheet (known X's). This is all encased in an if function to display a value only when the column (week) is later then the current performance period. The formula that I'm currently at is "=IF(AQ$3>Last_valid_Week,FORECAST(AQ$3,INDIRECT("CSS!RC11:RC"&MATCH(Last_valid_Week,Weeks0708,)+COLUMN($K$3)-1,0),INDIRECT("r3c11:R3C"&MATCH(La...

Mortgage (P+I+T+I) categories grouped for reports???
Hi. In MS Money 2006 I split my mortgage transaction down into 4 categories: Mortgage - Principal Mortgage - Interest Mortgage - Taxes Mortgage - Insurance I would like for my total mortgage payment to show up in reports like the "Spending by Category" on the Money Home Page or in my Budget. I would also like to do the same thing for my paycheck. I want to be able to get the detail level, but want the total transaction amount to show up on reports. Does this make any sense? Is this possible? Here's how Money intends to solve this: 1) Create a Loan Account to model t...

Using the EXACT Function
I've created a simple spreadsheet. Column A is a list of 15034 used Work Order reference numbers (many values are missing); Column C is a complete list (25,000 numbers). I want to get a separate list of unused numbers, but can't get the EXACT function to work. ( I believe it should place a TRUE value in column D when the numbers match.) I've used: =OR(EXACT(C1,$A$1:$A$15034)), which I replicated down the column. Please help... Thanks, Jim Berglund I don't think EXACT will work. Try a VLOOKUP: in D1, type =VLOOKUP(C1,$A$1:$A$15034,1,FALSE) in E1, type =IF(ISNA(...

why isn't this IF function working??
=IF(D26<>0,D26,IF(E26<>0,E26)) It currently isn't recognizing the values in column E. This is what I am trying to do: If D has text, return that value. If E has text, return that value. And if possible, can I also add this as the final IF variable: If both are zero, return "Zero" Thanks!!! you did say text =IF(ISTEXT(D1),D1,IF(ISTEXT(E1),E1,0)) -- Don Guillett SalesAid Software donaldb@281.com "broken" <broken.logan@gmail.com> wrote in message news:1126030178.370525.156610@f14g2000cwb.googlegroups.com... > =IF(D26<>0,D26,IF(E26<>0...

Using of Goal Seek function
Here is the tutorial for to use the Goal Seek function in Ms Excel for to complete the work in the less time. Its very useful to get to know this function. Just have look at http://t.co/le90e2O ...

Similar function needed to Excel VLOOKUP
Hi everyone, I hope someone may be able to help. I have a database with three tables. The main table is a portfolio of projects with budget, date and other general information. There are a couple of columns I need to update automatically by looking up information on the other two tables which hold budget and date information. The other two tables are linked to external excel sheets which allows the tables to auto update when excel is updated. How do I get the budget and date columns in the main table to lookup data from the other two tables and return the up to date figures ...

group cells....
Hi I've following information wanted to group by first column and show the sum of quantities: a b c ---- ----- ------ 123 10.8 123 20.2 123 44 124 2 124 11 124 45 The desired result: a b c ---- ----- ------ 123 10.8 75 -> sum(10.8+20.2+44) 123 20.2 123 44 124 2 58 -> sum(2+11+45+0) 124 11 124 45 124 0 note: I don't want to write sum(a1:a3) , ... because it depends on the number of rows has the same value. any help would greatly appricieted. -- Using M2, Opera's revolutionary e-mail c...

O2003/O2007: How to delete E-mail from server when I move it from Inbox (Netscape-style functionality)
Hello, all. We're using Netscape, and would like to switch to Outlook. However, there's a problem: Netscape has the option to [X] Leave messages on server .... [X] Until I delete or move them from Inbox. <------ Outlook does not seem to allow this option - only deleting messages from server when deleted from "Deleted Items" folder. Reason this is a problem: we need to keep all E-mails archived in local folders, on each computer. Deleting them is NOT an option. So, here's my question: Is it possible to set up Outlook to delete a message from server when ...

if function 01-19-10
in A1,A2 and A3 i have the salesperson name, in B1,B2 and B3 i have the sales person employee number. in A7 i have to input the sales person number and i would like their name to atuomatically show up on B7, i was thinking that if a used the If function i could achieve this please help asap thanks On Tue, 19 Jan 2010 14:50:02 -0800, Pinnacle Accounting <Pinnacle Accounting@discussions.microsoft.com> wrote: >in A1,A2 and A3 i have the salesperson name, in B1,B2 and B3 i have the sales >person employee number. > >in A7 i have to input the sales person...

scaling grouped objects
I have a few shapes and text boxes grouped together in Visio 2002. I wish to scale the group (and it's layout) proportionately. When I chech the "aspect ratio" box and scale only the shapes grow proportionately, not the text. Is there a work around? Regards, -- Bruce You have to enter shapesheet formulas to get text to grow, OR use a metafile version of your shapes. Metafile versions are 'dumb-picture' versions of your shapes. (Copy, Edit > Paste Specail > ...as metafile), which you can't edit or format very easily. But the text stretches on metafiles....

Grouping/ungrouging problem
I have several rows which give me the totals of information in other sheets. Let's put it graphically: This is my main sheet: _____________________________________ : Type 1 Type 2 : Category A 50 40 : Category B 20 10 : -------------------- : TOTAL 70 50 : :____________/ Sheet 1 - Totals \____________ The information of the different categories come from sheets like this: _____________________________________ : Date Type 1 Type 2 : xx/xx/xx 15 ...

Key function for scheduling template
I want to make a schedule program to schedule the employees using a daily planner with task list and customer combo's. Before I start this big challenge I need to know if the following is possible (= key function): When I choose: Employee name from combo Start time from combo End time from combo Customer name from combo A coloured bar is mentioned under the right time frame in the correct employee name row. This bar is Merged and Centered with task and customer name. 07.00 07.30 08.00 08.30 09.00 Patrick John [ Install AV at IBM ...

Function for different array
Hi all, I need help on writing formula for the problem below: Column A Column B Column C CHJ 15 AGB AGB 10 HHH JKX 12 NNN MNB 19 MMM AGB 20 BBB . . . . . . . . . I want to be able to write " If one or more value in...

Dynamic Charts with sub-groups?
I frequently create dynamic charts via look-up tables using Excel 2007. I want to create dynamic charts capable of displaying results for different types of sub-groups without having blanks or zeros appearing in the chart. As an example, I want to create a dynamic chart that charts 4 age groups or 2 gender groups (but the gender chart cannot have any blanks or zero/NA fills). I have to think there's a way around it but my Excel skills aren't that advanced. ...

Re: Disbale RSS Feeds Folder in Outlook 2007 with Group Policy
hi! I want to remove as well the Folder from the panel to reduce the number of folders. any way to do it? thanks, Nicolas. Window VISTA Home Premium SP1- Office 2007 "broonie27" wrote: > You're correct I am. So do you know how to remove existing RSS folders within > a user's mail stores in Exchange. I've had a look at Managed Default Folders > in Exchange but they are more about retention time than actually removing the > folders entirely. > > Also, do you know if after I have disabled RSS feeds with Group policy > whether alrea...

How do I make total records count faster?
Hi, I have a continous form and I sum the number of records in a text box in the footer. Control Source = Count([StockID]) This is a stock record of marine spares. Some combo boxes on the form provide filtering criteria so that sotck item details, location and number of Stock Lines (records) can be viewed. There are about 32,000 records in total and each time it runs it takes several seconds to present the result on screen. I wondered if there was a faster function or some code that could speed this up? -- John Whyte JohnW wrote: > Hi, > > I have a continous form and I sum...

Additional Loan pmt messes up total interest paid
Hopefully I have enough detail without flooding you with trivia. Money 2006 standard edition Advanced register I set up a car loan a while back. Money's been calculating principle and interest correctly (within a peny or so each month - roundoff error) The loan pmt in Money is set to pay automatically from my checking account I recently came into a bit of a windfall and made a large extra payment. I went into the account register for the loan and clicked [New] to add this additional payment. The "Make a regular loan payment" and "Make an extra loan payment" ...

Grouping inserted pictues with Charts
Is there a way to group pictures and text boxes with a chart? Hi, I was able to add textboxes and pictures and group them in the normal way even if they where embedded within a chartobject. Can you explain further what you are wanting to do? Cheers Andy Justin_Yuen@hotmail.com wrote: > Is there a way to group pictures and text boxes with a chart? > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info I want to be able to put pictures, arrows, lines, text boxes, etc in a chart so if I were to copy or move the chart, all of the things I put in the chart will go with it....

UPDATEBLOB error
OS: Windows XP. DBMS: SQL Server 2008. Development tool: PowerBuilder 10.5. I am attempting to save a digital photo image to a table ( datatype of column is varbinary(max) ) with an UPDATEBLOB statement via embedded SQL; however, I'm getting the following error - SQLSTATE = S1010 [Microsoft][ODBC Driver Manager] Function sequence error. (SQLCODE = -1 and SQLDBCODE = 999). Does anyone have any suggestions to help solve this problem? In addition, the connection to SQL Server 2008 is via ODBC. The DBPARM = "Connectstring='Driver=SQL Server Native Client 1...

Summarizing group totals in report footer
hello I have totals of a field of each group in group footer.Now I want to get the group totals as a summary in the report footer.Hope somebody can help me out in this. Sama I don't know what your underlying data looks like, so I'll have to make an assumption that your detail level group has individual/detail amounts in a field, and your group footer sums up all those individual/detail values. To get a report-level summary, you'll have to sum up all the individual/detail values. Create a text control, and in the ControlSource property, put something like: =Sum([YourD...

getpivotdata function #2
In Excel 2000, can I have more than one data field in my pivot table and still use the GETPIVOTDATA function? My pivot table is laid out as such: Date DesShop Pathway Data 200501 200502 240 Database Sum of DeliveryQty 12 14 Sum of DeliveryAmt 15853 15652 Retail Sum of DeliveryQty 3 4 Sum of DeliveryAmt 2997 7888 Grassroots Sum of DeliveryQty 0 Sum of DeliveryAmt 0 Managed Care Sum of DeliveryQty 1 Sum of DeliveryAmt 1995 240 ...