I have an old table that has many, many records. One of the columns lists the Date of Birth. Here's my problem:
Users have entered data into this column as "71462" This representing July 14, 1962. Is there a way to automatically format all data in this column so that it is more palatable - e.g. 07/14/1962?
Changing the column Data Type to "Short Date" in Design View would delete all existing data in that column, so this is not an option.
adding columns in Access2013,I'd like to add a column to an existing datasheet and place it between 2 existing columns,I see where and how to an a new column but don't see where I can insert it where I want it.
Hi, I am fairly new to doing DBA, and I am having trouble adding a new column to a table that is existing. Is there any simple way to do this or does it have to be done throught code? And if so how. Thanks zorter8
-Existing Access Database contains tables with 1-2 million records
I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].
In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.
Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].
I have a database that I import data from an excel spreadsheet into multiple times daily. The table that this data is imported into has several key fields that if the data already exisits in the table, and I attempt to import data that is the same except for one or more of the key fields is different. At this time the database it creates a different record. I am trying to get the database to overwrite the data in the database.
My db has multiple linked tables. As time passes and work on a project continues data needs to be added to a separate table that is linked to the first table to be populated.
i.e., Initial design data is entered first, when drilling is complete more data is added to a linked table, when drilling is surveyed yet more data is added to the second table.
What I want to be able to do is to query for a particular set of existing design data, display this on a form and on the same line enable data entry to other fields from other tables which are linked to the existing design data.
I have a database with existing data, that is not normalized, :eek: and all the data is in one field... This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY on the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1: C10A CHOLEST&TRIGLY ATORVASTATIN LIPITOR PFIZER SIMVASTATIN SIMVASTATIN TEVA TEVA M1A ANTIRHEUMATIC N-STEROID ETODOLAC ETOPAN TARO PHARMA Thanks so much for your help... :confused:
I need to categorize 35,000 cells of data in a column into 3 different groups (A,B, and C). Each cell will have something like "G00001" or "G00525" in it, but depending on when it was added, I want to change it to "G00001A" or "G00525C". I created a select query to show only the G0000's column and the column that indicates AB or C, but i don't know how to add the letters at the end of my G0000's. Do I need to use an update query or can I do something more simple? Also, if I need to use an update query, how would I build it to do what I need to get done?
I am not sure that I am in the Correct Section for this question but I am sure I can be forgiven for that :
I am attempting to build a db for a friend who has all his data in one table. During normalisation I have seperated the various threads of data into various tables of usable information. My problem is that I need to seperate his clients fullname into First & Surname i.e. Fullname: Mick Burke to FirstName: Mick and Surname: Burke - in other words make two fields from one.
Hi. I have a table with a NAME field and a GENDER field.
How would I code a query so that if the gender is MALE than MR. would be added to the front of the existing data in the NAME field? MRS. if gender is FEMALE?
i.e., the result would be MR. SMITH, MR. JONES, MRS. CARSON...
Please bear with me, first post, trying to get to grips with Access for work!
We (a primary school) had a "bespoke" database set up using MS Access by someone who has long left the area and now we need to make changes and I, as the most computer literate person on site, have been volunteered to make these changes.
My level is beginner (for now) and my first job is to add some new options to a "Titles" table, four columns, six records. Column1 = ID#, Column2 = Male Titles, Column3 = Female Titles, whilst Column4 seems to hold items from Columns 2 & 3.
If I add a futher option, I get the error "Can't perform cascading operation: Entry must be one of the Titles as featured in the drop-down box or in the Titles table.
Now, as far as I can tell, I HAVE added the new option to the Titles table, and they DO show in the drop down box in the main spreadsheet display, BUT the error comes back as soon as I try to confirm the new title.
I have a Form that enters all details for an Order.
On that form is a Tick Box that says Repeatable.
If that box is ticked in the future I am able to repeat on that stock item.
I have created a form with a List boxea that Lists all Suppliers Stock that is Repeatable
I want to be able to double click a Stock Item from that list, then let it populate another Order ( Unique Key ), fill in all the field from the previous order, so all I have to do is enter the Quantity I want to re-order.
When i try to import data from an excel file to an existing table in MS Access database i get a pop up saying "Access was unable to import data". When i click OK it says "finished importing data" .But when i go to the table there are no new records added. Can anyone please help me with this ? All the fields in the excel file are as they are in the table.
I have the following statement which exports data based on a query to a .xls:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryreportsbydate", "C:Documents and SettingspdaintyDesktopRawQualityData_Weekly.xls"
This works fine it exports the data and names the sheet rawqualitydata.
The problem i'm having is when i come to run the export again Excel tells me the file already exists. What I want it to do is overwrite the data in the rawqualitydata sheet in the same file.
I have a table that contains a notes field. What i would like to do is to append to this field in table with some more data (notes), however i dont want to over write the existing data. I would just like to add to it.
the current notes are added through the access front end, but in this instance i have a particular note that needs to be added to various enteries without overwriting existing data. I know this is to be done via an append query, any help would be great
When I built it I gave the MaterialsInventory# a field size of 3 characters, which I soon got to learn (stupid me for not thinking it through) has to be a lot bigger. I have now extended it to 5 characters which should be more than enough. Now, when my list comes up its not organized properly, 123 would com after 12299 for instance and so on.
my question is; how can I make a function/code/something-smart to extend the 123 type values to 00123? This table is linked as well so I guess I'd have to delete the links and do the same for every linked table? I got about 5000+ entries so don't feel like doing this manually:P
Hi i have 2 querrys with the same fields but different criteria (one is to search a person throw their id and the other one to search a person by their surname and dob). i can't work out how to display the results of the querries to an existing form i have. Can anyone help me please?
I've been tasked with creating a database for responses to a survey (that's already been created). There are several aspects most of which i've been able to handle (i.e. the Demographic portion)...The survey has 12 questions with pre-defined answers...What is the best way to set up the "question/answer" side of the survey.
I have to design 2 databases base on some data that was given by my instructor but I feel like I'm missing something on both specially the 2nd database. I have attached my work so far as well as the instructions and the existing data..
I have a form (frmCompany) which has two combo boxes (cmbCompany and cmbCity) which is used to add field data into a table (tblCompany). The Company field is the only required field the other is optional.
In some cases I may wish to add a City, at a later date, to an existing Company using frmCompany. I now select the company from the cmbCompany combo box then add the city to cmbCity. The problem is that when I close the form I get two companies in the table with same name, one has a city and one does not.
How can I add a new City to an existing Company, using the form, without adding a new Company?
I have a database where I need to append part of a new record to another table. But when I open the query with the link to the form that is open I cannot see the added record until it's saved to the open form. I tried to re-query the key ID field but that does not work. How do I do a re-query without the form requiring everything and then I am on the incorrect record?
First off, its been a long time since I have been here (since sometime in 2003 LOL), partly because I didn't have to create a new db in a while. Now, I have been handed a project and come across something new.
OK, here is what I want to do. I would like to be able to dump data from an Excel file into existing Access tables (two to be more specific). Now, I also have relating tables that add data into certain fields. I tried using a query to try to group data ad hopeful able to dump data through the query. I noticed that I couldn't import data through a query. Just for kicks, I tried to pull data through table and noticed that I can only create a new table, not able to update and existing table.
How would I go about performing this task? I have never attempted anything in the past.
Say I have a table [tbl1] and a recordset [rst1]. The fields in both items are the same. Now, how do I copy/dump/inject (what's the correct term?) the data from the recordset into the table?
i'm a Access novice. I have tables with existing data in numerical form, and would like to know how I can use Queries or VBA code to update these values into a new format in a new table. for example the original data might be of race type:
1 - caucasian 2 - african american 3 - hispanic 4 - other
how can i achieve this in Access? i know how i can do this conceptually with "if" and "case" statements, but I have no idea how i can do this in Access. I don't want to mess with the original data, so please help with CODE or QUERY examples.
