I have a table with two fields like this:
F1 F2
110 1
110 1
111 1
111 1
111 1
112 1
112 1
.....
I need to change the number in F2 like this
F1 F2
110 1
110 2
111 1
111 2
111 3
112 1
112 2
.....
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 RelatedHi 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
Hi
Can anyone help with this .
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
As always, any help would be greatly appreciated
Thanks
Andy
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 table with the following structure. I am attempting to do this in design view.
Region....District....WH....Product....Aged Inventory $
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.
Hi I obviously have a table with records in it sorted in assending order. What I need is to know how to make it add into one of the table fields the number it is in the table. IE
Name Time Position Number
Mr Jones - 12:30 - 1
Mr Evans - 12:45 - 2
So if i added 12:35 it would change mr evans position number to 3 and make the new record position number 2.
Data in table “tblEmployees”:
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
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
Thanks
Randy
Hello, I have a question. I don't really know much about access, but I am really comforatable with SQL (DB2 and MySQL Programmer). Anyways, I was wondering if there is any way to do the following in SQL.
I have a Students Table that holds the names of students, an applications table that holds different applications, and an offers table that holds offers for the different applications.
The relationships for the tables are :
One Student Has Many Applications, One Application has Many Offers
IE: One Application can have up to three offers attached to it. I have the following query running to get all of the offers for all students in a specified term:
SELECT DISTINCT Offers.tblStudentProgCodeFK, Names.StudentID, Names.FName, Names.LName, (Offers.Code), tblPrograms.ProgName, Names.[Country Of Birth], Offers.[OCAS Number]
FROM qryApplications_Offers AS Offers, [Names], tblPrograms, (SELECT DISTINCT StartingDate, EndingDate FROM StartDates WHERE Term LIKE "*"+[Forms]![frmSelector]![Term]+"*") AS B
WHERE (Names.StudentID = Offers.StudentID AND (tblPrograms.ProgCode = Offers.Code OR Offers.Code=0) )
AND
(Offers.StartDate BETWEEN B.StartingDate AND B.EndingDate);
What I was wondering is if there is any way to add a field in the output that would number the selections. Right now it returns a table like:
tblFK StdID Code
34440 20394 0112
34440 20394 0123
34440 20394 0234
34234 25847 0100
47364 34857 0111
47364 34857 0311
I would like to do something like this:
tblFK StdID Code Choice
34440 20394 0112 0
34440 20394 0123 1
34440 20394 0234 2
34234 25847 0100 0
47364 34857 0111 0
47364 34857 0311 1
Where the choice column would count the choice for that student. Is this even possible? There is no choice number in the Applications or Offers table so I would need to do this in the query....
Any help would be appreciated
Dear All,
Maybe my question is not too difficult.
I have a form-subform structure and my aim is to number certain records of the subform. These records would contain value: 1,2,...
And when changing the record on the main form, the subform's record should have the values 1,2,... again.
(These values are shown on the bottom of the subform, where we can step the subform's record.)
If I use an autonumber field, the numbering goes continously.
Please help me!
Thanks you in advance.
Sz.Cs.
I need to add a column to a sorted query which effectively numbers from 1 to N. It is intended as a ranking field for later statistical analysis.
I can do this manually by saving the query as a table, then introducing a new autonumber field.
However, I need to do this automatically, as this is just one query out of many in a large and complex setup. Is it possible to add an autonumber field to a query?
I would like to know how to code a report to add row numbering automatically?
View 6 Replies View RelatedHello,
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.
Thanks for any help!!!
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?
View 4 Replies View RelatedI have 26 different option groups all with numeric values set for YES , NO, and N/A.
I attempted code to have the code read if the frame values were 1 Or 3 for all frames then a text field would read as "Pass" or it would be "Fail" but its not working. How to link all the options groups so that if YES or N/A have been selected then it will generate a PASS but if any of them selected NO it will be a FAIL.
I have a series of records that I would like organised into groups.For example everyone with the surname Jones who lives in Wales and is between 18-30 years old.I would like a Combobox that has that group sorting visible in some way, possibly with colours.for Example if i had the records:
First Name Surname Country Age
Dave Jones Wales 24
Bob Jones England 28
Fred Jones Wales 19
Jim Jones Wales 22
Ian Jones England 29
Frank Smith Scotland 32
I would like a Combo box that shows the names Dave , Fred and Jim with a background colour of Red then Bob and Ian with a background colour of white and Frank with a background colour of blue.how I group (maybe you can add spaces between the names Dave , Fred & Jim and bob & Ian).
I have a database with numerous nutrient lab values per food item and zero to 20 tests per food item; some 600 food items
I want to select the last 5 tests per food item which should be no problem using the "TOP " type statement.
After I have the "TOP 5" record I would like to create another field to number each record automatically with in the query so I can run a cross tab query to display these records 1 thru 5.
Is that possible?
I have a table with 3500 records with following fields : Id_number, Num , Name , Address,.
This table used for store customer data .
My job is print 10 record randomly by customer arrival . I made this by use num field and put number manually, example I put 1 ,2 , 3 .... Till 10 , so for print i made criteria , I put 1 and 10 . Problem is because more and more customer is coming , I got confuse to put number manually . are there any way to get it automatic ?
I have a number of groups in a particular database. These are based on the month that they relate to, but they are also split into four separate groups for each month. So for example, I will have four groups for November 2014:
November 2014 Raw Data (which contains linked tables in another database)
November 2014 (which contains the bulk of the queries that are used for processing)
November 2014 Reporting (which are the queries that are used for month end reporting - obviously)
November 2014 Misc (anything that doesn't fit into the above, although there are always queries in these groups)
Now, I have been using this database since 2012, so as you can imagine there are quite a significant number of groups. Adding queries to new groups for each new month is therefore starting to take a while by scrolling down to the bottom... Is there a way to do this by using VBA? It would save a bit of time each month.
If I want to arrange records sequentially in a report I would do the following:
From the Toolbox (Access 1 - 2003) or the Controls group of the Design ribbon (Access 2007 and later), add a text box for displaying the number.
Select the text box, and in the Properties Window, set these properties:
Control Source =1 Running Sum Over Group...
How can I sequentially arrange records on a continuous form?
Hey guys,
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:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
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
Any help?
Thanks a lot in advance!
Daniel
I've received a database that is a digitized population register from the 19th century. All adults have been entered into the database, but all children are missing.
Every person has a unique number that corresponds with the original source (this variable is called 'no', this is not the autonumber primary key thing). Instead of searching in the original source which numbers are still missing, I would like to add the missing numbers (with no additional information, because I still need to type that in).
For example, the table now looks like this:
no - name_last - name_first - occupation etc
1 Smith Henry baker
2 Smith Mary
5 Williams John butcher
6 and so on
So 3 and 4 are missing.
How can I add these missing numbers automatically?
I have column called "order" in table called "mov" and this column has this layout
Code:
1
2
14
255
222
1755
12
And I want to update this column to be corrected numbering from 1 to the last cell number - lets say it 17540 - this update has no criteria conditions, just this field.
I am using an access program with a form with detail section. I would like to create auto sr.No when i am creating a new quotation. Using Autonumber didn't work because it start numbering from last quotation.
Snap shot is also attached.
I have put together a VBA sub to run Outlook.Application to send a report to selected email addresses. A Table's records contain an email addresses as well as category for grouping purposes.
I use a "Create Table Query" to extract the selected email addresses from the table into a temporary table called "ETransferAddress" then "Set rst = CurrentDb.OpenRecordset("ETransferAddress")" then concatenate the email addresses separated with commas.
This works fine for small groups, but the service provider blocks them if the number of email addresses go into the hundreds.
What I need is to break the email addresses into sub groups and then create a series of emails all with the same Report, Subject and Content.
One way might be to create a series of loops to create a number of smaller tables but to string that lot together to achieve it is beyond me at present.
Here is my code to date"
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim strPath As String
Dim strFilter As String
Dim strFile As String
Dim rst As DAO.Recordset
'Create a table with selected addresses
[Code] .....