Automatically Add Entry For Associate If No Entries For That Month Exist?

Sep 28, 2015

I have a database that is tracking attendance for several 100 employees. The Db is mainly used to log any policy occurrences (no call, no show). If an associate doesn't have any occurrences for a month, then they get a point credit. Right now, I set it up so the credit can be manually added. the problem is a supervisor (the user) may not know if their associate should receive this credit unless they review their attendance report. Opening the form to add a policy occurrence, then running a report to only re-open the same attendance form is inefficient. What I'd like to have is a way to have a credit automatically added if they did not receive any points for a particular month.

ps. My office does not allow uploading any data/files so I am not able to upload a copy/sample.

View Replies


ADVERTISEMENT

Queries :: Return 0 Where Data Does Not Exist For A Particular Month

Mar 13, 2015

I'm trying to make a query to return payroll data, but I'm not sure how to get Access to return 0 where data doesn't exist for a particular month.

For example, imagine this table:

Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160

How would I get this ouput:

Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160
Matt 2 0

View 10 Replies View Related

Showing Only One Entry If More Than One Exist

Mar 1, 2007

Hi all,
I have a form that is based on a query that uses a yes/no option as the filter. the problem i am having is that in this form if a user has more than 1 no entry the users name shows up as many times as entries that they have. i would like this to be shortened down to only one time for all no entries. is this done within the query?
SELECT Employee.EmployeeLastName, Employee.EmployeeFirstName, Employee.EmployeeID
FROM Employee INNER JOIN Expense ON Employee.EmployeeID = Expense.EmployeeID
WHERE (((Expense.AdminApproval)=No));


OR could this be done better in the on load of the form using a counting loop?

View 2 Replies View Related

Count Entries Within A 6 Month Period

Feb 23, 2008

Hello all,
I need your help please, i have a Form that also has a Subform in it. I need to create a Text box on the form that will display a number which will reflect the total enties made against a particular ID within a rolling six month period using the system date as a perameter. The number shown will only reflect previous entries made that fall within the six months from the current system date, and will need to update once new entry is made. Incase i have confussed the hell out of you, let me give you an example:

Example One

Entry made today = 24/02/08
Previous entry Date = 20/10/07

Textbox on Form = 2

Example Two

Entry made today = 24/02/08
Previous entry Date = 20/08/07

Textbox on Form = 1

View 4 Replies View Related

Is It Possible To Automatically Fill In Fields With Previous Entries?

Jun 15, 2007

Let's say in one table (called Employees) I have two columns, Name (the Primary Key) and Job (Both are text fields). One row contains "Jack" and "Gardener" in each column respectively. If I were to have a form for a different table where I select Jack's name from a drop down menu of all the "Names" in "Employees" and it then automatically fill in the next field with his Job (which would be "Gardener").

I really hope I'm making sense... Is this possible?

View 14 Replies View Related

Forms :: Automatically Clear Fields Between Entries?

Dec 23, 2013

I have made a form with a subform to search for existing records based on inputting a postcode then selecting the relevant address from the subform which then fills the form with details of that chosen record ready to issue a further package linked to that original record.

To prevent errors and confusion by the users I coded the following:

Private Sub CmdFindAddress_Click()
If IsNull(Me.TxtPostcode) Then
MsgBox "You need a Postcode to use this button. Please type one in."
Me.TxtPostcode.SetFocus

[Code] ....

'Where the postcode entered does not exist in records or if there is a mistake typing in the postcode, below will remove residual record details from previous postcode ready for a new postcode search:

For Each ctlCurr In Me.Controls
If ctlCurr.Tag = "clear" Then
ctlCurr = Null
End If
Next ctlCurr
Me.Refresh
End Sub
.........

The problem is that if I test the system by inputting a postcode where I know there are no records, the CtlCurr is highlighted and the error message is:

Compile error: variable not defined
..........

This does not happen if I have already selected a postcode so the record fields are filled and then change the postcode - so the record fields are cleared.

Previously, entering a postcode which did not match existing records generated the "there are no records...." error message instead. How do I define CtlCurr?

View 9 Replies View Related

Show Latest Entry Of Multiple Entries

May 25, 2005

I am trying to customize one of my query table, so that it shows the latest review date of an employee (with multiple entries). I have gotten the SQL statement to work so that it shows the latest employee review date which is greater than the current date. But if the employee has 2 records after the current date and I want it to show the latest entry of the 2. I don't know to put add a sub-Select statement or whether to add another criteria in the Where criteria.





e.g. Current Date = May 25, 2005If Employee A's next review date is set for June 1, 2005 but has already been reviewed (the next review date is May 31, 2006). The query will show the June 1, 2005 and not the latest entry of May 31, 2006.


Because there are 2 review dates that are after the current date. What should I do to make only the latest entry appear?


Below is the my VBA code so far, which produces the above result.



SELECT tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.[current store], tblEmp.position, tblEmpSalaryHistory.salary, tblEmpSalaryHistory.next_review_date
FROM (tblEmp INNER JOIN tblEmpSalaryHistory ON tblEmp.ssn=tblEmpSalaryHistory.ssn) LEFT JOIN tblEmpWorkHistory ON tblEmp.ssn=tblEmpWorkHistory.ssn
WHERE (((tblEmpSalaryHistory.next_review_date)>Now() And (tblEmpSalaryHistory.next_review_date)<=[Please enter the Next Review Date]))
ORDER BY tblEmpSalaryHistory.next_review_date;

My brain is stuck and can't figure it out. I hope any fresh mind could help me out. Thanks in advance.

View 5 Replies View Related

Form Entries Leaving Entry Boxes Blank

Sep 6, 2006

I have a form that allows users to search the database for records based on there criteria. The form allows the user to search, through combo boxes, by variables. The first is MAT, the second is Relocation Area. The search works fine when the user selects options form the combo boxes. However sometimes it is neccesary to leave the location field blank and only search by MAT. When the location combo box is left blank no records are found. Is there anyway around this. The SQL code for the query is below:

SELECT [Extract Data].[ID], [Extract Data].AREA, [Extract Data].[MAT], [Extract Data].[Relocation Area 1], [Extract Data].[Relocation Area 2], [Extract Data].[Relocation Area 3], [Extract Data].[Grade Score]
FROM [Extract Data]
WHERE ((([Extract Data].[MAT])>forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 1])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 2])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 3])=Forms![Data Search]!SearchArea));


Any help would be much appreciated
Nick

View 3 Replies View Related

Require Field Entry To Be One Of Entries In A Table? Validation?)

Oct 29, 2004

I am using Access 2002 database with ASP.NET(VB). I usually try to get something working in the access 2002 GUI 1st and I am having trouble with creating the code to require that a field entry be forced to be another exisiting entry in another table. For Example:

UNIT_TABLE has fields UNIT_ID(key), and UNIT_NAME having values of:
1 Tigers
2 Bears
3 Sparrows

BATTLES_TABLE has fields BATTLES_ID(key), ATTACKER_MAIN, and DEFENDER_MAIN


I want the ATTACKER_MAIN and DEFENDER_MAIN choices to ONLY be members from UNIT_TABLE.UNIT_NAME

I set up a combobox and lookup in BATTLES_TABLE as follows:
SELECT UNIT_TABLE.UNIT_NAME FROM UNIT_TABLE ORDER BY UNIT_TABLE.UNIT_NAME;

this OFFERS the CHOICE of any of the 3 clans (ie Tigers, Bears, Sparrows)

but I cannot not FORCE the USE of these....because the end user can option to enter in anything else.(which is not wanted).

How do I add SQL to the LOOKUP such that... the user can ONLY choose to enter text that EXACTLY matches an Already existing entry in the UNIT_NAME field?

I tried to enter a validation rule:
[ATTACKER_MAIN] In ([UNIT_TABLE.UNIT_NAME])

I get an error that the field 'UNIT_TABLE.UNIT_NAME' does not exist.

Thanks for any help with this validation problem.
HKP_MACK

View 3 Replies View Related

General :: Setting Default Value For New Entries As Previous Entry

Jan 17, 2013

Is there a method of setting the default value for new entries to the same value as the previous entry?

For example, I have a quality control information table that stores QC data for different lot numbers. It is annoying to enter in the lot number and product code field over and over again if there are dozens of records per lot number. Is there a way for Access to easily display the previous entry as the default value?

View 1 Replies View Related

Creating Combobox With Unique Month And Year Entries From A Date Field In A Table

Jul 18, 2005

Hi,

Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?

I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...

Any help would be much appreciated!

View 2 Replies View Related

Queries :: Multiple Entries For Same Date - Merge Data Into One Entry

Oct 24, 2014

Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.

Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:

For APR:

4/1/14 Dep A 12 2
4/1/14 Dep B 10 2

However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?

View 1 Replies View Related

General :: Automatically Send Emails At The Beginning Of Every Month

Oct 25, 2013

I would like to send an email from Gmail server automatically at the end of every month. I'm currently sending email on button click. But is it possible to do this without user intervention.

View 2 Replies View Related

Automatic Entry Of Transaction At End Of Month

Jun 5, 2005

I am trying to create an Access application where income recognition is automatically calculated and stored at the end of the month based on established parameters. How can this be done? Does it require VBA programming?

View 1 Replies View Related

Only Allow Date Entry For Current Month

Jan 8, 2006

Hello,

I am a little familiar with Access but having a problem figuring out how to alert a user that the date entered on a form is not the current month. I was hoping to have a warning msgbox pop up alerting the user. I am trying to prevent incorrect date entry.

Thank you very much

View 4 Replies View Related

Automatic Date Entry On First Day Of Each Month

Feb 27, 2012

How to design a Query that will add a specific date to a Table every month?

In my case, I would like to add / append new record entries on the first day of each month - for example.

Do I have to start with a table that has ALL the future dates required, or is there any other solution?

View 3 Replies View Related

Automatically Create A Table Entry

May 10, 2007

In my DB I'm tracking seed. I have a form that I enter in some info in and an ID number is created (based on that info). Then in a different form I track "events" that happen to that ID (drying, moved, bagged). Is it possible to have a button or something like that that after my ID is created from my frmHarvest to automatically generate an event in tblEvents that has the date the ID number and then have something like Harvested in the description field?

If possible can I get some hints on what to do?

Thanks,
Rick

View 7 Replies View Related

Queries :: Creating Field Names In A Query That Will Automatically Update Each Month

May 10, 2013

I am hoping to create a field name in a query that will change every month. Right now the filed name is qryTechQuintileMonth-7.am_quintile. this designates that the data is for October 2012. There are 6 other fields named similarly for Nov 2012 through April 2013.

Is there a way to name these fields with the proper month-year (mmm-yy) so they automatically update each month?

View 4 Replies View Related

Query For A Month Range Based On Combobox Entry

Aug 2, 2005

Hi,

I currently have a form that creates a report based on a query that takes in a start date and an end date.
What I would like to do is to create comboboxes that will let the user choose a month and year for the report.
Is there a simple way to choose an entire month in access? I've tried using the Month() function to no avail.

The SQL statement for where I select the start and end date is below:

WHERE ((([tblData].[EntryDate])
Between [Forms]![frmByRange]![BeginningDate] And [Forms]![frmByRange]![EndingDate])

Any help would be much appreciated!

View 2 Replies View Related

Tables :: Create A Table With Entry For Month /day - No Year?

Jul 8, 2013

I'm trying to create a table with an entry for month/day, no year. I want to create a report that can will sort the birthdays based on month in ascending order.

I'm currently using the text field to put in, "10/30" or "1/3" but when I try to order the dates, instead of "1,2,3,4,5,6..." I get "1, 10, 11, 12, 2, 3.." etc.

View 11 Replies View Related

General :: Automatically Update Field Based On Entry In Another Table

Jan 14, 2013

I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.

When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.

View 1 Replies View Related

Forms :: Select From Combo Box During Data Entry And Automatically Update Field

Mar 13, 2013

I have one field AccountName in customer table and another field AccountID.

In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.

View 2 Replies View Related

Tables :: Associate Items With Second Names

Apr 15, 2015

I have items, but some of them have multiple names for one item. Is there any way to tell Ms access that that item has those other names? I have 5 fields, named "Model" "2nd Model Names" "3rd Model Names" "4th Model Names" and "5th Model Names."

View 12 Replies View Related

How To Associate The Request With Existing Form

Jun 25, 2014

how to associate the request with column and rows with an existing form?

View 14 Replies View Related

Forms :: Subform Does Not Associate With Main Form

Mar 15, 2013

I have this form that contains subforms to hold different types of infomation for different clients. At the top of the form it has the case #, first name, last name, and then a tab element in which each of 6 pages (tabs) holds subforms for other information associated with the client.

I have a table that holds only client number and name.Then each of the six subforms operate off of a table, and all the tables are connected through relationships using client number, first name, and last name.

How could I make the subforms autofill their cooresponding tables using the case #, and name fields from the main form without having to repeatedly input the client #, names for each tab/subform?

View 4 Replies View Related

Forms :: Assigning Multiple Entries In One Table To A Single Entry In Another Table

Jul 24, 2013

I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.

The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.

Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).

How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved