Select Query For Active Personell During Certain Period

Sep 8, 2007

I would like to make a query which is based on a table that holds information on employees (past and current)

I take the fields:

Name
Entry Date
Exit Date


and now would like to write a query where I can select a start and end date and the outcome should then be the "active" employees during this time.
for example between 1-7-2007 and 31-7-2007

I can not get my head around the criteria as the dates lie in 2 fields.

The exit date field can be empty or have dates that lie outside the range (see above) like the end of a contract at 30-10-2007

Hope someone can give me a tip to get me on my way.

View Replies


ADVERTISEMENT

Queries :: Select Active Record In A Table Using A Query

Nov 30, 2013

Based upon a specific date (varDate), I want to select the record that is active (who drives the lease car).

row 1 user X from 13/11/2013
row 2 user Y from 15/11/2013
row 3 user Z from 17/11/2013

I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.

View 1 Replies View Related

Database Query Using Dynamic Date Period

Mar 31, 2008

Can anyone help?

want to establish the criteria for a report to include 6 months (into the future) from todays date. Have tried dateadd function but this doesn't seem to allow dynamic (automatic) start of period date

Cheers

View 3 Replies View Related

Queries :: Running 12 Months Query By Period Value

Oct 8, 2014

I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.

The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.

View 4 Replies View Related

Queries :: Make Query Expire After Defined Period

Dec 9, 2013

Any way to make the query expired after a defined period but it must remain with that query only not for other queries. Also the query expires should have an option to be alive again or redated for next period after updation means expiry means stop working but not corruption or anything else.

View 5 Replies View Related

General :: List Of Active Links Populated From A Query

Dec 25, 2012

I have a main form (in a single form view) that show a record from a table. I am going to add a sub form (probably a continuous form view) which can show a list of fields (probably a name) from all records from a child table that match condition PrimaryKey = ForeignKey.

What is the best way to make the list of the fields sort of clickable so that I can click on any of them and e.g. open a form with information about a child record? Should I look for hyperlinks in reports, a list box, a continuous sub form with records?

View 3 Replies View Related

Queries :: Selects Active Records For Three Different Selections - Concatenate Query Results

Nov 3, 2014

I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.

I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3

My query is:
SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header
FROM tblSomething
WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));

The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.

I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.

View 11 Replies View Related

Out Of Period

Mar 5, 2007

The 1st of each month I have to do a report that showes the number of cases closed the previous month and I have the following criteria in my query in my date entered field: >=DateSerial(Year(Date()),Month(Date())-1,1) And <DateSerial(Year(Date()),Month(Date()),1). This gives me all cases closed the previous month (for March it would show me all cases closed for Feburary).

I also have to track all the cases from 1-1-06 to the month prior to the previous month mentioned above, January in this case. For March my report needs to show all cases closed from 1-1-06 to 1-31-07. I would like to automate this so that my query knows to look for all cases from 1-1-06 upto and including the the month prior to the previous month of my report.

View 2 Replies View Related

About Period Calculating

Dec 1, 2007

i have query tthat calculate perioe between date. the result is number.
how can i modify so it will show something like 1 year, 2 moths 3 days?

Thanks

View 9 Replies View Related

Queries :: Sum Over Any 7 Day Period

Jun 16, 2015

Query that would flag me if a sum over any 7 day period reached a certain amount.

Setur : I have a query of SAMPLEs. Each sample can have a PERSON associated with it. The samples have an AMOUNT also associated with them.

What I'm trying to do is set a query or flag of some sort if during any seven consecutive days a PERSON has samples associated with them where the summed amount is some value, we'll say 10. I have no problem summing an amount associated with a person over a 7 day period but am having trouble trying to visualize how to do this for any seven day period without having a sum for every 7 day period throughout history.

View 4 Replies View Related

Queries :: Select Query To Gather Results Of Other Select Queries

May 11, 2014

I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.

e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B

I want a select query that returns 1 row showing (6 items):

Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.

I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.

View 3 Replies View Related

Figuring Totals For A 12 Hr Period

Jun 9, 2007

I am working on a database for work. It is a processing plant and once a product is complete it is entered into a table. At the end of each shift the workers will be logging out. What I need to figure out is how to just get a total product processed a 12 hr period. The periods will always be either 6:30 am to 6:30 pm or 6:30 pm to 6:30 am. After it is complete it will be added onto a report.

View 4 Replies View Related

Display Period From Date

May 16, 2005

Hi,

I am looking for guidance on how to get a query field to display a numeric period from an existing field that contains a date.

ie:

01/01/2005 = 1 (1st Month)
01/05/2005 = 5 (5th Month)

Etc.

Thanks

Darrell...

View 2 Replies View Related

Closing DB After Period Of Inactivity

Jan 28, 2005

sooo the people i work with are morons and will leave their desks with the db open and forms open to records. i know unbounded forms would solve this problem, but i also don't like the db being left open for anyone to access if they leave their desks. i want the DB to count down from 10 mins after the last action and then display a message that says

"you have been inactive for 10 mins. click 'Yes' to have the program remain open. click 'No' to exit the program. if you don't click either, you will be automatically logged off in two minutes."

then once this message pops up (maybe a modal form? or maybe a msgbox), a two minute timer begins, and if no button is clicked, the db closes itself.

i know this is possible, i just haven't used timers yet and am unfamiliar with how to use them. thanks

View 1 Replies View Related

How To Store Period Of Time

Jun 10, 2012

How do I enter and store a period of time that may be of any length: years (.. unlikely but ...), days, hours, minutes, seconds (or even micro seconds) that I can later add to other dates or times stored in my database? How do I create a sensible data entry mask for yyyy/ddd/hh:nn:ss.999 and then convert this to a field compatible with date/time functions? And what format should this data take (be converted to ..?) and be stored in?

View 2 Replies View Related

Period Based On Week Number

Aug 23, 2005

Hi,

I've got a combo box that uses
DatePart("ww",[Date],2) AS WkNum
to obtain the week number.

How would I obtain the start date and the end date from just the week number?

Is it also possible to change WkNum to the week number of the selected month?
E.g. if December is selected, instead of week 53, show week 5.

Any help would be much appreciated.

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

Returning Data For A Set 13 Period Sequence

Jul 2, 2005

Hi guys
Well Im stuck!

I have to produce a report (preview first)that acts like a cross tab query. By this is mean that it reports specific data in a 12 period sequence for each year. Therefore I have to set up dates which are NOT normal calendar dates ie one could be 02/05/05 to 29/05/05 then the next would be 30/06/05 to 24/07/05

These dates are issues at the start of a contract year. I have to therefore produce the report for each period that is given to me ( not an easy one is it) I have considered many options but I cant quite get the final effect

Any Ideas ??

gareth

View 4 Replies View Related

Modules & VBA :: Calculate Period Between 2 Times

Feb 8, 2015

I create form to calculate the difference between clock-in time and clock-out time, what is the code to do that

I have 2 fields :

field 1 name clockin
field 2 name clockout

I have a text box to display result equal the period between time1 and time 2...

View 7 Replies View Related

Modules & VBA :: Count Records In Any 40 Day Period?

Jun 4, 2013

I have a table that contains a person ID, a start date for that person, and a GroupType. I want a way to count the number of people who started within ANY 40 day period. Then depending on that number , assign a value to the GroupType field (ie. if the no. of people is 1-15 then groupType = groupA, if its 16-30 then groupType = groupB, if its over 31+ then grouptype = GroupC. This needs to be re-counted every time a new record is added.

As well as having the groupType visible on the form for that person, I also want to be able to run a report showing all people, their start date and group type. Note that when start dates are entered these may be either future dates as well as dates in the past.

If possible, I would also like to track /store when a person's group type changed in another or the same table perhaps.

View 4 Replies View Related

Adding Points To A Table On A Time Period

Aug 2, 2006

Can you help we have a data with a list of jobs that scores points, I need to build a query or scirpt that will add 5 points every 30 day fro the date the job was submitted this will help old jobs come the top of the list.

View 1 Replies View Related

Repeating Date Entries For A Given Time Period

Aug 3, 2005

Hi,

Over the past three months I have been trying to develop an application which can repeat appointment entries.

I would like to have the option to repeat an appointment entry for a given number of weeks.

For example if an appointment entry was placed for today (Wednesday), there would be a command to create additional duplicate enties for the same day of the week for a given period of weeks ie say every Wednesday for the next 52 weeks.

Any help on how I could approach this would be very much appreciated.

Thanks

View 1 Replies View Related

Date/time Period Criteria Confusion

Jan 30, 2008

Hi there

I have created a database in Access XP (2002). In a nutshell, the database records numbers of people attending a seminar; which can take place any number of times per week, and so hence can take place any number of times per month.

I have set up the query so that it can run immediately after a seminar to show the attendants who attended the seminar on that current date "Date()" in the criteria box. However, how would I go about setting it up so that it shows who attended every seminar in the current week or month?

View 1 Replies View Related

Average Number Of Sausages Over Time Period

Apr 8, 2008

We sell products out of vending machines and once in every few days (time span between readings may be different every time) read data from the machines using flash drive. Each reading from all machines produces an Excel file:

Reading_Date | Machine_ID | Product_ID | Amount_sold
2008-04-08 | 1 | 1 | 26

The only date that we have is the day on which the data has been read and this tells how many sausages were sold from the date of last reading till the date of current reading (after each reading counters are set to zero).

The problem is to get average amount of product X sold from machine Y on every calendar day. For a total rookie like me it seems like creating another column that would divide the Amount_sold by the number of days between two consecutive readings and put this average amount for every calendar day between these two readings. May it be done this way? How? Another way? How? I would be very grateful if anyone could help me and explain the solution in an accessible way.

View 4 Replies View Related

Queries :: Calculate Time Period For 24 Hours?

Jul 9, 2014

I have designed an access application that logs emails that come into a department. However, the team leader has asked me to create a report to work out if emails were answered within a 24 hour period.

Is there a method to do this. It is not something I have attempted to do within access before.

View 6 Replies View Related

Queries :: Calculating Time For Two Week Period

Jan 8, 2015

I am using access 2013 and have an issue creating a query with employee hours for a two week period.

For starters I have a table with the following:

Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)

My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.

At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.

In my query (to Start) I need to have the following with it set up per pay period.

Employee Number Rate Code Regular Hours Overtime Hours

How to set it up so that it calculates the hours based per rate code with the given pay period.

View 11 Replies View Related







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