I have a database that was built 5 years ago that has an auto field with an integer. There are relationships attached to this. I an rewriting it to simplify the database and I need to keep the relationships somehow. I want to make the auto field a text fields. How to work this out...
View RepliesI have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
Emily
Is it possible to run a SQL command to update a field within a table with random numbers?
More specifically - random long integers linking back to an ID (autonumber) field in another table?
Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)
The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.
So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.
I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.
I have a field in a table that is comprised of mostly numerical data but some records are text.
I want to convert this field to numerical only and make a new field to put the textual data in.
However converting the field will delete the textual data. What is the easiest way to convert the field but save the textual data AND append the textual data to the SAME record that they were in originally in the new field?
I'm trying to set an input mask for a text field.
The data will be 1 to 4 digits preceded by 1/
I have tried the masks 1/####;; , "1"/####;; 1/####;; , "1"/#### , '1'/#### but none work as if the first nubber entered is a 1 it replaces the 1 in 1/
If it is a 2 then it works fine.
I can set the 1 to and f (f/) and it works no problem.
Hi Guys,
First off, a big thanks to everyone on the site. I have learnt a lot since first discovering this site a few weeks back.
Problem:
Having understood that it is better to create SQL lookups to queries of tables rather than to the tables directly, I am having trouble understanding what value I should store in my main table, a text value or the ID (number) (of the text value.)
I have an asset table with a field Equipment Type. This field looks at a query of the EquipmentType Table.
Would it better to store the text value "Printer" in the main asset table (in which case I can query the table directly but the field will use up more space (i.e. 25 char)?)
or
Store the Equipment_Type_ID "1" relating to the Printer (will use up less space, but mean any queries querying the actual name would have to include the EquipmentType table).
Any advise would be much appreciated.
:confused: My thought was that I should go with the ID as otherwise I will be storing duplicate data. If this is the case, when would a text value be more suitable.
I am having a query which having a category field like Electrical, Sports, House hold etc.
What I want that if i select Electrical then it should return 15, if Sports then 10 and so on i think this could be done through this below mentioned VBA but it need change from integer to text...
Option Compare Database
Public Function fncGrade(intNum%) As String
Select Case intNum
Case 0 To 1: fncGrade = "Same as Previous"
Case 2 To 32: fncGrade = "C-3"
Case 33 To 40: fncGrade = "C-2"
Case 41 To 50: fncGrade = "C-1"
Case 51 To 60: fncGrade = "B-3"
Case 61 To 70: fncGrade = "B-2"
Case 71 To 80: fncGrade = "B-1"
Case 81 To 90: fncGrade = "A-2"
Case 91 To 100: fncGrade = "A-1"
Case Else:: fncGrade = "X-X"
End Select
End Function
I have a table with about 300,000 records. About ten fairly small fields per record. I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.
I have never seen this message before. I have about 64 Gig of free disk space. What can I do?
I created a database with 10 tables and 8 relationships. Then, I learned about the Autonumber field type and decided that I wanted that to be the primary key of one of my existing tables, for a field that was already linked to 2 other tables. I had to delete the relationships and the original field to make the change, and Access said it had to delete an index, too. Now, Access won't allow me to re-establish those relationships.
I found that I had to change the fields in the other tables to match the Autonumber type and name of the original field, but now I am getting an error message stating, "Invalid field definition 'CustomerID' in definition of index or relationship. What does that mean? The field types and names are all the same.
I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;
DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2))))
DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2))))
DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4))))
Time: IIf([TIME] Is Not Null,[TIME])
When I have a value of Null, i keep getting #Error, I think when it's null.
I would like to convert a text string to integer.
Lots of posts say to use val, but it is not listed in access 2010
So I am trying,
Creating a field that is numeric then just referring to the string field.
This works good except where it finds actual text. It puts the value "#Type!" in there.
Would there be some kind of function to check for an error or check if the value is text.
Hello everyone, I'm encounter a problem trying to write a code.
EX:
Date Months_to_credit Final_result
01/31/06 4 10/01/05
02/28/06 6 09/01/05
03/31/06 8 08/01/05
Does anyone have any ideas how to to substract Months_to_credit from Date and to return a date in the Final_result field?
If a user wishes to delete a record, rather than deleting the record i'd like to change its status to not current for instance.
The reason being that the information in the records may be useful for statistical use.
I added a yes/no field to the table and then added some code to a delete button to change the yes/no.
I can't seem to hide these records from my forms (using filter) but I could be going the wrong way about it.
I have built a table off a form that is being used for record keeping and my question is.... This table will be constantly updated with new information so how can I make it to where when new info is added the old info will not be removed or written over?
View 7 Replies View RelatedWhen I run the analyzer on all object types it recommends to change the data type for field "zip" (zip code) to "long integer to:
"benefit that table and potentially other objects in my database"
The field type is currently set to text, And I have the same setting for the same field in a separate table, yet it does not come up with a recommendation for that table.
Additionally, I don't seem to have the option "long integer" for the field data type???
jeds - using Access 2010
Successful in loading an Access database with data from the PLC's memory. Unfortunately, I'm only able to transfer Integer values. In the database I'd like to show one of the fields as a real number by formatting the Integer value to add a decimal place. Example: 2505 to 250.5
View 11 Replies View RelatedI am trying to make a database that will keep track of invoice backlogs for different locations within a company.
Each day a report file is imported into an Access database (2010), and a query will display all locations with the backlog count of invoices for each of them. Something like:
Code:
Location Backlog
00001 84
00002 53
00003 26
Now I would like to be able to store historical data in a new table on a daily basis so that it will be possible to see how the backlogs have fluctuated over time for a given location, but I am not sure how I could do this. I was thinking I could make a table with one row for every date and one column for every location, but then I am not sure how I would populate this table based on the query that shows the results based on the most recently imported report file.
I am trying to use a switch statement in a access query and i be leave i have the code right but for some reason it will only output a string i need it to output an long integer . (All the columns that are in the statement are Numbers).
Code:
columnName:Switch (([column1]<>0), [column1], ([column2]<>0), [column2], ([column3]<>0), [column3],([column4]<>0),[column4],([column4]=0),0)
Outputs a string i need an long integer
I have tried
Code:
columnName:Switch (([column1]<>0), CLng(column1), ([column2]<>0), CLng(column2), ([column3]<>0), CLng(column3),([column4]<>0),CLng(column4),([column4]=0),0)
and does not work just outputs a string. It outputs the right number but not as an integer...
I have a database with forms that use ADO to access data files. In the design stage, I went into Tools/References and checked "Microsoft ActiveX Data Objects 2.1 Library", which made the ADO work.
For each "case" that is run, the application creates a separate database and copies all forms, reports, etc into it. In Access 2003, the forms in the new database still have "Microsoft ActiveX Data Objects 2.1 Library" checked, but when I run the application in Access 2007, the forms do not have "Microsoft ActiveX Data Objects 2.1 Library" checked, and the ADO will not work until I go in and manually check it myself.
Is there a way to select the ADO objects programatically or to keep ADO set as a default property in Access 2007?
Thanks so much.
Hi, I am creating a time keeping db.
Before I jump in and start creating a way to record times worked I thought it best to ask for advice.
The db is created and the last part is to capture by member:
Basically I have the following fields:
MemberID
Job Type
Date of Job
Time Started
Time Finished
From the data captured I need to create a report to show month and ytd hours worked.
It would be easy to ask the user to calculate the hours worked and input the number of hours, however I would like to capture start and finish times.
Can anyone give me any pointers on the best field types and field formats for capturing times which would then make it easier to work out hours worked.
Thanks in advance
Darrell.....
We need to move rich text from an Access memo field to a Word text box. So far the best Ive been able to come up with is in the code below. In this code pprs!What is a record set field of a table memo field that is bound to a text box enabled for rich text. The rich text seems to be stored as html as so I can get word to convert it by enclosing it in html tags.
Dim What As Word.Shape
Set What = doc.Shapes.AddTextbox(msoTextOrientationHorizontal , doc.PageSetup.LeftMargin, 225, 534, 0)
Dim sPath As String
sPath = "G:Temp.html"
Open sPath For Output As 1
Print #1, "<HTML>" & pprs!What & " </HTML>"
Close #1
What.TextFrame.TextRange.InsertFile (sPath)
From a table I want a text field which has a path to a file to be copied automatically into a another field of the same table with a hyperlink text type...
View 5 Replies View RelatedI have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
I randomly read on another website that your field type in a table should be 'text' unless you're doing math on the number'. Is this true?
View 14 Replies View RelatedI have a Table Named "Combined List 05/06/03" Within this table there is a Field named "Scheduled Date" which has dates but the field was created as a Text Field. How do I go about changing this field to a date field. I have approx 95,000 records in this table. Is their a way to change this field to a date field or can I create another date field and move that info over. Im using MS Access 2010.
View 9 Replies View RelatedWe have a database (Access 2007) with several linked tables to an MS-SQL 2008 instance. All the text fields that I have issue with are nvarchar(255) on MS-SQL. The odd thing it will not allow a full 255 characters to be entered into the field. It will fail to save unless the number of characters is about 238 or 239 characters (not sure of the exact number of characters). It shows the field as a text and field size of 255 in Access .
View 2 Replies View Related