Selecting Most Current Value

Jun 8, 2006

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?

Thanks

Tony

View Replies


ADVERTISEMENT

Selecting Current Assignments

Jul 6, 2005

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 need the result to show:

100 Seton
200 AMH

View 1 Replies View Related

Selecting Current Record

Aug 23, 2004

Hi,

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

View 2 Replies View Related

Queries :: Selecting Most Current Record For Item That Has Multiple Records In A Query

Oct 31, 2014

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?

View 11 Replies View Related

Forms :: Goto Form With Current User And Current Date

Aug 27, 2013

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..

View 1 Replies View Related

Selecting Only The Best Three

Nov 13, 2007

Hello All,

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’d be very grateful for any advice/guidance.

Bomac.

View 4 Replies View Related

Selecting Record

Apr 20, 2006

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?

View 4 Replies View Related

Selecting First 10 Records

Apr 10, 2006

Hi,

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.

Any ideas?
Slighlty confused,
Rosxx

View 3 Replies View Related

ARGHHH Selecting All

Jan 17, 2007

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.


VERY frustrating.

cheers,
Spinkung :confused:

View 2 Replies View Related

Selecting Records

Jun 11, 2007

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

Cheers guys

View 9 Replies View Related

Selecting By Day From Date

Dec 6, 2005

Good Afternoon,

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.

Am I making sense?

View 13 Replies View Related

Selecting One Instance

Dec 16, 2005

Hi guys,

Still trying to get used to this Access lark.

A table contains a column with numbers i.e - 1,1,3,4,2,2,2,3,4,4

If i just want a query that shows one of each i.e - 1,2,3,4

what do i need in the criteria line?

Thanks in advance

View 2 Replies View Related

Selecting Rows

May 17, 2006

Hallo Everyone,

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.





aravind.s

View 1 Replies View Related

Selecting Dates

Jun 29, 2006

Quick one for you geniuses out there!

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.

Thanks for the help!!

View 1 Replies View Related

Selecting Top X Records

Jul 30, 2006

Hope someone can help.

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.

Would be very grateful if someone could help!

View 3 Replies View Related

Selecting Fields

Dec 17, 2006

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.

View 1 Replies View Related

Selecting Every 4th Record

Nov 21, 2007

Hi,

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?

Thanks

View 6 Replies View Related

Selecting Whole Field

May 2, 2005

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.

View 2 Replies View Related

List Box Selecting

Feb 8, 2006

Hi All,

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

Dim stDocName As String

stDocName = "Rpt_Route_MAIN"
DoCmd.OpenReport stDocName, acPreview

Exit_LstBox_DblClick:
Exit Sub

Err_LstBox_DblClick:
MsgBox Err.Description
Resume Exit_LstBox_DblClick

End Sub

I've got a query set up that supports the report the two fields that need to be trasmited to the criteria of the query are :
1. Route
2. Timescale

View 1 Replies View Related

List Box Selecting

Feb 8, 2006

Hi All,

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

Dim stDocName As String

stDocName = "Rpt_Route_MAIN"
DoCmd.OpenReport stDocName, acPreview

Exit_LstBox_DblClick:
Exit Sub

Err_LstBox_DblClick:
MsgBox Err.Description
Resume Exit_LstBox_DblClick

End Sub
I know i can use this to open the report but its not relevent to what has been clicked on

I've got a query set up that supports the report the two fields that need to be trasmited to the criteria of the query are :
1. Route
2. Timescale

Help Please


Thanking you in advance

View 1 Replies View Related

Selecting Value From Listbox

Mar 14, 2006

Hi,

I have a two column listbox, list1.
How do I save the data on the 2nd column of the selected row?

To save data from the 1st column I do:

myData = list1.itemData(list1.listIndex)

Now for the 2nd column???:confused:

Why are lists so complicated?:mad:

View 3 Replies View Related

Selecting An Image

May 1, 2006

So far I have used this code:

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.

New to all of this :confused:

View 1 Replies View Related

Selecting Different Currency

May 1, 2006

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?

Hope I am cleaar enough...

Kyriakos

View 1 Replies View Related

Selecting From Combo-box

Nov 8, 2006

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.

Thanks in advance.

View 1 Replies View Related

Selecting Fields

Dec 17, 2006

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.

View 1 Replies View Related

Selecting Data

Feb 18, 2005

hello,

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

any ideas?

thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved