I have a query that is used to capture all the fields that can be possibly used.When run & exported to excel the user then simply deletes the columns with the fields that they don't require.We do this so we don't have to keep creating queries over and over again when you just use one that does all but then delete what you don't need afterwards.
Any way of using a form with tick boxes for each of these fields so when they user ticks the fields they require reporting on then only those fields will be returned in the query.
I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.
I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.
Table: t_AmortPeriods Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate
Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.
My sql looks like this:
SELECT t_AllMonths.MonthStartDate, Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate, t_Assets.AssetID FROM t_AllMonths, t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);
I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."
I have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.
So, it would be something like:
Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;
So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.
I am looking for a way to generate a list of all fields within a table, have the end-user select which fields he/she wants to include, and then run the query. I am trying to create this within a form for a nice, easy to use GUI.
I am using Access 2010 on Windows. The fields I need them to select from are in one table, however there are many lookup (tblkp) tables related.
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example
SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot FROM EmpTable S1 WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);
Can I not select a value that has been calculated this query?
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I am trying to use a query to populate a combo box. works fine as I am using one table. I want to select DISTINCT records which works. However i need the ID of that record in the select statement. Anyone know how to select two things in a select distinct query??
Select DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;
this works. but i need to select the tbl_Aplaws.AplawID as well. throws errors
I'm teaching this term, and have put my grades and roster in Access. Each grading opportunity is a separate field.
Students will have to take 3 exams (Exam1, Exam2, and Exam3); however, their grade is based only on the 2 highest exam grades. There are other grades...FinalPaper, Attendance, Paper1, Paper2... the total of the grades is 600 points.
I can do the other calculations... what I do not know how to do is to select the two highest test values from the 3 Exam fields. I coudl do it manually, but I really prefer to simply enter grades, and then have the final grade calculated.
In my Patients table i have a field called PatientID (AUTONUMBER) as my primary key I hve another problem. Is this possible as i hve been trying for two days now. I hve tried a lot of ways but i cant get it to work right. I have a table called dependents. ID , PatientID,,Dependents,DOB,Age id being the PK AND PatientID as a number I have a table called vitals. with ID,PatientID,PatientName( which is a dropdown of Dependents), hpp,sats etc. (This is a subform in datasheet style in another form)
In my table vitals i have the follwing for PatientName . comboxbox.
SELECT Dependents.Dependents, DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age FROM Dependents;
So it selects fine in a dropdown like John 48, smith 36.
The problem i have is that it selcts all the records irrespective of PatientID.
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is : Private Sub cmd_mailreport_Click() Dim office As Object Set office = Checks.office DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _ "info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _ True, "" End Sub
I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this
On Open Select Case Forms!frmLogin!cboUser.Column(4) Case X = 4'the employees security level' Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee. End Select
of course this is a syntax error as I do not know the correct code words.
A query TaxType_Query produces the following results
TaxType; FromDate; ContactID, FullName W2; 7/1/15; 1, Tom 1099; 4/1/15; 1,Tom W2; 1/1/15; 1, Tom W2; 1/1/10; 2, Dick 1099; 1/1/09; 3, Harry
I need the query to show only the rows with the maximum FromDate for each ContactID (result would be the 1st, 4th and 5th rows only). I assume there is a quick SQL statement that will work better than Totals / Max criteria method. If I set the FromDate criteria to Max the TaxType field is a problem as they have different values I cannot group by that field.
I am needing help with how to set up the code to select different queries based on different conditions.
The database is to be set up so that I can pull studies at different time points based on if they are under long term, accelerated or stressed conditions. When this condition is selected there are only certain time points which are to be run, example..... Long term: 3,6,9,12,24,36,48 and 60 months. Accelerated: 3,6,9 and 12 months.
As you can see I have the dates calculated for all of the weeks and months that the sample could be pulled at, Inventory Transactions Table. And I am able to generate throught the Pull Dates Form a list of only the records to be pulled under Long Term conditions. I would like to have the form set up so that when I choose the Accelerated condition that it would pull from another Query like the Pull Dates Long Term, but only have the Accelerated time points listed.
I am not sure if I need an If/Then statement and if so how do I set this up.
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54 Maths 69 Maths 41 English 71 English 55 Art 44 Art 43 Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
I am doing an outline design of a database system to handle the results from an archery competition in a couple of weeks.
Currently I have a table with a primary key to hold the information on the competitors (name, age etc.) and this is linked to a table via a foreign key (the archers name) to enable multiple scores to be recorded against each archer. For the purposes of the competition only the first score shot each day is valid and so I am using a yes/no box to filter out these results.
What I would like to do is then filter the results such that only the largest of these first session scores is displayed for each archer. (i.e if archer A shoot 578 on day 1 and 585 on day 2 i would like the database to only report the 585, and similarly for the other entries).
I'm having trouble with a simple query. They have an access database with a table like this (simplified for this example):
-------------------------------- Name Result Date -------------------------------- Bob 5 1/1/2014 Bob 6 2/2/2014* Bob 7 3/3/2015 Bob 8 5/4/2015* Len 5 1/1/2014 Len 6 2/2/2014* Len 7 3/3/2015 Len 5 7/4/2015* ---------------------------------
how I would write a query that gives me the latest result that each person earned in each year (I put a * symbol against the rows above that I am talking about)?
Bob 6 2/2/2014 Bob 8 5/4/2015 Len 6 2/2/2014 Len 5 7/4/2015
(The database is normalised and this table has an ID field).
I am having some trouble with an iif statement as selecting the field..I have two columns, one called [sdCounterpartyprice] and one called [bid]I want to select the [bid] if it's not equal to Null or 0, if it is equal to that then pick [sdCounterpartyprice]this doesn't work
Code: CptyPrice: IIf ([bid] is null or = 0; [sdCounterpartyprice]; [bid])
I am trying to combine fields (different columns) into one field. However I am trying to have a criteria for the fieldname. For example I wish only to combine the columns that contain the text 2003 (ie [Value 2003],[Price 2003]). I have tried to use wildcards in SQl ie select * from [query] where * = %2003. I must be missing something because I can't seem to work it out. It must be simple. I have also tried several ways through the design grid with wildcards in expressions. I also don't want to union the columns by using the exact names ie [Value 2003]& [Price 2003] because additional columns may be added.
Any help, extremely appreciated, I have wasted a whole day with this.
Table1 has fields docno (text) title (text) progress (number)
sample data is like below:
doc-001 test1 90% doc-002 test2 25%
Table2 has fields
sample data is like below:
id (number) icon(attachment type)
1 image1 2 image2
Now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2
