I know how to do this in theory, it's the practical outworking of it that I can't seem to get my head around.

There's a table of incoming transaction data, to which new records are continually being added.

A succession of sales promotion periods takes place. They are of variable duration, but one immediately succeeds the next.

The process generating the raw transaction data knows nothing about the promotion periods and so cannot store the relevant promotion period ID in the transaction record.

So how do we describe the sales promotion periods? If we store a start and finish date for each, we then have to make sure there are no gaps or overlaps

And if we just store the start date of the promotion period, how do we determine which one of the periods any given transaction fell into? (because although the transaction date is greater than the start date of the relevant period, it's also greater than the start of the previous period).


I see scenarios like this quite a lot, when people talk about normalising their data and not storing something like cost prices, which can be calculated later - but how should they be stored, and how is the right one picked out later?

Sounds a bit like them on telly DFS - Doesn't Finish Sunday.

Can you not use the day of the year as a quantifier or the Julien number, actuallt this may be better as it will cope with crossing over into new years.

Sure, but the problem is that the table describing the promotion periods only contains their endpoints. How can I build a query that determines which promotion period was active for any given transaction?

surely you dont need to store the start AND end of a promotion.

if only one promotion can be active, just store the start date of the promotion

then the price for a transaction is the one ruling at the lowest date greater/equal to the transaction date. which is just a single dmin

ie - effectively (ignoring the syntax errors)

promotion date = dmin("promotiondate","promotion recordtable","promitiondate<=targetdate")

surely you dont need to store the start AND end of a promotion.

if only one promotion can be active, just store the start date of the promotionAbsolutely! I expressly don't want to store both, as I would then have to manage for gaps and overlaps

then the price for a transaction is the one ruling at the lowest date greater/equal to the transaction date. which is just a single dmin

ie - effectively (ignoring the syntax errors)

promotion date = dmin("promotiondate","promotion recordtable","promitiondate<=targetdate")That's perfect. Thanks!

I'm having trouble getting this to work consistently - I think it's because of date format constraints. My expression is:

DMin("[PromoCode]","[tblPromotions]","[DateEffective]<=#" & [TrDate] & '#')

Which should mean: find the Promocode for whicand it returns a value for every row, but it's not necessarily the appropriate value. I also tried:

DMin("[PromoCode]","[tblPromotions]",'#' & [TrDate] & '# - [DateEffective] >=0')

But the results are the same. For all but a couple of records, it's actually finding the promo code that has an effective date furthest in to the past.

I've also tried wrapping the date parameters with cdate(), but it makes no difference.

Stuck now. Help!

I cannot for the life of me get this to work. To keep things clean, I've created separate database for testing, containing the following tables:
Promotions:
PromoID PromoStartDate PromoName
1 01/01/2011 January Madness
2 01/02/2011 Valentines
3 01/03/2011 Mad March Hare
4 01/04/2011 April Fools
5 01/05/2011 Mayday
6 01/06/2011 Summer
7 25/07/2011 School's Out
8 01/09/2011 Back To School
9 15/09/2011 No Promo in effect
10 15/10/2011 Halloween
11 01/11/2011 Bonfire Night
12 06/11/2011 Autumn Special
13 01/12/2011 Christmas
Transactions:
ID Transdate TransAmount ItemID_fK Narrative
1 15/01/2011 £12.00 2 Should be in January Madness
2 19/01/2011 £1.00 5 Should be in January Madness
3 12/04/2011 £56.00 22 Should be in April Fools
4 19/04/2011 £5.00 6 Should be in April Fools
5 06/06/2011 £1.00 1 Should be in Summer
6 02/11/2011 £55.00 44 Should be in Bonfire Night
7 01/12/2011 £56.00 48 Should be in Christmas
8 12/12/2011 £4.00 3 Should be in Christmas
(NB the Narrative column in this Transactions table exists only to help me see at a glance whether the query expression is working)

Then I've got this query:
SELECT Transactions.*,
DMin("[promoname]","[promotions]","#" & [transdate] & "# >= [PromoStartDate]") AS Comparison,
DMin("[promoname]","[promotions]","#" & [transdate] & "# - [PromoStartDate] >=0") AS [Subtraction method],
DMin("[promoname]","[promotions]","#" & CDate([transdate]) & "# - [PromoStartDate] >=0") AS [Using cdate],
DMin("[promoname]","[promotions]","#" & Format([transdate],'mm/dd/yyyy') & "# - [PromoStartDate] >=0") AS [imposing mmddyyyy format],
DMin("[promoname]","[promotions]","#" & Format([transdate],'dd/mm/yyyy') & "# - [PromoStartDate] >=0") AS [imposing ddmmyyyy format]
FROM Transactions;

Which gives these results:
ID Transdate TransAmount ItemID_fK Narrative Comparison Subtraction method Using cdate imposing mmddyyyy format imposing ddmmyyyy format
1 15/01/2011 £12.00 2 Should be in January Madness January Madness January Madness January Madness January Madness January Madness
2 19/01/2011 £1.00 5 Should be in January Madness January Madness January Madness January Madness January Madness January Madness
3 12/04/2011 £56.00 22 Should be in April Fools April Fools April Fools April Fools April Fools April Fools
4 19/04/2011 £5.00 6 Should be in April Fools April Fools April Fools April Fools April Fools April Fools
5 06/06/2011 £1.00 1 Should be in Summer April Fools April Fools April Fools April Fools April Fools
6 02/11/2011 £55.00 44 Should be in Bonfire Night January Madness January Madness January Madness April Fools January Madness
7 01/12/2011 £56.00 48 Should be in Christmas January Madness January Madness January Madness April Fools January Madness
8 12/12/2011 £4.00 3 Should be in Christmas April Fools April Fools April Fools April Fools April Fools

I'm pretty sure this is something to do with a conflict between UK dd/mm/yyyy formatted dates and US/SQL mm/dd/yyyy dates, but I can't seem to coerce them to play nicely

possibly this - you need to format the trdate in a SQL statement

DMin("[PromoCode]","[tblPromotions]","[DateEffective]<=#" & format([TrDate],"DD/MM/YYYY") & '#')

Thanks - I tried that, but I'm still getting the same (wrong) results.

sorry try this (not sure without trying whether the target date needs UK formatting to avoid ambiguity.

this should get the lowest promotion starting on or after the targetdate. Then do a dlookup to get promoid. I would think you could do the same by using a findfirst in a recordset.

usedate = nz(dmin("promostartdate","promotions","promostartdate>= #" & targetdate & "#"),0)

if usedate>0 then useid = dlookup("promoid","promotions","promostartdate>= #" & usedate & "]#")

Thanks Dave - I need to do this in a query though. Not sure how to apply the above...

I think something is fundamentally broken about date handling here - I just tried checking each part of the date separately, like this:
Expr5: DMin("[promoname]","[promotions]","Day([PromoStartDate]) <= " & Day([transdate]) & " AND Month([PromoStartDate]) <= " & Month([transdate]) & " AND year([PromoStartDate]) <= " & Year([transdate]))

And I still get the same results.

This is my sample database... if you're interested in experimentation (I'd be interested to see whether this just works for people in other locales anyway)

i'll have a look, but i'm UK.

I see

a) you do need to wrap the dates in some formatting code, to avoid US/UK errors.
b) after having a go, with my original idea, I realised that rather than the dmin of dates greater than or equal to yours, you need instead to use the slightly different idea of a dmax of dates less than or equal to yours

so - I don't think you can do it in a single step - but I added a module, and an update query, and an extra field to the transactions table called promocode, to store the effective price list.

Hope this does the trick


35346

Dave, that's fantastic - thanks so much.

The getpromocode function you created can be called directly in a query, so it's still possible to do in a single step. Excellent!