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 Replies


ADVERTISEMENT

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 Times A Record Can Be Entered Into A Table

Sep 21, 2004

I want to limit the numer of times a record can be entere into a table. After a record has been entered 3 times, I want an error message or validation rule to tell me it can't be done.

View 7 Replies View Related

Limiting Number Of Entries A Specific Client Can Add To Table

Mar 10, 2015

For my database the business has Clients who they conduct Needs Assessments for, but they only conduct 3 needs assessments. I have a Needs Assessment table with Client ID as the foreign key. how I can limit the number of times a Client ID shows up in the foreign key field to three?

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

Limiting The Number Of Characters In A Text Box

Jun 13, 2007

I Have Created A Client Reference Allocation Screen Which Makes The Clients Reference From Details In A Number Of Text Boxes.

For Example:

Client Number: 0010, Forename: Andrew, Surname: Bloggs, Year: 2007,
Occupation: Driver.

Client Reference = 0010ab-dr07

I Have Copied The Text Boxes So The Information Is Automatically Displayed To Create The Client Reference, But Rather Than Shortening The Copied Text Boxes To Show Only The First Few Characters Is There A Way Of Limiting The Number Of Characters Shown In The Copied Text Boxes??

Matt

View 4 Replies View Related

Limiting The Number Of Concurrent Users

Aug 21, 2004

I would like to limit the number of users connected to a shared database. Each user opens a database as the default 'Admin' user. Does anyone know how to do that without user authentication? What event occurs when a user opens the database? How to supervise the ldb file that manages shared usage? I suppose it should be something with the Workspace object.

Any ideas?

Show me the door, please :-)

View 6 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 Number Of Child Fields Per Parent

Sep 23, 2005

I have a field in the parent table which indicates how many child tables there should be at the most. I'm uncertain about how to enforce this. I"m using access as the database to a Vb.net app. Should I do it in access or Vb.net or both.

View 6 Replies View Related

Limiting Number Of Times Users Can Add The Same Account Per Day

Feb 23, 2006

During a promotion we are running the customers are only allowed 3 entries into the system per day but the users are occasionally putting some customers in more than that. I could use some kind of function that limits the number of times the account number can be put in on a given date. (Or daily)

I have the following text boxes: ID, Customer first name, customer last name, account number, date, time.

Any ideas please?

View 1 Replies View Related

Limiting Number Of Times Users Can Add The Same Account Per Day

Feb 23, 2006

During a promotion we are running the customers are only allowed 3 entries into the system per day but the users are occasionally putting some customers in more than that. I could use some kind of function that limits the number of times the account number can be put in on a given date. (Or daily)

I have the following text boxes: ID, Customer first name, customer last name, account number, date, time.

Any ideas please?

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

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

Reusing Info On A Form

Apr 1, 2005

Hi, I have a from which is based on a simple query.

It prompt users to enter a booking number and then shows all info they have previously entered themselves.

I did this in order to give them te opportunity to alter the info they've entered earlier.

Now what I would like to have as an option is

to give them the same form, for which they enter a bookingno. and having the possibility to reuse the information attached to the booking.

My question is:
How do I create something like this?

Cause if they change the bookingno on the current form, it will not create a new record with a different bookingno but it changes the bookingno in the current record.
So what I want is to change the bookingno to create a new record with mostly the same info.

Can you guys help me out?

Thx,

Lion85heart

View 1 Replies View Related

Forms :: Reusing A Subform

Dec 4, 2013

Access 2007.I have 3 tables, T1, T2, T3 and 3 bounded forms to this tables F1, F2, F3..One more table T4 that contain 3 fields that look up (FK) on the previous 3 tables and a form F4 bounded to this table.

I need to use F4 as subform in each of the first 3 forms. So, the "active" FK will change based on form where F4 is used as subform.My actual approach is to lock and disable the field/control that represent the FK at a certain moment.Is there a better way ? Other than to create 3 copies of F4 with slightly differences in the fields arrangements ?

View 2 Replies View Related

Forms :: Need To Count Number Of Records In Table Between Dates In Another Table

Mar 6, 2013

I have created a booking system for a set of resources for schools. Most schools have a membership which entitles them to 2 free sets. I have a booking form with a membership subform (membership table), and a booking details subform (kitloan table).

Once a school is selected on the main form, the membership subform shows the most recent record for that school based on schoolID.I want to display the number of sets they have already had within their membership period (can start at any time of the year, and lasts for 1 year) on the membership subform, so we know how many free ones they have left.

I therefore need to count the number of KitBkID (ID of the booking) in the Kitloan table where SchoolID = the SchoolID displayed on the membership subform, and the DateOut (booking date on kitloan table) is between the DateJoined and DateRenewal displayed on the membership subform (from membership table).

I can do this with a query which works when run and provided with the parameters SchoolID, DateJoined, and DateRenewal.

SELECT Count(Kitloan.KitBkID) AS CountOfKitBkID, Kitloan.SchoolID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID
GROUP BY Kitloan.SchoolID, Kitloan.DateOut
HAVING (((Kitloan.SchoolID)=[Me].[SCHOOLID]) AND ((Kitloan.DateOut) Between [Me].[DateJoined] And [Me].[DateRenewal]));

What I can't do is get it to run on the form and take those values from the form.From the searching I've done, I'm thinking a DCount should be the way to go, but I cannot get the criteria right. I created a query (KitloanCountQry) so that criteria could come from both the kitloan and membership tables.

SELECT Kitloan.KitBkID, Kitloan.SchoolID, Membership.DateJoined, Membership.SCHOOLID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID;

I have put the DCount as the control source for a textbox on the Membership subform (but have tried it in VBA too):
=DCount("KitBkID","KitloanCountQry")
This works but obviously gives me the total for all bookings.

[code]....

Although I have to admit to getting lost in the syntax. This produces #Error.

View 6 Replies View Related

Appending Records With A Desired Auto Number In A Table With Auto Number

Oct 26, 2005

Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.

I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.

Jim

View 7 Replies View Related

Limiting And Storing Data From Another Table?

Dec 4, 2013

I have two tables one that lists accounting codes by county and one that a user will have an entry by county which needs to store the applicable acct code from the other table. I want the user to be limited to the use the accounting codes assigned to their county only when they enter data on the second table.

Table examples:

County
Code
Funds Spent
Accounting Code
Date Entered
Aitkin
123
$20.00

Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.

12/4/2013
Aitkin
112
$23.00

Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.

12/4/2013
Becker
145
$12.00

Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.

12/4/2013
County
Accounting Code
Aitkin
4872727001000000
Aitkin
4842727001000000
Becker
4872727005000000

View 14 Replies View Related

Number Of Records Based On Table Value

Jul 14, 2006

We want to send letters to clients on behalf of our salespeople on a weekly basis from a list of clients in a table. But some salespeople will have 5 letters go out and some will have 20 go out (depending on what amount the salesperson is able to followup on). I can store the weekly letter quantity number in the table no problem, but I am just not sure how to have the query show 5 letters for Bob and 20 letters for Sue.
I know about the "Top N" setting in query design, but not sure how to set it to read from the table.

Any ideas?

View 4 Replies View Related

Number Of Records Is Different In Table And Query?

May 25, 2012

I have table when I open it I found total number of records 1000 while I get result through query I found that 2 records or more than present in the Table mean in query number of record is 1002. I donot understand why these tow records are not seen in the actual table. and these are not deleted by delete record qurey. While I can see it in select command.

View 9 Replies View Related

Records In Table By Field, Not Record Number?

Oct 5, 2005

I am pretty new to MS access and have a question that I have not been able to figure out. I am making a database with a form to input the data in to the database. The only thing is I want to be able to search/lookup/record data by the products ID code and not by its record number. For example, product 1234 instead of entry number 1. How would I go about this? Thanks for your time!

View 1 Replies View Related

TextBox To Show Number Of Records In A Table?

Mar 18, 2005

I'm sure this should be simple but I can't figure it out. What I want is for a text box on a form to display the number of records within a table.

Basically I've got a make table query which creates a table ox X records depending on how many records in the original table match the criteria. So for example:

Table A has 100 records

Query identifies 30 that mee the criteria

Makes a new table called criteria_met with these 30 records

What I want is for a textbox on a form to display "30" and update whenever the make table query runs (probably via a macro??)

View 2 Replies View Related

Forms :: Counting Number Of Records Within A Table

May 24, 2013

I'm after some code as to how to count a number of records within a table, I then want to have this number returned into a variable so I can determine usages with it, e.g. If the value is < 1 and using conditional statements.

View 1 Replies View Related







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