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?
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.
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.
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.
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??
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.
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.
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.
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.
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.
I have a form which uses a loop command to output reports as a PDF. The reports take a bit of time to produce and the record set could contain 100-150 records. Any way that you can split the recordset down into batches. Maybe have a button which creates 1-20 and another 21-40 and so on.
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.
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?
How can I add an ID-number that always increases (by 1) each time I add a new record to a table (using a form)? I want to always use some info from the record that was added last to fill in a different form. The reason for my question is that I heard that the autonumber does not necessarily increase each time a new record is added...
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.
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.
hi all, I have created the database with multiple tables. I've created one main table to store all records from other tables. Then I have make several forms to enter record into several tables. What I'd like to make is that when I enter new record to any one of those other tables, then it will be automitically add to the main table also. The record no of main table will be automatically increase by itself when the new record came in. Can anyone help me with it? thanks.
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 :(
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.
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?
What I would like to do is for the BoatReg field in the Quotes Table to only display the boats that have been registered to the Client that has been selected, currently it displays all the BoatRegs.
I have a table with an empty column called ActionID. I need to generate a numerical number that begins with 5000 and goes up by 1 number on each saved record. I have a basic form that links all of my other field to the table except the one I need to generate (ActionID) but will need that number saved to the table once its created.
I have two tables, identical in terms of structure (number & names of fields etc.) but different in terms of content (records not necessarily the same, some may be common to both, some may only be present in one or other table)
I want to set up a query to count the number of records which are present in one table (Table A) but not in the other (Table B) using a common identifier field, and put the numerical result in a text box on a form (i.e. use the query as the control source for the text box)
I've managed to come up with the following which provides the list of ID's :
Quote:
SELECT tblA.CommentId FROM tblA LEFT JOIN tblB ON tblA.Id = tblB.Id WHERE tblB.Id Is Null;
Which will come in useful elsewhere but how do I adjust this SQL to only return the number of records? I think I have to use COUNT(*) somehow but I'm not familiar with it and not sure of the syntax?
I'm quite new to Access and am just working on a little practice database where I keep track of sales of a product. Basically I have a table keeping track of the different types of products, a table keeping track of customers, a table for orders and one for replenishments of stock. I have a query that calculates the stock of each type of product based on replenishments and sales.
Now I want to make sure that a customer can't order any more than there is in stock. I know you can restrict what's entered into a table with a validation rule but that's only for static restrictions. After searching the internet for an answer I've pretty much learned that I can't really make this restriction directly in the table, correct? But then how can I achieve this? Do I have to restrict the amount through a form somehow?
