Select First X Amount Of Records
Feb 27, 2008Hello All
if I have thirty records and have the end user select the first 8 or what ever amount they want, could be 3, 14, 9 etc, is this possible via a form ?
thanks
ADVERTISEMENT
Select Top X Amount Criteria.
Jul 14, 2007I'm ok with Parameter Queries now, but I'm stuggling for the certeria to select say the top 20 records for a list. This would be based on a number, say amount spent.
Any help?
Modules & VBA :: Adding Amount And Checking Daily In Hand Amount?
Jul 21, 2015I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
Retrieving The Right Amount Of Records
Dec 6, 2005I have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).
How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.
So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.
Thanks for helping me out!
Luuk
Insert X Amount Of Records
Feb 8, 2008Hi everybody....
I have a vba question....
I have a form that states how many entries a certain table can have related to that form.
For example:
Form1 - based directly on the table for diagnostic purposes
Cust ID - text - primary key
NumofEntries - number - integer
I want it to create X amount of records in form2 based upon the NumofEntries from form 1 based on table1
For example, I run form 1, and it asks for the custnum, I enter it, and then it asks for the number of entries allowed for the customer, and then I enter it. This number can be different each time. I want the 2nd form just to show that many fields for entry either by a running total (subtraction) in form view or by a for next loop to create X amount of records in the table.
Form1:
CustNum = 1011
NumofEntries = 3
CustNum = 2022
NumofEntries = 4
CustNum = 3033
NumofEntries = 2
Table2
Custnum = 1011, entrynum =1, entry data
Custnum = 1011, entrynum =2, entry data
Custnum = 1011, entrynum =3, entry data
Custnum = 2022, entrynum =1, entry data
Custnum = 2022, entrynum =2, entry data
Custnum = 2022, entrynum =3, entry data
Custnum = 2022, entrynum =4, entry data
Custnum = 3033, entrynum =1, entry data
Custnum = 3033, entrynum =2, entry data
the key is to have the entrynum to start at 1 each time, the rest I can handle.... I am at a loss right now, as I am down to one brain cell, and it's misfiring.....
Granted, tomorrow, when I wake up, I will prolly have a solution, but as always, I value all of your input and design suggestions. Yes, I know already that it's a one to many relationship from table 1 to table 2, I just want some alternate ways to do this. Thanks in advance for any insight you wish to give.
General :: Multiply Different Amount Of Hours By Different Amount Of Rates
Jul 17, 2014I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate
John Smith$15$10$15$30$8
Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am using Access 2013 ...
Limiting Amount Of Records In A Table?
Apr 25, 2005Hey! 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 :(
Selecting Equal Amount Of Records
Jul 12, 2007I was wondering if someone could help me.
I have a simple table with data that gets updated daily. I need to split the data into 5 equal parts for work distribution. Because the amount of records always changes I can not go by numbering so I need to go by percentage, to split it into 5 ( 20% ) parts. Please help.
Counting The Amount Of Records With The Same Value And A Message Box Response
Sep 15, 2004Hey. Basically I have several tables. One table (vistiors) has a combo box in one field called Hostee. Each hostee is listed in another table with all their information. When a hostee is selected in the hostee field of the visitors table I simply need it to count how many people including the currently selected person are being hosted by the person and check if it is greater or less than the amount of people set in the host table which has a field called Limit that has a number which is the maximum amount of people one person is willing to host.
If its greater than the amount in the host table a warning message should come up informing the person (but still allow the assignment to occur.)
As a note I have the combo box set to show the first name of the individual but select both the first name and ID
the code is below for the row source:
SELECT [Hosts].[First Name], [Hosts].[id] FROM Hosts;
If someone could tell me how I could get it to show multiple things within the combo box that'd be great.
IE Right now it shows just a first name liek Alex, Rachel, Bill. I'd like it to show the first name as well as the last name and a third additional field. IE Alex Lastname T1 or Rachel Lastname F4 etc.
Can anyone show me the general code to do this? I do know some VB but I'm used to using SQL with PHP primarily. I am new to access and only know it vaguely. Thanks in advance!
Modules & VBA :: Display Amount Of Records On Form
Jul 1, 2013I want to display on my form the amount of records and which one the form is displaying just like that, which is at the bottom, in text fields.
View 4 Replies View RelatedModules & VBA :: Create A Loop To Add New Records And Decrease Amount By One?
Apr 13, 2015I have the following code that works fine:
Dim db As DAO.Database
Dim rs As DAO.Recordset, i As Integer, ii As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("PatientPrescriptions1")
ii = [RefillAmount]
For i = 2 To ii
[Code] .....
However I am trying to make it decrease the value in [RefillAMount] each time it loops through the addnew function and I can't figure out how to do the rows keep saying the same number:
If I put Refill 3 it creates 2 extra rows and all these rows now say Refills = 2. What I want it to say is
Refills 3
Refills 2
refills 1
And end there. how to do this?
Forms :: Calculations In Subform - Display Total Amount For All Records
Nov 20, 2014I am trying to get the main form to display a total Amount for all records in Purchase order details but the control displays Error:
I have a Form called frmPurchaseOrder with a sub-form sbfrmPurchaseOrderDetails
sbfrmPurchaseOrderDetails takes item details from a table based on what is selected in the combo box then fills out four additional fields in the same sub-form.
SELECT tblItemListDetails.ItemID, tblItemListDetails.ItemName, tblItemListDetails.ItemSize, tblItemListDetails.ItemUnit, tblItemListDetails.ItemUnitCost, tblItemListDetails.ItemBrand FROM tblItemListDetails WHERE (((tblItemListDetails.Supplier)=[Forms]![frmPurchaseOrders]![SupplierCombo]));
Private Sub cmbItemName_Change()
Me.txtSize.Value = Me.cmbItemName.Column(2)
[Code] ....
Queries :: Count Amount Of Records That Fall In Each Month In Date Complete Field
Sep 13, 2013i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
View 1 Replies View RelatedModules & VBA :: Possible To Export Select Records And Fields In Those Records To A Specific Location?
Jun 15, 2013In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
Getting Query Criteria To Select All Records Or Specific Records In Query Design Section?
Jun 16, 2014How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
Select Top 2 Records
Jun 12, 2007Hi,
My question 'sounds' easy, but i can't think of a way to do this. Please help!
Ok, i have an 'employee' history table with 200K records. Each employee could have multiple records with different 'effective' dates(please see below for an example of two employees' records). What I want to do is to run a query where it will pick up ONLY the top TWO records of each emplyee. How would I go about doing this?
Thank you.
Joe
Employee History Table:
SSN--------------------EFFV_Date
123-45-6789---------------01/02/2006
123-45-6789---------------12/03/2001
123-45-6789---------------08/22/2000
222-33-4444---------------05/28/2003
222-33-4444---------------07/01/1995
222-33-4444---------------06/30/1990
Query result should be somthing like this:
SSN-----------------------EFFV_date
123-45-6789---------------01/02/2006
123-45-6789---------------12/03/2001
222-33-4444---------------05/28/2003
222-33-4444---------------07/01/1995
Select Last 3 Records
Dec 20, 2004How can I restrict a continuous form to select only the last three records
any help would be appreciated
Jabez
How To Select X Random Records
Jun 20, 2005I need to select 200 random customers from my table, how can I do that?
Table: tblCustomers
PK: CustID
Select Range Of Records
Sep 1, 2005Is there a statement that will allow me to select a range of records in the
middle of a selection of records? e.g. Rather than TOP 10, can I get like
records 10-20, or 50-65 etc?
Prodigy.
Calender To Select Records
Oct 24, 2006I was wondering if anyone has seen a easy way to use a calender to select records rather than having to type in a "between such and such a date".
Because what i have now is a qry that pulls data from a table between two dates. And i would like to make it more GUI rather than typing to prevent user imput error if you understand what i mean.
I tried searching but didnt know what to search for.
Thanks for anyone that has any input!
Regards,
Jon
Using A Form To Select Records
Jan 8, 2005Ok I am probably goining nuts by now. I could have swore that I had a referance book that showed how to set up a form with multiple combo boxes that were used to filter forms/reports for desired records. Now I can't find that referance anywhere! Does anyone know how to do this or can they point me to a referance with this information? Please help me, this is one of the final touches I need for a project I have been working on for months.
View 6 Replies View RelatedListBox To Select Records... Please Help
Aug 5, 2005Hello All...
I currently have a ListBox which I am using on a form as a "jump To..." record selector. It has to show 4 values to the users, and so far it has worked quite well. However, I recently added some queries to the footer of the form to help narrow down the data for searches, or if the user wants to see all open data of a certain topic. I'd like to have the ListBox "refresh" and only display the results of the query, but I cannot for the life of me figure out how to do it. The root of the problem seems to be that the initial SQL for the ListBox is selecting values from the under-lying table, and when the filter is applied, it simply doesn't care. When you click on entries that aren't in the filtered dataset, it doesn't error out or anything, and when you click ones that ARE in the filtered dataset, it does work properly... I'm close... How do I filter the listbox as well?
Thanks,
Group And Select First Records
Feb 9, 2015how I would write a code that will recognize information by same “ID” and then only take the first 6 years of that information. Each row has an ID and a corresponding year with other information associated with it. The data looks similar to this:
ID Year Avg HR
JohnSmith 1988 .789 78
JohnSmith 1989 .854 85
JohnSmith 1990 .456 85
TomJones 1978 .465 56
TomJones 1979 .165 85
Select And Write Keys From Two Records
Jun 7, 2007Hello All,
If I want to select a row from subform A and a row from subform b and click a button and have the keys stored in another table what would be the best way to do that?
Thanks.
How To Select Records Starting From Number?
Dec 2, 2005Hi!
Can anyone help me how to create query selecting only the records starting with the numbers?
Thanks a lot in Advance
Returning Select Records In Query
Mar 30, 2006Hello, I am sure this is quite simple, however I cannot figure it out. I have a query that shows tblPartNumber and tblAverage. The average is calculated in the query by taking the total minutes to assemble the parts, divided by the number of parts completed. What I am trying to do is run a query that will show me the most efficient (lowest average), time for each Partnumber. Ideally the query would show me something like this;
Part # Average
1234 .72
1234 .86
1234 .94
etc...
I searched on here and it seemed like the Top 10 is used, but in my application that ommits all of the other partnumbers because it only shows ten of the records for the first partnumber. I only want to see the five most efficient averages. Any help is greatly appreciated.
Thanks, Tim