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.
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?
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
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.
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 have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -
I have units A, B, C Parking units p1,p2,p3,p4,p5,p6 Storage units s1,s2,s3.
Unit A uses parking units p1, p2, p5 and storage unit s2.
Unit B uses parking unit p3 and storage unit s1.
Unit C uses parking unit p4, p6 and storage unit s3.
How do I write a query/report that would show:
Unit Parking Storage Unit A p1, p2, p5 s2 Unit B p3 s1 Unit C p4,p6 s3
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
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?
For 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?
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.
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.
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.
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.
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.
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?