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 Replies


ADVERTISEMENT

Selecting Multiple Rows

Jan 11, 2008

Hello everybody....

I was given a task by my boss to create an appraisal system for our department here in one of the local schools in the Philippines.

Anyway, what I created first a form from which all subjects being offered will be displayed. I did this by having a main form (bind to personal info table) and a subform that displays all the subjects (bind to subjects table). Then, the user will simply click on the appropriate rows bearings the subejct that the students wish to enrolled and when the save button is click, such will be saved into the enrol table.

My questions is, what control will I use to allow me to select multiple rows? and how will I be able to save selected rows into the enrol table?

Thanks in advance and more power...

~sirjhun

View 1 Replies View Related

Selecting Limited Rows

Oct 13, 2005

I have a table containing items and their prices. I need to run a SELECT Query which can return the last 5 prices for each item.

My Query is:
SELECT item, podate, price
FROM PurchaseOrders
GROUP BY item, podate, price
ORDER BY item, podate DESC, price

Let's say my Item A001 returned 12 records. I need only the 1st 5 records in my Report.

Can anybody help pls???????????

View 2 Replies View Related

Compare Several Hundred Rows Against Other Rows

Nov 21, 2005

Not sure if what I want to do is possible, or at least possible the way things are set up.

I have a massive table - c. 6 million rows. It contains data along these lines:

Plan#, Item, Price, Description, Colour, Value, Location, etc.

The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).

To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.

Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).

I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.

Any help or suggestions would be much appreciated.

Thanks!!!

View 3 Replies View Related

Converting Rows To Columns And Columns To Rows

Jul 31, 2013

I am trying to convert a table that looks like the following...

Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff

I would like to covert it to....

Customer 000462 000224

Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff

I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.

View 12 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 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 2 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