Allocating Values To A New Field
Dec 8, 2004
This may seem a bit of a strange one but I hope someone can help.
SCENARIO
1. Add a new column to a table that already has data entered in it.
2. Index this column (NO duplicates) but the field is not a required one.
3. Set it as an Auto Number.
Is there anyway of applying the autonumber to the data already in the table?
i.e. for row 1 in the table the new column gets a value of 1, the second row a value of 2 etc.
Many Thanks
Steve
View Replies
ADVERTISEMENT
Jul 26, 2006
Hi all,
Only discovered Access a few days ago, absolutely brilliant tool.
Have ordered 4 books (most with the words "idiot" or "dummies" in the title!), but would appreciate some advice with a problem i can't seem to get my head around.
As you can see from my attachment, i've got a lovely database that i made.
Well, i say made.. downloaded from Microsoft templates and turned it into my own. Very proud of it!
I'm a co-cordinator for the Tresco Marathon - a full marathon run around a 2 x 1mile island all for charity (Cystic Fibrosis Trust). This database is supposed to be an efficient way of keeping track of all the entrants, if they've paid registration, if packs have been sent to them, their eventual finishing times, their running numbers etc.
I've managed to do the most basic operations in Access - a nice form that keeps allows you to add a runner, and all their details.
The problem i'm having is...
Once the few hundred applicants have been entered, we then have to assign them Race numbers... but i can't figure out how do to this.
Ideally, something like a form openable from the main form (already have a tab showing an empty box for a manually entered race number - this is showable by choosing their status as "Active") showing 1 - 130- and then whether they've been assigned to a runner already (and their name), or whether they're available.
I really am stumped on this one.
Would appreciate any help or advice in the best way to do this!
View 1 Replies
View Related
Oct 4, 2007
Anyone any ideas, please?
I have a table of name and address data as a back end and have put a front end of each of 12 "user" computers on a workgroup-based small network. Each user can look at on twelfth of records, the range being specified in a query on each front end PC, which, of course, means that each front end has to be slightly different.
I wonder if it's possible to assign some sort of owner system so that whenever "user1" logs on he/she gets the first twelfth of the records, user2 gets the next set and so on.
As a separate issue, can I somehow work out the start and end records needed for each user by automatically dividing the total number of records by 12 (since the master table can have a different number of records each time we get new data). At the moment I am calculating (using Excel) and inserting the ">1200 <2400" for each user.
Any ideas would be gratefully appreciated.
View 1 Replies
View Related
Jun 14, 2005
Hi
Firstly let me thankyou for your help because I feel this may be a difficult question.
I have a stock control db that records transactions through purchase orders, which allocates each line item (product) in a purchase order to an employee and a project it is destined for. I have three tables, one for purchase orders, one for transactions and one for products.
I then have a seperate table for stock in and out with a two other look ups for projects and employees.
Now sometimes a product may become a stock item as it is in surplus and not used for the project.
My problem is how do I re-allocate the product as a stock item then when it is used allocated it to another project.
Basically we use an internal unique number for each purchase order and i have a query set up that will recall each product in a purchase order. The reason for this is to save time updating goods recieved when an order turns up as i call all the item in a purchase order in one go rather than going to each product one at a time to update.
I think the db may be to advanced in the set up and feel a restructure of the db is the only solution but hopefully i not. I am no means an advanced user buit have done ok so far in seeting this db up e.g. my knowledge of vba is limited etc.
This has only just come up in what is needed so it is an after thought, however is very important for the operation of the db as a stock control system.
All advice much appreciated in advance.
scott
View 2 Replies
View Related
Oct 19, 2013
I have a database that uses a table to capture work by reference number. I would like to be able to allow users to only be able to see work they have been allocated.
View 4 Replies
View Related
Mar 31, 2013
I am creating a DB for race results and have a field which calculates the elapsed time based on the start and finished times. What I want to do now is populate a field "position" with the finishing position relative to other competitors elapsed times in the race. But I cant see how to do this.
I understand I would probab;y have to use some sort of query but not sure how to create it.
View 2 Replies
View Related
Mar 20, 2015
I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center
Job Type
Hours Paid
Pay Code
Earnings Amount
20530
Security Guard II
7.5
REG
190.37
[Code] .....
View 14 Replies
View Related
Nov 22, 2013
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
[code]...
View 14 Replies
View Related
Mar 2, 2015
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
View 4 Replies
View Related
Nov 23, 2005
Hey all,
I have two fields 1 & 2
field 1 is a simple combo list of user defined values ie A, B, C or D
Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, C and D. i.e
A could have a,b,c,d or e
B could have c,e,f,g or h
C could have a,g,h,i or j
D could have v,w,x,y or z
I would field 2 to have a combo box which only displays a,b,c,d, or e when A is chosen in field 1; c,e,f,g or h when B is chosen; etc
How do I do this?
Cheers all,
Matt :confused:
View 2 Replies
View Related
Feb 16, 2014
I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)
Tables:
* Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player)
* Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box)
* Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box
* Venue - similar to Competition table
* Opponent - Similar to above two tables
* Match - MatchID, Competition, Venue, etc (form corresponding to table attached)
Forms:
* Player form
* Match form
Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).
View 1 Replies
View Related
Jul 25, 2014
i want to add a control in that form that sums up and aggregate all the values of field called [amount] based on the value of [Name_Patient] as criteria
View 2 Replies
View Related
Apr 7, 2015
I have several fields that I fill in on a form and they all need to add up in the last field. The first way that I tried this worked good for the initial data entry on the form. However if I need to edit one of the fields in the form then it resets the TotalCost field and I have to enter all of the numbers again. Is there some way that I can make this work? Or is this an Access quirk I will have to live with?
I tried to do this two different ways.
Here is the code for the first method:
Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
[Code] .....
This way works great the first time that I tried it, I had to re-enter info in all the fields if I wanted to change one.
Here is my second method:
Which didn't work, i received an error message:
"The expression After Update you entered as the event property setting produced the following error: Invalid outside procedure."
And then nothing changes.
Code:
Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
[Code] ....
So I know this second method is incorrect.
View 3 Replies
View Related
Nov 18, 2014
I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:
[Field1]&", "&[Field2]&", "&[Field3] and so on.
However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?
View 10 Replies
View Related
Nov 10, 2005
If I have the following Code to disable a field based on the value of another field:
Private Sub lstAgreementType_AfterUpdate()
If Me.lstAgreementType.Value = "BN" Then
Me.txtSenateAandCDate.Enabled = False
Else
Me.txtSenateAandCDate.Enabled = True
End If
End Sub
But I also need it to disable the field is equal to "BA" or "BT" as well, how would I add that to my code?
View 3 Replies
View Related
Jan 5, 2006
I really need some help with this! I have a table with a field called [days.out]. It contains values from 1 to about 350, non sequencial with duplicates. I would like to run a query that would create a new field called [days.grouped]. This field would contain the word "1-15" if the value was between 1 and 15 in field [days.out] and "15-30" if value was between 15 and 30 in field [days.out], etc....
Hope someone can help me!!
View 1 Replies
View Related
Apr 27, 2005
undefined
I am Access novice but am creating a databasr of over 250 names, addresses etc for a later mail merge. The problem i have is that the fields for phone/fax numbers require me to input unwanted data, thus creating a number that is longer than the actual number. I can i tell Access to disregard the unwanted data and set the fields to the value i require not want Access requires ?
View 1 Replies
View Related
Nov 16, 2007
I think the title pretty much sums it up....
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
The query is refusing to return any results...
Any ideas?
View 10 Replies
View Related
May 31, 2005
Hi all..
I have a DATE/TIME field in a table. I want this field to be optional.
But when I try to insert a record without a date value for this field, the SQL fails. How do I fix that ?
Also I want to display this field only if there is a valid date...How can I do that?
Thanks in advance..
View 2 Replies
View Related
Sep 28, 2005
I have a field - Period in the format YYYYMM.
How can I split this value so that I only extract YYYY?
Thanks
P
View 2 Replies
View Related
Sep 29, 2005
Hi
I have the field fullname in my database which is in the format
Mr John Smith
What I have been trying to do is to extract the Mr then the John then the Smith into 3 separate columns. Can anbody help please?
Thank you
Marcus
View 3 Replies
View Related
Feb 13, 2006
Hi
I have a numeric field called FileNo and an autonumber field called FileID in a table called tblFile.
I also have a lookup numeric field called FileTypeID (with values 1 or 2 or 3) on another table called tblFileType.
I need the value of FileNo which I am showing on a Form frmFile to change dependant on the value of the FileTypeID
i.e. If FileTypeID = 1 FlieNo should start at 100
If FileTypeID = 2 FileNo should start at 200
If FileTypeID = 3 FileNo should start at 300
Then when I create a new record I need to increment by 1 the value of FileNo according to the FileTypeID
I've setup a button and attached this code to its onclick event but it only works as long as I don't change the FileTypeID
Dim B As Integer
Dim H As Integer
Dim N As Integer
Do While FileID > 0
DoCmd.GoToRecord,,acPrevious
If FileTypeID = 1 Then
B = FileNo
Else
If FileTypeID = 2 Then
H = FileNo
Else
If FileTypeID = 3 Then
N = FileNo
End If
End If
End If
Loop
DoCmd.GoToRecord,,acLast
If FileType = 1 Then
FileNumber = B + 1
Else
If FileType = 2 Then
FileNumber = H + 1
Else
If FileType = 3 Then
FileNumber = N + 1
End If
End If
End If
ViRi
View 5 Replies
View Related
Feb 14, 2006
I have a list of Vehicles in which each have their own maintanance schedules. Most of the maintanance for the Vehicles overlap. Instead of entering the same information over and over I'm assigning each type of vehicle a code number (example '05 Cargo is a 1, '04 Cargo is a 2). Now what I want to do is assign each maintanance the vehicle code, for instance I have 13 different types of vehicles that all need oil changes, so I want to assign the Oil change maintanance 13 codes. Is there a way to assign that maintanance 13 codes without having to enter it 13 times??
Thanks, Joe
View 1 Replies
View Related
Feb 28, 2008
Hey, I'm new to microsoft access, and I could do with a little help please :) . On a database I have created, I have a table with the field "Results" in it. This field has been set up in the Lookup properties to be a choice from "Win" "Loss" or "Draw". My question is, what is the expression I would use to count the total number of records in my form with "Win" selected?
I tried to set up the DCount expression, but this gave me some odd results.
Currently my database is set up like this,
TBL_Match_Results:
Fields:
GameNumber (Autonumber + Primary key)
Date
Result (With the choices above)
Then I made a continuous form from this table, and on the Form Footer I had a text box with the value,
=DCount([GameNumber],"TBL_Match_Results",[Result]="Win")
I had hoped this would simply give me the total number of records with a win in them. However, insted the value changes depending on which record I have selected on the form. It is "10" if the selected record is a win, or "0" if it is not. I'm not sure if the problem is the poorly written expression (I'm not 100% sure how to work them), or if I'm even using the right expression (I was only told to use DCount). If anyone could shed some light on this, I would greatly appreciate it!
EDIT: I've just realised where the 10 is coming from, it's the number of records in my table, when i added a new one it changed to 11.
View 2 Replies
View Related
Aug 9, 2005
I have a db with a field that I need to split and put values into two other fields. The format of field1 is either a-b, aa-b, aa-bb, or a-bb. I want to take the value before the "-" and put it into field2 and then take the value after the "-" and put it into field3 and then delete field1. Is there a straightforward way to do this? I want to do it in the query design grid so let me know what should be in the "criteria" line versus the "update to" line.
I'm using Access 2000 and have approx 5500 records to convert.
Thanks!!
Tom
View 2 Replies
View Related
Oct 20, 2005
I am using a form where I select values from combo boxes to enter parameters for a select query. It works fine where the table queried only has one value in the field concerned. However, it does not work on the fields that have several values (entered through a multi-selection box).
Are there any ways to query fields with a multi-selection?
Thanks, Niels
View 6 Replies
View Related
