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 Replies


ADVERTISEMENT

Adding Sequential Numbering To A Query

Oct 5, 2005

Hi,

Just trying to figure out how I can run a select query on some data I have and how to also include an additional field which has a value counting the number of rows.... eg in the first row there would be the usual results and the new field would have a value of 10001, second row would be 10002, third would be 10003.

I've done some searches for sequential numbering but couldnt find anything so far.

Thanks, j

View 8 Replies View Related

Tables :: Modified Sequential Numbering In A Field

Feb 10, 2015

How I can create a sequential number in a field that looks like this:

Year-XXX-00

(Example: 15-100-00 -- the next sequential number would be 15-101-00)

I don't want to hard code the year, and the last 2 numbers must be entered manually.

It gets even more exciting --> this number has to be able to be duplicated in a table. Those last 2 numbers is a revision number. We might have a 15-100-00, 15-100-01, 15-100-02, etc.

View 3 Replies View Related

Need To Insert Blank Rows If Record < 1000

Mar 24, 2005

Hello all!

I have a database that needs to post records that are joined from three different sources into a table. The query is done, and I get about 1,489 records out in 4 different states.

What I need to do is make a table with these records. Furthermore, it must be separated by state, whereas if there are less than 1000 records for each state, it must insert blank lines until it reaches then next thousandth (sp?) row (i.e. 1001, 2001, etc.), and then start posting the next state.

For example, AZ has 420 records. There has to be 580 blank lines before the query can start posting the next state, CA. At row 1001, CA starts posting, but there are only 200 records for CA so there must be another 800 blank lines before moving on to CO at row 2001, etc. etc.

Anyone have any ideas on this? Thanks!

View 5 Replies View Related

Sequential Numbering

Apr 3, 2007

Shall be grateful for clarifications for these two :

1. I have two fields - TheYear (set to take the current year) and another for sequential numbering. In fact I created this to replace the autonumber field. As suggested by a member I created a Generate button with the criteria as under

Private Sub Generate_Click()
If IsNull(Me![NumFld]) Then
Me![NumFld] = Format(Nz(DMax("[NumFld]", "[DiaryTable]", "[TheYear]='" & Year(Date) & "'"), 0) + 1, "00000")
End If
Me![NumFld] = Format([NumFld], "00000")

End Sub

It works fine. and when the year is changed, the numbering starts from 1 again. My question is how do I make it to auto generate the number so that the user doesnt have to click the Generate button everytime to get the number. Suggestions please.

2. Is it possible to change this sequential numbering midway i.e. to start from a different number and increment by one?

Grateful for help

View 6 Replies View Related

Sequential Numbering With A Difference!

Oct 12, 2005

I have an existing table with a field labelled Job Number (17 Job Numbers). Each month I import a new table and match each record via another field (serial number). For each new record, one or many, I want to attach a sequential Job Number. In this example the new job numbers need to start at 18 then 19 etc. How is this possible? I am fairly new to Access so please be gentle!. Cheers

View 2 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

Sequential Numbering Of Query

Nov 19, 2005

I need to generate line numbers for an access query. This query is run via VB code. After it runs, a Dlookup is performed to search for the line number of a particular order detail id. When it finds it, it puts the line number along with other unique order detail information into a text file to be used by a computerized saw that cuts the parts out. Here's a sample query:

DetailID SortID Frmlgnth Stilelgnth OrderID SeqNo
1874864127.12511.9375160004
1874866127.1259.6875160006
1874868127.1258.5160008
1874869127.1255.8125160009
1874867111.8758.8125160007
1874863110.12514.875160003
1874865110.12510.5625160005
1874871221.62514.3751600011
1874872221.62513.81251600012
1874873221.62513.56251600013
1874875221.62511.68751600015
1874876221.62511.6251600016
1874877221.62511.06251600017
1874870221.2516.31251600010
1874874219.12511.751600014
1874878219.12510.251600018
1874879219.1257.1251600019
1874862534.5103.375160002
1874861534.536.875160001

The query is supposed to be sorted with SortID Descending, Frmlgth Ascending, Stilelgth Ascending - in that order. The sort is working fine. However, the Sequential numbering is not. The OrderdetailID has NO bearing on the sort order. Here is my SQL code:

SELECT OrderDetails.OrderDetailID, ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual, OrderDetails.FrameRailLengthActual, OrderDetails.OrderID, (SELECT count(*) FROM OrderDetails As x WHERE x.orderdetailid <= OrderDetails.orderdetailid AND x.orderid = orderdetails.orderid) AS SeqNo
FROM OrderDetails INNER JOIN ProductTypes ON OrderDetails.ProductType = ProductTypes.ProductType
GROUP BY OrderDetails.OrderDetailID, ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual, OrderDetails.FrameRailLengthActual, OrderDetails.OrderID
HAVING (((OrderDetails.OrderID)=[forms]![orders]![orderid]))
ORDER BY ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual DESC , OrderDetails.FrameRailLengthActual DESC;

Does anyone have any suggestions? I'm not too good with SQL, but I can figure it out (I think!). I've searched all the formus, but all the reference I can find regarding this don't work for me in my application...

I greatly appreciate any help you can give!

View 3 Replies View Related

Queries :: Sequential Numbering Within A Query

Feb 6, 2014

I've taken on the task of transitioning excel reports (and their format) to a database. One report summarizes all the parts of a piece of furniture, and the order of the machines each part goes to.It looks similar to this:

Part# Part Name Machines/Operations
M7264Top Panel 112114116120121325216
M7265Under Top Panel 112114116120121
M7266Base Assembly 411325216311310312316

The table this comes from has both operations and setups, so I used a query to filter only the operations.The query (qryOperations) result looks like this:

PART NUMBEROPERATION ORDER
M7264 112 2
M7264 114 4
M7264 116 6
M7264 120 7
M7264 121 8
M7264 325 9
M7264 216 10
M7265 112 2
M7265 114 4
M7265 116 6
M7265 120 8
M7265 121 9

i need to add sequential numbering (1,2,3,4...) to each line of the qryOperations and use the sequential numbering as the column header.How do I add sequential numbers to the query, that restart after each change in part number? I can do this in a report easy enough, but not a query.This is the SQL of the query I need to have the sequential numbers in...

SELECT tblparts.[PART NUMBER], tblOperationCodes.MachineCode, tblOperationsList.Order
FROM tblparts INNER JOIN (tblOperationsList INNER JOIN tblOperationCodes ON tblOperationsList.OperationCode = tblOperationCodes.OperationCode) ON tblparts.[PART NUMBER] = tblOperationsList.[PART#]
WHERE (((tblOperationCodes.Function)="O"))
ORDER BY tblparts.[PART NUMBER], tblOperationsList.Order;

View 5 Replies View Related

Queries :: Sequential Numbering In Query Field

Jun 5, 2013

I have a query that groups the data by customer. How can I create a "Transaction Number" field where I assign a sequential number to each invoice, starting at 1 with the oldest invoice and increasing the sequential number by 1 with each invoice. I need to create this field in a query without code.

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 :: Sequential Numbering Dcount Dmax Composite Primary Key

Sep 23, 2014

I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.

Tbl_treatment:
ID=numeric field (also in tbl_pt and in tbl_tumor)
Tumornr=numeric field (also in tbl_tumor)
Treatmentnr=numeric field

[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:

1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 3 1, etcetera.

However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:

1001 1 1
1001 1 2
1001 1 3
1001 2 2
1001 2 3
1001 3 3, etcetera.

If (DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) = 0 Then Me.treatmentnr = fRowNum(False)
Else Me.treatmentnr = (DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) + 1

View 3 Replies View Related

Forms :: Text Box Bound To A Field - Change Sequential Numbering

Jul 31, 2013

One of my forms has a text box which is bound to a field called teenumber. This form is set up that "on current" has code

If Me.teenumber = 0 Or IsNull(Me.teenumber) Then
Me.teenumber = Nz(DMax("teenumber", "tblteeofftimesshotgun"), 0) + 1

This enables the text box teenumber to be auto filled with sequential numbers growing by 1 for each record. This database is for a golf tournament and this form enables user to set up tee times for shotgun start for the players.

When the user gets to tee number 18 or whatever the last hole on the course is the teenumber field needs to be reset to 1. With the above code I a unable to do this.

View 11 Replies View Related

Forms :: Sequential Numbering Based On Information In Combo Field

Sep 4, 2014

Creating a form against a table with the following fields

EC
Project
Originator
Title
Full description
Effectivity
Etc....

Easy enough to create the Project combo box, problem comes with trying to systematically assign the next EC number.

When I select a Project from a combo box I want an EC assigned with format ECXXXX-### where XXXX is the project number from the combo box and ### is a sequential number. ECs should start at -001 for all projects.

Do I need to add an EC suffix field to hold just the ###? Then what?

View 6 Replies View Related

Semi-urgent Help With Linking Tables/forms

Aug 2, 2006

Hi there - I have a database for a conference. I have an attendee's form where I enter their personal details. Then I have another form called Options Form (which I've set up as a command button to open the form) where I can enter the workshops/sessions they sign up for.

As attendees send us their completed options forms, I need to enter their choices into the options form.

TheProblem: If attendee 103 sends in his form, I enter his choices and close out of the options form. If attendee 67 sends his form, I do the same. BUT when I go back to these attendees and open their forms, those choices I had just entered have disappeared.

The Options Table (that I set up and designed the Options Form off) has the following fields:
AttendeeID
FirstName
LastName
Workshop 1
Workshop 2....etc

When I designed the form, I formatted the AttendeeID, FirstName and LastName field to use this same info from these fields in the Attendee Form (ie, =Forms!Attendees!AttendeeFirstName). However, when I tried to find out where these options 'went', I had a look on the Options Table and they are there, but there is nothing in the AttendeeID, FirstName or LastName field - these fields are blank.

Originally when I first started entering the options, it just seemed to Autonumber the choices I entered, so even though I may have entered them for Attendee 103, in the Options Table is showed in the Attendee column as 1, then if I entered info for Attendee 86, the Options Table showed it as 2 and so on, so whatever order I entered the information it put it in numerical order as opposed to the actual AttendeeID number.

What I need: I need to be able to enter any attendee's options (whether they are attendee 1, 26, 200, or 105 - ie not necessarily in numerical order) on their options form and for this info to be saved.

I hope this is not too confusing for anyone and someone is able to help. I would really appreciate it, or if you need further clarification, please just let me know.

(My guess is it has something to do with making sure my fields are formatted correctly, ie on the Options Form the AttendeeID field was set on Autonumber which is why it may have been putting the choices in numerical order instead of whatever the AttendeeID number is. I'm also wondering if I need to set up some kind of relationship between the Attendee Table and the Options Table - but I'm not sure what type of relationship it should be on. I've tried several options and none of them seem to work)

Many thanks
Kath Price
Auckland, NZ

View 1 Replies View Related

Tables :: Adding Multiple Fields With Blank Section

Mar 27, 2014

I am creating a table in access 2010 for my consumable and bench stock report. I made a 12 fields which I name it the month of the year and another 1 field to add the total disburse materials in one whole year. I did this formula to add the 12 fields

[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sept]+[Oct]+[Nov]+[Dec].

But the problem is its just adding the complete consecutive months that I disburse and the row with blank section the total disburse doesn't show on the total disburse for the whole year. I try to use the code =Nz([Total Disburse],0) but it shows on the screen i cannot be used in calculated column.

View 4 Replies View Related

Reports :: Adding Specific Number Of Blank Lines Between Records

Jun 20, 2013

I have a WO Table and a related WO Procedures Table. I would like to add a specific # of blank lines/records between each record but that # will vary. So the report will show the WO Procedure Desc then a specified # of blank lines below it and then the next WO Procedures Desc then a specified # of blank lines below it etc.

I thought to add a field to the Proc tbl called #ofLines and whatever number was entered would be the # of blank lines to appear below the record on the report.

View 3 Replies View Related

Forms :: Subforms Randomly Adding Blank Records Not Clearing Fields

Sep 18, 2013

My subforms are randomly adding blank records and one subform I would like to stay blank repopulates with data, though not necessarily the most recently added record. Both of these seem to happen when I navigate to other main records in the database and then return to this page.Using Access 2013, I have a large form with 10 pages. On one of the pages, I have two subforms. This is set up to gather many-to-one data. The top subform is my data entry form with three fields (two combo and a text) and a command button. The bottom subform is a datasheet displaying the three fields.

The two combo boxes are cascading, and they work great. The text box is there to collect additional info for each selection.
The command button works to

1) save the record,
2) requery both subforms in order to display the new data on the datasheet,
3) clear the combo and text boxes, and 4) set focus back to the initial combo box. It all works!

But then when I leave that main record, the horror begins: blank records (from the "many" table) show up on the datasheet and the data entry fields do not stay blank. I suspect my problem is in the command button. I added this code to the OnClick for the command button:

Code:

Private Sub addMinistryItems_Click()
'save record
If Me.Dirty Then Me.Dirty = False
'requery both subforms
Forms![BCD MAIN 2013]!Child572.Form.Requery
Forms![BCD MAIN 2013]!Ministries1.Form.Requery

[code]....

View 1 Replies View Related

Forms :: Adding Sequential Alphanumeric Record To A Table?

May 18, 2015

I have an Access table with records that have a two letter, two number alphanumeric identifier (AA01, AA02 etc)

I am trying to create a form whereby, when the user types in the first two letters, the next sequential number from the table is generated.

The letters can be totally random and selected by the user, but the numbers do need to be sequential.

View 1 Replies View Related

Tables :: Updating Blank Fields For Existing Records

Oct 9, 2014

I am in the process of building Append Queries for new records, and I know I can do an Update Query to enter specific information. However, how can I update multiple records from a (externally sourced) linked table to fill in blanks of an existing table? I have created a query to identify records with 1 or more matching criteria which contain the blank fields. I now want to update those blank fields. The data in each blank field is different for each record (same type, just different data).

View 1 Replies View Related

Modules & VBA :: Adding Additional Sequential Number To Avoid Duplicate Value

Jun 10, 2013

I am not sure if it is possible to implement this but I would like your input. We get request from client via a web based form which contains a unique ID number called RDEFNumber. When we process the request internally, we use this number to identify the request throughout the process. The request from client contains processing of multiple files which are transmitted to us in different times of the day or days.

Every time we receive a file, an email is sent to the IT team with all the information about the file. We use an internal database to send the email to the IT team and use the RDEFNumber as the identifier in the subject line. What I am trying to do is to add an additional number at the end of the RDEFNumber automatically to differentiate the subject line.

For example, we take "1791" as a RDEFNumber. For the first time we enter 1791 to the database RDEFNumber field, it should check the table "tblRDEF" (record source) for duplicate value. If no Duplicate exists, it should keep the number as same. When we get the next file for processing with the same 1791 number and enter this number in the RDEFNumber field, it should automatically change it to "1791-1" since "1791" already exists in the database. When we get the next file, the number should change to "1791-2" and then "1791-3" and on.

View 13 Replies View Related

Queries :: Adding Sequential Numbers To Generate A Unique Reference Number

Oct 5, 2013

Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.

two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship

I also have a query (Named: FullLog) that picks up the following data from the tables:

Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count

The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:

Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]

This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.

Effectively what I want to be able to get is something of that resembles the following

DateReceived - ReferenceNo

051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001

View 6 Replies View Related

Adding Images Into Tables/Records

Jun 26, 2006

Hi there,

I'm trying to add an image into a table. I know I can do it manually by creating an OLE Object and so forth. I am actually trying to get the user to enter in the path of the image and add it into the record/table.

Basically this is for a database that will hold a scanned image of a certificate associated with each person entered into the database. Anyone have any suggestions?

Thanks

View 2 Replies View Related

Tables :: Adding One Value To Multiple Records

Jul 31, 2013

I am new to Access here, and looking to start up a database that will contain data for thousands of footballers.

I have on one table ("Directory"), all 5000+ players, with nationalities, names etc, and on another ("Flags"), I have all nations of the world and their respective flags (as an attachment).

How do I add the flag of a players nation into the "Directory" table for each record? Is there a quick way? I've tried "Lookup" but it won't let me do it for images, I've tried "Relationships" but without success.....surely I don't have to go through the "add attachment" process for all 5000 records?

View 3 Replies View Related

Queries :: Pulling Records Based On Blank / Non-blank Criteria

Jul 18, 2013

I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..

See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.

How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...

View 3 Replies View Related

Adding Records To Multiple Tables Using One Form

Sep 20, 2004

OK I'm totally lost again.

What I want to do is;

Use one form to add records into several tables.

I have seperated my data into seperate tables and set up one to many relationships.

tblincident
--------------
Key#
Report#
Time
Date
Location

tblPerson
----------------
Key#
Last Name
First Name
Middle Name
Address
etc.

tblItem
----------------
Key#
Make
Model
Serial#
etc.

What i want to do is use one form to enter all this data into the different tables.

There can only be one record in tblIncident that can match multiple records in the other tables.

i.e. for each record in tblIncident, I want to be able to have the capability to have as many persons related to that incident as I need.

I was thinking about setting up a tabctl for the data that has to be entered for tblIncident and then change the property setting for it to go away and then have the next tabctl appear in it's place for entering data into the next table. The problem I am having is that apparently I can only have one table as the control source for the form.

So next I tried using subforms, but that isn't working either. I can't figure out how to make the subform appear in a specific place on my form, sized in specific dimensions I want where I want.

I am just starting to learn about this relationships stuff and it's kind of difficult. How do you make it where you can do this from one form? Do I need to make a query of some sort?

Any help is appreciated.

View 1 Replies View Related







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