I know this is an Access 101 question, but what is the best way to view text rather than numbers in my tables. In an example which I have attached, an Orders table shows the Customer field as a number. I would like to view the actual customer name when I am looking at the table.
View RepliesI need to convert a numbers column in an access table to a text format
View 3 Replies View RelatedI have various columns of data, a mixture of text and numbers, which all have data in them, and a few columns that have numbers or text. When I copy'n'paste-append into the Access table, the text in the sparsely populated columns doesn't copy across but the numbers do.
It works if I import the spreadsheet from scratch (don't want to do that however, that data comes from somewhere else and would require modification to suit), or if I sort the data so that the text is at the top of the column, it works. Don't really want to do that either as there are 5 columns that this applies to and performing a 5 level sort shouldn't be necessary IMO. It seems that the paste is analysing the data and deeming that it should paste as a number even though the column contains text. There are only 120 rows of data, not a lot! I've tried redefining the field as Long Text (currently Short Text) but it doesn't make any difference.
I know you can't store text in a numeric field but I always thought you could store numbers in a text field - provided you didn't need to do any calculations on them. My problem is as follows:
I receive an Excel 2003 spreadsheet once a month, which I save to a specific filename/location overwriting the previous file. My Access 2003 database uses this as a linked table and (among other things) runs an append query to add the new data onto an existing table.
We have now added a new column called Reference in the spreadsheet. Often, this will be empty, but it could contain numbers or text. This is the first month I have received it and most entries are blank (including the first row) but further down there are some numeric values.
So I added a new Reference field to my main table and set it to text. Then I amended the append query to include the new field. But when I run it I get the error "Numeric field overflow". If I take that column back out of the query, it runs fine, so that's definitely the offending data. And when I open the linked table in Excel and scroll down to where I should see the reference numbers, I see #Num! So it looks to me like it doesn't recognise numbers as text.
Things I've already tried
In Excel, I formatted all the Reference cells as text.
That didn't work, so next I added a dummy record at the top of the Excel file (just under the headings), with zeroes in the numeric columns and 'X's in the text columns including Reference.
But that doesn't work either. Given the above circumstances, what's the best way to proceed with this?
I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.
It looks a little like this (and you can see my not matching ID issue):
Homeowner table
ID First Name Last Name...........Total Dogs
1 Max Maximus 5
2 Min Minimus 0
3 Mus Musculus 1
Dogs 1-5 table
ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name
1 (Max's) 4/11/14 Bobby Billy Betty
2 (Mus's) 4/11/14 Jojo
Min will have no dog records at all, just home information.how to link the dog's to the homeowners .
I was wondering if there is some setting that can be set such that certain records within a table are not visible when the table is opened, or a form is accessing its records.
It seems from time to time, some records seem to 'disappear' from view?!?
Other than a filter...
Does anyone know how this is possible, and what can be set or specified in order for this to be happening?
Thank you for any enlightenment...
Greetings to All,
I have one table (tblLink) that resides on an SQL Server quite a distance from me. I have another table (tblLink_r) that resides in a local .mdb file. The tables are identical except for the data they contain and where they are located. Only one index (LinkIdx=autonumber) exists. I am making sure there are no duplicate indicies in the LinkIdx fields because I started the LinkIdx field in tblLink_r at 2100000000.
When I open tblLink (remote), I can scroll to the end of the 21,000 records relatively quickly - read: little latency. Queries performed on this table similarly show little latency.
When I open tblLink_r, I also have little latency. This table has 100 records.
I need a single object which contains the contents of tblLink and tblLink_r. I have tried a UNION ALL, but this query takes upwards of 4 minutes to complete which I can't understand since either of the constituent tables only take ~.5 seconds to call up.
I am hoping someone can provide a way to speed up the UNION ALL query or provide an alternate means to combine the contents of these tables.
Hello,
I am an Oracle DBA with little to no Access experience so please be kind. :D
As of yesterday a frequent user of Access 2003 (11.8166.8172) SP3 reported that all columns in all rows have the value #Deleted in a number of tables linked back to an Oracle database.
I am not experiencing this problem but I have Access 2007. It is not an option for this user to upgrade.
I've discovered several issues over the web with data type issues/ characterset issues but nothing seems to fix the problem. I've tried the Oracle supplied 11g and 10g ODBC drivers. I've tried the Microsoft supplied ODBC for Oracle drivers.
None of it seems to work and I am out of ideas. Can anyone suggest anything?
The problem wasn't noticed until yesterday so it can't have been happening for long. The problem is occuring in Oracle 10.2.0.3 and 10.2.0.1 databases. There has been no change in the Oracle environment.
There was a windows update recently but we tried uninstalling that and it didn't help.
any ideas?
I have made a table and I enter data in that table through a form. The form also include two sybchronized comboboxes. When I select a category in one combo box, corresponding items are selected in second combo box. The problem is that when I select the category in the form (with ID+Category Name), it stores only the ID in the table. How can I see the Category Name in the table (which is the control source of the form) instead of ID?
View 4 Replies View RelatedMy db is split into front-end and back-end. In the f-e's Relationships schematic, I can see the relationships as they were defined at the time when the db was split, complete with the one-to-many symbology.
I can add a new table to the b-e and set its relationship as one-to-many, enforcing referential integrity and cascading as I wish - and the schematic (in the b-e) reflects that.
In the f-e, I can then use the <Get External Data - Access Database> function to link to the new table, and I can add the new table to the relationships diagram in the f-e. I can also drag and drop to link primary and foreign keys (within the f-e), but cannot select one-to-many. I'm OK with that, as I understand that the relationship is within the b-e, and this is just a diagrammatic representation.
But I can see the one-to-many relationships between the tables which existed when the db was split, and I would like to be able to see the new table's relationships in a consistent fashion. Updating the linked tables via the Linked Table Manager does not do the trick.
Surely I don't have to split the database again in order to achieve a consistent diagram - do I ?
show the total/sum of the input fields (unbound form).I tried sum function in control source for "Total working day", but I am getting #Error =sum ([working daysperiod1][workingdaysperiod2]...)
I used plus feature; like we do in Excel =[workingdaysperiod1]+[workingdaysperiod2]...... the answer I am getting 22192120.....
Period; Month; Working days
1 - Jan - 22
2 - Feb - 19
3 - Mar - 21
4 - Apr - 20
I have two tables in the database I'm working on from Excel.
Table1 has demographic information for faculty, to which I added an AutoNumber Primary Key.
Table2 has the courses those faculty members taught, but it does not have the FacultyID I added to Table1; it has their last name, first name.
Is there a way I can have Access look up the employee's FacultyID from Table1 based upon their last name and first name, which are contained in both tables?
Thanks in advance.
I am pritty new to access and have been tasked with creating a database !
I have two tables one called main which has 26,000 claims (listed under there part number).
The other table called part numbers is a list of all the part numbers in the main table but with no duplicates (126 part numbers)
I need to somehow count how many times a part number apears in the main table. Maybe display a count in the part number table as a extra field ? then i could put this field into the form i created to browse the parts numbers (to show how many times the part number appears in the main table)
How I can get the zero in my telephone dialling code to show up in my tables?
I have set the number display to the Long Integer.
My autonumbers are not in order for tables.As in for example for a customer
customerid customer first name customer last name
17 john parker
18 james johnson
19 alex scott
This is right at the top of the table the first sets of data,i want it to be
customerid
1
2
3
4
5
,etc
Why does it have a random number like "17" when I DO NOT have the first 17 customers !
I got a table with "ID" as autonumber field. However when I have ID numbers:
1
2
3
4
5
6
7
And I remove record 4, then I got
1
2
3
5
6
7
How can I make it fill up the missing "4"?
I would like to expand on this questions. I would like to generate a number based on a reference I use, a format like "14/000" where I use "14" with the last 2 digits of the year. However i want the auto number to generate the the last 2 digit automatically when the year changes i.e. when 2015 comes the field generates "15/000" without any prompt from the user.
View 1 Replies View RelatedAccess 2010.
Have a table with columns of data which I want to total at the end of each row. The Calculated Field does this simply enough but it only seems to be able to add whole integers? How do I get it to accept and add values to 2 decimal places?
I am creating a training database and i have a courses table. And I need to somehow record all of the different employees that attended a course?
What i can do to make this happen? My employees have an employee number so is there a way of putting all of the employees together in one field? what field would the attendees be? currently is numeric which doesn't seem to work.
I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.
View 14 Replies View RelatedHow can i generate automatic reference numbers that can serve as primary key.
I want the prefix to include year. Eg Tr/yyy/incremental number...
Okay, this one should be simple and can be solved in one of two ways. I have two seperate tables with a 12 digit identifying number that is ALMOST exactly the same in each. I am trying to match up these two tables, which works amazingly well, except for the occasional case where the 12 digit ID (which is called API) ends in 01, instead of 00. The last two digits do not make a difference at all, but in one table it will end in 00 and the other it will end in 01, even though they are the same record.
Basically, it looks like this...
Table 1---------------Table 2
541236554700-----541236554700
541236123700-----541236123700
443231246700-----443231246701
So basically, in my final query, the last entry will not show up since these two are not related by this API (ID) number. Any way to either replace the 01 on the end with 00... OR to remove the last two digits?
Is there a way to show negative numbers in brackets without selecting the Currency format?
I want to show (75,000.00) and not -75,000.00
I have a table, at the table I'v got these fields:
ID | num1 | num2 | sum
I want that the user put numbers at 'num1' and 'num2' fields and then the 'sum' field will calculate automatically the operator (sum=num1+num2).
I've tried to put any combination at 'Default value' of the sum field (all the fields are numbers , also tried to change the sum field to text...nothing works).
I'm getting a message that 'num1' field is not recognize at the table
(I tried =[num1]+[num2], without the '=', num1.table+num2.table , ...nothing works)
I also tried to do it with SQL command but it dosn't work.
There is any way to do it, is it possible? Or other way to do it at least at Form or at Report ?
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies View RelatedHow can I get a message box apper when you position the mouse on a text box or a field to display further information?
View 3 Replies View Related