Query to create a new column for each week interval

[April15Hater]

Hi-
I have a query (qtrContractorProduction) that has 4 fields: TrackingNumber, Contractor, CompleteDate, and Units. What I would like to do is create a report (but I'm having trouble with the query) that shows the total units for each contractor by week. I need the columns to represent the separate weeks, and the rows to show each contractor. I think a crosstab query might be the best solution, but I can't figure out how to make the columns group by week. Any suggestions?

thanks,

Joe

 

[WayPay]

Add a column to your query with the following expression:

CompleteWeek: Iif(IsNull([CompleteDay]),Null,DatePart("ww", [CompleteDay]))

and put it in the 'Group By'.

 

[April15Hater]

That worked great! Now I have another predicament, I need to label the report for the week ending date. So how can I revert that back? I thought maybe multiply the datepart by 7, but I don't think that is entire correct since the year doesn't always start ont he same day.

Thanks!

Joe

 

[datAdrenaline]

I use a UDF like this ...

Public Function LastDOW(ByVal dtDate As Date, Optional intWeekBegin As Integer = vbUseSystemDayOfWeek) As Date
'Returns the last day of the week of the passed date.
    
    LastDOW = DateSerial(Year(dtDate), 1, DatePart("y", dtDate, intWeekBegin) + (7 - Weekday(dtDate, intWeekBegin)))
        
End Function

Then I would call it from my query with an expression like this:

WeekEnding: LastDOW([CompleteDay])

Then you can just group on that date as appossed to grouping by the week number ...

 

[April15Hater]

I think I might have opened a can of worms here. Don't get me wrong, the UDF works great, and I def appreciate the help, but what I'm trying to do might be a lot more complicated than I thought. I'll explain and perhaps you can shed some light.

The actual Units have identifiers identifying what was done to those units in a separate table (tblFunction). Those "functions" are further broken down into Aerial Footage, Underground Footage, or Units in another table (tblContractorFunction). The Aerial and Underground needs to be summed up and put in one "subcolumn" then units need to be in another "subcolumn". In other words, I need it separated by complete week, then further separated by Aerial/Und and Unit. Would I need two queries for this?

Thanks,

Joe

 

[WayPay]

Would I need two queries for this?

Not necessarily, but that might well be the best solution. You might even use 3 or 4 queries .

I'd probably use a separate query to do the SUMming, then JOIN on that query. I usually try to put as much in one query as possible, then JOIN on separate (sub-)queries where necessary. Works pretty good for me, even on large data sets.

 

[April15Hater]

You might even use 3 or 4 queries .

Is it better to do make them in Access or should I use ADO? Is there even a way to get the Report to use an ADO query as a Recordsource?

I usually try to put as much in one query as possible, then JOIN on separate (sub-)queries where necessary.

I'm not quite sure what you mean when you say that you JOIN on them. Do you mean JOIN on the individual queries in Access?

Thanks,

Joe

 

[WayPay]

This example is a bit strained, but here goes:

Table tblOrder
OrderID PK

Table tblOrderLine
OrderID PK
OrderItemID PK
Article
Amount
Price

Suppose you want a grand total for each order:

SELECT 
 tblOrder.OrderID, 
 SUM(tblOrderLine.Amount * tblOrderLine.Price) AS [Grand Total]
FROM tblOrder 
 INNER JOIN tblOrderLine ON tblOrder.OrderID = tblOrderLine.OrderID
GROUP BY tblOrder.OrderID

Instead of that, you could also do this:

SELECT 
 OrderID,
 SUM(Amount * Price) AS [Grand Total]
FROM tblOrderLine
GROUP BY OrderID

Save that as query qryOrderTotals. Then:

SELECT
 tblOrder.OrderID,
 qryOrderTotal.[Grand Total]
FROM tblOrder
 INNER JOIN qryOrderTotal ON tblOrder.OrderID = qryOrderTotal.OrderID

Like I said, the example is a bit strained; I would NOT do it this way given these tables. It's just to illustrate the general idea of taking an expression out of a query and isolating it in a subquery. This is a useful technique when you need to make a query with lots of subtotals etc.

 

[April15Hater]

I see what you mean. Do you know of a good tutorial that I can read up more on SQL?

Thanks,

Joe

 

[datAdrenaline]

>> Is it better to do make them in Access or should I use ADO? Is there even a way to get the Report to use an ADO query as a Recordsource? <<

I wanted to comment on this ... you can only bind a recordset to a report when your application is an ADP.

Also, since this info is on a report, it seems you may want to consider sub-reports, or utilize a reports' sorting and grouping capabilities.

 

[WayPay]

I see what you mean.

Joy! Even I was hard put to get the point of that .

Do you know of a good tutorial that I can read up more on SQL?

Not really; I learned most of my SQL through osmosis from SQL veterans (guess I'm one myself now ). Fortunately, Google knows zillions of'em.