This is probably really easy but i am having brain fade with it.
I have a table called tblPlanControl. One of the fields is called copy number. I want to automatically calculate that field by the amount of records.
For instance.
The frist record would be copy no 1
second copy 2
third copy 3 and so on.
I do not want to do it by autonumber and cant decide on whether to do VB or if there is an easier solution!!
Thanks
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
Hey, long time reader, first time poster.
I have a database created that is used to catalog different projects. When the user adds a new record and that project is already in the database it gives the user a message stating that that project already exists. It gives the option of cancelling and goes back so the user can name it something different. Or the user can continue and it creates a duplicate copy.
Instead of creating a duplicate copy, I want it to increment a number field named [Duplicates] by 1.
Example:
If project 'A' is already in the database and the user adds it again it increments the duplicate field by one. So now the properties of project 'A' shows that there is 1 duplicate. Does that make sense?
Here's my duplicate code so far:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
If Not IsNull((DLookup("Project_Name", "Development_Projects", "[Project Name]= '" & Me.Project_Name & "'"))) Then
Response = MsgBox("That Project already exists" & vbCr & vbCr & "Select Yes to duplicate record, No to cancel.", vbCritical + _
vbYesNo + vbDefaultButton2, "Duplicate Data")
If Response = vbYes Then
[Duplicate] = [Duplicate + 1]
Exit Sub
Else
Cancel = True
End If
End If
End Sub
Hey,
I'm currently working on a database for my school project.
Currently, I have a table:
Job:
Contractor_ID [Foreign Key]
...
Contractors:
Contractor_ID [Primary Key]
Contractor [Text]
Basically I want to create a query that shows a table like so:
Wages:
Contractor [Contractors.Contractor]
Jobs_done [counts the number of rows from Job that has the Contractor_ID equal to the Contractor_ID specific to Contractor (above)]
Eg.
Job:
Contractor_ID
1
2
3
2
1
4
Contractors:
Contractor_ID Contractor
1 Bob
2 Bill
3 Jim
4 Jill
Wages:
Contractor Jobs_done
Bob 2
Bill 2
Jim 1
Jill 1
I have a unique database that I am building. I am new to Access and am trying to learn what the best practices are.
We are a manufacture and I am trying to build a database that captures all the unique features that define an individual product.
From these unique variables I can generate a BOMs, Production times, Box content labels, job tickets, etc. All this information is entered using a form, I want one location to input all the pertinent information to avoid having to enter anything twice.
The trouble I am having is that I have hit the 255 fields. I know that this typically means that there is a poor structure, however this is the amount of data required to define one product. Is it possible to split the database into two or more databases? I built a second database, but how do I sync the "Part Number" fields so that they always match each other?
I have a question regarding the number of fields in a query. I have a query that is looking at production scrap by categories. There are fifteen categories of type of scrap and in addition to these I am using the query to work out the total % of total scrap for each category. My problem is there are not enough fields to do all the calculations. Is there a way of adding more or is there a better way of doing what I am trying to achieve?
View 10 Replies View RelatedMy boss has a table with tons of fields on it, he designed it this way because it was easier to add data to one place, but now the table keeps crashing, does ms 2k have a limit to the number of fields in a table??
Thanks in advance.
I have run into a problem with an ODBC table connection. The table I am connected to exceeds the number of fields allowed in an Access table. I am using Access 2003. When I looked on Microsoft help site I think the max number of fields in a recordset is 242 so I am unable to see all of the fields in the original table I am linking to. Does anyone know if this limitation has changed in the newest version of Access? I checked the Filemaker www site and that program does not have the same limitations. Thanks in advance for any help!
View 4 Replies View RelatedHello there.
Is it better to have many fields in one table or to split the data between a number of tables and link them?
Thanks.
# gingette
i have a query which looks at 2 fields: "employee number" & "agency number", i would like the query to list the employee numbers in order first (1,2,3...) then the agency numbers in order (100, 101, 102...). ive changed the ascending/ descending order for them both but doesnt help. Its mainly the form that looks at this query i would like to fix so that is shows all employee records in order of employee number first and then underneath all agency numbers in order of their number (some employee numbers & agency numbers are null) hope this makes sense , any help would be great
View 7 Replies View RelatedHi Guys and Girls,
While doing a rabbit DB for the missus I have hit a problem, if one of the does gives birth to 3 babies i wish to have the details entered onto a form (not a problem) but i would like to do this:
if number of babies = 3 then fields to display to enter details is 3 on the form, i have my table setup with up to 10 seperate field for the babies per record but am stuck trying to do this
any help or direction would be great
Hi there,
I'm making a document control database where the a unique reference number is automatically generated from 2 tables and updates a 3rd table.
xxxx or yyyy/lookup in 2 table/autonumber
I have been trying to do this is a form but because the control sauce is a calculation 'Text1&Text2&Text3' I am unable to update the 3rd table with the combined reference number.
I have limited knowledge particularly of VBA coding
Any help much appreciated
I want my db to generate our reference # automatically based on input from other fields. The number is the date record entered, the users initials, and an incremental number. Ex. 120505GMM02
My form is based on a query and I have the query generating the first two components from the date entered in reqdate and the initials entered. How can I get an incremental number generated to go with the rest.
The number should start with 01 for each day and also for each user. Therefore when GMM is entering his third record on the 5th it should generate 120505GMM03 even if there were 10 other records entered on the fifth by other users. Each users numbers would start at 01 for each day.
I asked this earlier but thought I would restate my question in hopes of getting my thoughts around this. I appreciate your patience with me.
Thanks.
Gary
I only seem to be able to add some 30 calculated fields to a form or report. Anything over this returns a #Name? error. I've seen this before but have searched this and other forums but cannot find an answer. I suspect a memory issue but I have 2 gb on Pc. Any Ideas?
View 10 Replies View RelatedI am using Access 2013 and in table design view. Whenever I add a new 'Number' or 'Currency' field the 'Default Value' property is (by default) always 0.This is fine except when i want to refer to a foreign key or amount which could be unknown. In either of those cases the resulting field will either be invalid (because no foreign key has ID = 0) or inaccurate (because 0 is not the same as Unknown).
I would prefer if the 'Default Value' property for new fields was always nothing, then I can add my own if and when required. I want to change the default setting on a property for new fields, not the value of the field itself.
I'm guessing this isn't actually possible, but at least this post has served to vent my frustrations! What bugs me even more is that i'm sure it wasn't like this in Access '10, is this another "upgrade".
I have a table with two numberfields (let's call it NumbersToFilter and FilteringField).
I want a query that shows me ALL NumbersToFilter data WHERE FilteringField is the highest.
Eg. if this is my table:
NumberstoFilter / FilteringField
999 / 50
947 / 44
920 / 50
941 / 48
I want my query to return 999 and 920 (filteringfield max = 50)
I'm currently using an aggregate query, using group by NumberstoFilter and max FilteringField, but it returns all the fields (the maximum is simply on top )
I want to export the results of a query to Excel. All of the fields in the query are number fields. But when I export to Excel, the fields are stored as text. In fact I get the error message "number fields stored as text" in Excel. Why this is happening?
View 3 Replies View RelatedWe have a model at my job which shows every job code one can do (there are only about 25 of these jobs) For example, let's say one job is called "Trade Settlements" and it's been estimated that we spend about 1000 minutes a day doing this particular job across the entire floor.
Well, I want to come up with a way to allocate the minutes of this to certain hour blocks and determining whether our group is over/understaff given the results.
So let's say I want, 30% of it to be done from 9-10 am, 10% done over the next 4 hour time blocks, and 2-3 pm for the remaining 30%.
Thus, we'd get something something like 300, 100, 100, 100, 100, 300 minutes and 0's for other time blocks.
These percentages would be input fields so if anyone types in a percentage, they get # of minutes spent for that hour on that job. Ultimately we would add these numbers up with other jobs and be able to easily update from there for any job type we wanted.
I'm just to work with Fox Pro, and I am therefore puzzled with how to define the exact number of digits in a field in a table in Access. If I need 9 digits + 2 decimals in a number filed, how do I define that in design view?
Also, I need to know how to export a table into a text file, with a format without any spaces, and each record is divided with a new line. This an old IBM text format file.
Thanks, Torsan
I have a field in the parent table which indicates how many child tables there should be at the most. I'm uncertain about how to enforce this. I"m using access as the database to a Vb.net app. Should I do it in access or Vb.net or both.
View 6 Replies View RelatedI need to trim a lot of tables. Is it possible to delete table fields with reference to the field number instead of field name?
The command 'tblMyTable.Fields.Delete ("FieldName")' takes a string (field name) as input. I rather like to - in a loop - delete say,
Sub DeleteFields()
For i = 40 To 60
tblMyTable.Fields.Delete Field(i) ' Not correct syntax
Next i
End Sub
thh
I have a form that records followup activities for protocols. The form allows the user to enter up to six f/u actions from a dropdown menu in six separate fields (called fu1_action, fu2_action, etc).
I am trying to write a query that retrieves only protocols that only have one f/u action selected as "telephone correspondence" and am having trouble. It seems I could write a function in VBA that would somehow loop through the 6 f/u action fields and give a value of 1 to those that have 'telephone correspondence' as a response, and then add them up to give a total of calls for that specific protocol, and then in a separate query only retrieve those that have a total number of calls = 1, but how to get started in writing this.
I have an excel data file that is updated monthly (and I am not able to have the source file format changed of course) that will be my linked table for Access 2007. The file reports monthly data and adds the new month to the right of the historic in a layout like this:
Tree | Orchard | Province | Jan % red | Jan % green | # Limbs | Feb % red | Feb % green... etc so each month it adds 3 new fields to the data.
I have queries (rightly or wrongly) that will calculate the number of apples that month and report on those higher than a certain percentage. I would like to do this without having to create a static table to append to each month as the new data file will always show the full YTD results anyway (and I don't like to store data in my database).
I have a query that counts the number of fields in the raw data file and am wondering if there is a way to have access only run the number of queries required based on the number of fields. i.e. if 11 fields then run queries 1 thru 5, if 14 run 1 thru 6 etc.
I am new to Access and I am developing a form. I have already read and found useful resources on internet but there is something I am not able to sort out.I have seen that the limit of fields I can enter in Access is 255 and my survey is far below that number.So no issue at all when I created the fields in Data view.However, the issues started when I created a form.
I created a form by Create>Form.The last few fields, at the very bottom of the form, are squeezed onto each other as if there was not any space available. Is there a limit to the number of fields I can have in a form? There should not be if I am allowed to enter up to 255 fields. How can I go about it? Should I alternatively create 2 forms? I would rather not to though.
I am having a table for invoicing and vouchers, and I need to have two fields that will have auto incremental numbers. How I can have two fields in the same table with autonumbers and how can I get one of them to start from a different value.
Eg. Record 1 - [inv No] 1 [Vno] 0005
Record 2 - [Inv No] 2 [Vno] 0006
I have a table that has fields for number input
On my form I have in a TAB form 3 fields to input a figure, in the table these are set up as a Text NOT a Number. If i set it as a Number it keeps rounding up? even after i enforce 2 decimal places it still keeps rounding up.
Field 1 - 42988.62
Field 2 - 0.00
Field 3 - -14330.84
I then created a unbound txtbox so I can add these up
=[ResBF]+[ResRefReC]+[ResSurDef]
what i get in return is this :- 42988.620.00-14330.84
I also tried =Sum([ResBF]+[ResRefReC]+[ResSurDef])
and that just comes up with #Error
I even tried in the table directly to have a calculated field and still having the same problems.