Week ending Friday Inventorys

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I'm trying to get a Annual Summary Report that will show
the Inventory Count and Value of Animals for each farm on
hand as of each Friday for the year. The SQL for the
current inventory looks like this:

SELECT [TBL-Farms].Company, [TBL-Inventory].FarmID, [TBL-
Inventory].[Entry#], [TBL-Inventory].EntryDate, [TBL-
Inventory].TagID, [TBL-Inventory].Birthdate, [TBL-
Inventory].Breed, [TBL-Inventory].InventoryValue, [TBL-
Inventory].ExitDate, [TBL-Inventory].Reason, [TBL-
Inventory].CullValue, [TBL-Inventory].PaymentID, [TBL-
Inventory].PaymentDate,
FROM [TBL-Farms] INNER JOIN [TBL-Inventory] ON [TBL-
Farms].FarmID = [TBL-Inventory].FarmID
WHERE ((([TBL-Inventory].ExitDate) Is Null))
ORDER BY [TBL-Inventory].ID, [TBL-Inventory].EntryDate,
[TBL-Inventory].Breed;

This will give the list of Animals and then I use another
query to give me a summary by counting the active animals.

SELECT DISTINCTROW [Active Inventory by Farm Query].ID,
[TBL-Farms].Company, [Active Inventory by Farm
Query].Breed, Count(*) AS [Count Of Active Inventory by
Farm Query], Sum([Active Inventory by Farm
Query].InventoryValue) AS [Sum Of InventoryValue]
FROM [Active Inventory by Farm Query] INNER JOIN [TBL-
Farms] ON [Active Inventory by Farm Query].FarmID = [TBL-
Farms].FarmID
GROUP BY [Active Inventory by Farm Query].FarmID, [TBL-
Farms].Company, [Active Inventory by Farm Query].Breed;

Any suggestions?
 
Tom said:
I'm trying to get a Annual Summary Report that will show
the Inventory Count and Value of Animals for each farm on
hand as of each Friday for the year. The SQL for the
current inventory looks like this:

SELECT [TBL-Farms].Company, [TBL-Inventory].FarmID, [TBL-
Inventory].[Entry#], [TBL-Inventory].EntryDate, [TBL-
Inventory].TagID, [TBL-Inventory].Birthdate, [TBL-
Inventory].Breed, [TBL-Inventory].InventoryValue, [TBL-
Inventory].ExitDate, [TBL-Inventory].Reason, [TBL-
Inventory].CullValue, [TBL-Inventory].PaymentID, [TBL-
Inventory].PaymentDate,
FROM [TBL-Farms] INNER JOIN [TBL-Inventory] ON [TBL-
Farms].FarmID = [TBL-Inventory].FarmID
WHERE ((([TBL-Inventory].ExitDate) Is Null))
ORDER BY [TBL-Inventory].ID, [TBL-Inventory].EntryDate,
[TBL-Inventory].Breed;
Click to expand...

SELECT [TBL-Farms].Company
,[TBL-Inventory].FarmID
,[TBL-Inventory].[Entry#]
,[TBL-Inventory].EntryDate
,[TBL-Inventory].TagID
,[TBL-Inventory].Birthdate
,[TBL-Inventory].Breed
,[TBL-Inventory].InventoryValue
,[TBL-Inventory].ExitDate
,[TBL-Inventory].Reason
,[TBL-Inventory].CullValue
,[TBL-Inventory].PaymentID
,[TBL-Inventory].PaymentDate
,
FROM [TBL-Farms]
INNER JOIN
[TBL-Inventory]
ON [TBL-Farms].FarmID = [TBL-Inventory].FarmID
WHERE ((([TBL-Inventory].ExitDate) Is Null))
ORDER BY [TBL-Inventory].ID
,[TBL-Inventory].EntryDate
,[TBL-Inventory].Breed;

I'm removing the extra ","


This will give the list of Animals and then I use another
query to give me a summary by counting the active animals.

SELECT DISTINCTROW [Active Inventory by Farm Query].ID,
[TBL-Farms].Company, [Active Inventory by Farm
Query].Breed, Count(*) AS [Count Of Active Inventory by
Farm Query], Sum([Active Inventory by Farm
Query].InventoryValue) AS [Sum Of InventoryValue]
FROM [Active Inventory by Farm Query] INNER JOIN [TBL-
Farms] ON [Active Inventory by Farm Query].FarmID = [TBL-
Farms].FarmID
GROUP BY [Active Inventory by Farm Query].FarmID, [TBL-
Farms].Company, [Active Inventory by Farm Query].Breed;
Click to expand...

SELECT DISTINCTROW
[Active Inventory by Farm Query].ID
,[TBL-Farms].Company
,[Active Inventory by Farm Query].Breed
,Count(*) AS [Count Of Active Inventory by Farm Query]
,Sum([Active Inventory by Farm Query].InventoryValue) AS [Sum Of
InventoryValue]
FROM [Active Inventory by Farm Query]
INNER JOIN
[TBL-Farms]
ON [Active Inventory by Farm Query].FarmID = [TBL-Farms].FarmID
GROUP BY [Active Inventory by Farm Query].FarmID
,[TBL-Farms].Company
,[Active Inventory by Farm Query].Breed;
Any suggestions?
Click to expand...

Making many, many assumptions . . .

What we need to do is create a table including all dates in the year in
question.
Then, we need to derive the Fridays in that year.
Then, we need to get the Inventory Quantities (an assumption on my part) on
each Friday.


Execute the following Create Table Queries.
I abbreviated the Tbl-Inventory table for simplicity.
I assumed each animal was uniquely identified by it's TagID (from a school
field trip to a dairy farm twenty-five years ago, I remember cows all have
tags pierced into their ears, or some such, so I hope I made the right
decision there).

CREATE TABLE [Tbl-Inventory]
(TagID INTEGER
,EntryDate DATE
,ExitDATE DATE
,CONSTRAINT pk_TblInventory PRIMARY KEY (TagID)
)

Sample Data

1, 02/01/2003, 10/31/2003
2, 01/01/2003, 01/31/2003
3, 03/01/2003, 03/02/2003
4, 01/01/2001, 12/15/2003
5, 02/01/2003, 04/30/2003
6, 06/01/2003, 06/07/2003



CREATE TABLE CalendarYear
(CalendarDate DATE
,CONSTRAINT pk_CalendarYear PRIMARY KEY (CalendarDate)
)


Run the following VBA Code.
Modify the date of CalendarYear to suit, or otherwise modify to accept
parameters.
You may create larger CalendarYear tables or otherwise modify the start and
end periods to encompass reporting across different types of Calendar Years
or reporting across multiple years simultaneously.

Public Sub FillCalendarYear()

Dim db As DAO.Database
Dim CreateCalendarYear As String
Dim LoopDate As Date
Dim YearEndDate As Date
Dim rs As DAO.Recordset
Dim CalendarYear As String

CalendarYear = "2003"

Set db = CurrentDb()

Set rs = db.OpenRecordset("CalendarYear")

LoopDate = "01/01/" & CalendarYear
YearEndDate = "12/31/" & CalendarYear
Do While LoopDate <= YearEndDate
With rs
.AddNew
.Fields("CalendarDate") = LoopDate
.Update
LoopDate = DateAdd("d", CDbl(1), LoopDate)
End With
Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub


Create this Query named CalendarYear_Fridays

SELECT C1.CalendarDate
FROM CalendarYear AS C1
WHERE datepart("w", C1.CalendarDate) = 6;


Finally, this is the query that will give you the data you want.

SELECT CF1.CalendarDate
,COUNT(I1.TagID) AS InventoryQuantity
FROM [Tbl-Inventory] AS I1
INNER JOIN
CalendarYear_Fridays AS CF1
ON (CF1.CalendarDate BETWEEN I1.EntryDate And I1.ExitDate)
GROUP BY CF1.CalendarDate

There were 50 output rows, which is pretty much what I was expecting,
given no inventory in the last two weeks.

I hope this works out.
 
Back
Top