MS ACCESS :: Sorting Query By Date & Employee Name

Help With Query - Employee Heirarchy
Hello,

I have an employee table set up like so:

NAME, SALRY, SUPERVISOR_NAME

This table contains a large group of employees, and every employee has a Name, Salary and Supervisor. Some of these employees are also Supervisors, and hence they are in the table once as an employee but also have their name multipe times under the SUPERVISOR_NAME field. But not all employees are Supervisors. So if "Joe" is a Supervisor a section of the table would look something like:

Alan $12,000 Joe
Sue $14,000 Joe
Rob $19,000 Joe

"Joe" would also have his own record in the table somewhere else and have a Supervisor of his own.

Joe $22,000 Fred
Rex $48,000 Fred
Sam $12,000 Sue

The top-level person in the table has themself listed as their Supervisor. There are a large number of employees who are not Supervisors, and I need to do some data manipulation on these employees (and be able to check whether or not any employee is a Supervisor or not). So these "subordinate-only" employees have a record for their Name, Salary and Supervisor, but nowhere else in the table are they listed as anyone else's Supervisor.

I would like to set up a query that, in English, says: Show me all of the names of the employees who are NOT supervisors. Or (maybe using parameters), is this particular employee a Supervisor?

Any ideas?

Thanks,

Scott

View Replies !

Sorting By Date
I have the data below in a query in Access, and I want it to sort by the Week Begin date.
I have put a sort on for 'Ascending' but it doesn't work. (Due to the 29th being after the 28th - regardless of month)

Is there any way I can force the sort in this query ?
It must be in the same query though, as I could probably do it by using this query in another one then sorting that. However, I don't want to do that for reasons I won't bore you with.

WeekBegin Count
05/09/05 137
12/09/05 90
19/09/05 22
26/09/05 55
29/08/05 122

PS - I should mention that I think it is because the WekBegin date is an expression based on an actual date field. Therefore I believe it's treating it as some sort of text field.

Any help would be appreciated.

Thanks.

J.

View Replies !

Sorting By Date
Hi,

I've just started playing with Access 2002 (Office XP) and I@m having a little bit of trouble.

I'm using the Query Wizard to summarise my database giving me average values for each month. When the query is first run it displays in the correct month order: April, May, June, July, August, September. But viewing the query again or when trying to graph the data in a Form, the sorting forces alphabetical order: April, August, July, June, May, September.

How can I get the query/form to return the results in true month order?

Here's the SQL info which the Wizard created:

SELECT DISTINCTROW Format$([Raw Data].[TimeStamp],'mmmm yyyy') AS [TimeStamp By Month], Avg([Raw Data].[Indoor Temperature]) AS [Avg Of Indoor Temperature], Min([Raw Data].[Indoor Temperature]) AS [Min Of Indoor Temperature], Max([Raw Data].[Indoor Temperature]) AS [Max Of Indoor Temperature]
FROM [Raw Data]
GROUP BY Format$([Raw Data].[TimeStamp],'mmmm yyyy'), Year([Raw Data].[TimeStamp])*12+DatePart('m',[Raw Data].[TimeStamp])-1;

Thanks in advance for your help.

David

View Replies !

Sorting By Date
I have a subform that simulates a listbox and I click on the header label to activate the sort. My problem is sorting by date. Below is the sort function and one of the date labels that I need to sort on.

Sort Function:Code:
==========================================
Private Function SortOrder(col As String, xorder As String) As Integer
Dim strSQL As String
Dim sf As Form
Set sf = Forms!frmMainEntry!fctlNotifications.Form
strSQL = "SELECT DISTINCTROW ProgramID, ProgramDescription, Facility, ResponsibleParty, DueDate, FrequencyOfService, AdvancedNoticeDate "
strSQL = strSQL & "FROM qryProgramList "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
sf.RecordSource = strSQL
sf.Form.Requery
End Function
==========================================

On-Click Date:Code:
==========================================
Private Sub lblDueDate_Click()
Dim response As Integer
If Me.txtSortOrder = "DESC" Then
response = SortOrder(CDate(DueDate), "asc")
Me.txtSortOrder = "asc"
Else
response = SortOrder(CDate(DueDate), "DESC")
Me.txtSortOrder = "DESC"
End If
End Sub
==========================================
I'm not sure if I should convert the date into something that can be sorted or just leave it as a date. I can't get it to work. Help please.

Thanks,
PC

View Replies !

Sorting By Date
help w/sorting two columns one with date checked out, other with date due back. some are overdue and there is not a date entered yet. need to sort by the longest overdue first and leave out those that have already been returned

View Replies !

Sorting By Date And Time
Hello,

I am having some trouble sorting my date and times. I have them both working great individually with RunCommand Sort Ascending after Update. The problem is, the dates will get jumbled up after the time is sorted. Is there a way to connect the two fields where the dates will always stay in order? I have attached a pic of this DB if it helps any. Thanks so much for any help.

Fritz

View Replies !

Sorting A Date Field
i have a date field where about 80% of the data is entered in the format dd/mm/yyyy but the rest may be year only since the exact date is not known. Therefore, it does not have an input mask or a format set. Is it still possible to sort on that field? (As i write this i realise it is probably a dumb question but always worth a shot!)

View Replies !

Sorting By The Most Current Date.
Hey all,

I have what is probably a simple question.
I want to sort my date field in a query by the most recent date.
When I use ascending or descending it looks at the Month/day/year to sort it.
I need it to look at year/day/month.

Any help would be awesome.

View Replies !

XTab Colun Heading Date Sorting
I have a cross tab query for which I want to display the dayes in mmm-yy format. I am grappling with the problem that this is essentially a string, so gets sorted alphanumerically.

Having read around related forums I have tried basing the XTab on a select query, which is sorted by the conventialal date, but also has another field that gives a date using Format([DateAdded],"mmm-yy").

Trouble is, Access only lets me bring across 1 column heading, so I must sort on the column I display, and this doesn't solve the problem.

The coulmn headings are not be fixed (i.e. "Jan";"Feb" etc) as the months are financial year months (April - March).

I can get this too work by using yyyy/mm, but my board of directors are not happy with this format.

Any ideas. I would be open to re-querying my Xtab from another query of from a report if that worked.

Cheers

Mike

View Replies !

Help With Sorting A Query?
I have a job database where I sort jobs by upcoming, inactive, closed and active. Each has a corresponding number 1-4 respectively. The main table I update the jobs in is the JOB TABLE. I print a weekly report for our weekly meeting but I only want the active jobs in the report. My question is: how do I perform a sort function in the query, so I will have only the active jobs in the database table show in the query? Thanks in advance! If more info is needed I wil post additonal.

View Replies !

Sorting In A Query
I'd like to sort in my query - used to populate a combo box - first by name, and then by ID.
Is there a way to force Access to sort in a particular order? It seems like it starts sorting with the first field that's to be sorted, but I want it to start with field 2 and then sort by field 1.

View Replies !

Sorting A Query
I am fairly new at relying heavily on Access for my programming. Problem: I have a telephone database with call detail for every call. I only want specific records, which I am pulling by selecting sort (in query design mode) for that particular field. I need to sort like you would in Excel (Column A first, Column B second, and etc). Can someone help direct me in the right path as to how I need to run a mulitple sort? Your help is much appreciated.

View Replies !

Sorting A Table, Apply The Sorting To A Form
Hi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.

Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..

I'd apreciate some help with this :) Thanks

Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)

View Replies !

Sorting Further From A Duplicates Query
I have a Duplicates Query that extract the following information from my Table

Name Surname DOB Joined

The query then extracts info such as...

Bill Smith 25:12:70 1999
Bill Smith 25:12:70 2005

Most of the time the 'Joined' field has the same year. But I need to find the ones where the year differs but the rest of the information is exactly the same. Because there is 15000 odd records to check, I thought that there would be a quicker way to check.

Could someone please assist me with this query if it is possible?

Thank you in advance.

View Replies !

Sorting Crosstab Query
I have a crosstab query where my column headings are the names of the months based on invoice dates.
what i currently have:
Month: MonthName(Month([AnnualIncome]![InvoiceDate]))
so this spits out April May and June in the column headings.
The problem is that it is sorting it alphabetically, not by the numerical value - so my column headings are coming out as april, june, then may. (I assume once I have july that will come in between april and june.)

Any help would be REALLY appreciated.

Thanks in advance.

View Replies !

Sorting Records In A Query
I am having a problem sorting records in a query. I have about 5, 000 entries in a table and when I build my query based on certain fields, the numbers in certain columns are not in order although I use the sorting feature. ( e.g.) I have 5 columns with numbering data contained in the columns, and I have it set up to sort in ascending order, which it did, but I noticed that the Volume # column sorts the Volume numbers differently (see example below):

V. 1
V. 10
V. 11
V. 12
then it goes back to
V. 2
V. 3
V. 4
V. 5
V. 6
V. 7
V. 8
V. 9

It looks like it sorts anything with a 1 first then the rest later. How do I fix this?

Thanks.

View Replies !

Sorting Query Results
Hi

I have a problem sorting some query data and I wonder if someone could help point me in the right direction.

I have a query with two columns, in the first column is a number (either 6 or 7) and the second is the name of a business. Eg

Column 1 Column 2
6 Pizzas & Co
6 Pizzas & Co
7 Pizzas & Co
6 IPM Ltd
7 IPM Ltd
6 Computer Sales Ltd
6 Computer Sales Ltd
6 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd

And I want to produce some another query so that the output which looks like:

Column 1 Column 2 (6's) Column 3 (7's)
Pizzas & Co 2 1
IPM Ltd 1 1
Computer Sales Ltd 3 5

So for each company name, I am essentially doing a frequency count for all the 6's and 7's.

I think this must be relatively easy to do, but I am getting it wrapped around my head!

Many Thanks,
Lee

View Replies !

Sorting Query & PivotChart
Hey everybody,

I know this question maybe be very lame but its been ages since i've touched on my sql knowledge. I've set out my database and i have 1 table which is holding the info i need to extract so that i may put it into my report. I wish to count the different values in a specific row:

Example:

Whats the problem
Behavioural
Physical
Physical
Behavioural

I wana be able to count these seperately as in:

SELECT COUNT(Client.[Whats the problem] LIKE 'Physical') AS Phy AND COUNT(Client.[Whats the problem] LIKE 'Behavioural') AS Beh
FROM Client;

Result:

Phy Beh
2 2

I havent got a clue if i'm doing it right but i need to get it to work in access 07. Also one more question i have created a Pivotchart in a form but when a create a button on my main startup form and click into the Pivotchart form all i get is the Datasheet view, how can i get it to open up the Pivotchart through the form.

Any info would be greatly appreciated Thank You.

View Replies !

Genaral Month Sorting Query - Help?!
Hi i have a general query and im not sure which section to put it in yet

Basically i need to have a date choosing system which allows me to enter dates once into the form and then print out monthly reports on which of our clients is to be sent a report,

some clients are half yearly, some quarterly, some yearly, at present i have a drop down box for these choices and a drop down box for month

is there a way of combining the half yearly and month ie May to come up with nov so that When i want to print out may's report, Mr X (who has a half yearly report) can have half yearly chosen and May chosen but still get on both the novembr and May print out along with mr Y who only has a yearly report in May

I do not really want to have to change the values in the drop down boxs as i do not think employees would do it and the system would be inaccurate

If a query could be made which somehow combined annual may and half Annual May, AND would also work out that half annual may would become Nov? ie: Annual Nov and Half Annual May

Im confused about the best way to do this so any suggestions would be v much appreciated :)
I dont know if the drop down system is the best way or how i would make a query to make this work and what the best method of making the reports is? any help much appreciated

H

View Replies !

Sorting Of Query In Subform Doesn't Happen
Hi,

I have a query for a subform which is displayed in datasheet mode (see attachment). I've created a form based on a query and used it as a subform.
The "casenumbers" per clients are displayed from 1 and upwards, I would like to have them sorted Descending. So in the query I sorted them that way. When I run the query they are displayed in descending order. When I open the subform based on that query however it is not. I can sort descending anyway by clicking the column on which I want to sort and do it that way, but that becomes bothersome and it doesn't need to be that way.

Record source is ok:
SELECT KlantNAW.ClientNumber, CaseInfoTable.Casenumber, CaseInfoTable.DateInitialCall, CaseInfoTable.DateCaseCreation, CaseInfoTable.CaseAccepted, CaseInfoTable.[1stAppointmentDate], CaseInfoTable.DateCaseClosed, CaseInfoTable.About1 FROM KlantNAW INNER JOIN CaseInfoTable ON KlantNAW.ClientNumber=CaseInfoTable.ClientNumber;

Order by:
Casenumber DESC

I thought, well, lets create a new form based on the query to see if that changes anything, maybe I've changed something I shouldn't have.

But nope, same result.

How can make the form sort descending in datasheet mode?

View Replies !

Stopping A Crosstab Query Sorting Fields Alphabetically
HI all,
In my database i have a crosstab query that has a text field as a row heading and a number field as a column heading. The query is sorting the row heading on the left hand side alphabetically. I dont want it to do this, i want it to show the fields as they are in the table.

Is there anyway of stopping the query automatically sorting alphabetically?

Thanks in advance

View Replies !

Access Employee DB
Is anybody out there kind enough to post a good head start sample db for managing employees with photo included in form and in report.

Would appreciate any help and thanks,
cagay

View Replies !

Employee Job Administrator
Hi There
I've Been Asked For A Software That Manages Employee Where The Software Has To Assign Shifts For Employees Without Time Conflicts Or Duplicate Job For The Same Employee in One Shift

Hope I Described My Problem Clearly

Any Ideas Or Anyone Has Done This Previoulsy

Thank In Advance

View Replies !

Employee Record
Hi all, I am looking for an employee record template for data including sickness, vacation, skills, CVs etc. I would have thought there would be loads available but alas, I can find none

Can anybody point me in the right direction please.

Thanks in advance
Rob

View Replies !

Employee Compensation
Trying to setup a database to track employee compensation and need some advise on the setup. I was told in a previous post to normalize my tables which I think I've done. I would just like some other input on this to know if I've done it right.

I created a table called Employees with the following fields: autonumber id field, firstname, lastname, department, position, hiredate.

Next I am creating a new table for each year which will contain the following tables: autonumber id, base, bonus, addbonus, overtimecomm, car, 401kcon.

The autonumber ID fields in each table have been set as the primary keys and I've setup a relationship between Employees and each of the compensation tables.

Does this sound like it will work? Or do I need to go back and rethink some of the process?

View Replies !

Employee Quit?
I have a form which has a drop down box which links to a table with Employee names. One employee quit and I want to delete her name from the table. But... if I do that, I will delete her entirely from all previous records. Is there a way to keep all previous records, but take her out of the drop down choices?

View Replies !

Employee Payroll
I'm trying to use a query to calculate the payroll for all the employees but it won't work. The two tables I have are 'Employees' and 'Employee Payroll'. In the 'Employee Payroll' table there are three fields- Employee ID, Days worked and Earnings. I need to calculate the sum of the earnings field but it just displays a blank list of records when I try it.
Can anyone help?
Thanks

View Replies !

Employee Database
I'm trying to create a database in Access to track various employee data for the company I work for. We have about 44 employees. There are about 7 different groups of data our poor secretary now has to keep up with. These include: a list of the employees, with name, address, phone, etc. A handmade form with salary information like current wage, raises and when they were given, etc. A bonus section, how much and when. A list with attendance information including tracking missed days and tardies. A list of temporary employees and when their incremental reviews are due. There are other lists we use and others we are thinking of starting having to do with training, etc. It’s too much to keep up with manually now.
One of my questions is: can there be one table with the basic employee information that other tables can access without having to enter the same names again into other tables? (Why enter all those names more than once)? My other question has to do with linking tables together, I just can’t seem to grasp the concept even after reading two Access training books on the subject. I made a simpler database for another project and gave up on linking tables and the database seems to work just fine.
Any help is appreciated and sorry for the long post.

View Replies !

Add Employee Form
I have an Employees table. I have a form to Add Employee and a form to Remove Employee. Remove Employee works great. It's a drop down box, they select the employee and click on remove. With Add Employee, I have a textbox where they enter the name and a button to add that employee to the database. I setup a VBA script to take the value in the textbox labled "empname" and insert it into the Employees table when the button is clicked through the onClick event.

Is there an easy way to do this? I need the UI to be simple. I know you can bound the textbox to the employee name, but then when they open up the form it will show all the records and they have to click the new record button to add someone. I know this question was asked very recently, but it wasn't answered very well.

I have the db attached. It's in Access 2003 format. The macro in there is the VBA script that I wrote, so don't worry, it's not harmful. I'm very good with SQL, and I would've chosen to use ASP for the user interface, but the people that need this don't want to run any servers (IIS). They just need a file they can double click on and work from there.

Thanks

View Replies !

Query Date From DateTime - General Date Format Field.
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).

I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.

Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.

How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?

View Replies !