Counting Frequency Of Numbers And Comparing To Frequency Of Another Set Of Numbers In Same Row
Oct 28, 2014
I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zeros and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zeros preceding it identify that person.
For example Joe would be identified below because he had 6 consecutive zeros and then he had 5 weeks of numbers immediately following the string of zeros. Bob would not be identified because he had 5 consecutive zeros and then 5 sets of numbers immediately following the string of zeros so the zero frequency isnt higher than the number frequency immediately following.
ID
Name
Date of Hire
1
2
3
4
5
[code]...
View Replies
ADVERTISEMENT
Aug 14, 2005
Hi all,
I am wondering if it would be possible to do this:
Use a query to create a list of items from the column (duplicates removed) ordered by the frequency that they occur.
For example the column lunch
-------
|lunch|
|-----|
|pizza|
|kebab|
|tacos|
|pizza|
|tacos|
|pizza|
-------
Would end up like this:
-------
|pizza|
|tacos|
|kebab|
-------
Thanks in advance :)
View 2 Replies
View Related
May 2, 2006
Hi, am sure this is simple....! Can't find a solution.
I have records which have YEAR and APPARATUS as two of the fields. All I want to do is write a query which displays all the records chronologically by YEAR then within each YEAR to group them by the biggest subset according to APPARARTUS.
Therefore if there were 20 people who used Motorised equipment, 15 who used electrical and 10 who used gas, the records would be grouped in that order. Thus showing the pattern in change in use of specific apparatus over the years.
Why can't I DO THIS!!??
aLI
View 3 Replies
View Related
Dec 27, 2007
Hi everyone.
I have a pretty basic database with about 8 tables linked together.
The database is going to be used for keeping an electronic record of claims for upto 5000 people.
I have been asked though to have box on the menu screen which shows both the number of claims and the number of batches (where the claims are put)
All i require really is a way to count each individual number in a coloumn
EG
abc123
abc123
abc124
abc125
number of batches 3
any ideas?
Kev
View 12 Replies
View Related
Aug 2, 2007
I am pritty new to access and have been tasked with creating a database !
I have two tables one called main which has 26,000 claims (listed under there part number).
The other table called part numbers is a list of all the part numbers in the main table but with no duplicates (126 part numbers)
I need to somehow count how many times a part number apears in the main table. Maybe display a count in the part number table as a extra field ? then i could put this field into the form i created to browse the parts numbers (to show how many times the part number appears in the main table)
View 12 Replies
View Related
Feb 12, 2007
Hello All!
I'd really appreciate some help for what I fear is actually a very amature question...any assitance would be greatly appreciated!
I have the following table:
SubCount---------HomeDetailsID------FoodDetailsIDFK--PrimaryName
Ruteete--------------199------------------54-----------Amatehe
Ruteete--------------218------------------54-----------Amatehe
Ruteete--------------199------------------54-----------Amatehe
Ruteete--------------198------------------90------------Amoozi
Ruteete--------------204------------------90-------------Amoozi
Ruteete--------------192------------------20----------Banana Juice
By Subcounty, it shows what homes in the subcounty are consuming a particular food item. What I would like to do is count the number of times that food item is being consumed in each subcounty and generate a new table to compile this data.
And I am trying to use the count function to return the frequency of each Food being consumed. However, the count function counts all the records and returns a value of 6. What I would like is it to count the frequency of each food and ultimately build the following table:
SubCounty---NumHomesFreqBasedOn--FoodDetailsIDFk-PrimaryName---Freq
Ruteete----------------6------------------54-----------Amatehe------3
Ruteete----------------6------------------90------------Amoozi-------2
Ruteete----------------6------------------205--------Banana Juice----1
I was thinking that this may involve using a loop to get a count on each specific food but the logistics of doing this are beyond me at the moment. Does anyone have any suggestions?
Thanks!
View 3 Replies
View Related
Aug 12, 2014
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
View 14 Replies
View Related
Jan 10, 2007
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies
View Related
Aug 24, 2014
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1
1 2
1 3
2 1
2 2
2 3
2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
Example
1 1
2 2
3 3
2 1
2 2
2 3
View 5 Replies
View Related
Apr 20, 2006
I am trying to write a function along the lines of the following:
If Me.Text1 > 0 but < 2 Then
Something happens
Clearly the top line isnt correct, so how do I write a function like that? Thanks
View 2 Replies
View Related
Dec 11, 2007
My brain has locked up, I just added a field to an existing db and I need to to hold exactly 8 intergers / numbers; typically the first few numbers are 0's, but the 0 do not display; still using 97, what is the field size/format/ going to be?
Thank you in advance
View 1 Replies
View Related
May 16, 2006
Not sure if this is easy or not, I have searched the forum but am not finding what I am looking for. I need to see if there is a way, in a query to extract data specific to either even or odd numbers. So if I have a field on a table with data like this:A01AA02AA03AA04AA05AA06AI want to pull just the even or odd numbered values.Any ideas??
View 5 Replies
View Related
Jun 15, 2007
Is there a way to write a query where it will only capture numbers? For example, if I have the following:
"Testing transactions 11100202020 in the following order"
What I would like to do is capture the numbers that is in the middle of the sentence. I have tried using the len, left, mid, right functions in my expressions, but the sentence length may change from time to time. Is ther a way to tell access to only capture the numbers?
Thanks in advance
mlr0911
View 14 Replies
View Related
Sep 2, 2004
The database that I will be referring to in this querstion ws developed to help track interviews at a local helth care facility (dates, times, location, ethnicity, age, state etc.)
I am trying to develop a few queries to pull certain information out this database. Specifically I am trying to pull out information in NUMBER or % format. Currently I can only figure out how to pull out the information that is currently listed. For example: We have had 57 interviews come into the door this month. All of these inteviews are going to different areas and they all are of different ethnic origin and all have an age difference. When I go to query how many total interviews we have had, I am getting names and not numbers. When I try to query how many different ethnicities have been interviewed, I still get names attached to what ethnicity they are.....so on and so forth. What I would like to do is turn these names into statistics or numbers. Is there any way to query this information and get it to come out in number format? The following is an example of different metrics that I am trying to get from this database.How many total out of state interviews per monthThe number of interviews per state per monthHow many african americans interviews peer per month, how many pacific islander interviewed per month, how many hispanic interviews per month........etcHow many internal candidates iterviewed per month/How many external candidates interviewed per month.The information entered into my fields are not entered in a numercial format. They are intered as names, places, dates, times, etc....My question is can I query this information in such a way that I can generate a report that would allow me to view it in numerical formatAny help will be GREATLY appreciated. Thank you soooo much!!!!!
View 3 Replies
View Related
May 4, 2005
Hello,I am new here and new to Access.
I have started to build a database that will be for storing the results of horse races.
However my problem arises when inputting the weight a horse carries.
The weight can be input as, for example, 11.12 or 11.00 or 10.10 however whilst I have no problem with 11.12 the other two appear as 11 and 10.1 is there any way round this.
Thanks for any Help
Treggy
View 3 Replies
View Related
May 5, 2005
hi,
I have a table that has results from races, eg.
Race No, Racer ID, Time taken.
i want to have a position field that will say which position the runner finished in. This is complecated as there are many different races.
At the moment the best thing ive been able to do is set up a query with a parameter on the race no. then the time taken is put in order and i have to manually put in the positions.
Manual is bad!
does any one have any idea about how i could make this automatic.
Thanx,
MAtt
View 1 Replies
View Related
Jul 6, 2005
Tried to search for this, maybe I am searching the wrong term.
Anyways.. I have a table linked from a large mainframe, and what I am trying to do is trim any numbers that are 1 million and over, and still keep it a number.
Currently I have this:
VEHICLE_KMS2: IIf([VEHICLE_KMS]>999999,Left([VEHICLE_KMS],6),[VEHICLE_KMS])
What I need is to have it remain as a number, seems that the left function turns it into a string value.
Thanks
View 7 Replies
View Related
Jul 7, 2005
I have 8 databases (A97) (don't even ask me why) they are identical.
I want to combine all the tables from them into one big table.
There are two tables 1 called Transactions and it has a transactionID (number) (parent of DetailID) and the other table is called Detail and it has a DetailID (number) (Child of TransactionID).
The problem is in all 8 databases the transactionID is an autonumber starting at 1 so I will have duplicate TransactionID & DetailID numbers. Each of the databases comes from one of eigh different "Regions" of our company and we assign Region numbers I can use as a prefix to the transID/detailID.
I tried to put a "25" (region#) in front of the one of the TransID# by using the format property in the table design and that seems to act as some sort of gost because while you can see it in datasheet view when you click on the cell the "25" disapears and all you can see is transID#1...#2....and it also gets dropped on paste append to the new combined table.
The question: is there an easy way to put a region number prefix on transID# so I can merge all 8 transaction tables together into one combined table?
Thank you
View 3 Replies
View Related
Aug 18, 2005
I am looking to create a make table query to eliminate some portions of numbers from a field.
i have 23899999999
I need to parse 899999999 into a new field.
Is there a formular for that. Do I use a LEN formula.
View 2 Replies
View Related
Aug 30, 2005
Hi,
Can you please help me out--give me some guidance-if you have knowledge about random numbers. Say, for instance, I have 2,000 records and I want to randomly create a final list with 800 of these records, how do I go about it? Thanks.
View 2 Replies
View Related
Jul 7, 2006
Dear all,
If some have the code for converting amount to words in rupees, please help me...
Thanks in advance
Thanks:(
View 1 Replies
View Related
Jul 7, 2006
Dear all,
If some have the code for converting amount to words in rupees, please help me...
Thanks in advance
Thanks:(
View 4 Replies
View Related
Oct 16, 2006
Soon I'll be starting a new DB which will be used to issue receipts to people who attend courses run by a business. I don't want or need it to be very complicated so was thinking a something like the following;
TblClient (ID,client name & address fields)
TblCourse (ID,Date, course name & Price fields)
Each client will eventually have numerous (many) Course records because they often complete more than one course, over many years.
When I issue clients with Receipts I want each receipt to have a unique receipt number.
On each receipt I will need the Client name, Price paid, date(s) & course name(s) with the unique receipt number. How can I impliment the receipt number?
View 1 Replies
View Related
Apr 6, 2007
Hello,
I maintain an Access database at my workplace, and have been asked to bring in information from other company locations. Unfortunately, there are conflicts; e.g. part number 12345 might be something totally different at another facility than it is here. My first instinct was pretty obviously to add a Location column and all queries would include that as well as the ID. However, someone else is telling me we should build metadata into a new part numbering convention. An example:
PlantA.12345 (all one field) would be a totally different part than
PlantB.12345
He says it would be very beneficial to ensure that, seeing only a part number, you would know where it was from. I understand his point, but it just seems like strange database design.
Has anyone done something like this, or considered it??
Thanks in advance.
View 6 Replies
View Related
Jan 8, 2008
I am trying to get Access to round numbers to the third decimal place and I can only get it to round up to the nearest whole number. I have used the help function and tried several things but can not get ACCESS to round as I need it. Any HELP is greatly appreciated!!
Thanks,
DOUG
View 5 Replies
View Related
Feb 15, 2008
Hi everybody. It must be simple answer i guess.When i sum numbers of fields where information is entered by myself, it is ok.When i sum numbers of fields where fields where information is formated by IIf function, i always get 0. SQL clauses are ok, problem elsewhere.
View 3 Replies
View Related