Queries :: DCount To Add Sequential Numbers By Group Only Works In Some Instances

Jun 24, 2014

I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.

This query works, on anything that starts with the letter D or later... A-C will not work.

Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.

This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.

SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;

The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.

Running Access 2013.

View Replies


Queries :: Missing Sequential Numbers

Feb 25, 2014

I have a list of what should be sequentaia numbers, but I am finding instances were a number seems to be missing..Is there a way to query the whole dataset and find all missing instances without having to trawl he list?

View 4 Replies View Related

Queries :: Add Sequential Counting To All Items That Are Part Of A Group

Feb 25, 2014

I have a query where I want to add sequential counting to all items that are part of a group. I have grouped the query on MaterialCode and want to add a sequencial counter for all orders that have been assigened to a materialcode

For Example:

Key MaterialCode Order Counter
abc1 111111A 987654 1
abc2 111111A 687654 2
abc3 222222B 655511 1
abc4 333333A 251544 1
abc5 333333A 555555 2

View 3 Replies View Related

Queries :: Unable To Find Missing Sequential Numbers In A Text Field

May 5, 2013

I am trying to create a query to find missing sequential numbers in a text field. I am using this specific field as a case number which is designated as two letters, the # sign, two digits indicating the year, a dash, and then a four digit number; For Example: AB#13-1234.

The reason for this query is to tell the user of this database that a specific case number has yet to be entered and needs to be. The case numbers are unique and will never be referenced more than once.

My table name is "MainDataTbl" and the field i'm trying to find the missing case numbers is titled "CaseNumber".

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

Modules & VBA :: DCount Works But DSum Doesn't

Jun 19, 2013

Mycode works great when I use the DCount function, but fails when I change it to DSum which errors with: Error 94 - invalid use of null.

dblCntr = DSum("[intEventCount]", "tblResourceEvents", "[ResourceEventTypeID] = " & myKey & " AND [TimeFrameID] = " & Me.cbo1)

The entire table has valid data - no nulls. myKey and Me.cbo1 both have correct values.

View 2 Replies View Related

Detailed Record Numbers By Group In QUERIES

Oct 24, 2007

I have a table that looks like this:


I want to create a crosstab query that looks like this:

Date1 Date2 Date3 etc etc
caseID value value value

There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).

Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.

Any ideas??

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

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

Queries :: Way To Group Model Numbers Once 1st 6 Characters Are Identical

Jan 30, 2015

In my transaction table, I would like to find out the latest transaction dates of each family models. It sounds a simple Max function can get the desired result. However model number consists of the first 6 characters for the family model and the rest for the versions (variants). The Max function fails to work in this scenario. Is there any other way to group the model numbers once the 1st 6 characters are identical (the rest is ignored)?

View 3 Replies View Related

Assigning Sequential Numbers.

Feb 12, 2007

Yo there - thought Id fire a question out regardin numbering:

I have a table players and a table team.

Players sign up to teams, and I have a report which shows each team, and the players underneath.

Player 1
Player 2

Now, after all the players are assigned to a team, I want to assign each player within each team a random number, sequential.

So when I run the report, it will be like:

Team A
1. Player 1
2. Player 2

Team B
1. Player 1
2. Player 2

Any pointers!?

Cheeas -

View 9 Replies View Related

Unique Sequential Numbers

Nov 22, 2004


I have a field called "Project #" that needs to generate a unique number each time a command button is clicked. For example, when the button is clicked the blank "Project #" field is filled in with "653001". After the "Project #" field is filled in the button also runs a code to create a report exported into Excel. This report will only have the records with the Project Number of "653001".
Then, when more records are added and it's the end of the next week, the button is clicked again and the "new" blank "Project #" fields will be filled with the next Project number which would be "643002". A report will again be created but now with only the records with the Project number of "643002".
The first three characters will always be the same for the Project Number "653" but the last three will increase whenever the button is clicked to update any new records that have a blank "Project #". So, it should look like: 653001, 653002, 653003, 653004, etc.

I know it sounds confusing but I hope someone can help.


View 1 Replies View Related

Reports :: Sequential Numbers For Records

Mar 10, 2015

Need a sequential number on report. Have an unbound text box and set it to list a running sum over group. The twist is that I'd like to have it start with an assigned starting number [Starting Number] and then add 1 to each subsequent record. It works for the first record but then the next record is last value + [Starting Number]. Basically it is a check run where I would list the starting number and the report would do the rest.

View 2 Replies View Related

Auto Enter Sequential Numbers?

Apr 2, 2012

Ineed to track Trailer security seals that are sequentially numbered with 8 digit numbers...They are physical seals and no efile with the numbers to upload into Access...is there a way I can put in a range and Access will populate the rest in a table?

View 5 Replies View Related

Sequential Number By Group In A Field?

Jul 19, 2015

I have a table like the one below. I need to Auto Sequence based on JobIDOne in the field that says sequence in the manner that I've typed. Hand typing is not an option because by table will be updated regularly. All other fields are updated via a form. I need a either a macro or VBA solution that can reconcile this, preferably through the table. Note, I do not want to use a query to create this sequencing or SQL language as I do not know how to write SQL commands.

I know it can be done but I've seen about a thousand ways to do it that I haven't been able to modify for my specific table.

Dates Worked One

6:45:10 PM


View 14 Replies View Related

How To Isolate Records Based On Sequential Numbers

Dec 27, 2011

I am trying to create a database that will have records that include dates and a number assigned to that date. ex., the 3rd day of the quarter will be numbered 3 and the 43rd day numbered 43. I will do this via table.

After running a query that will identify a subset of records, I only want to see those records that are part of a 10-day or greater span. There could be multiple 10-day+ spans over a given quarter.

View 2 Replies View Related

Entering Multiple Identical Orders With Sequential Numbers

Jul 12, 2006

Hello all.

I need to be able to enter a number of orders that would all be the same, but they will have different order numbers, in sequence.

I'd like to enter all the information in a form, then have "First Order Number" and "How Many?" fields, and have the system grab the detail, apply it to the order number entered, create a record in the table, then reapply the detail to the next sequential number, repeating the process until it has created the number of orders shown in the "How Many?" field.

I am at a total loss here, and any help would be GREATLY APPRECIATED!!



View 3 Replies View Related

Modules & VBA :: Database For Quotations - Modifying Sequential Numbers

May 29, 2015

I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.

This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?


View 10 Replies View Related

Queries :: Possible To Have Multiple Instances Of Same Provider ID

Feb 9, 2015

I have a table called providers with the following 3 fields:

Provider ID
Cancellation Reason
Visit ID

It is possible to have multiple instances of the same provider ID, cancellation reason and visit ID on the same date. I'm trying to count just 1 instance based on visit ID if there are multiple instances of the visit ID and reason on the same date. Select distinct doesn't seem to do the trick.

View 1 Replies View Related

Group NUmbers

Mar 3, 2007

I am using an access database and have range of years for customers
and want to group them together. for example:


and what to display them as 1990-1994, 1998.

How do I do this??? Please note that I am an Access newbie....

View 1 Replies View Related

Group Numbers

Mar 3, 2007

I am using an access database and have range of years for customers
and want to group them together. for example:

The table looks like this:

CustID YearID
cust1 1990
cust1 1991
cust1 1992
cust2 1990
cust2 1991
csut2 1993

And want a query that will display it like this:

CusttomerID YearGrouped
cust1 1990-1992
cust2 1990-1991, 1993

These are just examples not the actuall info... Is this even possiable???? Please note that I am an Access newbie....

View 3 Replies View Related

Queries :: Modifying A Query To Return Only Instances Where There Is More Than 3 Records

Sep 26, 2014

I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.

Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.

I am thinking along the lines of DCount? but how it would be phrased in the query?

View 14 Replies View Related

Totals From Instances (not Of Instances)

Jul 21, 2006

I've come across an interesting problem a colleague has been trying to solve and even though I can 'see' what needs to be done and could probably program it on our mainframe, I'm really stuck on how to achieve the same results in MS Access

I'll do my best to explain the problem by showing an example of how the data exists in the DB and also how it 'should' be output on a report.


29/06/06 - Oper A - 32
29/06/06 - Oper B - 12
29/06/06 - Oper A - 10
29/06/06 - Oper C - 13
29/06/06 - Oper B - 10

The output needed however should read as follows

29/06/06 - Oper A - 42
29/06/06 - Oper B - 22
29/06/06 - Oper C - 13

Setting up a date selection shouldn't be a problem, its just adding the totals on each instance of each operator into one figure without ending up with a total of the number of times an operator appears on a date.

Any help and guidance about how best to approach this problem (macro/query/script) would be appreciated.

View 2 Replies View Related

Reset Page Numbers For Each Report Group

Mar 25, 2005

I've got an empty pageheader, and a customer group header that repeats on every page. the customer group does not have a footer, and I have a standard agreement paragraph and a signature line in the page footer.

I want to apply a page number to either the top (group header) or bottom (page footer) of my page to reflect the PAGE OF PAGES for the group, not the overall report.

The printed report gets separated into pages for each customer and mailed to them - doesn't make sense to have running page numbers on these - I'd like the customer to be able to see that they're looking at page 2 of 4 of what I mail them, instead of page 202 of 412.

How can I accomplish this?

Sidenote: I ran across a few references to the MS Knowledgebase report samples file (RptSmp00.mdb) during my pre-post answer search. I downloaded - I browsed - I learned a few new tricks. Good stuff.

slaughter at missouri dot edu

View 2 Replies View Related

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

Aug 12, 2014

I'm having multiple problems with my database like things such as -

i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too

I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?

i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:

Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"

The Query is the one im most concerned about , i can live without a form.

View 14 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:

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 :: Insert Works In SQL VIEW But Not In VBA?

Mar 5, 2014

This code:

Private Sub txtPart_LostFocus()
Dim db As Database
Dim strSQL As String


But no entries are added to the table.

I open a query in design mode, paste the first debug above in SQL VIEW and run it:

INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 1);

And the entry is added in the table.

PartID and SpecID are long integer, Sequence is integer.

I originally had Me.txtKey in the SQL, but I saw a comment in a post about that being a possible culprit. So I copied it into a long integer, still didn't work (latest trial above).

View 2 Replies View Related

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