Modules & VBA :: Create Sequence From Table?
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 Replies
ADVERTISEMENT
Jan 14, 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 12 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
Aug 30, 2014
Have two tables: Assignment and StudentHeader - they are related by AssignmentGUID
Have the SQL:
SELECT StudentHeader.[Student ID], StudentHeader.GUID
FROM Assignment INNER JOIN StudentHeader ON Assignment.GUID = StudentHeader.[Assignment GUID]
WHERE (((Assignment.[Assignment Type])="Q") AND ((Assignment.[Assignment Number])=2))
GROUP BY StudentHeader.[Student ID], StudentHeader.GUID
ORDER BY StudentHeader.[Student ID], StudentHeader.GUID;
This returns:
Student IDGUID
al003072274
al154636303
al154636463
al595048272
al941626429
am180614426
am750033300
am750033462
I want a third field - Sequence - based on the Student ID and GUID, therefore
Student IDGUIDSequence
al0030722741
al1546363031
al1546364632
al5950482721
al9416264291
am1806144261
am7500333001
am7500334622
View 14 Replies
View Related
Sep 5, 2013
I want to be able to compare two tables to see whether their Column Names & sequence are the same.
View 1 Replies
View Related
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 5 Replies
View Related
Feb 28, 2014
I have a Microsoft Access database with SQL Server backend with 10000 records in my table, I have a autonumber field and in my database the purpose of the autonumber field is just to keep the record sequence (not to treat as a unique identifier) and I don't want to use a number field to manually enter the sequence. Whenever the record is deleted or the user won't save the record, it put gaps in the record sequence, Is there any way to refresh the autonumbers automatically "After delete confirm" event or "After Insert/update" even.
View 13 Replies
View Related
Apr 7, 2015
I need to automatically generate a 5-character value for my Business Key. Without any user interaction.
2 character -> from 01,02,03,04 to 05
+
3 character -> Sequential Number (001,002,003).
The Sequential Number must reset on each new day.
View 8 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
Jun 24, 2015
I'm trying to create a new record in another table and copy the value of two fields in the current table's record to the newly created record.
View 6 Replies
View Related
Aug 26, 2014
I have the following vba script and I can not get the yes/no field to work, also with adding field properties of required = yes and the text field to be 50, just examples are fine and I will update what I want.
Code:
Sub test()
Set Db = Application.CurrentDb
Set tdf = Db.TableDefs("1Testing") ' 1Testing is your table name
' First create a field with data type = Text
Set fld1 = tdf.CreateField("FieldT1", dbText) ' Field1 is your Field Name and formatted as text
[Code] ....
View 1 Replies
View Related
Oct 27, 2004
I have a user who wants to automate copying a table from our main database. Basically, he is naming the new table with the original table name and the current date. For example, Part_Table_9-3-04, Part_Table_9-4-04, Part_Table_9-5-04 etc for each day of the month.
He wants me to write a macro, module, or vb code that automates the steps.
I have DoCmd.CopyObject,"Table1",AcTable,"Table2" I want to concatenate the date function (now) or (today) with the new table name but can't seem to get this to work.
Any suggestions?
Thanks for helping,
Jeff
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
Oct 13, 2014
I'm creating a table with times of the day from 08:00 to 17:00 in 15 minute increments.
I'm going to use the table as a lookup to make it easier to enter an appointment time.
I don't really want to type them all by hand - does access have an equivalent to excel's drag and autofill a sequence? is it just as easy todo it in excel then cut and paste it?
View 6 Replies
View Related
Jan 7, 2014
How to create backup for my table in another DB with VBA ....
View 3 Replies
View Related
Oct 22, 2014
I have a VBA routine that runs some queries and then creates a report as a PDF document using my virtual PDF printer. At this point it brings up the Save As dialog and I give the file a name and select a folder to store it in.
But I run this report 30 times for 30 different parameters. The parameters are obtained from a table which contains values such as Cancer, Diabetes, Stroke
-I always name the PDF file according to this value.
-I always choose the same folder.
-I always overwrite the reports I produced last month (copies have been sent elsewhere by then).
how can I get VBA to cycle through this code 30 times, each time selecting the next value from my table and creating/overwriting those files without the need for me to tell it the filename and path?
Code:
Private Sub lstSpecialties_DblClick(Cancel As Integer)
At the moment I am running this code by double-clicking an entry in the Specialty listbox, 'but I would prefer the whole thing to run multiple times for as many Specialties as exist in the table '(currently 30 but could be more in time)
'Warnings off
DoCmd.SetWarnings False
'Make the tables needed for the cumulative queries
DoCmd.OpenQuery "Spec 002 Monthly recruits - part 2 - make table" Each of these queries
DoCmd.OpenQuery "Spec 005 Monthly recruits - part 2 - make table" uses the Specialty selected above
DoCmd.OpenQuery "Spec 022 ABF previous year - part 2 - make table" as a parameter to get
DoCmd.OpenQuery "Spec 025 ABF current year - part 2 - make table" the right data for the report
[code]....
View 5 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
Apr 2, 2014
I have an access table and I want the code that will check two columns in the table "EnvelopeType" and "EnvelopeSize" and create headers in Excelsheet automatically. In the attached workbook, like in sheet1 the headers are already appeared, I want this to be done dynamically using vba code so that if new values get inserted in EnvelopeType and EnvelopeSize then we won't have to change the code to display more headers.
Please see attached workbook named Sample and Access table. E.g.
EnvelopeType EnvelopeSize
TNT 2nd Class C5
PP1 2nd Class C5
PPI 1st Class A4
Recorded A4
TNT 2nd Class C5
PP1 2nd Class C5
Recorded A4
PPI 1st Class A4
Recorded C5
With the code it should display following headers in excel sheet:
TNT 2nd Class C5
PP1 2nd Class C5
PPI 1st Class A4
Recorded A4
Recorded C5
View 14 Replies
View Related
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 1, 2014
Code:
Private Sub Consolidate_Click()
Dim temp As Variant
Excel.Application.Visible = True
temp = Dir(CurrentProject.Path & "Inputs")
Do While temp <> vbNullString
[Code] ....
From the second iteration its not picking the error.
View 5 Replies
View Related
Nov 4, 2014
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
View 6 Replies
View Related
Jun 4, 2015
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
View 7 Replies
View Related
May 1, 2012
Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
View 5 Replies
View Related
May 25, 2006
I have duplicate loan numbers in a table. I want to create a query that will assign a number so the the duplicates will have a new field called sequence.
example
LNNUM SEQ
999999 5
999999 4
999999 3
999999 2
999999 1
888888 3
888888 2
888888 1
777777 2
777777 1
Any suggestions?
View 2 Replies
View Related
Jul 19, 2012
I have two tables. The first one is as follows:
MP No (Primary key), Sales, Date Entry, Specs, Email
The second Table is as follows:
MP No, Quantity, Description, DWG, Price
There is a "one-to-many" relationship between the two tables through the MP No field so whenever I go to the first table there is a sub-table for each MP No. Correct?can I create an extra sub table under the sub table already created?
View 10 Replies
View Related
