Daily/weekly/quarterly Reports

[vbaInet]

See the attached.

 

[umair434]

thanks vbaInet. I ended up working on it last week and here is what I did:

I put =datepart("getinterval(), [dte], 7, 3) as the expression in the query. Then using Report wizard, I grouped on it and it worked

just 1 offtopic question, If I have two tables, with the same fields (not the same order). how can I join them? i have 1 table for employees 1-4, and another table for employees 5-9, some of the fields are common and some are not.

what would be the appropriate way to make 1 table which displays all the results? other than manually

thank you again!

 

[vbaInet]

You would use a UNION query. Look into that.

But why did you put them in separate tables if they have some common fields?

 

[umair434]

some of the fields are common - i wanted a separate table for each subform, and this seemed to work then :/

 

[vbaInet]

some of the fields are common - i wanted a separate table for each subform, and this seemed to work then :/

Yep, that was why I said "some fields". Sounds like you haven't properly normalized then. If they relate to the same entity but with slightly differing properties, then they can go into one table. Plus a subform should not determine your tables. Your table determines the subform.

 

[umair434]

yea I didn't know anything about normalization and I guess I am learning the hard way hopefully, I will make proper tables in my next database application though.

anyways, so the reporting code works fine. Weekly/quarterly/year to date data works as well.

two things though: how do I get the daily data interms of DATE - I know I can get "day of the year", but can I somehow get date to appear in the report?

If I can get the date to show, then how should I select "starting data" from the main form. right now I have a text box, which I use for entering week numbers, quarter numbers or year and it gives me the filtered results - but I adding date to the text box doesn't result anything.


i am using forms!main!txt as the criteria for the =datepart(getinterval(), [dte], 7, 3) column in the query.



in other words, this is what I am lookign for:

if case "weekly", then ask for the week number - but if case "quarters", then show all all quarters. If case "daily" then look for the date you want to see data for



thanks!!

 

[vbaInet]

two things though: how do I get the daily data interms of DATE - I know I can get "day of the year", but can I somehow get date to appear in the report?

I don't quite understand what you're getting at. Your [dte] field exists in the report's Record Source. You can use it.

in other words, this is what I am lookign for:

if case "weekly", then ask for the week number - but if case "quarters", then show all all quarters. If case "daily" then look for the date you want to see data for

* Create another textbox on your form and change its Enable property based on the selection. So Weekly and Daily will enable it and Quarterly will disable it. Call this textbox txtCriteria
* Use something like this:

Dim strCriteria As String

Select Case Nz(Me.ComboBoxName, "")
    Case "Daily"
        SetInterval "d"
        If IsNumber(Me.txtCriteria) Then
            strCriteria = "[dte] = #" & Me.txtCriteria & "#"
        End If
    Case "Weekly"
        SetInterval "ww"
        If IsDate(Me.txtCriteria) Then
             strCriteria = "DatePart(...) = " & Me.txtCriteria
        End If
    Case "Quaterly"
        SetInterval "q"
End Select

DoCmd.OpenReport "ReportName", acPreview, strCriteria

 

[umair434]

thanks again I did a similar thing, but added "date()" part in it to get the current week/day/year.

 

[vbaInet]

Good job!