I have a small problem which I feel I should be able to solve but the hours of trying are proving me wrong!
From a choice of 16 events, competitors can enter as many as they like providing they are eligible. For each event they receive performance marks which are duly stored in a table. Some compete in 1 event others in 5 or 6. (I do not have a control showing ‘number of events entered’ and have tried several options, all unsuccessful, to create a ‘count’ of events entered. Is there a way?) My main problem is, from each individuals’ records, how do I select only the highest 3 marks from the classes each individual entered.
I have a table with customer details, probs over 3000 customer, and i need a method of selecting the customer in a form by their surname. Currently i have a combo box, but as you can imagine, this is not practical with so many records. anyone think of any other way to do it?
I have a query that gives an out put of 'worst offenders'. these offenders are in order of on field ascending (number of entries made per week) and another field descending (total sales). It is by the combination of the sorting of these fields that we have an ordered list of offenders.
For my report i want to only see the top 10 records of this sorted list.
so for this selection i simply want to select the [B]first 10 records [B]of the ordered list. I cant make a selection by any one particular field(as explained above)
i have looked into the 'select top' functions and 'dfirst', but I dont think they'll work for me.
Can anyone tell me why sometimes when i'm using access all of a sudden when i click in cells, control boxes etc that i select everything and i cant position the mouse with a string of text with selecting all of it.
hey guys, im having one last problem with a report im making. just want to thank boblarson, Rich and Dennisk for all the help they have given me upto now, i have added to your reputation guys, thanks!
My problem is as follows:
I have a form for my products and a subform for the suppliers, each product can have many suppliers.
I need to have a report of products to send out to customers, so i created a check box to "tick" if i want to include it in the report. So far so good, however, the report shows each product several times for each supplier :(
can anyone tell me how to select only the supplier with the lowest supplier price for each product
I am currently trying to create a query that will allow me to do the following:
Prompt the user with a pop up box, asking:
Please enter start date Please enter end date
It will then select everything from my table between those two dates (this works), and then it will ignore all those items that have a time after 7am or before 7pm (this works), but I need it to also select all entries that fall on a saturday or sunday. Now because my raw data comes in the form of (australian dates): 7/09/2005 9:39:10 AM, I am using an update query to split the field into two fields: DateFrom and TimeFrom (both are date/time fields). I then need to create another field called Day, which takes the data in DateFrom and converts the date to dddd (using Format(Date()) i assume), giving me a field with the day name in it, ie: Sunday.
I can then use that to differentiate between weekdays and weekends.... as I use MySQL and PHP more often than I do access, I have no idea how to do this.
So in short:
How do I (this is what i think i need):
Select * from tblData WHERE Day!=Sunday OR Saturday AND TimeFrom > 7:00pm OR TimeFrom < 7:00am
Select * from tblData WHERE Day=Sunday OR Saturday
Both of those selections, will of course need to be performed within my date constraits specified by the user.
I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.
I am working on a query to select the most current value for a given record based on dates. The record has an associated cost for a given year say 2000. For years 2001 to 2006 there is no cost. I want to use the cost in 2000 for the most current cost even though my date criteria specifies say 2002. Can this be done within the select query?
I would like a command button to print records from a select query displaying dates for the past 2 days but not for todays date. e.g. today is the 29th, I want to print records for the 27th & 28th.
Data is being recorded hourly and kept for 2 months so there will be new and old data that I don't want printed.
I am building a database to evaluate football team performance. I have a table with results (tblResults) where a team will have records in either the Home Team or Away Team fields depending on the fixture.
I want to include a "Team Form" calculation that selects the last 6 games for each team (both home & away) and allocates points to calculate a recent form stat for comparison with the opposition.
I have tried using the TOP VALUES option in a query, but I can't seem to get it to extract the top 6 in descending date order for every team.
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 with over 90,000 records, how do I create a query to select only every 4th record? If this cannot be done with a query, what are my options?
I have text boxes on a Form that I need to update with figures each week. I have tried to have the entire field selected by right clicking with the mouse but I keep getting an error. The coding I am using in the 'OnClick event' is:
Private Sub Ctl1_Click() Me!R1.SelStart = 0 Me!R1.SelLength = Len(Me!R1) End Sub
R1 being the txtName.
The errors I have received are Object doesn't support this property or method. I have tried to replace the Ctl1 with R1 but still get errors. Can anyone please shed some light on what I am doing wrong. I am using Access 2002.
I have a database that has route lists for drivers, I have a combo box that lets the user select a route and a timescale and with that data it populates the list box,,,
The list box displays Route Period Month
Now then, I need the database to be able to double click on the list box and for it to preview a reoprt, but i can work out how to set querry criteria from that command
Private Sub LstBox_DblClick(Cancel As Integer) On Error GoTo Err_LstBox_DblClick
I have a database that has route lists for drivers, I have a combo box that lets the user select a route and a timescale and with that data it populates the list box,,,
The list box displays -- Route -- Period -- Month eg -- 1 -- 6 montly -- jan, jul
Now then, I need the database to be able to double click on the list box and for it to preview a reoprt, but i can work out how to set querry criteria from that command
Private Sub LstBox_DblClick(Cancel As Integer) On Error GoTo Err_LstBox_DblClick
Private Sub Form_AfterUpdate() On Error Resume Next If IsNull(Me![ImagePath]) Then Me![ImageFrame].Picture = "" Else Me![ImageFrame].Picture = Me![ImagePath] End If End Sub
Private Sub Form_Current() On Error Resume Next If IsNull(Me![ImagePath]) Then Me![ImageFrame].Picture = "" Else Me![ImageFrame].Picture = Me![ImagePath] End If End Sub
To make a form which will show a picture (different for each record) if the field 'ImagePath' has an entry in it. If not, it does not show.
The table containing the image is 'tblAnimal' and the field name where the path is stored is 'ImagePath.'
I would like my user to be able to click a button on the form 'frmAnimal' which says 'Add/Change Picture.'
This would then open a File Dialog window where they can navigate to an image (.jpg or .bmp) and select it.
The file path of this image will be subsequently placed into the 'ImagePath' field of the current record that the form is showing (based on 'tblAnimal.'
I saw a similar technique on the Northwind example database but this uses an extra part and I am a little confused as to which VBA code I need as the OnClick Event of the button.
Hi to everyone. I have this form that my let's say clients should fill in. In one drop down list I need the client to be able to choose different currency i.e. euros or pounds or dollars. But I need the symbol to appear in the list and since the field is currency type I can't use the lookup wizard to decide what values should appear in that list.
How is this possible? Should I make an extra field of text type to choose the currency and on another field of number type to place the value I need or is there another way?
I have a quick question on Forms. I have a combo-box in a Form that displays the student ID, first name, last name, and test scores from a source table. I have selected only specific fields to display from the source table in the combo-box. The intention is to note down the ID of a student for future querying purpose and I’m looking at easier ways to do it.
The question I have is, as I look down the drop-down and select a student by his last name (say Johnson), I want the student ID to be selected (or copied) when I click the specific student. There could be many students with same last name, and once I select a particular student from the drop-down, I was wondering if there was a way for this ID to be copied or selected. At present, the student ID is written down and used later for querying other information.
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.
im trying to select data from an access database via asp. the data must be stored in a recordset and fit the criteria as given in the variable varItemSelected:
heres what i have
SQLstmt = "SELECT * FROM Products WHERE product = " &_ varItemSelected
set rs = con.Execute(SQLstmt)
but i get the error: Microsoft JET Database Engine error '80040e10' No value given for one or more required parameters
Heya al, ive got a problem with my select max statement. What i want to do is select the maxium value from my table- howver its not returning the maximum value- Instead its returns a middle value e.g. the number 1-50 in my column in my db. I want 50 to appear but it only displays the number 10??? Heres my sql statement- pls i need some advice or code
Code:sub sqlCmd( )'==SQL STATMENT========================================== =================================set objrs2 = server.createobject ("ADODB.recordset")dim sqlStatmentsqlStatment = sqlStatment + "SELECT MAX (Area_Code) AS AreaC FROM [Areas]" objRs2.OPEN sqlStatment,objConn,1,3'================================================= ========================================end sub
heya all- ive got sql max working on my table beacause i changed a field type to number and then sql selects the maxium value- but i now need to select a specific number along with its record from that same table. This specific number will depend on whats been selected in a drop down box- but when im running the sql its comming up with this error:
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Could this be to do with the field- beacuse i needed to change it to numeric type as i wanted to use sql max, and what way could i get round it??? Heres my sql command that im running to get a value:
Code:sub sqlCmd( )'==SQLSTATMENT=================================set objrs = server.createobject ("ADODB.recordset")dim sqlStatmentsqlStatment = sqlStatment & "SELECT * FROM [Areas] WHERE [Area_Code] = '"& Code &"' "objRs.OPEN sqlStatment,objConn,1,3'==============================================end sub
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.
