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
I have two tables: tblA with PersonID field; tblB with PersonID field and AppointmentDates field. Tables are joined on PersonID field.
How to run a query which would return all records from tblA and only the most recent date from AppointmentsDates field from tableB?
Do I use GroupBy somewhere or Top 1 to acomplish this? Or maybe I should use a sub-query?
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 RelatedHello, 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 :)
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 RelatedHello 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.
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
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
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.
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.
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
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
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.
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
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?
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 ?
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]...
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 RelatedI 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....
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!
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 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
Hi,
Probably a really simple question, but is causing me problems! I need to be able to limit the amount of characters that can be entered into a field. For text fields I can just enter the relevant field size, but can not find an equivalent for a memo field. Validation rule warns you that there are to many characters, but doesn't seem to actually limit you.
Any suggestions would be appreciated.
J
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
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 RelatedHi guys,
What would be the best control source to use for my sub forms detailed below:
I am designing an Asset Management System. Each the main Asset Table "TblAssetMain" has child tables "TblAssetType", "TblAssetDetails", "TblAssetEventLog".
When a new asset is created, the AssetMain form will have subforms for these three child tables (linked by Asset_ID = Subform![Asset_ID]).
Can I use a query of the child tables for data entry or, if I set the control source to the actual tables, can I limit the number of records in the child tables for each record in the Asset Main table to "1".
Basically each Asset will have one record in AssetMain and 1 relating record in each of the child tables i.e. you cant have more than one AssetDetail record for one Asset.
Did do a search and found the following code but not sure whether there is a way without using code?
Code:
Private sub Form_BeforeInsert(Cancel as Integer)
If Me.txtCount > 2 Then
Cancel = True
Me.Undo
Msgbox "Please return a book before checking out a new one", vbOKOnly
End If
End Sub