Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Sequentially Number Records

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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Sequentially Numbering The Records In A Query
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 Replies !   View Related
DLookup In Query To Find Next Record Sequentially, Help!
This involves in a DLookup() Function in Queries and I really need help on.
My main purpose here is to find the next record that matches the criteria.

I have 2 Column Headings in table [tblFruitType] and they are: [chrFruits] and [Quantity]

Example View: ** I'm Using (-) as a delimitor in the forum like a separator or space so it will be much clearer to see what I'm doing.
chrFruits - Quantity
Apple - Ap3
Apple - Ap5
Apple - Ap7

I have another table called [tblSales] with 2 Column Headings and they are: [chrSalesDate] and [chrFruitSold]
Example View:
chrSalesDate - chrFruitSold
1/25/2006 - Apple

However, I want to run a QUERY that looks like this:

chrSalesDate - chrFruitSold - Quantity 1 - Quantity 2 - Quantity 3
1/25/2006- Apple - Ap3 - Ap5 - Ap7

So Far, Here is my QUERY DESIGN:

Field: chrSalesDate
Table: tblSales
Sort: Ascending

Field: chrFruitSold
Table: tblSales

Field: Quanity 1: DLookUp(“[Quantity]”,”[tblFruitType]”,”[tblFruitType].[chrFruits] =’”&[tblSales].[chrFruitSold]&”’”)

Field: Quantity 2: DLookUp(“[Quantity]”,”[tblFruitType]”,”[tblFruitType].[chrFruits] =’”&[tblSales].[chrFruitSold]&”’”)

Field: Quantity 3: DLookUp(“[Quantity]”,”[tblFruitType]”,”[tblFruitType].[chrFruits] =’”&[tblSales].[chrFruitSold]&”’”)

WELL, I can’t get the Quantities to display in sequence where it would find the next record that matches Apple. I did some research and saw a formula that will find the next record which it looks like the one below but I don’t know how to use that.

Expr1: DLookUp("[Field1]","Table1","[ID]=" & [ID]+1)

That +1 looks like it would search for the next record. And if I put a +2 then it would search for the second next record, and +3 with the third next record and so on…

But I don’t know how to use it in my Quantity field. Can anyone help? Please!!!

View Replies !   View Related
Report Prints The Same Number Of Times As The Number Of Records
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 Replies !   View Related
Appending Records With A Desired Auto Number In A Table With Auto Number
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 Replies !   View Related
Maximum Number Of Records
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 Replies !   View Related
Limit On Number Of Records?
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 Replies !   View Related
Limit Number Of Records....
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 Replies !   View Related
Combining A Number Of Records Into One.
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 Replies !   View Related
Selecting Specified Number Of Records
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 Replies !   View Related
Number Of Records In A Query
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 Replies !   View Related
Auto Number Records
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 Replies !   View Related
Total Number Of Records
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 Replies !   View Related
Maiximum Number Of Records
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 Replies !   View Related
How Do I Set The Number Of Records For A Page?
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 Replies !   View Related
Duplicate Specified Number Of Records
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 Replies !   View Related
Number Of Records Found
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 Replies !   View Related
Incrementing Number For Old Records
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 Replies !   View Related
Display Number Of Records
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 Replies !   View Related
Show Number Of Records
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 Replies !   View Related
Fixed Number Of Records In A Query
Hey all, i just would like to know if there is a way to have a fixed number of records in a query.
Let's say, example (a), a query returns 2 records (qry1); i would like to have my query (qry2) to have 5 records, so the first two records will appear and then 3 blank records. (b) A query (qry1) returns 4 records, then this query(qry2) should return the 4 records plus a blank record



Why do i want to do this?
Because i want my report to show 5 records so when i print it i have those blank spaces to fill if necessary.

I want to have this:

Instead of:

What im doing now is creating a temporary table and add the records to it plus blank records.
What i used to do was add blank records in the same table meting the criteria of the report, so they will appear. After close the report, those records would be deleted.

I would like to know if theres another way, like a command in sql, or maybe some property on the report. What ever is most efficient and a good database practice. Thanks in advance

View Replies !   View Related
Any Limitation On Number Of Rows/records
Today a guy who has done some Access programming told me that an Access table has a limit on the number of records.

Whereas I was thinking the only limit is the 2 gig size.

Which one of us is correct here?

(Access 2003).

View Replies !   View Related
Number Of Records Doesn't Match
(this might be more appropriate in Forms; apologies if I'm putting it in the wrong place.)

My company runs a home repair program and we keep track of our clients in a database I made a few months ago. We use almost exclusively a form drawing from 3 tables. We just hit 800 records (according to the form) and I was checking some things, and found that there are 807 records in each of our 3 tables.

I did a preliminary check, and the primary keys match as best I can tell, so I'm trying to figure out how 7 records can be missing from our form.

Any ideas?

Thanks in advance.


View Replies !   View Related
Query - Retrieve A Set Number Of Records
Hi all,

I want to retrieve a set number of records from a table, using a query. I'm not sure of how to do this.

I can't use the ID, because the table gets its data replaced contantly by imports (so the ID doesn;t start at 1...)

I want to be able to retrieve, say, the first 25 records on that table.

Any help is appreciated

View Replies !   View Related

Copyright © 2005-08, All rights reserved