Modules & VBA :: Database With 7 Columns - How To Number In Sequence
Jun 11, 2015
I have a table in a database with 7 columns. The data is sorted by Date1 in descending order.
For each pid I want to put the sequence numbers
First record has two conditions
If string 2 is null then start numbering from sequence1
If string 2 is not null then start numbering from sequence2
If string 2 = string1 then
Sequence1 = 0
Second record has two conditions
Number sequence2 with the value 2 or 3 depending on the line one
If string 2 = string1 then
Sequence1 = 0
Else
Sequence1 = next number
Same condition for the rest of records
View Replies
ADVERTISEMENT
Sep 20, 2014
creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5
---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------
View 2 Replies
View Related
Sep 11, 2014
I am trying to get the max of an integer field if the ProjectNumber field is equal to the ProjectNumber field in the current record. I have used code similar to the one below before but it was on a date field, then add one.
Me.RevisionNumber = Nz(DMax("[RevisionNumber]", "dbo_ProposalLog", "[ProjectNumber] =" & Me.ProjectNumber), 0) + 1
The ProjectNumber field is a text field.
View 6 Replies
View Related
Dec 8, 2005
The title probably doesn't give an idea of what I need, so here goes.
We currently have reference #'s for our bills that include the date requested in mmddyy format, the first 3 letters of the customer, the initials of the order taker and finally a sequential number to show the sumber of bills that day.
example: 120705SEAGMM02 2nd order taken by GMM for Seagate on 12/7
120705SEARLH01 1st order taken by RLH for Seagate on 12/7
I want to generate this number automatically based on the date entered and the initials given of the user. We only deal with one customer at our desk so that will always be "SEA".
I have a query that generates the first portion (date, customer, and initials):
SELECT Format([REQDATE],"mm") AS [Month], Format([REQDATE],"dd") AS [Day], Format([REQDATE],"yy") AS [Year], Format([REQDATE],"mmddyy") AS [Both], [Both] & "SEA" & [PickUpReqData]![INITIALS] AS REFNO
FROM PickUpReqData;
But I can't seem to get my head around the part of generating the number. I know I had done this in a database I created 2 jobs ago but all my files were flooded out in Katrina. (I have since relocated to Atlanta, although I'm not looking for sympathy. But I will take what I can get!)
Anything to point me in the right direction will be appreciated. I am looking to have this on a form and feed the REFNO field in the PickUpReqData table.
Thanks.
Gary
View 3 Replies
View Related
Jul 22, 2013
I am trying to split a number sequence (Ex. 25372-400-E10-0000-D0021) Where the third number in the sequence can be either 2 or 3 numbers/letters.
The idea is to have a query that will split this number into its individual parts. I have seen several forums about splitting two numbers and such but I can't seem to make that work when I need to create 5 columns from this one field.
View 7 Replies
View Related
Mar 30, 2006
Hi!
1. If accidently a record or two in a sequence are deleted, how will you know that this has happened! What type of validation can be enforcedto prevent this?
2. How do you make your Tables, Quries, Forms, Reports, Macro tamper proof?
Please help!
Orsonros
View 2 Replies
View Related
Apr 3, 2007
I have a query that captures date values from a table based on a between function that works fine. What I would like to do is associate a sequence number fro each date value returned and either perform a make table or leave it as a select query. If I create a table I know that I aam able to add an autonumber field but the query is executed frequently and performing the addition of the autonumber field is not feesible. Any thoughts how I can generate the sequence number each time the query is executed?
Thanks
View 3 Replies
View Related
Sep 17, 2013
i want to know such a method that if i give a number, the database find me the next available number from the given list in the table.for example, i have a table having list of numbers like 6500, 6501, 6502 etc, I give the number 6500, the database should give me the next available number in sequence from that given list in the table.
View 7 Replies
View Related
Nov 12, 2013
I would like to insert a sequence number into a query that currently returns 7367 rows.
Here's what it looks like now:
ID_NUM
DTE_FIN_TRANS_RCV
1322
9/27/2012
1322
7/10/2012
1011
7/2/1999
1011
9/22/1999
1011
10/21/1999
Here's the desired outcome with sequences:
ID_NUM
DTE_FIN_TRANS_RCV
SEQUENCE
1322
9/27/2012
1
1322
7/10/2012
2
1011
7/2/1999
1
1011
9/22/1999
2
1011
10/21/1999
3
All I need is a way to number rows with a value higher than the previous for that particular ID_NUM.
View 4 Replies
View Related
Feb 1, 2013
I'm trying to code for a 'duplicate record' button on a form that, as well as copying the values of all the controls of the previous record to the new record, makes a certain control (which contains a number) increment as appropriate.
There are two controls on the form which contain the start and end values of a numerical sequence (of potentially any length) e.g. the range 48-82 shows 48 in text box boxREGSEQSTART and 82 in the text box boxREGSEQEND OR the single value 99 shows 99 in text box boxREGSEQSTART and text box boxREGSEQEND is empty. In a record, the value of boxREGSEQEND would always be higher than that of boxREGSEQSTART or would be blank.
The appropriate incrementing action would be:
After clicking 'Duplicate Record' button, If there is no value in boxREGSEQEND in the record being copied, then in the new duplicated record the value of boxREGSEQSTART should be 1 greater than the value of boxREGSEQSTART in the record it has been copied from.
i.e. the record being copied is records a single item, a single value, and the next record should show the sequence moved on by one
OR
If there is a value in boxREGSEQEND in the record being copied, then in the new duplicated record the value of boxREGSTART should be 1 greater than the value of boxREGSEQEND in the record it has been copied from.
i.e. the record being copied is recording multiple items, a sequence, but the next record should still show the sequence moved on by one
I can get the duplication to work no problem, and I can get the incrementing to work fine for the first set of conditions, but can't for the life of me get it to increment when there is a range!
The logic (in my head, expressed as best I can in the macro language ) for the whole sequence of actions goes like so:
RunMenuCommandCommand SelectRecordRunMenuCommandCommand CopyRunMenuCommandRecordsGoToNewRunMenuCommandCommand SelectRecordRunMenuCommandCommand Paste
If [boxREGSEQEND]=[IsNull] Then
GoToControlControl Name boxREGSEQSTARTSetValueItem = [boxREGSEQSTART]
Expression = [boxREGSEQSTART]+1Else
GoToControlControl Name boxREGSEQSTARTSetValueItem = [boxREGSEQSTART]
Expression = [boxREGSEQEND]+1GoToControlControl Name boxREGSEQENDSetValueItem = [boxREGSEQEND]
Expression = [boxREGSEQEND]=Null
Which I would assume would tell the program to copy the current record, make a new one and paste the copy, then if there was a range recorded in the original, start the numbering of the new record one number higher than the end of that range and clear the way in the new form in case it also records a range, otherwise just increment the sequence along by one.
View 3 Replies
View Related
Mar 23, 2006
Hi -
Suppose I have a generic query that returns a group of records.
Is there some way to generate a field containing the number sequence of each record? E.g. if there are 10 records, generate the numbers 1 through 10?
Must be a simple way to do this, I'm just a bit dense today...
Thanks,
-g
View 1 Replies
View Related
Nov 18, 2014
I have two tables - one with parent Records, the other with child records. The ID links the two tables .I want to add a sequence number on the child table which resets at each ID change. What would be the best way to accomplish this?
Code:
PC_ID ID Name Address
P 1 Parent1 Address1
P 2 Parent2 Address1
PC_ID ID Name Address SEQ
C 1 Child1 Address1 1
C 1 Child2 Address1 2
C 1 Child3 Address1 3
C 1 Child3 Address1 4
C 2 Child1 Address1 1
C 2 Child2 Address1 2
C 2 Child3 Address1 3
View 4 Replies
View Related
Jun 6, 2012
I have data in a table that looks like the first table and I would like to write a query to update the sequence so it looks like the second table.
PersonID
Color
Sequence
1
Red
1
Blue
1
Green
2
Blue
3
Red
3
Blue
PersonID
Color
Sequence
1
Red
1
1
Blue
2
1
Green
3
2
Blue
1
3
Red
1
3
Blue
2
View 2 Replies
View Related
Jan 14, 2014
I am looking to make a table which adds a sequence number to my records.
Example data would look like this
Acc no Date
1234535 01/01/2013
1234535 05/01/2013
1234535 11/01/2013
9876543 02/01/2013
9876543 22/02/2013
9876543 01/03/2013
I want it to add a deq based on the account no then date in sequence oldest to newest.
Acc no Date Seq
1234535 01/01/2013 1
1234535 05/01/2013 2
1234535 11/01/2013 3
9876543 02/01/2013 1
9876543 22/02/2013 2
9876543 01/03/2013 3
View 5 Replies
View Related
Oct 8, 2013
Occasionally a user of mine needs to reset a sequence number in a table.
A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:
Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
View 2 Replies
View Related
Jan 4, 2014
How to extract or calculate the number of records in a sequence - e.g. in a table/query ordered by ID and date, the number of consecutive records by date for a given ID that have a value >= 50 in a given field. Is there a way of doing this purely within a query, or would I have to resort to a VBA loop through a recordset and keep a count of the consecutive records that match that criteria?
View 3 Replies
View Related
Aug 28, 2013
I have a Primary Key field named Member ID(Number format) in a Table named Mail List. The field is populated with existing ID numbers and my need is to use my Member Entry Form to increment by 1 from the last entry.
I have gleaned from other references that this can easily be achieved by the following entry in the Default Value of the property sheet for the relevant field;
=DMax("Member ID","Mail List")+1
The Form saves without error but when I attempt to add a new record in the entry form, the ID Number reads "#Error".
View 14 Replies
View Related
Nov 21, 2011
At present I am running Revit Architecture with DBLink to access to edit fields for shelving etc.
What i am doing is fitting out warehouses with 600+ shelving units and they are all numbered.
These numbers are tagged in the drawing.
What I am hoping to do is export the data to access (completed) then amend any changes within access.
Normally, we would put say 600 shelving units on a drawing all tagged 1-600. But then the client may want to delete shelving unit 321.
Leaving the drawing with all shelving units tagged 1-320,322-600.
How would i go about it so access renumbers all data after the deleted field (ie number 321)?
View 2 Replies
View Related
Apr 30, 2015
I do not want to use AutoNumber to generate my Stock Control Number. I need to somehow automatically generate a number so that when I enter data and then make an error it does not mess up the numbering system like AutoNumber does.
I am a novice who is will to try to do what it takes to get this going properly. I cant find anything online relating to Access 2010.
View 3 Replies
View Related
Jul 29, 2014
I have a set of transactions with a Unique ID field and a date. I want to add a field based that gives me the sequence of events for each Unique ID in order of when it happened.
For example, if customer X has 6 transactions, the sequence field would have a number (1-6) in each record that corresponds to the order in which those transactions occurred. The first transaction would have the number 1, the second would have the number 2 and so on.
View 3 Replies
View Related
Jan 23, 2015
I need to first generate a random number between 1-4.
Then I would like to use that number to pull that many records out of the database; the records pulled can be random, or whatever.
For example, we have 4 people and my goal is to choose a random # of records to pull for each person. So, for person #1, it might only pull 1 record, then for person #3, it may pull 2 records, etc.
Ideally, it wouldn't pull the same # twice when run for that "session".
After this, I'm hoping to generate all those results into emails (eg for person 3 it would generate 3 emails, etc)
Currently it's all done manually and I think it's doable with Access.
I have some code already for generating "1" email message, but do not know how to pull X number of emails all at once. (X is the random #).
View 2 Replies
View Related
Aug 8, 2013
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
View 2 Replies
View Related
Jan 24, 2015
I am writing a deposit recording database for archaeological layers. My units are recorded in one table, and then the relationships are recorded in a separate table. I have limited the recording of relationships to be either "same_as" or "below".
So in tblContexts is have say: 1,2,3,4,5,6
in tblcontextrels I have say : 2 is below 1; 3 is below 1; 4 is below 3; 1 is below 5; 6 is the same as 5
I want to view the deposits on screen in their stratigraphic sequence. So I think I need to write a piece of code to run through "tblContextrels" which will then create a hierarchy attribute. This attribute can then be used to sequence the context deposits I am looking at.
View 2 Replies
View Related
Nov 1, 2013
We have a database that is split. Every user has their own front end (installed via a .bat file so that the users always have a local copy of the most recent version), linked to a shared back end on a server. This seems stable and functional for us so far.
However, because our users are geographically separated, I don't actually know how many folks are using the database. I know the folks who contact me, but everyone in our company could use it if they wanted to.
For requesting funding, guiding future development, etc., I need to get some metrics:
Total number of unique users
Avg uses per day
Total number of simultaneous users (daily, weekly, and monthly maxes and mins)
What is the best way to get this info? Write user info and a timestamp to a table each time a front end is launched? Are there tools for monitoring this sort of information?
View 3 Replies
View Related
Jan 17, 2015
creating the VBA code to find the total number of tables in my database.
View 2 Replies
View Related
May 30, 2014
Lets say you have a table with an AutoNumber field.You populate it with 100 records.You delete the last 10 records (91-100)Now, I certainly know you can refresh the AutoNumber sequencing by compacting the database so the next record entered is 91.
My question is about NOT refreshing the AutoNumber sequencing.Lets say you DO NOT COMPACT and DO NOT refresh the AutoNumber Sequence.So, in that scenario, after deleting records 91-100 from the table, is there any programmatic way to determine that the next actual AutoNumber for that table will be 101?
View 3 Replies
View Related
