My favorite way to handle any report where you are looking for items
in one list that don't exist in another is to do it in Access and use
the Unmatched Query Wizard.
The basic steps would be to link the EmpBasic and LaborHed Tables to
Access with ODBC. Create a make table query for each with the fields
you want to include. Run the queries so the tables are created.
Create a query based on EmpBasic to filter out employees you don't
want included. Create a query based on LaborHed and filter by date.
Now you have a list of all employees and a list of everyone who
worked on a specific date. Create a query using the Unmatched Query
wizard and you will get all employees who didn't work on that date.
Now you could create a report based on that query. Create a form
with a date field so the user could enter any date prior to running
the report and change the criteria in the query to look at that
field. Create a macro and button on the form to run the make table
queries (refreshing the data each day) and print the report with 1
click.
HTH,
Brian Stenglein
in one list that don't exist in another is to do it in Access and use
the Unmatched Query Wizard.
The basic steps would be to link the EmpBasic and LaborHed Tables to
Access with ODBC. Create a make table query for each with the fields
you want to include. Run the queries so the tables are created.
Create a query based on EmpBasic to filter out employees you don't
want included. Create a query based on LaborHed and filter by date.
Now you have a list of all employees and a list of everyone who
worked on a specific date. Create a query using the Unmatched Query
wizard and you will get all employees who didn't work on that date.
Now you could create a report based on that query. Create a form
with a date field so the user could enter any date prior to running
the report and change the criteria in the query to look at that
field. Create a macro and button on the form to run the make table
queries (refreshing the data each day) and print the report with 1
click.
HTH,
Brian Stenglein
--- In vantage@yahoogroups.com, "Aaron Hoyt" <aaron.hoyt@v...> wrote:
> Shirley, I have good news for you...LaborHed has Employee Number
called
> EmpID, and LogIn ID called DCDUserID, so the link is possible.
> For the rest that have been trying to figure this out, I took the
challenge
> and this morning solved it.
> The report runs slow so there is some tweaking that could be done
for sure.
> But...
>
> Link EmpBasic to LaborHed using Company to Company and EmpID to
EmployeeNum.
> Then filter on Company, EmpStatus = 'a', and ActiveTrans = no.
> Then group on EmpID.
> Then create an Aggregate field using LaborHedSeq, Max, reset on
EmpID,
> Prepass, accumulate every composite.
> Then create a Calculated field using the formula...
> (LaborHedSeq = (aggreate field from above)) and (PayrollDate <
(TODAY() -
> 1)).
> Put the name and any information you want in the detail line.
> Now conditionally print the detail line based on the calculated
field above
> being true.
> You get anyone who has clocked in in the past and is active, but
did not
> clock in yesterday.
> As a first attempt, I am already aware this could be fooled by
someone
> punching in and out today before you run the report, but I am sure
there are
> others that will tweak this for performance and other issues like
this one.
>
> Good luck,
> Aaron
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf
> Of Shirley Graver
> Sent: Friday, June 24, 2005 5:56 AM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] [6.1] Report Builder - Who never clocked in
> Yesterday
>
>
> I'm basing this on Version 5.0. Here are your options for
generating a
> report like this.
>
> Report Builder:
> You want your labor header to be your Master Table.
And you
> want to link to your employee file with a right outer join. (This
will force
> all employee records to be displayed so you will want to filter your
> employees on "active only" ) In version 5.0 the Labor Header table
only has
> "Employee Number" not Employee ID which you need to connect to Emp
Basic.
> Emp Basic does not have "employee number" and you would have to add
a VB
> form field to enter the "Employee Number" to make the connection.
(pretty
> messy). Assuming you did this and made the connection you would
get a list
> of all "labor header transactions for the day in question. Your
list would
> display all employees, and any associated header transactions.
(two per
> employee, clock in clock out) for those employees with out
transactions you
> would get. "?" Because there was no associated transaction
record. At
> this point you would create a calculated field ( if( field = "?",
true,
> false). You would then go to the appropriate report band and
activate
> conditional printing, use the calculated field to "print if
true". You
> would also filter your Labor Header record for the day you want to
scan.
> The resulting list will display only those employees without a
labor header
> record for the day in question. You cannot link to the
EmployeeMaster
> because of Vantage security.
>
> Crystal (ODBC)
> ODBC bypasses the security on the Employee Master. You
would
> link from Labor Header to EMPMaster. Using the Employee Number in
the Labor
> Header transaction record. You would also use a Right Outer Join
from the
> Labor Header to the EMPMaster (company, EmpNumber) You would use
the
> "suppress line" function based on the same formula used in the
report
> builder version. The limitation of this report is that unless you
have the
> means to distribute the report you'll have to run it through
Crystal.
>
> Shirley Graver
> Rubber Associates Inc
> _____
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf Of
> Juliet Martin
> Sent: Thursday, June 23, 2005 3:08 PM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] [6.1] Report Builder - Who never clocked in
Yesterday
>
> I think yours is the report that I mentioned was in the files.
>
> I will try this and get back to the list. If I get it - I will
post it.
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf
> Of Todd Caughey
> Sent: Thursday, June 23, 2005 2:00 PM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] [6.1] Report Builder - Who never clocked in
> Yesterday
>
>
> I took a stab at this once but don't recall if it worked right.
Here is
> what I did:
>
> 1. Link EmpBasic to LaborHed (LOJ) (filter only on company and
active
> employees)
> 2. Sort on EmpNum then LaborHead Sequence
> 2. Pre-pass aggregate for Max Labor Head Sequence number
> 3. Condition band line to print employees where:
> IIF(LaborHedSeq = EmpMaxLaborSeq and ClockOutTime > 0 ,Yes,No)
> ***this means LAST head sequence was a clock OUT not an IN - if
OUT they
> are not here
>
> This particular report shows who is not now here. I think maybe it
would
> still work if you addtionally filtered <= date and added a date
test in the
> line print condition for max labor head date not that date.
>
> -Todd C.
>
> -----Original Message-----
> From: Juliet Martin [mailto:jmartin@r...]
> Sent: Thursday, June 23, 2005 1:47 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] [6.1] Report Builder - Who never clocked in
Yesterday
>
>
> I am trying to build a report showing who never clocked in at all
yesterday.
> I can't believe how hard I am making this. I am looking for any
clues if
> anyone has tried this. There is a report in the files section for
> Absenteeism but it shows anyone who didn't work their full shift
not who
> never got here at all.
>
> I have tried to do a left outer join between EmpBasic and LaborHed.
> Sorted to only active employees and the payroll date.
>
> My theory being if you are an active employee and you are not in
laborhed
> (for that date) it would fill you in with a ?. No record existed
for you in
> laborhed on that day because you never clocked in. Then I could
filter for
> the ?s. Nope.
>
> Anybody have any ideas? I feel really stupid here but not too
stupid to not
> ask for help.
>
> Thanks in advance,
>
>
> JM
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You
must have
> already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report
Builder and
> Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> <http://groups.yahoo.com/group/vantage/files/>
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/links
>
>
>
> _____
>
> Yahoo! Groups Links
>
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/vantage/
>
>
> * To unsubscribe from this group, send an email to:
> vantage-unsubscribe@yahoogroups.com
> <mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service
> <http://docs.yahoo.com/info/terms/> .
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You
must have
> already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report
Builder and
> Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> <http://groups.yahoo.com/group/vantage/files/>
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/links
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You
must have
> already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report
Builder and
> Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> <http://groups.yahoo.com/group/vantage/files/>
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/links
>
>
>
> _____
>
> Yahoo! Groups Links
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/vantage/
>
> * To unsubscribe from this group, send an email to:
> vantage-unsubscribe@yahoogroups.com
> <mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service
> <http://docs.yahoo.com/info/terms/> .
>
> Tracking #: 1BF4474DFCD91848872AF27BCE7B1797719F1F30
>
>
> [Non-text portions of this message have been removed]
>
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You
must have
> already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report
Builder and
> Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/links
> Yahoo! Groups Links