Weekly Query

[stirofechoes]

I work for a pallet recovery company. I was asked to create a report that sums the number of pallets that are picked up over a weekly period. I wrote a query that can perform the operation but have to hand key the dates for each weekly period.

Is there a way that I can use a start date of 1/4/04 and have the query give back the results for each weekly period up to the current date?

Thanks.

 

[ChrisUK]

Hi and welcome to the board.

I can't think of how you could do this with a single SQL statement, however, you could use one SQL statement to pull al the information in the correct order then use an access report with a little VB to break the totals down.

For example;

Select * from pallet-table where startdate > "1/04/04" and startdate < "7/03/05" order by startdate

This pulls all the rows between two dates, when you write a report you can put a loop in to sum the data from the first startdate to the startdate + 7

You could also put some aggregate functions into your SQL, for example

Select *, datediff("1/4/04", startdate) from pallet-table where startdate > "1/04/04" and startdate < "7/03/05" order by startdate

(sorry, I'm not 100% sure of the syntax and I've not got a copy of access on this PC to try it, but I think you should get the drift)

HTH

Chris

 

[tails]

using that same idea to allow the date to be entered so that you can use different dates rather than just the date that is in the code. You can do the code as
sdate = inputbox ("Enter starting date")
edate = inputbox ("Enter ending date")

Select * from pallet-table where startdate > sdate and startdate < edate order by startdate

that way you can enter the date so you dont just have to use the 1/4/04 and the 7/4/04. It will allow you to enter the date you want it between and then it will select the dates from the database.
can't be positive if it will work but the code seems right to me.

 

[stirofechoes]

Thanks for the info. I will give it a try.

 

[ChrisUK]

OK, it's a long long time since I wrote an access report, but, this is what I was trying to show.

'Declaration section
Dim x As Integer
Dim stDate As Date
Dim PalletCount As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Setup an initial start point
If x = 0 Then
stDate = PalletDate
x = 1
Else
'Jump forward in blocks of 7 days
If PalletDate > stDate + 6 Then
Print "PalletCount: ", PalletCount
PalletCount = 1
stDate = stDate + 6
Else
'Inciment the pallet count by 1 for each pallet in the week
PalletCount = PalletCount + 1
End If
End If

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

'Set this value in the report header - ie the first row!
x = 0

End Sub


It needs some work but I hope you get the general idea

Chris