IIF Statement from Microsoft Acccess
Ok I am trying to create an if statement that returns a yes or a no or some type of false true I have a promised date and a Shipped date. If ship date is passed promised then its late and i need to return a false so I need to build an expression I assume then a count of items that were on time. So I can divide that by number of lines to get a percent
SELECT Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));
Do you want your output to just be the % of items shipped on time?
I think this will get you in the right direction.
SELECT SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber)
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))
GROUP BY Invoice.InvoiceTypeASKER
yes .. but i guess i want 2 expression one with the number of on time deliveries and one with the %
ASKER
Syntax Error (missing operator) in query expression 'SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber
invoice.writtenby'.
invoice.writtenby'.
Ok. You are doing this in Access, right?
Try:
Try:
SELECT IIF([DateShipped] <= [DatePromised], 1, 0)
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))
ASKER
Yes sorry forgot to metion it.
I get the same error. Syntax Error (missing operator) in query expression 'SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber
invoice.writtenby'.
i can get the expression to return a 1 or 0 buy this code
I get the same error. Syntax Error (missing operator) in query expression 'SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber
invoice.writtenby'.
i can get the expression to return a 1 or 0 buy this code
SELECT Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, IIf([DateShipped]<=[DatePromised],1,0) AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));
Ok, so far so good then. I guess it doesn't like my GROUPBY/Summary piece
Try:
Try:
SELECT Invoice.InvoiceType, IIF([DateShipped] <= [DatePromised], 1, 0)
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))
ASKER
nope still same error.. got it working like this giving me a 0 or 1 .. now should i try and figuar out the percent in the query .. I need a chart Graph showing percent should i do the rest in a form and figuar it out there?
SELECT Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, IIf([DateShipped]<=[DatePromised],1,0) AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));
I can't tell what part it doesn't like. In order to do the count and percentage you want, you're going to need to group the data by something. I assumed that Invoice.InvoiceType would be the same for every record, which would make this possible.
Maybe I'm just missing the as...
Try this:
Maybe I'm just missing the as...
Try this:
SELECT SUM(IIF([DateShipped] <= [DatePromised], 1, 0)) as num_on_time, SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber) as percent_on_time
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))
GROUP BY Invoice.InvoiceType
If that doesn't work, try this...
SELECT Invoice.InvoiceType, IIF([DateShipped] <= [DatePromised], 1, 0) as on_time
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))ASKER
hmm nope i am grouping by INs Rep when i try and create my chart
I do get the number of on time perfect
shouldnt i try and add an expression now that saids sum[Item] / [Expr1] I like SQL server reporting services so much better,much eaiser I just need to build this metric in access driving me nutz
access.bmp
I do get the number of on time perfect
shouldnt i try and add an expression now that saids sum[Item] / [Expr1] I like SQL server reporting services so much better,much eaiser I just need to build this metric in access driving me nutz
access.bmp
No the expression you want is the Expr/total records. Ie. Total on_time divided by total invoices.
ASKER
So how do i right that in builder?
ASKER
sorry for taking up alot of your time should i start a new thread?
Well, since you have something that's working, let's try this...
SELECT COUNT(Invoice.SONumber) AS [Orders], SUM(IIf([DateShipped]<=[DatePromised],1,0)) AS [OnTime]
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));
No, that's alright.
ASKER
well kind of confusing cause your code returns one count on all items and all item ontime.
which i already have in the old code. returns 1 if good 0 if pass date
Expr1: IIf([DateShipped]<=[DatePr
if i try added this to a field
OnTime: Sum(IIf([DateShipped]<=[Da
you tried to execute a query that does not include the specified expression"ins rep"as part of an aggreegate function
access.JPG
which i already have in the old code. returns 1 if good 0 if pass date
Expr1: IIf([DateShipped]<=[DatePr
if i try added this to a field
OnTime: Sum(IIf([DateShipped]<=[Da
you tried to execute a query that does not include the specified expression"ins rep"as part of an aggreegate function
access.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmm ok well i am confused with your code.. it on the right track it retruns 2 number one ... one ont time and a percent
i need to group INS reps then show there on time then there percent
cant i just do this on a graph
it cant be this hard
i need to group INS reps then show there on time then there percent
cant i just do this on a graph
it cant be this hard
Oh, ok, so you want it grouped by ins reps. That's easy. In the query builder - if you have my last code in there - add the INS Reps columns. It should Default to "Group By" and then it will work.
Otherwise you can use this code:
Otherwise you can use this code:
SELECT Invoice.WrittenBy AS [INS Rep], SUM(IIf([DateShipped]<=[DatePromised],1,0))/COUNT(Invoice.SONumber) AS [PercentOnTime], SUM(IIf([DateShipped]<=[DatePromised],1,0)) AS [OnTime]
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))
GROUP BY Invoice.WrittenBy;
ASKER
this is what mine returns .. i want it to group the ins reps other line to show me totals and other to show me percent
access2.JPG
access2.JPG
Try Taking out all of the columns except for the agent, and the on_time_ness. And see if that looks like what you're expecting.
ASKER
ok that last code you sent me returned name number on time and a percent.. i am trying to add one more with total lines but looks good. now to get it on a graph i will start a new thread for this