Is there any way to create sums based on a weekly range? (1 Viewer)

[Kellen]

I'm creating a query for a someone who wants quantities summed by a weekly range and correspond to a week number. This person wants Access to do something that is a simple function on Excel.

I have no clue how to do this or if Access is even made to do something like this.

This is an example of what I have so far

P/N QTY Work Date WW (Work Week)
25COMP 1 6/11/2014 28
25COMP 2 6/20/2014 25
25COMP 2 7/9/2014 28
25COMP 3 6/20/2014 25

....And so on

My code is

SELECT dbo_JBKLG.JKPRT AS P/N, dbo_JOB.JBQOR AS QTY, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4)) AS [Work Date], DatePart("ww",[Work Date]) AS [WW]

FROM dbo_JBKLG LEFT JOIN dbo_JOB ON dbo_JBKLG.JKJOB = dbo_JOB.JBJNO

GROUP BY dbo_JBKLG.JKPRT, dbo_JOB.JBQOR, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4))

This is what the guy wants. There are more part numbers and the data from the other parts must be in the same format in the same query.

P/N QTY WK WW

25COMP 155 06/09-06/15 WW24
25COMP 227 06/16-06/22 WW25
25COMP 160 06/23-06/29 WW26
25COMP 200 06/30-07/06 WW27

.....And so on

 

[plog]

Can you post 2 sets of sample data: A-starting sample data from your tables (include field and table names) and B. what data should result based on the data you provide in A.

Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 23, 12/2/2013
Sally, 44, 12/13/2009
Larry, 35, 8/16/2009

 

[Kellen]

Can you post 2 sets of sample data: A-starting sample data from your tables (include field and table names) and B. what data should result based on the data you provide in A.

Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 23, 12/2/2013
Sally, 44, 12/13/2009
Larry, 35, 8/16/2009

My tables have a ton of columns and basing my query from another query. I'll do an example based on one work week using actual data.

What I have:

JBLKG
JKPRT, JBQOR, Work Date (Converted date), Week Number
25COMP, 20, 6/18/2014 , 25
25COMP, 20, 6/20/2014 , 25
25COMP, 5, 6/20/2014 , 25
25COMP, 3, 6/20/2014 , 25
25COMP, 2, 6/20/2014 , 25

What he wants:
JKPRT, JBQOR, Week, Week Number
25COMP, 50, 6/16/2014 - 6/20/2014, 25

 

[plog]

First, you should avoid non alpha-numeric characters in field and table names. That means no spaces ('Week Number' -> 'WeekNumber'), it just makes writing code and queries a little more difficult. Also, I'd rename Week by prefixing it with what the week represents (WorkWeek).

For your query, it's a simple aggregate query based on your data:

SELECT JKPRT, SUM(JBQOR) AS Tot_JBQOR, get_WorkWeek([Work Date]) AS WorkWeek, [Week Number]
FROM JBLKG
GROUP BY JKPRT, get_WorkWeek([Work Date]), [Week Number]

That query isn't going to run for you until you create the function get_WorkWeek(). You will also need the below code pasted into a Module to calculate the work week a date falls into (e.g. 6/16/2014 - 6/20/2014):

Public Function get_WorkWeek(in_Date) As String
    ' determines what work week a date (in_Date) fallse in

ret = "Weekend Date Submitted"              ' return value, by default sends error message
int_WeekDay = Weekday(in_Date)              ' gets day of week in_Date falls on


If (int_WeekDay >= 2 And int_WeekDay <= 6) Then
    ' if in_Date is Monday - Friday calculates work week
    ret = DateAdd("d", -1 * (int_WeekDay - 2), in_Date) & " - " & DateAdd("d", 6 - int_WeekDay, in_Date)
End If

get_WorkWeek = ret

End Function

Paste that into a module and then the SQL into a query and it should produce the results you want.

 

[Kellen]

First, you should avoid non alpha-numeric characters in field and table names. That means no spaces ('Week Number' -> 'WeekNumber'), it just makes writing code and queries a little more difficult. Also, I'd rename Week by prefixing it with what the week represents (WorkWeek).

For your query, it's a simple aggregate query based on your data:

Paste that into a module and then the SQL into a query and it should produce the results you want.

Thanks but can this work for different JKPRT values and cover all weeks in the year?

EDIT: fixed it sorry

 

[plog]

Quit doing that. My posts are there for everyone to see, they can scroll up. If you need to reference something specific I said, quote that portion, quit spitting back my entire posts to me.

It will work for different JKPRT values you have. As it is set up now it will display all weeks in the year that you have data for. If you have no data for 3/3/2014 - 3/7/2014 it will not display data for that week.

To force it to have data for weeks that you have no data for, you would need a table that contains all the weeks of a year.

 

[Kellen]

It comes up with an error saying "undefined function 'get workweek' in expression".

I added the function through the expression builder and the function was in the list.

Also WorkDay isn't defined and converted into a date value. Do I need to add that to your code?

 

[plog]

The actual name of the function is 'get_WorkWeek', there's an underscore in there. Also, 'WorkDay' isn't in the query or VBA I posted. Where is it in your code?

 

[Kellen]

The actual name of the function is 'get_WorkWeek', there's an underscore in there. Also, 'WorkDay' isn't in the query or VBA I posted. Where is it in your code?

I meant WorkDate.

Tried with the underscore still no luck.:banghead:

 

[plog]

'Work Date' is the field you posted that was in your query. Can you post your database?

 

[gemma-the-husky]

play around with the PARTITION function in your query

 

[Kellen]

'Work Date' is the field you posted that was in your query. Can you post your database?

Sorry I can't, it's not my database it's my company's. And all the names I gave you are different in the actual database. Also my database is ODBC too.


The Work Date in my field is in text format. The date is also written as 20140101 which I've converted normal date form 1/1/2014.

 

[plog]

I don't know what to tell you then. You need to be really careful in correctly typing your fields' and tables' names from the ones I used. I set up a fake table of the data you provided and both my query and my VBA code work on mine.

 

[Kellen]

I don't know what to tell you then. You need to be really careful in correctly typing your fields' and tables' names from the ones I used. I set up a fake table of the data you provided and both my query and my VBA code work on mine.

I've noticed in your SQL code that you sent me that JBKLG is the only table. My thing is a linked with another table JOB and thats where JBQOR comes from. Maybe if join the tables in the code it will work.

Nope. I still get the undefined function error.

Should I change int_Weekday into int_Workdate?

How did your results come in?

 

[plog]

Undefined function means the query can't find the name of the function you've used. It has nothing to do with the variables inside the function.

Did you post the VBA code inside a module?

 

[Kellen]

Okay I figured out the function problem but now I'm getting asked for parameters for the work date.

 

[plog]

That means your work date field isn't named correctly.

 

[Kellen]

Okay I got the query to open finally but the function you gave me gives out blanks.

Here's my code (I changed some aliases):

SELECT 
dbo_JBKLG.JKPRT AS Part_No, 
Sum(dbo_JOB.JBQOR) AS Qty, 
CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4)) AS Work_Date, DatePart("ww",[Work_Date]) AS [Week Number], 
get_Week(Work_Date) AS WorkWeek

FROM dbo_JBKLG LEFT JOIN dbo_JOB ON dbo_JBKLG.JKJOB = dbo_JOB.JBJNO

GROUP BY dbo_JBKLG.JKPRT, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4))
HAVING (((dbo_JBKLG.JKPRT)="25COMP"));

I'm close I can feel it.

 

[plog]

What Work_Date values are creating those blanks?

 

[Kellen]

What Work_Date values are creating those blanks?

I'm not sure what you mean. The whole column is blank. And the work_date values are valid converted dates from CDate.

What is workweek supposed to show when it works?

The current problems I'm having are:

If I switch Work Week into group by it asks my for a parameter but for expression the query runs blank on it's column.

If I switch work date into expression then I get the the error that it can't run because it's not part of an aggregate function.