I have a database with around 5083 records and I am trying to find the three most common numbers called in any one record such as 5 22 and 39 has been called together 50 times
Here is the structure # 1 through 5 is a number between 1 and 39 but never duplicated in same record
Record 1 |draw#|Date|#1|#2|#3|#4|#5|
Record 2 |draw#|Date|#1|#2|#3|#4|#5|
Record 3 |draw#|Date|#1|#2|#3|#4|#5|
Record 4 |draw#|Date|#1|#2|#3|#4|#5|
Record 5 |draw#|Date|#1|#2|#3|#4|#5|
Record 6 |draw#|Date|#1|#2|#3|#4|#5|
Record 7 |draw#|Date|#1|#2|#3|#4|#5|
and so on for 5083 records
Is it possible to create a query that will display the top x (say 10) records for each code in a set?
The record set that I have includes a country id and some quantity information. In short the record looks something like
Country Code (approx 30) Service Code (approx 20) Qty
What I would like to do is to aggregate all instances of a service at country level and provide a sum of the service count. Then, I am only interested in the top x services (biggest quantity) and am looking for query output along the lines of
Country1, Service Ranked 1 , Qty Country1, Service Ranked 2 , Qty ... Country1, Service Ranked 10 , Qty Country2, Service Ranked 1 , Qty Country2, Service Ranked 2 , Qty ... Country2, Service Ranked 10 , Qty
down to CountryX, Service Ranked 10, Qty
Can anyone help me here, I have tried top x etc. to no avail
i am making a contacts database. I have a table to hold the names and addresses of 700 people. I have another table holding the names of the mailing groups those 700 people can be in. One person can be in multiple mailing groups.
i made another table, and i typed in all the people and the multipe groups they were in. For example, a there would be seven records for a person if they belonged to seven groups
then i made a query relating that table to the addresses table. I want to be able to open forms to show an individual group, and cycle through the people in that group only.
i can do this, but access wont let me edit any of the records.
is there somthing wrong with my relationships? how do i tell accesss to allow records be in multiple groups?
There are thousands of records, unique by the Prod WH combination, across 5 districts. What I want is to pull the (highest) Top 3 Aged Inv$ values (Prod WH) for Each district.
Hello, I am wondering if it is possible to have several combo "boxes" in one form. The form is based on one table. In the table there are several fields which use a look-up (combo) drop down box to choose from: Type, Description, Manufacturer and Location.
In the form I want a combo box for Type, to bring up specific records in a subform, then I want a combo box for Description to bring up another set of records (within that Type of equipment) and a third combo box for Location to bring up all equipment within that location.
When I have tried to set this up - it changes some of the data in the in the subform - which changes it in the underlying table. If it set the form to open in New Record, then nothing shows up in the drop down box. Is there some If, THen code I could use to make it look first in the Type, then in the Description field, then close those out and look for the set of records that match in the location field?
Does that make sense?
I know how to manipulate in MSAccess templates, etc., but I don't know much about the underlying codes.
Hi, I have a personell DB an I vant do be able to select only the persons from a specific "city" with a specific " genre" and a specific "skill" and also i want the query to "ignore" one of the criteria if I press Enter or input all. I tried with "query design " and I don't seem to make it work for more than one condition.
I have built a query that lists all the members in a group aged 15 to 18. Now I need to build another query that counts a subsection of that group (the number who gained employment in the quarter) and express this as a percentage of the whole of the 15 - 18 group.
I presume I can use the original query to get the number of 15 - 18 year olds. I can write the query to calculate the subset of 15 - 18 year olds who gained employment, but how do I get the total numbers of the groups and express them as a percentage?
I have a table of order lines. Each line of data the order number is repeated for every line. I need to just pull out one line for each order number. I assume a query would do this but I don't know how to create it. The order number is numeric. Can some one show me the way?
Hello, I have no idea what to search for to see if someone else has already asked this question. I've tried several things with no luck. So here goes...
I work in a library. We deal with thousands of journal subscriptions--we cancel our subscriptions and order new journals all of the time. Every paper journal we have is assigned a unique number, called a ZP number. When we throw out a journal, that ZP number can be assigned to a new journal.
I would like to find the earliest missing number in the sequence without having to check manually. Is there a way I can do a query or something to find missing numbers in the sequence? The ZP numbers start with 1 and go up to 9999.
So, I have two fields: Title and ZP#.
How can I do this?
This is a cloned table, used solely for assigning ZP#'s, so it can be altered in any way--including adding records with blank titles for each missing number.
I've searched the forum and only found one thread which covers this but doesn't help me.
I have a table with peoples individual information in it.
Every person has a unique ID number.
I want to find the missing ID numbers when I delete a person so I can keep the numbers in sequence.
I am not using autonumber. The reason for this is coloured wristbands. Each wristband has a unique number and they are broken into 4 colours. each colour has a number range 1 - 300, 301 - 600, 601 - 900, 901 - 1200. Each person in the table has to have an ID number matching a wristband. The people are also broken into 4 colour categories. This means that not every range is filled before i need to start using the next range.
I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.
Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.
Dim varRange As Variant Dim lngLow As Long Dim lngHigh As Long Dim lngCounter As Long DoCmd.Hourglass True
[code]...
I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.
Public Function FindNum(strName As String) As String Dim strTemp As String Dim i As Integer For i = 1 To Len(strName) strTemp = Mid(strName, i, 1) If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then FindNum = Right$(strName, Len(strName) - i) End If Next i End Function
how to alter the code to make it work for my specific situation.
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.
I'm creating a simple relational database of all the attorneys our company work with. I'd like to have three tables: "Law Firms" (firm name & other info), "Attorneys" (what firm, personal info, and what practice groups s/he is involved in), and lastly "Practice Areas" (all different practice areas with codes).
Now, my issue is that people are usually involved in multiple practice areas. Thus, I can't just put a code for one practice area in "Attorneys" table, but I can't put multiple codes in either in one field (right?) because it'd mess up the relations.
What I'd like to accomplish is for people to be able to go to our intranet and, using a form (?), select a law firm and a practice group and get a list of attorneys.
Does anyone know of a good way to solve this? It doesn't have to be pretty, since the DB isn't going to be huge (2000-2500 records).
I am trying to do is to make a for loop to insert multiple text fields in on table.
Depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that I'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.
Code: For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr) SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _ "KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _
EmpName GroupNum Jon Group1 Sam Group2 Tom Group1 Bob Group1 Hal Group2 Dan Group3 Cal Group2
With sample table above, can I write a query in Access that lists the data by GroupNum based on my criteria, say Group2 1st, then Group3, then Group1? Basically, how do I make the result show:
Sam Group2 Hal Group2 Cal Group2 Dan Group3 Jon Group1 Tom Group1 Bob Group1
In my table, a column contains different fac# like fac-0086, fac-6200, and fac-0049 (they can be duplicated). These fac# can be categorized into 4 groups. Each week I need to load a list of records from spreadsheets into my cumulative table. The original spreadsheets have fac# but no group code. If I want to group the records by their fac#, how should I deal with it?
OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:
At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:
If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.
My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so
I am designing a database which keeps track of door access levels at a college, using Access 2010. A door access level is programmed to a key card, which grants access to a number of different doors throughout the college.
So, what I have is an "LevelID" and a "DoorID", where each LevelID has zero to many DoorIDs associated with it, as well as each DoorID has zero to many LevelIDs associated with it.
example table:
LevelID DoorID 1 1 1 2 1 5 1 6 etc..
All the data has been inputted into Access 2010 successfully, however I am having difficulty in developing one of the main functions of the database..
What I want to do is have a checklist of each DoorID displayed on a form, and when any combination of DoorIDs are checked, Access will search to see if a LevelID is associated with that combination. This information can tell me whether a new LevelID is needed to be created.
I've had to export data from an old third party database to Access with the task of reporting on the data. I have a client table and work tables, one for each year going back to 1992. In the original database, the clients do not have a date of when they were set up. I need to find out how long clients have been with us, so I thought of going back through the work tables 2006 to 1992 and finding out where they first occurred, by looking for duplicate records in the tables. Is this a simple enough thing to do? The client table and work tables are linked through the field Client.
Can't think how I would do this. If I create a query with a certain date then it will need to be changed every time the query is run. How would I write a query that automatically looks for a date 3 months or more old.
I have a table, and there are many records, each with a unique value in the OrderNumber field
I have a text box on a form, and i want it to work so that the user types in a OrderNumber value, and it loads that record into the form (the form is bound to the table)
I already know how to load a record by record number, but how can i do it by OrderNumber value?
Hello, i need to have my database find records that were entered between two dates. To do this i made a query that included the dates the records needed to be in between....[date1] and [date2]. Then i had the record dates be called [sent date]. i made my query and in the criteria for [sent date] i wrote (Between [Date1] And [Date2]) when i enter the two dates [date1] and [date2] the query does not show the records that are between that date. why is this happening? is there and easier way? another way? For example, say that i have records with their [sent dates] equaling 12/30/2004, 1/1/2005, 10/1/2005 i want to make it so that when the user inputs a records with dates [date1] and [date2], that those records will show up if their [sent dates] are between [date1] and [date2]. if [date1] is 1/1/2003 and [date2] is 1/1/2006, all of the records will show up. if [date1] is 12/31/2004 and [date2] is 1/3/2005, then only the record with the date 1/1/2005 will show up. how can i do this?
I have patients and their details all listed in onetable. I want a message box to come up if a patient also has a family memeber somewhere else in this table. To do this i need to match surname and mother's christian name. Ani ideas?
fiscal_year acct_period loc_rc account afe description amount system 2015 6 01001028 745003 100050652John A Doe -69.72 E 2015 6 01001028 745003 100086599Jane Doe -33.37 E 2015 6 01001028 745003 100086599Jane Doe -587.69 E 2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 1 33.37 L 2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 2 587.69 L 2015 6 01001028 745003 994709 AP - EXPRPT05 2115PK - John A Doe - 1 69.72 L 2015 6 01001028 745006 100048910KROGER -389.74 E
It's combining data from 2 system Identified by E or L. The data is similar but not exact & I'm comparing it using the description. What I need Access to do is find the record that doesn't "zero-out". In the example above the record it should pull is the last one "Kroger $389.74". How to do this but I do have a query/report that will subtotal based on the LOC-RC field.
