Limiting Records In Query

Feb 11, 2008

Hello,

i have tried to find an answer but can't seem to find any posts that apply.

I need to limit the records returned in a query to 3. Well, that is fine, i have set it to do the top 3, and it is working well, I am looking at the top percentages received in a number of criteria. However we have just discovered a situation where someone has 7 all with 100%, so all 7 records are being returned. I can see why, i just don't want them to be!

The report based on the query requires only three records, in this case we don't care which 3, seeing as they are all equally good, in fact randomness is a boon.

Any ideas out there - i 'm stumped!

Anna

View Replies


ADVERTISEMENT

Need Help Limiting Displayed Records In A Query

Apr 24, 2008

Hello again my forum o' saviours, Current filter: WHERE (Assignments.[AcademicYear]) Like ([Please Enter Academic Year])AcademicYear is a field in the Assignments table (duh). In this same query, I have a couple fields (equations and if/thens built on the assignments table) that will need to be used as further filters. I thought something like WHERE ((Assignments.[AcademicYear]) Like ([Please Enter Academic Year]) )& (len([groupwinterbox])+len([groupspringbox])+len([groupfallbox]) > 3) would work, but I keep getting pop-ups asking what the values of the three query-based calculations are. Where did this noob screw up?

View 9 Replies View Related

Limiting Records In A Subform To 24 - How To

Nov 17, 2005

Ladies / Gents !

I am writing a database for my work environment to enter sample numbers in a predefine batch. My Form contains information to identify batch ID. Attach to this form is a subform which I would like to limit the number of records to 24.

exsample: form : as unlimited records
subform: only limited to 24 records within the master form.

Now, everytime i create a new record in the master form , I should be allowed to enter only 24 records in the associated subform.

Does anyone have an idea on how to accomplish this !! Your help would be greatly appreciated.

Thank You

View 1 Replies View Related

Limiting Amount Of Records In A Table?

Apr 25, 2005

Hey! I'm doing Computing AS level and the coursework is to create a system for "Terry's Turkey". This is basically my first introduction to Access, and our teacher is little to no use!

After alot of work working out things in access and reading alot of books, other than creating the switchboard, theres only a few things left to do!

The first is to limit the amount of "turkeys" that can be ordered. I have 2 tables that this envolves... Order and Customers, they have a one to many relationship (each customer may place one or more orders, each order must be placed by one and only one customer). They are linked by Customer ID. I need to set two limits, I guess by a validation rule of some kind. The first is that each Customer can place no more than 5 orders. The second is that their can be no more than 40 orders in total. A slight complication to this is that a customer can "cancel" an order, in this case its marked as cancelled (theres a yes/no field called Active to determine this), and ideally, these wouldnt be included in the counts.

So far I have the following as a query, though I have no idea how to put this into a validation rule
SELECT Count(Orders.OrderID) AS Var1
FROM Orders;

I guessed that to expand this to not including inactive orders something like
SELECT Count(Orders.OrderID) AS Var1
FROM Orders
WHERE active="yes";

Any help at all would be most appreciated! I have spent many hours pondering over this is and looking at as many access sources that I can! I think this is my last resort :(

View 3 Replies View Related

Limiting Records Retrieved To 10... With A Catch.

Jul 12, 2007

Hello all,

I am a buyer and I'm attempting to create a query in Access 2003 that will list all of my vendors (20 in total) and their top 10 selling sku's. Here are the fields I am using:

Buyer_Code
Supplier_Number
name
Sku_Global
OEM_Number
Part_Description_English
Status
C_Last12_SLS_QTY

I have created one Query including all of the above fields as well as the following field, "XP1: GetTen([Supplier_Number])"
In this query I have set "Supplier_Number" to sort ascending and "C_Last12_SLS_QTY" to sort descending.

I then created a module, written as follows:

Option Compare Database
Option Explicit
Public wSUPPLIER_NUMBER As String
Public wNum As Integer

Function GetTen(SUPPLIER_NUMBER) As Long
If wSUPPLIER_NUMBER = SUPPLIER_NUMBER Then
wNum = wNum + 1
Else
wNum = 1
End If
If wNum > 10 Then
GetTen = 0
Else
GetTen = wNum
End If
wSUPPLIER_NUMBER = SUPPLIER_NUMBER
End Function

I then created a second query with all of the above fields, and the criteria for the XP1 field as ">0"

As far as I can tell, this should bring me back a list with the first 10 rows for each of my Supplier numbers... unfortunately it is for some and for others it's bringing back more than 10 rows. The other problem is that it seems to be picking random rows to bring back, and not just the first 10 (which because in query1 I set the C_LAST12_SLS_QTY field to sort descending, should be my top 10 selling sku's).

Does anyone have any idea as to what I've done wrong? I'm pretty new with Access so I may be missing something pretty simple.

Any help would be greatly appreciated.

Antonio

View 3 Replies View Related

Tables :: Limiting Number Of Records At A Time

Sep 9, 2014

I'm trying to create an Access database for my college so that our call centre can allocate students to a specific interview time/date. The problem I'm having is that each interview slot must only have a maximum of 10 students at a time.

I'm creating tables for the student details, address and for each interview slot available.

I'm using Access 2010.

How I can limit the number of records in a table to 10 so that when that interview slot is full it informs the user?

View 14 Replies View Related

Limiting The Number Of Available Records In A Table, And Recycling/reusing Them...?

Oct 29, 2007

Well boys and girls, its about that time in the week where I have once again come up with a brilliant application for my little Access project, but have no idea how to make it happen.

I will start with a little background info... In the course of the parts recoveries we have different reasons for recovering parts, different people (locations) that request them, and different priorities for recoveries. For example, when a new model comes out, we will recover 100% of the parts for investigation by the factory... however, we may want to take a look at failed water pumps first. In this case, our recovery, until it is complete, will have higher priority than the 100%, otherwise we would not receive any parts.

This is because of the way our warranty system works... It assignes a three digit number to each part number of a recovery (between 1-999). The lower the number, the higher the priority.

So if I am setting up a 100% recovery, I would want to put it in the, say, 200's. This would leave room for an overlapping recovery to be inserted in the 100's where it would divert a certain number of parts away from the normal recovery stream. Then, once a user-defined date or quantity of parts has been reached, the corresponding number goes dead, and any of those part numbers are reunited with the original recovery stream.

Sticking to the example above, typically 100% recoveries would go for say... 2-3 years, where as an in-house recovery might be for 20 parts... Another difference is that the longer term recoveries are managed by the end date, where shorter term recoveries are by number of parts recieved.

What I want to do is be able to auto assign these numbers based on criteria derived from user-inputted data... (i.e. destination, start/end date, recovery type, etc.). Then, when the recovery is complete, whether based on date or quantity recieved, to delete the information associated with the 3 digit number, block its use for 45 days, and then be able to reuse the number for a new recovery.

Whew.

Thanks to anyone who can be bothered to read past the first paragraph, let alone offer any advice.

View 14 Replies View Related

Forms :: Limiting Combobox With Query

Oct 16, 2014

I'm trying to filter the options in a combo box based on two criteria. The first criterion is another combo box on the same form, for a cascading combo effect. This criterion works fine, no problems at all. The second criterion is a query. So the combo box should display only those values that match both the upstream combobox AND the results of the query. What's really frustrating is that this combobox works just fine in my development dbase. It only stops working when I move it into my active dbase. Anyway, here's the SQL for the combobox rowsource:

SELECT DISTINCT Size2.SizeID, Size2.XYdim
FROM (Shapes INNER JOIN (qrySignsInInventory INNER JOIN Size2 ON qrySignsInInventory.SizeID = Size2.SizeID) ON Shapes.ShapeID = Size2.ShapeID) INNER JOIN Signs ON Shapes.ShapeID = Signs.ShapeID
WHERE (((Size2.SizeID)=[qrySignsInInventory].[SizeID]) AND ((Size2.ShapeID)=(SELECT Signs.ShapeID FROM Signs WHERE Signs.SignID = [Forms]![frmUsedDropdown]![cmbSignID])));

I've hilighted the criterion that isn't working. Also, I'm not getting any errors or anything, it's just that the results aren't being filtered. I've triple-checked the references for the active dbase, and I've checked the results of the query. Everything should be working.

View 4 Replies View Related

Queries :: Number Limiting Query

Jun 2, 2013

In table1 students lectures data are stored as:

Code:
ID studentname lecturesubject lecturedate
1 steve politics 3/2/2013
2 jack politics 3/2/2013
3 steve math 5/2/2013
4 steve politics 7/2/2013
5 jack politics 8/2/2013
6 steve math 8/2/2013
7 jack politics 9/2/2013
8 steve politics 10/2/2013
9 steve math 11/2/2013
10 steve physics 13/2/2013
11 jack politics 15/2/2013

We need a query in which for each student is allowed to preset a limited number of lectures on a specific subject. For example each student can provide only two lectures on a subject and any more lectures on the same subject presented by him will not be counted. In the above example records 7 8 9 and 11 will not be shown in the query.

View 3 Replies View Related

Limiting A Value In A Field

Oct 21, 2005

I'm trying to limit the amount a user can enter into a employee discount field in a table. I would like to set up a validation rule in the table to limit the discount to no greater than $0.20 or 20 cents. Anyone have any recomendations? Thanks..

View 1 Replies View Related

Limiting Entries

Feb 14, 2008

Hello, I need help. I have two tables.

horse_entries
ent_HorseName (PK)
ent_entries

horse_information
inf_HorseName (PK)
inf_Ground
inf_Notes

It is a many-to-many relationship. When I open a table and enter a record there is a small + sign that allows me to open up the rows from the other table to enter directly into it. Can this be turned off? Also I can enter more than one record through this way. I only want one entry in inf_Ground, inf_Notes and inf_Entries for each HorseName. How would I do this? As you can tell I am completely new to Access and trying to learn. Thanks :)

View 2 Replies View Related

I Need Help Limiting A Field

Nov 8, 2005

Im doing a database booking system and i need to limit the amount of people to 160 per night. At the moment i have a query that sums up the bookings per date but i dont know how to then limit this column to 160.

View 2 Replies View Related

Limiting Duplicates

Jan 31, 2006

Hello All,

I have a table “subdivisions”
With a daughter table “lot numbers”

I need to limit lot numbers to no duplicates in the LotNumber field, within a subdivision, without limiting it to no duplicates in the table.

Any clues?

LotNumber is a text field.
LotNumberID is autonumber, primary key

Preferably this would be accomplished in a query.

View 3 Replies View Related

Limiting Results

Aug 15, 2006

I have this table

AircratfID
Flight
Time

I have a aircraft logbook with 4 entries for page

I tried to build a query that shows
"Time until this page"
"flight time"
"flight time"
"flight time"
"flight time"
"Time to copy to next page"

Anyone? Thanks

View 3 Replies View Related

Limiting Changes To A Combo Box

Oct 12, 2005

hi,

we have this database that's used by differerent departments and one of the fields on the form has a combo box for status (pending / completed etc). when this is set to 'Complete' we would like to make sure that it can't be changed again, is this possible?

there is only one table used in this database and updates to other fields are still required so it is only this one combo box that need to be blocked from change once in the completed stage...

Thanks in advance

View 3 Replies View Related

Self-limiting Combo

Mar 24, 2006

Hi,

Just a quick question, is it possible to have a combo box in a continuous subform that automatically limits its rows based on the selections made in the other child records/combos (for the same parent record)?

Cheers,

Bobadopolis

View 10 Replies View Related

Limiting Drop Down Value Use

Apr 22, 2015

I have a subform in datasheet view on a main form. The subform contains a drop down field with multiple values. I am wondering if it is possible to limit the use of one of those values by account. Basically there are 5 values (contact info updated, initial DM Contact Made, sent email/left voicemail, opportunity create, Follow up call with DM) and I only want user to be able to select the value Opportunity created once per account. The others he can select as many times as he wants.

I attached a screen shot of what the form/subform looks like.

View 14 Replies View Related

Limiting Play Within The Databank

Sep 22, 2005

Hi there

This is my problem

1) I have just spilt my data base into the front end and back end. I did this not using the wiszard. What i would like to do is to limt total acess to my databank. I want guest to be able to view my data and those who are allowed to can edit the data. How do i do this exactly

2) I want to be able to watch closely and see who is doing what, i would like to record, in a table, those who edit the data and those who enter data. i would like to keep track of whats going on in my data bank. So to seen when the data was newly added and what data to my data bank and to see who and when the changes have been made.
____
new
if there is a simple way to add the password system i am all ears

Thanks in advance.

g.

View 2 Replies View Related

Time Now Between Limiting Values

Mar 21, 2006

Need some ideas on how to detect if Time Now is beteen preset values.

It is in connection with a Machine Monitor system being I am probably going to be asked to develop.

Problem is night shift. They work normally 20:45 to 06:45 following morning

I will be accumulating elapsed minutes of shift and working minutes by running an On Timer event updating both values by 1 each minute providing certain criteria are met.

One of the criteria will be that machine is supposed to be working.

So I was planning to set a flag indicating Working Yes or No based on the Time Now being between start and end times.

This is fine for normal day working and normal day shifts but is a problem with Night Shift.

Ideas on how to handle welcome

len B

View 8 Replies View Related

Limiting To Just Combo Box Choices

Dec 20, 2006

Hello,

I am figuring this must be an easy question. I have combo boxes setup and working great. I just noticed that I can put my own text into the box and not be limited by just the choices in the combo box. If that makes sense.

When I try to lock the boxes then it doesnt allow me to select. I want the combo boxes to be the only selection they can make, and not be able to put their own text in.

Attached a screenshot to help clarify.

Anyone know what option I am missing?

Thanks.

View 2 Replies View Related

Limiting Subform Data

Jul 8, 2005

Hi,

I'm baffled by this ...

One of the forms in my database has a subform. The subform displays data from a table which is related to the data in the main form (also powered by a table) through a key relationship. So long as the relationships are set up correctly, the correct data magically appears in the subform as you scroll through the records in the main form.

I've tried to do this a second time, except in this instance the subform is displaying data from a query, and the data is related to the data in the main form not through a direct key, but through a link table. I've set up the relationships properly as before, but this time the subform stubbornly displays all the data from the query, not just that which is related to the record in the main form.

What am I doing wrong?

View 2 Replies View Related

Limiting A Field To A Particular String

Dec 14, 2005

On My form I would like to have code to limit what can be put into a field e.g

If Me.Myfield is not = to A, B or C
then
message
"You must enter A, B or C in this field"
EndIf

Any help please ?

View 6 Replies View Related

Limiting List Box By Another Field

Apr 25, 2012

I'm trying to limit the funders under previous applications to ones with the same name so then when you click on one it loads that other funding application details...

Code:
SELECT [Sheet1].[ID], [Sheet1].[Funder] FROM Sheet1 WHERE [Sheet1.Funder] Like [Sheet1].[Funder] ORDER BY funder;

Code:
[Sheet1.Funder] Like '" & [Sheet1].[Funder] & "*' ORDER BY funder;

[code]...

View 14 Replies View Related

Limiting Size Of A Field

Aug 14, 2013

Im doing a coursework on ms access on School Management System. I have a table 'tblSubject' containing fields: 'Subject ID' and 'Subject Name' . A student has the right only to choose a maximum of 8 subjects, that is , the field 'Subject ID' must have only 8 records/entries. How to do this ???????

View 1 Replies View Related

Limiting A List In Combo Box

Aug 20, 2013

I am very new to database design and application building. I work as general office support for a very small non profit. I have been tasked with developing a database to track HR and timesheet data and turning that into benefit reports. Actually, I was asked to create some spreadsheets to do this - but I strongly feel that access is a better solution than excel for this as we have around 150 individuals to track between all staff (part time, temporary, fulltime etc + regular volunteers).

So, the wall that I've hit that brings me to actually post instead of just search....

Some of our employees have more than one position, and in turn more than one rate of pay per month.

I created a compensation table that has the employee ID (from the employee table) and fields for position title, rate of pay and if that is per month or hour

I have a main timesheet table that assigns employee and month; and a details table that takes the timesheet ID and compensation ID with fund codes and hours worked.

My problem is that I don't know how to limit the combo box on the details sheet to just the positions assigned to the employee ID that matches the timesheet being worked with.

I tried all the design view access query options I could think of to limit the records in the combo and I always end up with all positions/rates of pay listed with no regard to employee ID....

View 1 Replies View Related

Limiting The Number Of Characters Displayed

Apr 12, 2006

Hello,

I'm trying to limit the number of text characters displayed in various fields on a form. I want it to appear so that after a certain number of characters it then displayed "..." indicating there is now information in a field.

Thanks for any help!

View 1 Replies View Related







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