Now() - 1

[Jon von der Heyden]

Hi,

I have a query running or a table of orders. All orders have order dates, what I want to do is return all orders from yesterday. What do I use for the criteria? I tried now()-1 but it returned nothing.

Also, I don't know if it's possible but I would like to add a new column into the query that assigns codes based on criteria. I have two particular fields from the table that I am querying - revenue and margin. I would like the new column to assign codes to each of the records as follows:

A - If margin/revenue is less than 11% and greater than 8%.
B - If margin/revenue is less than 8% and greater than 3%
C - If margin/revenue is less than 3%

I'm not really interested in any records that do not fall within the ABC criteria.

I would be fine doing this in Excel but I have in excess of 80000 records.

Thanks,
Jon

 

[stevey03]

Hi,

The first part I can definately help you with:

Try using DateAdd("y",-1,Date()) as your criteria and that should solve your problem.

The second part you will need to use a stacked if expression.

Something like this maybe:

IIf (margin/revenue Between 10% And 8% , A) Or IIf (margin/revenue Between 7% And 3% , B) Or IIf (margin/revenue < 3 , C)

You may need to put the percentages as decimals, i don't know.

Hth! Steve

 

[Jon von der Heyden]

Thanks Steve, date calculation works perfectly. Avoids me having to go in each time to amend the query. I'm still going to be a pain though, because I just thought how convinient it would be to be able to specify the number of days to dedeuct by having a box appear when executing the query. Any idea?

Haven't figured out the stacked if expression yet, I understand what it's trying to do but it's not working for me. I'm still working on a variety of different combinations though.

Thanks for all your help.

 

[flashsam]

Hello,

I'm just guessing here, haven't tried it but try this:

DateAdd("y",[Enter no of days],Date())

You never know, it might work!

Sam

 

[Jon von der Heyden]

Great - date thingy is working fantastically.

I still can't get the knack of the ABC status though? I've tried adding a new column and place in an existing column, with the stacked if expression as the criteria. Paperclip keeps tapping on my screen with a variety of errors.

Any ideas?

My SQL looks as follows:

SELECT [Pad by customer Q106].[Sales Division], [Pad by customer Q106].[Customer Name], [Pad by customer Q106].[Customer Num], [Pad by customer Q106].[Internal Sales Rep], [Pad by customer Q106].LoB, [Pad by customer Q106].[System Qty Excl Racks], [Pad by customer Q106].[Order Num], [Pad by customer Q106].[Order Date], [Pad by customer Q106].[Revenue GBP], [Pad by customer Q106].[Margin GBP], [Test] AS Expr1
FROM [Pad by customer Q106]
GROUP BY [Pad by customer Q106].[Sales Division], [Pad by customer Q106].[Customer Name], [Pad by customer Q106].[Customer Num], [Pad by customer Q106].[Internal Sales Rep], [Pad by customer Q106].LoB, [Pad by customer Q106].[System Qty Excl Racks], [Pad by customer Q106].[Order Num], [Pad by customer Q106].[Order Date], [Pad by customer Q106].[Revenue GBP], [Pad by customer Q106].[Margin GBP], [Test]
HAVING ((([Pad by customer Q106].[Order Date])=DateAdd("y",[Enter no of days],Date())) AND (([Pad by customer Q106].[Revenue GBP])>0) AND (([Test])=IIf([Margin GBP]/[Revenue GBP] Between 0.1 And 0.08,"A") Or ([Test])=IIf([Margin GBP]/[Revenue GBP] Between 0.07 And 0.03,"B") Or ([Test])=IIf([Margin GBP]/[Revenue GBP]<0.03,"C")));

 

[flashsam]

Hello again.

glad it worked!

I don't know too much about SQL in access but if i was doing what you wanted on DB2 SQL i would have a case statement in the select list.

For example:

SELECT [Pad by customer Q106].[Sales Division],
Case when [Margin GBP]/[Revenue GBP] Between 0.1 And 0.08
then 'A'
when [Margin GBP]/[Revenue GBP] Between 0.07 And 0.03
then 'B'
when [Margin GBP]/[Revenue GBP]<0.03
then 'C'
end as 'Test'

FROM [Pad by customer Q106]

GROUP BY [Pad by customer Q106].[Sales Division], [Pad by customer Q106].[Customer Name], [Pad by customer Q106].[Customer Num], [Pad by customer Q106].[Internal Sales Rep], [Pad by customer Q106].LoB, [Pad by customer Q106].[System Qty Excl Racks], [Pad by customer Q106].[Order Num], [Pad by customer Q106].[Order Date], [Pad by customer Q106].[Revenue GBP], [Pad by customer Q106].[Margin GBP], [Test]

HAVING ((([Pad by customer Q106].[Order Date])=DateAdd("y",[Enter no of days],Date())) AND (([Pad by customer Q106].[Revenue GBP])>0);

I don't guarantee that this will work but this is the format i'd do it on DB2 SQL so it might work. If not then look up case statements in access help to see if that helps.

Sam

 

[Jon von der Heyden]

Thanks Sam, still haven't figured it out but I'll have a few more cracks with it. I'll do some reading through help as per your suggestion.

 

[Andrew Fergus]

Try something like this in your query :
IIf([Margin]/[Sales]>=0.08, "A", IIf([Margin]/[Sales]<0.03, "C", "B"))
You mentioned that you weren't interested in those products with margins outside of these ranges - unless I'm not reading your question correctly, the only range left is >11%. If you want to exclude those records you could have a criteria of <=0.11 under the [Margin]/[Sales] field.

If however, you want to include those records but not assign an A/B/C value then you could use the following expression instead :
IIf([Margin]/[Sales]>0.11, Null, IIf([Margin]/[Sales]>=0.08, "A", IIf([Margin]/[Sales]<0.03, "C", "B")))

HTH, Andrew.