Help please. I have a database with patient authorizations to various providers. They may have more than one authorization on the same day. I need to select the patient and the most recently assigned provider (other selection criteria will also be used). Data may look something like:
Authno Authdate Membno Provnam
1 20050201 100 AMH
2 20050402 200 Seton
3 20050404 100 Seton
4 20050404 100 Seton
5 20050501 200 AMH
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 have a form (in datasheet mode) that lists many records. I need some code that will select all fields in the current record. (That is, the current cursor location.) Thanks
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?
How can I create a report to project out 6 quarters what qualified Job assignments will still be available :confused: The qualification code will be Q for qualified people.
* A table of consultants * A table of contracts * A table of assignments (shows which consultants are assigned to which contracts, along with the start and end dates of those assignments) * A table in which consultants book their time to projects
The consultant assignments change over time. What I need to do is create a query that shows a) the amount of time a consultant booked to a project(s), and b) the contract to which he/she was associated at the time. For example, if a consultant was associated to Contract A in February, Contract B in March, and Contract C in April, I want to be able to accurately reflect that in my query. I'm not sure how to go about doing this in my query.
I'm sure many here have solved similar problems, so any help you can give will be appreciated.
A have a table of volunteers and a table of assignments. I can run a query/report of volunteers who are assigned. How do I create a query of who is not assigned.
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
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 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?
