Query help to summarize data by work week

[saseymour]

---

Ok, here is my issue.

I've got three fields - date_time, # of issues, issue reasons

I want to summarize these by work week.

So,

WW # of issues issue reasons
1 <sum of all issues for the week> list of all reasons entered
2 <sum of all issues for the week> list of all reasons entered
3 <sum of all issues for the week> list of all reasons entered
4 <sum of all issues for the week> list of all reasons entered
5 <sum of all issues for the week> list of all reasons entered

I know how to get the WW part - I do the datepart("ww",[Date_Time] for the expression. But how to write the query to do the other 2 parts, I'm lost.



Any ideas?

[offie]

Assuming Issues is numeric, click the Totals button, brings up Totals, Group By the WW field, Sum the Issues field

For the list of reason, I like to use ConcatRelated, a fuction that Allen Browne made, google it, it will put everything together with commas separating them.
To made the function press Ctrl+G, the second button to the left on the bottom row is Insert Module, copy and paste the code, save, and then follow the instrucions on the website.

[saseymour]

OK, so I've copied the ConCatRelated into a module. I'm confused about these two:

' strWhere = WHERE clause to choose the right values.
' strOrderBy = ORDER BY clause, for sorting the values.

I want to select all of the reasons entered, so do I need this where clause?
Also, I'd like to have them listed alphabetically, what would the vba syntax for that be?

Then, once that is entered, do I call the function in the field portion for the Reason list box in the query? I'm more familar with calling sub's in forms or tables, but haven't used functions yet.

Thanks for your help so far!

[offie]

If you don't need them, you don't need to put them in.
To list it alphabetically that is when you use ORDER BY, ex: ORDER BY [table].[field]

I don't really get what you are talking about when calling...
In SQL view in a query, you can even add everything else first in design view then change to query,
In the SELECT portion, add, just like adding a field, ConcatRelated(your info), thats it, it just replaces [table].[field]

Here is an example that I got from one of my queries

SELECT dbo_FieldOrder.ProjectCode, ConcatRelated("FieldOrderCode","dbo_FieldOrder",("[ProjectCode] =""" & [ProjectCode] & """")) AS FieldOrders
FROM dbo_FieldOrder
GROUP BY dbo_FieldOrder.ProjectCode
ORDER BY dbo_FieldOrder.ProjectCode;

Edit: If you're thinking about editing the code for the function, don't. Your wording confued me a little so I'm not quite sure what you are doing

Also, the GROUP BY in the ConcatRelated() funtion makes is so that the information in the cell will be alphabetical ex: (a, b, c), the GROUP BY in the general SQL makes it so that the cells in the field at alphabetical
ex:
alpha, ect
beta, ect

[saseymour]

I've inserted the module and tinkered with the ConCatRelated code, then I've created the following in the query SQL view:

SELECT MaintenanceTurnover.Time_Date, MaintenanceTurnover.Coater_1_NCR, MaintenanceTurnover.Coater_1_NCR_Reason, MaintenanceTurnover.Coater_1_NCR_Reasons, ConCatRelated("Coater_1_NCR_Reasons","Maintenance Turnover","Time_Date = " & [Time_Date]) AS Expr1
FROM MaintenanceTurnover
WHERE (((MaintenanceTurnover.Coater_1_NCR)>=1))
GROUP BY MaintenanceTurnover.Time_Date
ORDER BY MaintenanceTurnover.Time_Date;

I get the error - undefined function "ConCatRelated' in expression. Why is this query not seeing the public function created in the database VB?