Queries :: Multiple Sorts In A Query
Nov 5, 2013
I am looking to modify this query to only show my records that have a "no" answer. I have tried to sort each column by using the Like "no", but that only sorts the first question. Is there a way to get all NOs from all 68 questions in one query to work with trending?The goal is to be able to show what questions are trending with an answer of NO, in Desending order by question. So then I would in theory have a report that shows trending questions with the answer "no" in order of greatest to smallest.
View Replies
ADVERTISEMENT
Apr 24, 2015
I have a union query of the Employees table and Dependents table on [Employee ID] which I build for a census report, and when I run the query, it sorts correctly, but it's not sorted at all on the report. I tried to add a sort on the group, but it just ignores it.
I need it grouped by [Employee ID], then sorted by [Last Name], then [Relationship], then [Age], so it will be alphabetical by the employees last name, then put the employee first, then the dependents by oldest to youngest. Is there a way to do this in the code, and where would I put that?
View 14 Replies
View Related
Feb 21, 2005
i receive a date and hour in the past
and i've got to make an form who displays the next things:
total amount of days from received day till today (day may only update after the received hour)
total amount of months from received day till today (month may only go 1 up after the received day/hour)
total amount of years from received day till today (year may only go 1 up after the received day/hour)
(the DateDiff function is good but isn't 100% correct)
and at the end of the form i got to give an conclusion like:
XX YEARS and XX months and XX days (not the totals but exp: --> 2 years 3 months 5 days 7 hours 3 minits)
i hope to find out the time myself, if the other are correct
anybody did anything like this before?
can anybody help me out?
thx Killroy
View 4 Replies
View Related
Nov 2, 2014
I have a continuous form that is used as a sub form, and one of the fields is a check box which ideally I would like to set not visible; however, I would like the font colour of the other fields to turn red if this check box has a value of false.
View 9 Replies
View Related
May 24, 2014
I am creating a Machinery database to match parts up with equipment. So they can see what the Equipment is and all the available parts. The sort needs to be like this
First Sort:
If the manufacturer of the machine is the same as the manufacturer of the part sort that at top then sort alphabetical
Is this possible? Each equipment would have a different manufacturer so I can not do some type of hard coding.
View 1 Replies
View Related
Jul 18, 2013
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
View 1 Replies
View Related
Oct 19, 2014
I have two true/false check boxes on a form, one for Paid and one for pickedup.
On the query builder I have these two fields listed, and I want the records to show on the form if either box is not checked(false). So in the Query builder, I have False for the paid and false for the pickedup fields in the criteria portion.
However upon running the query, if I click on ethier box, the form will update and will not show the record. I have to have both fields True to update.
In other words it's acting like a "or" instead of a And.
SELECT SO.SOID, Players.FName, Players.LName, SO.Completed, SO.Priority, SO.SODate, Players.PLAYERID, Players.Phone, SD.Service, SO.Paid, SO.Picked_Up, Frames.Frame, SD.Amt_Due, SD.Paid, SD.Exteneded_Price
FROM ((Players INNER JOIN SO ON Players.PLAYERID = SO.PlayerID) INNER JOIN SD ON SO.SOID = SD.SOID) INNER JOIN Frames ON SD.FrameID = Frames.FRAMEID
WHERE (((SO.Completed)=True) AND ((SO.Paid)=False) AND ((SO.Picked_Up)=False));
View 1 Replies
View Related
Apr 20, 2014
I have create few option buttons in a form and assigned values. when each option button selected it will store a value in tbl.attend.log
Exp - Opt 1 button = 1 = OnDuty
Opt 2 Button = 2 = DutyTravel
Opt 3 Button = 3 = SeekLeave
[code]....
View 2 Replies
View Related
Jan 22, 2015
I have made a database to show me dates that I need to check various documentation from my contractors.
Once a month I want to print out a query/report to tell me what checks need to be made in the following month.
The dates are
Licence Check
Licence Expiry
Van Ins Exp
GIT Expiry
MOT Expiry
Passport Check
Vis Expiry
I need a query table that shows a list of names that have anything to be checked in the month.
So the Column headers would be Name, Surname, Licence Check, Licence Expiry, Van Ins Exp, GIT Expiry, MOT Expiry, Passport Check, Vis Expiry.
Its easy to do a list with one date but when I add multiple dates into the query it looks for names and surnames with the date within the next 30 days for every date and therefor brings back no records.
I have attached the picture. Obviously not all the records will show dates. Some will be blank.
View 14 Replies
View Related
Jun 8, 2015
I want to run a query that returns a "small" or "medium" or "large" etc on each respective row against another columns respective numeric value...
< 500 = "small"
500 up to 1000 = "medium"
1001 up to 10000 = "large"
10001 up to 100000 = "very large".
View 8 Replies
View Related
Jan 30, 2015
I have a query that I just can't figure out. We are a facility that works on rail cars. We have over 200 cars in house at the moment and the database has a daily production report in it with the status of all of the cars.
I need a query that will pull out the "AAAA" and "XXXX" cars from the list only IF the cleaning field or the mechanical inspection or BOTH fields are blank.
View 6 Replies
View Related
Jun 20, 2013
I have an access database which is going to present a front end webpage form. The form has several drop down lists and I need one of those drop down list to display only certain results dependent on what the previous drop down list has selected e.g. if Box A is selected as Aces then I need the next drop down box to only display A building
1-A building
2 A building
3 and not the entire list.
How can i get this to work?
View 2 Replies
View Related
Sep 16, 2013
I want to create a query with multiple conditions. Basically if the person Passes any of this trainings they need to show up in my query..how do you do it?
SELECT tblMasterUsers.userid, tblMasterUsers.Licenses, tblMasterUsers.firstname, tblMasterUsers.lastname, tblMasterUsers.email, tblMasterUsers.npn, tblMasterUsers.Region, tblMasterUsers.ABSID, CMPreport2014.[Ahip status], CMPreport2014.[LP Status] AS [AZ Product Training], CMPreport2014.[LP Status1] AS [CA Product Training], CMPreport2014.[LP Status2] AS [OR WA Product Training], CMPreport2014.[LP Status3] AS [Fraud Waster Abuse],
[code]....
View 1 Replies
View Related
Jun 10, 2013
I have a table where and account could have multiple rows with different data like Applied_Date and Trans_Code, and AMOUNT. The AMOUNT in two of the rows will be a positive and a negative and will be zero each other out. I am trying to create a query that will only return the the rows that do not offset each other. Here is an example of my table:
ACCOUNT CODE APPLIED_DATE AMOUNT
292020 M 5/11/2012 ($33.95)
292020 11 5/14/2012 $33.95
292020 A 5/30/2012 ($33.95)
View 3 Replies
View Related
Jul 11, 2013
I have a DB where you there's 5 tables all linked together by one project ID
tables below
Project , Staff, Asset, allowances, travel, mark up
What I can do is create a new project, then add records to each of the other tables on what different items I require,
i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)
Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.
At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.
View 7 Replies
View Related
Feb 27, 2006
Hi All,
I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.
The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:
QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -
So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.
Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.
The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.
Thanks,
Evan
View 2 Replies
View Related
Jul 10, 2006
Can anyone help me with how I can accomplish this?
Here is what I currently have:
10 SQL Pass-through queries to update different Date field (Date1, Date2, Date3, etc.) based on the value of the next date field.
This is how each individual query is set up (there are 10 in total)
UPDATE tblApplications SET tblApplications.LastContact = [date1]
WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
Here is what I would like to have: (as 1 query)
UPDATE tblApplications SET tblApplications.LastContact = [date1]
WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
UPDATE tblApplications SET tblApplications.LastContact = [date2]
WHERE (((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
UPDATE tblApplications SET tblApplications.LastContact = [date3]
WHERE (((tblApplications.[Date Completed]) Is Null) AND ((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Not Null) AND ((tblApplications.Date4) Is Null));
Can I somehow combine them together like this or do I have to keep them as 10 seperate queries?
View 5 Replies
View Related
Aug 29, 2013
I have 3 tables of data, where I want to cross reference information to produce exception reports.
The tables all have Employee number held within them, and these form the basis of my queries.
We want to be able to cross reference locally held staffing information with centrally held HR and payroll records.
The type of information we want to be able to check, is if the hours of the individual are the same in all 3 records, the grade they are being paid for, the cost centre, and that the names match.
Currently we run separate queries for all of these, and I want to be able to consolidate the exceptions in one report, to save looking at the same persons record more than once.
For example, if John Smith has a different name, grade and cost centre are all different - we may be accessing his record 6 times, once for each variance against payroll and HR records.
Is there a way that I can pull all this information together to speed up the reconciliation process?
View 1 Replies
View Related
Mar 4, 2015
i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.
View 2 Replies
View Related
Sep 11, 2013
I'm doing a database for a friend and the challenge I have is there will be a lot of attachments going into this database, which will hit the 2gb file size limit in no time.
My solution is have each year of attachment in a separate databases and link them to the main database. The attachment are tax documents.
They table layout is all the same. It will be like this:
TaxYear, ClientID, 1040, 1040NR, 1040X, etc.....
TaxYear will be whatever year the database is designated and ClientID is the client for that particular year. The same client can shows up in multiple years.
I have thought of inserting each table in a form, but when a new year come, the form will need to be adjusted. And it will has empty field if a client skip a year or two.
View 6 Replies
View Related
Apr 6, 2015
Student ID
Command over subject
Teaching Subject
Explaining things
Pedagogy Methods
Solicited_Participation_Class
1
Good
Average
Bad
Good
Bad
2
Bad
Average
Good
Bad
Good
3
[code]......
I have a table given above, table name is student. Which has following sample data.
I want to count the no. of students who say Good, Average ,Bad for every indicator Output from query:
Good
Average
Bad
Command over subject
2
3
1
Teaching Subject
1
5
0
[code]......
How this can be achived from query in MS Access 2010
View 4 Replies
View Related
Mar 4, 2015
I have a list of dates and I want 1 query which counts the dates between numerous criteria. for example, colum 1; dates between 1/1/14 and 16/02/2014. Colum2 between 17/02/2014 and 15/04/2014.
I have attached a screenshot.
View 4 Replies
View Related
Apr 23, 2013
I've been asked to get involved in some access development but don't seem to be get my dlookup syntax correct.I essentially want to lookup what salary band different employees are in.I have two tables:
Employee Table) has the fields: Name, Type, Salary
Salary Band Table) has the fields Employee Type, Salary Band, Lower range, Upper range..my query syntax is:
Code:
Band: DLookUp("[Salary Band]","Salary Band Table", [Salary] & " BETWEEN [Lower range] and [Upper range]" & "AND " & [Type] & " = [Employee Type]")
I can get the first criteria to work but can't get the second part to work - currently it produces an error.
View 1 Replies
View Related
Apr 18, 2013
Is it at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.
I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.
I have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.
View 7 Replies
View Related
Jun 21, 2014
modify the code below to Show the LocationName in the Schema Column instead of the MPID? I attached a pic showing the relationship between the two tables which contain the data I'm trying to query.
SELECT [Locations Query].LocID, Qry_MPLoc.MPID AS Qry_MPLoc_MPID, [Locations Query].Location, [Locations Query].Schema
FROM [Locations Query] INNER JOIN Qry_MPLoc ON [Locations Query].[LocID] = Qry_MPLoc.[LocID];
View 11 Replies
View Related
Mar 29, 2014
I am working with a database which contains dates and a measurement value with data within the following fields:-
Record
SurveyID
DateTimez
mX
mY
mZ
Here is an snapshot of my main table "SurveyData"
Record|SurveyID|DateTimez|mX|mY|mZ
7194|A2|16-Mar-14|12797.4815|25781.8672|81.1848
7195|A2|17-Mar-14|12797.4863|25781.8672|81.1867
7196|A2|18-Mar-14|12797.4907|25781.8698|81.1875
7197|A2|19-Mar-14|12797.4844|25781.8642|81.1846
7198|A2|18-Mar-14|12797.4883|25781.8646|81.186
7199|G23|21-Mar-14|12419.8779|24822.6836|132.6177
7200|G23|12-Mar-14|12419.8928|24822.6812|132.6075
7201|G23|13-Mar-14|12419.9211|24822.6829|132.5915
7202|G23|14-Mar-14|12419.9392|24822.6837|132.5795
7203|G23|15-Mar-14|12419.9581|24822.684|132.5662
Record is the primary key which is a auto increment integer. The Survey ID is a text field that contains the name of the survey sensor, DateTimez is, we the Date and the time of survey and mX is the X coordinate. There is also mY and mZ, but once I can do the mE then its just a matter of duplication.
Now, I am trying to develop a query that will add some additional calculated fields to calculate the difference between the mX coordinates from the previous pickup, which is called 'dX'. The code will be duplicated to calculate the dY and dZ. I have managed to solve this using a Dlookup statement as follows:-
Code:
dX: [mX]-(DLookUp("[mX]","SurveyData","[Record]=" & [Record]-1))
This works fine, however I am struggling to make it work so it will only take the difference for each SurveyID. For example, the calculation does not calculate the difference between the X-coordinates between the Sensors A2 and G23,as they are not spatialy related. The code I am trying to use is shown below:-
Code:
dX: [mX]-(DLookUp("[mX]","SurveyData","[Record]=" & [Record]-1 & "AND [SurveyID]=" & (DLookUp("[SurveyID]","SurveyData","[Record]=" & [Record]-1))"))
I think I have got myself totally confused, but what I am trying to achieve is calculating the difference between the x coordinates from a previous record but only if the SurveyID matches on the previous record. I am using the record field to find the previous record.
View 5 Replies
View Related
