Complicated Query/Function Question

  • Thread starter Thread starter Can Of Worms
  • Start date Start date
C

Can Of Worms

I designed a query which calls the the companion function below, although it
is running REALLY slow, likely from the multiple uses of DLookup in the
function. I have tried to think of a better way to do this, but I am coming
up dry.

2 Source tables:
- 1 table with event information tied to a week ending date, going back to
2007, updated weekly
- 1 table with inventory dates from each location, going back to 2006,
updated weekly

The desired result:
Return the correct inventory year associated with each event week ending
date. For example, if a location has had an inventory on 4/4/08 and 2/3/09,
then I want to add '2009' to a field for all events that are dated 4/5/08
through 2/3/09. Events before would be '2008', events after would be '2010'.
Basically, tag each event with the inventory year it is affecting.

The trouble I have is that not every location has an inventory every year,
and when they do have an inventory, it could be at any point during the year.
If this was static information, then I could use a crosstab as the base and
do the calculations off of that, but it fluctuates enough that this could
easily cause field reference problems.

So I need to account for if a location doesn't exist at all on the inventory
table, or exists for only part of the time. (Lots of {nulls} are possible)

Any suggestions? I feel I am missing something obvious that is query based
instead.

Function:
Function fInventoryYear(StoreN As Integer, WeekEndDate As Date) As Integer
Dim InvDate_Current As Date
Dim InvDate_Next As Date
Dim InvDate_Last As Date
Dim InvDate_Prior As Date
Dim Year_Current As Integer
Dim Year_Next As Integer
Dim Year_Last As Integer
Dim Year_Prior As Integer

fInventoryYear = 0
Year_Current = Year(Now())
Year_Next = Year(Now()) + 1
Year_Last = Year(Now()) - 1
Year_Prior = Year(Now()) - 2

If IsNull(StoreN) = True Or StoreN = 0 Then
Exit Function
End If

If IsNull(DLookup("[StoreN]", "tbl_YearLastInventory", "[StoreN] = " &
StoreN)) = True Then
Exit Function
End If

InvDate_Current = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Current), #1/1/2090#)
InvDate_Next = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Next), #1/1/2090#)
InvDate_Last = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Last), #1/1/2090#)
InvDate_Prior = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Prior), #1/1/2090#)

If IsNull(InvDate_Current) = False Then
If WeekEndDate > InvDate_Current Then
fInventoryYear = Year_Current + 1
Exit Function
End If
End If
If IsNull(InvDate_Last) = False Then
If WeekEndDate > InvDate_Last Then
fInventoryYear = Year_Last + 1
Exit Function
End If
End If
If IsNull(InvDate_Prior) = False Then
If WeekEndDate > InvDate_Prior Then
fInventoryYear = Year_Prior + 1
Exit Function
ElseIf WeekEndDate < InvDate_Prior And Year(InvDate_Prior) =
Year_Prior Then
fInventoryYear = Year_Prior
Exit Function
End If
End If
End Function
 
UNTESTED QUERIES follow.

Create and save a query like the following to establish the date range
of the inventories.

SELECT IA.StoreN
, IA.ID_Date as StartDate
, Nz(Min(IB.ID_Date),"#1/1/2090#) as EndDate
FROM tbl_YearLastInventory as IA LEFT JOIN tbl_YearLastInventory as IB
ON IA.StoreN = IB.StoreN
AND IA.ID_Date < IB.ID_Date
GROUP BY IA.StoreN,
IA.ID_Date


Now use that stored query to get the proper year associated with the
events. Because you are using a non-equi join this query can only be
constructed and viewed using the SQL view.

SELECT E.StoreN
, E.EventDate
, Year(Q.StartDate) as InventoryYear
FROM EventsTable as E LEFT JOIN queryInventoryDateRange as Q
ON E.StoreN = Q.StoreN
AND (E.EventDate >= Q.StartDate
AND E.EventDate <= Q.EndDate)


If you have trouble building these queries, post back and I will try to
tell you how you can use design view to build the first and use design
view to build most of the second.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I designed a query which calls the the companion function below, although it
is running REALLY slow, likely from the multiple uses of DLookup in the
function. I have tried to think of a better way to do this, but I am coming
up dry.

2 Source tables:
- 1 table with event information tied to a week ending date, going back to
2007, updated weekly
- 1 table with inventory dates from each location, going back to 2006,
updated weekly

The desired result:
Return the correct inventory year associated with each event week ending
date. For example, if a location has had an inventory on 4/4/08 and 2/3/09,
then I want to add '2009' to a field for all events that are dated 4/5/08
through 2/3/09. Events before would be '2008', events after would be '2010'.
Basically, tag each event with the inventory year it is affecting.

The trouble I have is that not every location has an inventory every year,
and when they do have an inventory, it could be at any point during the year.
If this was static information, then I could use a crosstab as the base and
do the calculations off of that, but it fluctuates enough that this could
easily cause field reference problems.

So I need to account for if a location doesn't exist at all on the inventory
table, or exists for only part of the time. (Lots of {nulls} are possible)

Any suggestions? I feel I am missing something obvious that is query based
instead.

Function:
Function fInventoryYear(StoreN As Integer, WeekEndDate As Date) As Integer
Dim InvDate_Current As Date
Dim InvDate_Next As Date
Dim InvDate_Last As Date
Dim InvDate_Prior As Date
Dim Year_Current As Integer
Dim Year_Next As Integer
Dim Year_Last As Integer
Dim Year_Prior As Integer

fInventoryYear = 0
Year_Current = Year(Now())
Year_Next = Year(Now()) + 1
Year_Last = Year(Now()) - 1
Year_Prior = Year(Now()) - 2

If IsNull(StoreN) = True Or StoreN = 0 Then
Exit Function
End If

If IsNull(DLookup("[StoreN]", "tbl_YearLastInventory", "[StoreN] = " &
StoreN)) = True Then
Exit Function
End If

InvDate_Current = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Current), #1/1/2090#)
InvDate_Next = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Next), #1/1/2090#)
InvDate_Last = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Last), #1/1/2090#)
InvDate_Prior = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Prior), #1/1/2090#)

If IsNull(InvDate_Current) = False Then
If WeekEndDate > InvDate_Current Then
fInventoryYear = Year_Current + 1
Exit Function
End If
End If
If IsNull(InvDate_Last) = False Then
If WeekEndDate > InvDate_Last Then
fInventoryYear = Year_Last + 1
Exit Function
End If
End If
If IsNull(InvDate_Prior) = False Then
If WeekEndDate > InvDate_Prior Then
fInventoryYear = Year_Prior + 1
Exit Function
ElseIf WeekEndDate < InvDate_Prior And Year(InvDate_Prior) =
Year_Prior Then
fInventoryYear = Year_Prior
Exit Function
End If
End If
End Function
Click to expand...
 
UNTESTED QUERIES follow.

Create and save a query like the following to establish the date range
of the inventories.

SELECT IA.StoreN
, IA.ID_Date as StartDate
, Nz(Min(IB.ID_Date),"#1/1/2090#) as EndDate
FROM tbl_YearLastInventory as IA LEFT JOIN tbl_YearLastInventory as IB
ON IA.StoreN = IB.StoreN
AND IA.ID_Date < IB.ID_Date
GROUP BY IA.StoreN,
IA.ID_Date


Now use that stored query to get the proper year associated with the
events. Because you are using a non-equi join this query can only be
constructed and viewed using the SQL view.

SELECT E.StoreN
, E.EventDate
, Year(Q.StartDate) as InventoryYear
FROM EventsTable as E LEFT JOIN queryInventoryDateRange as Q
ON E.StoreN = Q.StoreN
AND (E.EventDate >= Q.StartDate
AND E.EventDate <= Q.EndDate)


If you have trouble building these queries, post back and I will try to
tell you how you can use design view to build the first and use design
view to build most of the second.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I designed a query which calls the the companion function below, although it
is running REALLY slow, likely from the multiple uses of DLookup in the
function. I have tried to think of a better way to do this, but I am coming
up dry.

2 Source tables:
- 1 table with event information tied to a week ending date, going back to
2007, updated weekly
- 1 table with inventory dates from each location, going back to 2006,
updated weekly

The desired result:
Return the correct inventory year associated with each event week ending
date. For example, if a location has had an inventory on 4/4/08 and 2/3/09,
then I want to add '2009' to a field for all events that are dated 4/5/08
through 2/3/09. Events before would be '2008', events after would be '2010'.
Basically, tag each event with the inventory year it is affecting.

The trouble I have is that not every location has an inventory every year,
and when they do have an inventory, it could be at any point during the year.
If this was static information, then I could use a crosstab as the base and
do the calculations off of that, but it fluctuates enough that this could
easily cause field reference problems.

So I need to account for if a location doesn't exist at all on the inventory
table, or exists for only part of the time. (Lots of {nulls} are possible)

Any suggestions? I feel I am missing something obvious that is query based
instead.

Function:
Function fInventoryYear(StoreN As Integer, WeekEndDate As Date) As Integer
Dim InvDate_Current As Date
Dim InvDate_Next As Date
Dim InvDate_Last As Date
Dim InvDate_Prior As Date
Dim Year_Current As Integer
Dim Year_Next As Integer
Dim Year_Last As Integer
Dim Year_Prior As Integer

fInventoryYear = 0
Year_Current = Year(Now())
Year_Next = Year(Now()) + 1
Year_Last = Year(Now()) - 1
Year_Prior = Year(Now()) - 2

If IsNull(StoreN) = True Or StoreN = 0 Then
Exit Function
End If

If IsNull(DLookup("[StoreN]", "tbl_YearLastInventory", "[StoreN] = " &
StoreN)) = True Then
Exit Function
End If

InvDate_Current = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Current), #1/1/2090#)
InvDate_Next = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Next), #1/1/2090#)
InvDate_Last = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Last), #1/1/2090#)
InvDate_Prior = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Prior), #1/1/2090#)

If IsNull(InvDate_Current) = False Then
If WeekEndDate > InvDate_Current Then
fInventoryYear = Year_Current + 1
Exit Function
End If
End If
If IsNull(InvDate_Last) = False Then
If WeekEndDate > InvDate_Last Then
fInventoryYear = Year_Last + 1
Exit Function
End If
End If
If IsNull(InvDate_Prior) = False Then
If WeekEndDate > InvDate_Prior Then
fInventoryYear = Year_Prior + 1
Exit Function
ElseIf WeekEndDate < InvDate_Prior And Year(InvDate_Prior) =
Year_Prior Then
fInventoryYear = Year_Prior
Exit Function
End If
End If
End Function
Click to expand...
 
Whoops! Got an unneeded quote mark in the first query.

SELECT IA.StoreN
, IA.ID_Date as StartDate
, Nz(Min(IB.ID_Date),#1/1/2090#) as EndDate
FROM tbl_YearLastInventory as IA LEFT JOIN tbl_YearLastInventory as IB
ON IA.StoreN = IB.StoreN
AND IA.ID_Date < IB.ID_Date
GROUP BY IA.StoreN,
IA.ID_Date

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


John said:
UNTESTED QUERIES follow.

Create and save a query like the following to establish the date range
of the inventories.

SELECT IA.StoreN
, IA.ID_Date as StartDate
, Nz(Min(IB.ID_Date),"#1/1/2090#) as EndDate
FROM tbl_YearLastInventory as IA LEFT JOIN tbl_YearLastInventory as IB
ON IA.StoreN = IB.StoreN
AND IA.ID_Date < IB.ID_Date
GROUP BY IA.StoreN,
IA.ID_Date


Now use that stored query to get the proper year associated with the
events. Because you are using a non-equi join this query can only be
constructed and viewed using the SQL view.
Click to expand...
SNIP
 
Whoops! Got an unneeded quote mark in the first query.

SELECT IA.StoreN
, IA.ID_Date as StartDate
, Nz(Min(IB.ID_Date),#1/1/2090#) as EndDate
FROM tbl_YearLastInventory as IA LEFT JOIN tbl_YearLastInventory as IB
ON IA.StoreN = IB.StoreN
AND IA.ID_Date < IB.ID_Date
GROUP BY IA.StoreN,
IA.ID_Date

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


John said:
UNTESTED QUERIES follow.

Create and save a query like the following to establish the date range
of the inventories.

SELECT IA.StoreN
, IA.ID_Date as StartDate
, Nz(Min(IB.ID_Date),"#1/1/2090#) as EndDate
FROM tbl_YearLastInventory as IA LEFT JOIN tbl_YearLastInventory as IB
ON IA.StoreN = IB.StoreN
AND IA.ID_Date < IB.ID_Date
GROUP BY IA.StoreN,
IA.ID_Date


Now use that stored query to get the proper year associated with the
events. Because you are using a non-equi join this query can only be
constructed and viewed using the SQL view.
Click to expand...
SNIP
 
Thanks for the help. Took me a few false starts, was over-assuming and not
cleanly reading the sql :)

Works great, thanks so much for the help.
 
And I like how you used " as E" etc... on the join. I can certainly see the
benefits to SQL references there. Thanks for the indirect tip :)
 
Back
Top