I have a form (frmTraveler) in which a user is given a selection of levels for a type of trip from a drop down box (TripLevel). Basically, if a user picks level A, then I would like a value to be written to the corresponding record in another table (tblHotel). The PK is an autonumber (TravelID) and the tables have a one to many relationship. Hope that's enough information for some tips! :)
I having been going mad for a week trying to write a query, please help me.
I am trying to get the MaxServicedate for each Product (PIDFK) for every Location (LID), but then I also need the MaxServicedate regardless of Product use, for each Location that is smaller than the previosly obtained MaxServicedate.
I hope that makes sense.
I have tried this query ---------------------------------------------- SELECT T.LID, T.PIDFK, T.MaxOfServicedate, Max(tblTakings.Servicedate) AS NextMax, T.Installdate FROM (SELECT tblLocations.LID, tblTakingsProductMM.PIDFK, Max(tblTakings.Servicedate) AS MaxOfServicedate, tblLocations.Installdate FROM (tblLocations INNER JOIN tblTakings ON tblLocations.LID = tblTakings.FKLID) INNER JOIN tblTakingsProductMM ON tblTakings.TID = tblTakingsProductMM.TIDFK GROUP BY tblLocations.LID, tblTakingsProductMM.PIDFK, tblLocations.Installdate) AS T LEFT JOIN tblTakings ON T.LID = tblTakings.FKLID WHERE (((tblTakings.Servicedate)<[T].[MaxOfServicedate])) GROUP BY T.LID, T.PIDFK, T.MaxOfServicedate, T.Installdate ORDER BY T.LID, T.PIDFK ------------------------------------- But I am missing some records!!
The subquery gives me all 90 records with the MaxServicedate for each PIDFK in each LID
The main query is missing the records that dont have a service date previous to the MaxServicedate in the subquery!
I have problem in creating conditional expression for query table. I want to add IF Else statement in the statement so that if the value is more than 50, a new field called grade will display the grade of "A" in the new field.
Can anyone teach me how to write conditional expression.
I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.
The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.
The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?
Dim strSQL as string strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME FROM tblPERSONNEL WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
all using access 2010. I have a date field. I need to write a query to pull out dates with current year only. ex data: 1/1/10, 1/1/11, 2/1/12, 2/1/13. Need to pull all dates with current year which would be 2/1/13. Tried in query criteria of the date field: =Format(Now(),"yyyy") I received data mismatch. this is a date/time field.
see below the code . The select statement searches the Printpoolno value from the top to bottom in table tblmaster. As in my table tblmaster there are thousands of records and it takes long to search for that Printpoolno from the table . Is there anyway we can write a query that will search the table from bottom to top as the Printpoolno will always be in the bottom records and not in the top records.
Code:
Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim r As Long
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code: SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
I need to display the year, Date, agent name, amount field, note and sub total for each year.I need to write the SQL query code in VBA and i designed the report with proper field positions.
YEAR column: Display Value only once for the FIRST ROW for Maximum value of Date field value for each set of YEAR. If it has only one record, it display that year.SUB TOTAL Column: Display Total Value for Amount field only once for the LAST ROW of each set of year record (Mininum value of year). How to make it available the values for first and last record alone ?
I just wrote my SQL code based on your inputs in my editor..Based on the input from Plog (from this forum), i wrote a code which listed below.
Code: Private Sub Report_Open(Cancel As Integer) Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, CessioneCredito.Importo, " & _ " Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize,
[Code] ....
While saving it, it shows the code in RED COLOR due to compilation error.As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query as per Plog???
Change the Year Control Source to this: =IIf([GroupPos]=1,[Year]) Change the GroupTotal Control Source to this: =IIf([GroupPos]=[GroupSize],[GroupTotal])
If this is not possible in Me.recordsource, then its advisable to use recordset like writing in two different queries.
Code: Dim ds1 As Recordset Dim ds2 As Recordset myquery1 = "SELECT CessioneCredito.ID_Agente, CessioneCredito.Data_Movimento, CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))"
I can write an UPDATE statement fine, what I don't quite understand though, is updateable statements involving forms.
To populate a form you have the form record source set to some SELECT statement and then you place your controls and set the control sources to the fields in the SELECT statement. This I understand, what I don't understand is the mechanism that allows updates.
This may seem obvious but do I need to reference the key for that particular record as well? How does this work?
i need to update some textbox value when insert some value at another textbox.. like when i add 3 at 1st textbox, 2nd text box will calculate it auto matically wit this formula, 2ndtextbox = 1sttextbox - 1 ...
so where should i type ? query? how? default value? if need to wrte code, which 1 i need to write it? after update? before update?
HelloBascially I am trying to make a query to check if certain Parameters are met over all in the table then add up records.For example i have a record set up likeBob Brown|Access Programmers|18/3/08|1Bob Brown|Access Programmers|17/3/08|5I would want to make a query compound those records up in a report (e.g the compounded values don't need to be saved just displayed)If the records name field is the same and the dates fall in a certain range then the records will be compounded together e.g the numbers being added upSo the end result for that example would beBob Brown|Access Programmers|6Because the records name and "Access Programmers" is the same and they fall within a certain date range the records are compounded togetherMy question is, Would you make a query to do that or can it be done with a report. Bascially what would be the best way to do it.:)Thankyou In Advance!
I am trying to create a parameter query to be able to access from 5 different tables data on a product that has been delivered to retail stores between a range of dates.
I need the query to have the ability for the user to enter the product number and the range of dates to activate the selection.
I have created a query (its a holiday chart). The table the query is created from shows dates taken in each of the 12 months under the 'month name' column and then in the next column it has the total for each of the individual month.
For example,
Field 1 (Jan) Field 2 (total Jan) Field 3 (Feb) Field 4 (Total Feb) Field 5 (March) Field 6 (Total March)
and so on.
Then at the end I have a field for amount left. So what I need the query to do is add all the numbers in each individuals record in all the 'total month' column and then show this in another column which is my 'amount taken' column.
Hey, I'm a VBA Newbie. I have a query that pulls information from my form as criteria and i want to create a table with the results...I am having trouble searching online for a solution and Access Help is no help. Is there a way I can do this using vba?
I need to create a query that will output the number of times doors have been used during time bands of a day. I have no problem getting the info I want from the doors that have been used (in other words where there is a record in the field) however, how can I ammend the attached statement so that it reports no records as a "Zero"? in the same format always ie, North Entrance, 2 South Entrance, 4 East Exit, 0 West Entrance, 3
I need the data in this format so that I can export it to excel.
Here is the SQL view of the query I am using
SELECT [Staff Entrance].[Door Location], Count([Staff Entrance].Time) AS CountOfTime FROM [Staff Entrance] WHERE ((([Staff Entrance].Time)>#12/30/1899 2:30:0# And ([Staff Entrance].Time)<#12/30/1899 23:0:0#)) GROUP BY [Staff Entrance].[Door Location];
i was wondering how to go about doing this, i currently have a query which returns all the payments due in the next month, going to a report which acts as an invoice. I was wondering how to create a unique id for each invoice and store the last id so that i can automatically itterate it later