I need to run a query using a list of unique values. I open a new query in design view, pick my table that I want to use, pick the field, but then in Criteria, I need to use a list of values. The list is 62 values long. Any help here would be greatly appreciated. I hope I am explaining myself thoroughly.
Thanks,
a_brooks
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?
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
I have a situation where I am using a maketable query to create a table and then I need to use append queries to then add additional records to the created table - some of these are just run once and some multiple times.
if possible, I do not want to hard code the query multiple times i.e.
Code:
DoCmd.OpenQuery "qryCreate_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
etc
So is there a way I can run the make table query and then get some sort of loop to run the append query a number of times ?
I am having an issue with my running total query.
It consists of a running total per vehiclenum. All data comes from one table.
It works properly only on the first vehiclenum of the query. After that, the first "previous" odometer reading of each subsequent vehiclenum starts at some erroneous number, throwing the remainder of each vehiclenum running total.
Here is the code for the query,
SELECT qry_ODO_TotalSub.ID AS OdomAlias, qry_ODO_TotalSub.ODate, qry_ODO_TotalSub.VehicleNum, qry_ODO_TotalSub.Odometer, Nz(DLast("Odometer","qry_ODO_TotalSub","[ID] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference, Nz(DFirst("Odometer","qry_ODO_TotalSub"),0) AS StartOD, [Odometer]-[StartOD] AS RunningSum
FROM qry_ODO_TotalSub
ORDER BY qry_ODO_TotalSub.ID;
I am using access 2010. I use basic error handling in my routines:
Code:
On Error GoTo errHandler ... exitHere: ... errHandler: MsgBox "Error " & Err.Number & ": " & Err.Description
The problem is lately; while testing I am running multiple queries in a routine. When it fails; its hard to identify which query has the problem. So I hit control break; debug and try to find it. After I fix it; I debug and reset; i get this continuing hourglass thing in the form of a spinning circle until I close and reopen the database. I think I need better error handling but not something really complicated because I need to put it in quite a few routines throughout the database.
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies View RelatedI have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
Is there a way to produce one query that will produce several results that display in a report that is generated from a button? The only difference is the criteria.
EX:
SELECT tblDownTime.dtDate, tblLine.lineName, Sum(tblDownTime.dtDowntime) AS [Total Time Down]
FROM tblMachCent INNER JOIN (tblLine INNER JOIN (tblCategory INNER JOIN tblDownTime ON tblCategory.catID = tblDownTime.catID) ON tblLine.lineID = tblDownTime.lineID) ON tblMachCent.machID = tblDownTime.machID
WHERE (tblDownTime.dtDate) Between [Forms]![frmDTGraphs]! And [Forms]![frmDTGraphs]![end]))
GROUP BY tblDownTime.dtDate, tblLine.lineName
HAVING (tblLine.lineName)="[B]name of line");
criteria being name of line. Choices being line 1 or line 2
What I am looking for is one query to somehow generate the info on the two different lines in two different reports.
Is there a way or do I actually have to write the different reports?
I have multiple queries pulling data from multiple data tables but are all common by a date that I have to enter in the criteria field for each query each time I need to change the date. Is there a way for the criteria field to read data (the date I want) from somewhere so I only have to change the date in one place and is read into each query where specified? I'm having to go into many queries to change the date criteria which I change to the same date for all queries - I'd like to do it once and have the queries read that date.
View 1 Replies View RelatedI have a table that I would like to run multiple filters and criteria.
I would like to have the user enter a word that matches a specific word that matches a field name and all the data fields = 1.
i.e user inputs word baa baa and the query criteria is set to one, it will output all the surveys that match that.
I am trying to build a query where the result is one line per ID with all results for W(eek)E(nding) on the same line.
ID ---- UOM ---- WEJUN20 ---- WEJUN27 --- WEJUL4
6 ---- Hours --- ---250 -- --- --- - -- 278 -- -- --- --- 242
The result I am getting is in the format below:
ID --- UOM --- WEJUN20 --- WEJUN27 --- WEJUL4
6 --- Hours --- ---250
6 --- Hours --- --- --- --- ---- --- --278
6 --- Hours -- --- -- --- --- --- --- --- --- --- ---- -- 242
This is the trimmed down part of the query that is causing the result.
Code:
SELECT [tblP&E].PnE_ID, tblUsage.Measurement AS UOM, IIf([tblUsage]![Week_Ending]=#6/20/2014#,[tblUsage]![Usage],"") AS [June 20], IIf([tblUsage]![Week_Ending]=#6/27/2014#,[tblUsage]![Usage],"") AS [June 27], IIf([tblUsage]![Week_Ending]=#7/4/2014#,[tblUsage]![Usage],"") AS [July 4], IIf([tblUsage]![Week_Ending]=#7/11/2014#,[tblUsage]![Usage],"") AS [July 11]
FROM tblUsage RIGHT JOIN ([tblP&E] LEFT JOIN tblCosts ON [tblP&E].[PnE_ID] = tblCosts.[PnE_ID]) ON tblUsage.PNE_ID = [tblP&E].PnE_ID;
I can't seem to get my head around an issue i'm having with a DSum, the statement is:
Code:
=DSum("[Amount Used]","[tbl_Rwk_Used]","[Cage Number]='" & [IBC_Number] & "'" And "[ID]=" & [ID])
I've tested both statements separately and they work ok, but as soon as I add the "AND" operator, the whole field is summed and the criteria thrown out of the window apparently.
[ID] Is the same in both tables and relates to a main record
[Cage Number] and [IBC_Number] are related in both tables, but are and are strings
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]....
I have a table similar to the following
area q30 q40 q60
A 2 1 3
B 1 1 3
A 2 1 3
A 1 1 3
C 1 1 3
C 1 1 3
A 1 1 3
A 1 1 3
B 1 1 3
C 1 2 3
A 1 1 3
B 1 2 3
Grouping by the area and using the criteria
(Q30 = 1 AND Q40 = 1 AND (Q60 = 1 OR 2 OR 3))
I would like to see a table similar to the one below counting those which fulfils the criteria mentioned above
Area COUNT
A 4
B 2
C 2
If I want to sum the percentages from April, May and June only if a column is Not Null, how would I do that?
example
Tbl 1
PK, Month
Percent
Tbl 2
FK, Month Enrolled
Qty of Rx in the 1st month enrolled
Qty of Rx in 2nd qtr
Base (if Qty of Rx in 2nd Qtr is null then Qty of Rx in 1st month enrolled)
If Qty of Rx in 2nd QTR is NOT NULL then QTY of Rx in 2nd Qtr * Sum of April Percent+May Percent+June Percent, otherwise Qty of Rx in 1st month enrolled * Month Percent
Im getting stuck on how to sum the percents of April, May and June and then multiplying the result times the Qty ONLY IF the field is not null.
I only know how to create Query's using the design mode. I dont know how to write SQL statements.
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.
I have table with [EmployeeName] and [DateReceived] column what I am hoping to accomplish is to get 5 samples of each Employee for each Date received.
View 5 Replies View RelatedI 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.
I am trying to set up a query by form with multiple criteria.For the majority of the criteria I'm using the format: Like [Form]![formName].[txtInputboxName] &"*" .
I copied the Like (...) &"*" from someone else in order to allow for multiple, optional criteria, which does do the trick, however I don't understand why.
I now want to set up a criteria on my query to search for values between two input values to gives the records in the range e.g between 50 and 100 Following the above format, I want to put something that achieves this:
Between Like [Form]![formName].[txtInputboxName1] &"*" AND Like [Form]![formName].[txtInputboxName2] &"*"
with Inputbox1 being 50 and Inputbox2 being 100, however that code doesn't work and I don't know how to get around it as it keeps giving the 'incorrect syntax' error.
Preamble edit: I'm running Access 2003.
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
How can i update fields of one table with update query where creteria are more than one 1
Example:
DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"
What is the syntax error ...
I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".
This is what I have so far for checking the minimum value:
field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)
I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.
I have a table that contains readings from several pieces of equipment as well as the status of each one. Each record has a timestamp, equipment number, status, etc. What I want is to create a query that will return the latest record for each equipment number. Simplified example table:
Timestamp EquipmentNumber Status
Today ------Machine1 ----------Running
Today ------Machine2 ----------Running
Yesterday -Machine1 ----------Down
Yesterday -Machine2 ----------Running
There are more than 20 different Equipment numbers and they are read several times per day and sometimes some of them get missed. What I'm looking for is a way to get a list of all the machines with their latest reading so they can tell which machines are running and which are down based on the last time they were read (instead of specifying a date). I can get this for one machine with no problem. I'm having trouble getting it for more than one machine. I tried a union query (with just 2 of the machines included for testing) but it only returns the results from one machine:
Code:
SELECT TOP 1 TestCompressorRoundQuery.LoggedAt, TestCompressorRoundQuery.AssetNumber,
TestCompressorRoundQuery.CompressorID, TestCompressorRoundQuery.Status, TestCompressorRoundQuery.CompressorIntegrity, TestCompressorRoundQuery.Notes
FROM TestCompressorRoundQuery
WHERE (((TestCompressorRoundQuery.AssetNumber)="104399"))
UNION ALL
[Code] ....
I'd rather not have to create a seperate query for each machine and then join all of those together! I think perhaps a Union query might not be the correct approach. All the data is coming from only one table.
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
Can I do this using only update query?