Create a report with trackability (1 Viewer)

Number11

Member
Local time
Today, 00:52
Joined
Jan 29, 2020
Messages
607
So, I currently have a query that i export into excel as a weekly report but i need to add some reporting history

So need to show how many times each customer outstanding order has been shown on the reports

So currently have
Customer customer
Order No
OrderDate
Number of days outstanding

need to add and find how to generate the following fields

Date the same customers order appeared on the weekly outstanding reports

1st appeared date
2nd appeared date
3rd appeared date
4th appeared date

not sure best working approach :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:52
Joined
May 7, 2009
Messages
19,169
you need to add a table for your tracker with fields like:

customer
Order No

in the attached demo, i used query as source of report (based on table1).
the tracking table is zzzReportTrack.
open the report in Print/Report view.
when you close the report it will ask if you want to Update the Report tracking table (zzzReportTrack).
normally you choose Yes.
 

Attachments

  • reportTrack.zip
    34.6 KB · Views: 80

Minty

AWF VIP
Local time
Today, 00:52
Joined
Jul 26, 2013
Messages
10,355
If this is based on the order date and current date I'm sure you can simply calculate this (air code but hopefully point you in the right direction);
Rich (BB code):
Sub tryout()

    Dim iCurrWeeks  As Integer
    Dim iNoOfWeeks  As Integer
    Dim strAns      As String

    iNoOfWeeks = Int(NoOfDaysOutstanding / 7)    ' = no of weeks it's likely to have appeared?

    iCurrWeeks = iNoOfWeeks

    While iCurrWeeks > 0

        strAns = strAns & "DateNo" & CurrWeeks & " " & DateAdd("w", -CurrWeeks, Date) & vbCrLf
        iCurrWeeks = iCurrWeeks - 1

    Wend

    MsgBox strAns

End Sub
 

Number11

Member
Local time
Today, 00:52
Joined
Jan 29, 2020
Messages
607
you need to add a table for your tracker with fields like:

customer
Order No

in the attached demo, i used query as source of report (based on table1).
the tracking table is zzzReportTrack.
open the report in Print/Report view.
when you close the report it will ask if you want to Update the Report tracking table (zzzReportTrack).
normally you choose Yes.

ok thanks thinking along the lines but i was looking for the report to be exported and on the 1st time enter that date and then a week later if the order is still oustanding add that date and so


Customer Order Date order Number 1st Report date 2nd Report

ect
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:52
Joined
Feb 28, 2001
Messages
27,001
The operational rule here is that Access won't tell you anything that you didn't tell it first, or at least tell it how to tell you. Sounds convoluted? Not really. The idea is that if you need to know "X" then you need to have a place to remember "X." If you want some sort of accountability for something having been seen in a report, you need a place to remember the report was seen AND a way to make the note to that effect.

Which means that if you haven't got one, you need what is commonly called an "Auditing" table. It usually has two fields - a tag and a text field. Then to remember events, you have code in event routines to make an entry. If you want to have a record of a report being opened, you could use the Report_Open event as a place to put some code. If you more specifically wanted to know if a report was actually printed, that can be tricky because Access doesn't really do the printing. It just hands it off to the print spooler. You COULD put an event in a ReportFooter_Print event (even if the footer is no more than a section with a small line in it.) And be aware that if you view the report rather than printing it, _Print events don't fire. You have to be opening it for printing purposes.

 

Users who are viewing this thread

Top Bottom