I have a large data set with values ranging from 1-25,000. I would like to be able to calculate how many entries there are in each increment of 10 (1-10, 10-20, etc.), then perform the same query for each increment of 100.
Any ideas?
I have a query that will sort groups of zip codes using the following expression: Between [T_ZipRange]![StartZip] And [T_ZipRange]![EndZip]. The first and last zip codes are entered and the expression returns the zip codes between the "start" and "end" range. The problem is that if a zip code is only 5 digits: xxxxx- instead of xxxxx-xxxxx, the zip code will show up in other sorts. The zip code field is a text field. How can I limit the sort, based on the number of digits in the zip code?
View 2 Replies View RelatedI have a Size Breakdown with 8 Buckets..I'd like to Sum the contents of the buckets and divide that to Total Yards then have the result saved to a field(Per Garment) in a table (Marker Details Table).Per Garment = Size Breakdown / Total Yards (25 / 7 = 3.57).
View 5 Replies View RelatedI have a database with a code field which contains alfanumerical values like:
IB1, IB2, IB3...IB180, and so on.
When I sort the usual way A-->Z, I obtain:
IB1, IB10, IB100, IB101...IB109, IB11, IB110, IB111
How can I sort nicely to obtain IB1, IB2, IB3...
To complicate things, there are alfanumerical values with a different number of letters before the figures: like F1, F2... and SER1, SER2 and so on.
Is there any plugin to nicely sort those codes?
thanks in advance
Romain
:confused: :confused: :confused:
Please bear with me. I'm new to using access and plodding along little by little and I had little luck finding relavent past posts. I have a table with the column headings and a couple of records listed below. Each of those records has a subdatasheet pertaining to data for the commands. For every command, my dept does a survey or inspection every so many years. How often we do these surveys depends on the HAZ CAT. If the command has a HAZ CAT of I, they have a survey done every year. (HAZ CAT= II, every 2 years; HAZ CAT = III, every 4 years). On a form, I want to be able to generate the next serveral survey years (let's say for the next 10 years) based on the command's most recent survey year and its HAZ CAT. So for each command, there should be a listing of all its survey years. Then I want to be able to sort by year so I'll know what commands will need to be surveyed each year. I'll eventually have a form which will be my control panel. On the control panel will be command buttons labeled with years for the next 10 years. The button should open up a form with the records for the respective command information.
[CommandID] [Command] [HAZ CAT] [Last Recent Survey]
1 AIMD I 2004
16 DODDS III 2002
Hi All hope some one can help with this.
I have a sub for which is set to sort by ascending order for a given field which happens to be a text field with vales set something like abc1.1,abc1.2 ect. The problem is, is that when you get to abc1.10, and more these are then sorted as so
abc1.1
abc1.10
abc1.11
abc1.2
abc1.3
ect
How can I stop this and sort it:
abc1.1
abc1.2
abc1.3
.......
abc1.10
abc1.11
Thanks for any help with this
Paul
Hi there, thanks in advance for any help or input with this. I am working with tables that have annual data on insurance policies that are effective for 1 year. I have Policy number (PolicyNum), current premium (CurrentPrem), the date the policy went into effect (PolicyEffDate), and cancellation (CXDate, which is 1/1/2001 if the policy was not cancelled). I am trying to earn out the CurrentPrem into monthly buckets. The tables are by the effective year going back to 2004, so basically 2004, 2005, 2006.
I'm not sure how to go about tackling this and have tried a number of different ways. I sense that I will need a query that will have 24 fields for all the months over a two year period that any given 1 year policy can be effective over. For instance, a policy created on 5/1/2004 (May 2004) is effective until 4/31/2005 (April 2005), and a policy created on 12/31/2004 is effective until 12/30/2005.
So for every month between January 2004 and December 2005, there needs to be a value generated (either zero or a monthly premium value).
Attached is a sample table with data. Really all I've been able to do is calculate how long a given policy is in effect for (how many months) and then how much the monthly premium is. I cannot figure out how to appropriate the monthly premiums to the corresponding months a policy is effective for.
Note that the current premium value takes into account cancellation which implies that the monthly premium for a cancelled policy is not 1/12 * [CurrentPrem] but 1/[MonthsInEffect] * [CurrentPrem].
Im trying to sort a form on a date. it only has dates put in once a confirmed date is known so when i sort all the blanks come to the top is there a way of sorting excluding null values
View 9 Replies View RelatedI've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
e.g
Vehicle Sort Order
UNT/FL 500
FL4/45 400
E7/F5 600
UNT/FL 500
I want as
FL4/45 400
UNT/FL 500
UNT/FL 500
E7/F5 600
I currently have records that end with a letter and 2 numbers. For example, A1, A2, ... , A10, A11. When I try to sort my table/query by these values, A10 & A11 come before A2. It seems that it is sorting by the first digit shown. Is there any way to fix this quickly within table/query properties so that this can be displayed in proper numeric order?
View 7 Replies View RelatedI'm trying to make a report that a untrained user can use to review the relevant data from the database I'm creating. All of the work I've done so far has been directly in the tables . Here's a quick outline of my general data organization:
Department:
DepartmentID(PK)
DepartmentName
Employee:
EmployeeID(PK)
EmployeeName
DepartmentID(FK)
Needs_Work (check box)
[code]....
Basically, I want the user to select a department to view using a combo box or option group (which I'm not sure of how to do in a report). Then, I want to view the employees within that department sorted first by whether or not the Needs_Work check box is selected, and then by how many of their competencies are unchecked.
Hi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.
Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..
I'd apreciate some help with this :) Thanks
Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
I have a query that returns the following values:
Group_Name
Group_Priority
Person_Name
Amt_From
Amt_To
Branch
ID_Number
How can I select from this to get the top 10 'Amt_From' values within each Group_Name, in one query?
I can get the top 10 overall, and I can find the top 10 by specifiying a Group_Name value, but I'm hoping to find all in one query.
Any ideas gratefully received.
I think I am missing sth here, after I set my new group then I create my new user, then what! How can I specify which user to login with? Will the user be promoted for a username and a password?
View 3 Replies View RelatedFor a little light relief I thought I would delve into the joys of security groups and immersed in searches and reading material. From what I have learnt so far, yes FE / BE best approach and take time, as obviously easy to get wrong and difficult to undo.
Not looking for the answer, just a hint that I'm on the right track
The FE groups settings is where the bulk of the detailed work is required?
The BE groups settings are to ensure the correct read / write access to the tables?
I am thinking of making FE groups as BE users (smart or dumb) to simplify this end?
Any encouragement / early warnings appreciated
Hi,
I have a dataset with an age field which just stored an age. I want to run a query which will calculate the frequency based on all age groups 20-30,30-40.
Does anyone have any ideas
I have created a work order application, and the process is that it takes total number ordered of an item, divides it by a factor, and then prints x number of work orders, example qty ordered 300 = 5 work orders, 4@72 and 1@12, my question is, now I want to appended the work order number with a count number, example wo555-1, wo555-2 and so on, grouping on the work order number, so each work order start again at 1. I am stumped, can anyone help.
View 3 Replies View RelatedtblCountries -- CountryID, Country
tblAthletes -- AthletesID, FirstName, Surname, CountryID, M/F
tblSports -- SportID, Sport
tblEvents -- EventID, Event, VenueID, SportID
tblVenues -- VenueID, Venue, Location
tblMedals -- MedalID, MedalType
tblResults -- ResultID, AthleteID, MedalID, EventID
Primary Keys - Bold
Foreign Keys - Italic
This is the tables in my database, it is a database of the upcoming Beijing Olympics.
I am aiming to have a medal count for each country which is in the current database.
As it has the capabilities to expand therefore I am trying to avoid the need to create a whole new query for each country I add, also it would be preferable to be able to have all of the countries on the same report, so that I can compare the totals.
Is there some way I can count the total of each group of records (each country) for the field where 'MedalType' = "Gold", "Silver" and "Bronze". Also the calculation needs to be done before the report so that I can order by the total.
I am new to this so can answers be preferably in layman's terms.
Hello Everyone.
I have a form that gets its data from a query.
There is a field in the query called "FinalisedDate" which is a date/time field. I want a button called Current that when pressed it shows all records where the FinalisedDate field is Null which is to be the default of when the form is open.. Another button called "Aged" is pressed it only shows records that have the FinalisedDate field has a value (i.e. not null). I want another button that shows All Records (i.e Current and Aged).
I hope anyone can help. I have searched the forums but did not find a similar problem that I can copy.
Regards
This is a multi-faceted question may the answer to the first may help the second.
I have two option groups each with two options in each.
The first is called Frame 57 and has two options A ( the wizard assigned a value of 1) and B (assigned a value of two).
The second is called frame 58 and has two options C ( the wizard assigned a value of 1) and D (assigned a value of 2).
I have set enable on Frame 58 to No.
My aim:
If I choose A in Frame 57 then I want Frame 58 (with both options) to become enabled.
If I choose B in Frame 57 then I want Frame 58 to remain un-enabled.
When writing the code do I
Put it in the Click event of the FRAME?
and should i refer to the option groups by their name (ie: A or B) or by their assigned value (1 or 2).
This is what I have at the moment
Private Sub Frame57_Click()
If Frame.57 = 1 Then
Frame58.Enabled = True
Else
Frame58.Enabled = False
End If
End Sub
This seems to work though when I return close and re-open the form Frame 58 (even if it was chosen and enabled) is now not enabled even though the option is Frame 57 is correct and visible.
Hope this makes sense - any help appreciated
Hello all,
I need to randomise participants in an Access 2000 db into four groups. Let's say these are 1, 2, 3 and 4. This in itself is not the problem. The problem is that I need to keep the numbers of participants in each group approximately equal, say to within a tolerance of 4.
Can anyone point me in the right direction please?
Thanks very much.
please does anybody know how i can know inside my code what is the Group of my actual user???and what is the current user ?...
View 6 Replies View RelatedI want to import all the objects from a databse, and there are loads of object shortcuts.
Any ideas?
Hello!
I have a problem figuring out a solution for following problem.
I have a database of details and their properties.
All the details have been described in one table.
As there is a need to describe groups of details I have found myself in a hard situation that needs to be resolved.
The structure of described detail groups is as follows:
Product
Detail Group1
Detail groupx etc.
Detail4
Detail groupx etc.
Detail groupx etc.
Detail1
Detail2
Detail3
Detail Group2
Detailx
Detailx
Detail groupx etc.
Detail groupx etc.
Is there a way of decribing my products in access so that there are no limits of how many groups does a product have and how many levels of groups the product have.
Thank you
Hi all,
Simple question (I hope)...
Need to devise a way of updating a field with an ascending sequential number within a group of records of similar type.
e.g.
Say I have records which consists of numerous IDs and various job descriptions within the each ID
13000 Head of Maths 1
13000 School Secretary 1
13000 Head of Maths 2
13000 Head of Maths 3
13000 School Secretary 2
14000 Head of Maths 1
So in this case each 'Head of Maths' and 'School Secretary' sequentially increments until there is a new ID.
Any help appreciated.
thanks,
Alex
I'm trying to create a form for entering data for new items for a warehouse inventory. So far, I've figured everything out, but now I'm stuck.
I want to create an option group for two separate snippets of information: Whether a product is damaged or not, and whether a product is in numerical order or not.
I have created two bound option groups with option buttons, one for "Damaged?" (Yes or no) and one for "Sequential?" (Yes or no).
When I open the form, however, only the "Damaged?" buttons work. I can click either one, and it changes from yes to no and vice versa. However, the "Sequential?" options do not work. It doesn't matter which option group I try to click first, I cannot select "Yes" or "No" under "Sequential.
Any input would be greatly appreciated!! Thank you!