Sequentially Number Records

Jan 10, 2007

Hi, i have a query which contains two fields: PtId and visitdate. I'd like to create a field that sequentially numbers the visits.

If you're really smart, the second part of my problem involves computing the time from the last (or max) visit to the visit before it. I was thinking if I had the visits numbered it would make this task easier, but if there is another way, that would be even better!
Thanks so much for your help!

View Replies


Modules & VBA :: Table With Auto Number Key Field That Is Numbered Sequentially

Oct 10, 2013

I have a table (tblContact) with an auto number key field that is numbered sequentially (1-8) there are no deletions, each new record is appended.I wrote a simple FindFirst line to locate a record that is the first record in the table. The FindFirst failed to find the record.So, I wrote a Do Until Loop that cycles through each record looking for the record that I want to find (the first record with key field 1).


Debug.Print "ContactID: " & rstContact.Fields("ContactID")
Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf

Do Until rstContact.EOF
Debug.Print "ContactID: " & rstContact.Fields("ContactID")
Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf

This works to find the first record... eventually, because it does not begin its search at the first record. The results in the immediate window are below.


ContactID: 4
CEmployerID: 2
ContactID: 4
CEmployerID: 2
ContactID: 5
CEmployerID: 4


I believe the field CEmployerID is unrelated to the issue. I am also attaching screen shots of the table "tblContact" and code with immediate window. I have tried indexing and not indexing the CEmployerID field in the Contact table to no avail. Even though the Do Until Loop eventually finds my record,

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

Making Access Display Records Sequentially

Oct 5, 2013

I have records of vehicles numbered from B1 to B300. Can I make Access display records in the sequence B1 B2 B3 B4 B5 instead of B1 B10 B100 B101 etc?

View 1 Replies View Related

Modules & VBA :: Opening 2 Forms Sequentially On Button Click

Jun 20, 2013

From formA I click on button cmdFormB and I open FormB. From formB I click on button cmdFormC to open formC.

Is there a way to open forms formB and formC by clicking on a button on formA?

View 5 Replies View Related

Modules & VBA :: Reads List Of Files Sequentially - Check For Variable In A Table

Aug 2, 2013

I have a process that reads a list of files sequentially and then processes them .... As each file is processed, its name (strfile) gets written to a Table (Vault.[Processed_file])

I want to verify if the file exists in the table prior to processing it - if it exists, I won't process it again ...

View 5 Replies View Related

Forms :: Auto Generate File Number Based On Number Of Records In Year

Jan 21, 2014

I have a form [IUDATA]

I have a add record button.

I have a date field [DATEIN]

I have a text field [DRPNO]

If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.

I'd like the format of [DPRNO] to be "dpr YY-XXX"

YY is the year of the [DATEIN] field and
XXX is number of records in that year.

So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.

View 12 Replies View Related

Report Prints The Same Number Of Times As The Number Of Records

Jul 10, 2006

I've created a report and report has the same number of pages as the number of records that it's displaying.

If there are two records, the report has 4 pages...the first 2 are the actual report and then the other 2 are a copy. If there are 3 records, the report would have 3 copies (...6 pages).

Any idea how to change this so that I have only one copy of the report?

View 1 Replies View Related

Forms :: Limited Number Of Records In Continuous Form / But Now Can't Add Records

Mar 22, 2013

One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code

Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 3 Then
Me.AllowAdditions = False
End If
End Sub

to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":

Data Entry
Allow Additions
Allow Deletions
Allow Edits
Allow Filters

View 3 Replies View Related

Sorting Text Field Containing Both Text And Numbers Sequentially?

Mar 26, 2014

We have a database that we use to track the tablets in our organization, a combination of Apple, Android, and Windows.

We label them with a hostname depending on their branding. Example below.:




In the database we use the field name HOSTNAME for these entries. When sorting the field, due to it being a text field, we get the traditional sort of



We would like to have them show up as


What is the best way to tackle this?

View 1 Replies View Related

Appending Records With A Desired Auto Number In A Table With Auto Number

Oct 26, 2005

Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.

I am sure Iím not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.


View 7 Replies View Related

Maximum Number Of Records

May 3, 2006

hi all,

This is general question only. How many number of records that Access can handle as a maximum records?
Or it only depend on size limit (4GB)???

View 2 Replies View Related

Limit On Number Of Records?

Mar 10, 2008

Is there a limit on the number of records you can have in an access database? I've got one with 8500 records and it's been a PITA! I have a webpage on our intranet so users can go and enter their information to it, and they get a nasty error whenever they try to enter information. I'm trying to figure out why and i was just wondering... i know it's not the webpage because when i connect it to my test database it works fine? so i dunno :confused:

any help is greatly appreciated!!

View 4 Replies View Related

Limit Number Of Records....

Mar 29, 2005

Hi All.
Is it possible to limit the number of records in a form, to a number given in a linked form.

For Example.



I would only want the number of records in tblB to be the same as No_Of_Houses in tblA.
I have seen previous threads where you can limit it to a number, but do not know where to go from there.

Can anyone help?



View 1 Replies View Related

Combining A Number Of Records Into One.

Jan 17, 2006

I have a table that contains a number of email addresses. I need to combine a number these into a single record to allow me to email a report to a number of different recipients depending on the client the report is for.

At this point Iím not even sure were to start.

View 12 Replies View Related

Selecting Specified Number Of Records

May 19, 2005

I have a database that allows the user to order clothing items.
Each size of each item has a different stock number.

I have [StockNo] - [StaffNo] - [Qty] i need to generate [OrderNo].

The problem i have is that [OrderNo] is incremented after every five five items (ie[StockNo]).
This gives me my first problem of selecting just the first five records of a table in order to allocate an order number.

The second problem i have is that in order to allocate an order number, i have to summarise the [Qty] by [StockNo].
This gives me the opportunity to select the order number but i lose the individual [StaffNo] information.
This information, together with the [OrderNo] is needed for a master order table.

I have created a crosstab query which summarised the [StockNo]but how do i retrieve the [StaffNo] information from the column headings.

I am new to Access and would be grateful for any help or comments as to whether i am going about solving this problem in the best way.


View 1 Replies View Related

Number Of Records In A Query

Dec 20, 2005

I have a form (frmdetails) to store job details and another form (frmMaterials) to add materials to each job. They are both linked via a field Project_No

I have a query (QryTotal)that when run will list the materials used for each project, that is all OK.

What I am tryng to do, is add a text box to frmdetails, that will just tell me how many materials have been booked against each job. Just a running total that will tell me that for project number 100000, 4 items have been booked.

I have tried using subforms (using =count(*) ) but this doesn't seem to be working. I also added =Count(QryTotal!Material) to a text box but this doesn't work.

Can someone help?



View 2 Replies View Related

Auto Number Records

Feb 13, 2006

I have a query that pulls data from two tables. I need to have another column in the query that numbers the rows like a primary index or gives each records a different number. Any ideas without adding any columns to the orginal tables.

Thanks in Advance,

View 2 Replies View Related

Total Number Of Records

Apr 17, 2006

I want to run a query to add up the total number of records in a table.

i have looked at the Microsoft help but that tells me how to add together the totals of the fields but not the total of fields

any ideas?


View 5 Replies View Related

Maiximum Number Of Records

Apr 9, 2007

Im importing 80,000 records into a table and then using a query to reformat the records and append them to a second table. The import works, but the append quits after about 6500 records. Is there a limit to the number of records an ACCESS query can handle?

View 3 Replies View Related

How Do I Set The Number Of Records For A Page?

Apr 26, 2005

Is there a way to set the number of records to say....25 per page then starts a new page?

I do have a footer with the inserted "Page Numbers"

View 3 Replies View Related

Duplicate Specified Number Of Records

Feb 22, 2006

Acc 2003


Does anyone know if this is possible. I've looked but can't find anything like it posted within the forums unless I'm searching for the wrong keywords.

If I have a list of records on a continuous form, I want to drop a combo box at the end of the each record with a list of numbers in ranging from 1 - 100.
When a value is chosen, the selected record is then duplicated by that number.

For example, Select a record, choose 20 from the combo box = 20 new entries of the record.

Also, as a side issue, is there a way of omitting or clearing certain fields from the process.
For example, Serail Numbers are unique, so I want the user to have to manually key these in once the duplication process completes.

Hope I have explained this in enough detail.


View 4 Replies View Related

Number Of Records Found

Mar 23, 2006

A form i have gains its records from a query. I know that the number of records found is displayed down the bottom, but is it possible to have a text box displaying this, so i can choose where on the form i have it?


View 1 Replies View Related

Incrementing Number For Old Records

Nov 8, 2006

I have 4 tables on my dbase. On the last table underlies all records pertaining to customer payments.


ColID - PK
PolID - FK

I want to add another field which i would name -- CollectionNumber. In this field, i need an incrementing number for each PolID.

For each PolID (no duplicates), there could be 1 to 6 records of payment. I need the number to start at 1 for each unique PolID.

Please help me on this. I've been looking at different posts but i still can't find answer. :-(


View 8 Replies View Related

Display Number Of Records

Feb 15, 2006

Is there a way to display a number (on a Form) for the number of records in a specific table? I just want to reference how many records there are. I tried to search this topic, but couldn't find anything. Thanks.

View 10 Replies View Related

Show Number Of Records

Jul 26, 2006

Hi everyone

My problem goes as followed:

As you can see in the picture there are 175 records currently showed. What I want to achieve is that this number is displayed in a Text Box.

Thanks in advance.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved