Modules & VBA :: Numbering Of Records Within Groups

Jul 4, 2013

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
.....

View Replies


ADVERTISEMENT

Numbering Groups

Jan 8, 2008

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 Related

Sequential Numbering Within Groups

Aug 8, 2005

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

View 11 Replies View Related

QueryTop X Records In Multiple Groups

Jan 26, 2006

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

View 4 Replies View Related

Having Individual Records, In Multiple Groups

Jul 14, 2006

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?

View 1 Replies View Related

Top 3 Records For Multiple Groups (District)

Jun 27, 2012

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.

View 2 Replies View Related

Numbering Records

May 11, 2005

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.

View 5 Replies View Related

List Records By Desired Groups Instead Of Sort

Aug 3, 2007

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

View 3 Replies View Related

Finding Groups Of Numbers From Multiple Records

Jan 2, 2008

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

View 12 Replies View Related

Numbering Records In Query

Aug 11, 2005

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

View 3 Replies View Related

Numbering The Subform's Records

Mar 23, 2006

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.

View 2 Replies View Related

Sequentially Numbering The Records In A Query

Jan 27, 2006

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?

View 2 Replies View Related

Numbering Records Automatically In Report

Oct 16, 2013

I would like to know how to code a report to add row numbering automatically?

View 6 Replies View Related

Multiple Combo Boxes In Form To Choose Groups Of Records

Feb 16, 2005

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.

Thanks for any help!!!

View 1 Replies View Related

List Of Records From Spreadsheets Into Cumulative Table - Categorized Into Groups

Oct 13, 2011

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 Related

Modules & VBA :: Linking All Option Groups

Mar 13, 2014

I 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.

View 3 Replies View Related

Modules & VBA :: Combo Box With Colored Groups

May 12, 2014

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).

View 2 Replies View Related

Queries :: Numbering Records By Group In A Query

Jun 2, 2013

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?

View 2 Replies View Related

Table With Several Records - Automate Numbering In Form

Feb 8, 2013

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 ?

View 3 Replies View Related

Modules & VBA :: Adding Queries To Groups In Particular Database

Dec 29, 2014

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.

View 6 Replies View Related

General :: Auto Numbering Records In Continuous Form

Oct 15, 2012

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?

View 3 Replies View Related

Auto Creating Record In One Table For Groups Of Related Records? + Stock Levels

Mar 23, 2008

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

View 4 Replies View Related

Tables :: Adding 1000 Semi Blank Records With Sequential Numbering

May 21, 2013

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?

View 3 Replies View Related

Modules & VBA :: Update Column With Sequential Numbering

Jan 8, 2014

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.

View 4 Replies View Related

Modules & VBA :: Auto Serial Numbering For Each Quotation

Apr 8, 2014

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.

View 3 Replies View Related

Modules & VBA :: Requirement To Break Bulk Emails Into Smaller Groups For Transmission

Mar 10, 2014

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] .....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved